How can we make a database collection, change its data itself??

rinshulgoel · October 31, 2020, 4:17pm

Hey guys, I have a new question.
A bit complex to explain for me, so I request you to read carefully.

Colours-
Black - Headings
Red - Something Important
Orange - Field or cell in my dataset.
Blue - Calculations

Database Description - Suppose I have 3 fields in my database: Customer Name, Executive & Follow up Date. The data represents that which executive has to make a call to which customer on which date .

Aim - Think, my Executive 1 is on holidays for 5 days, then it might become a loss for my company that I loose 1 customer named ‘A’ as he was not called on the specified date. Therefore , my aim is that if any customer is not called for more than 2 days then that customer is passed to another executive .

Procedure (I think) -

I have to make a job scheduler that runs every day on the particular dataset.
In the job scheduler, I have to compare today’s date with the date present in the follow-up date column in the dataset that should run for each row (I have 10,000 rows in my dataset).
And if it is found that the difference between the dates (Today’s Date - Date in the field) is more than two days then the Executive of that row gets changed to what I Specify.

For Ex- If Executive 1 has missed 10 calls for that day then 5 calls should be assigned to Executive 2 and other 5 calls should be assigned to Executive 3 .

So that’s it, I hope I will be helped soon.
Thanks in Advance!!

I don’t know whether the procedure I thought would work or there is any other way too. I have just listened about Job Scheduler, that It is made when we make a task in repetition in the backend.

J.D · October 31, 2020, 4:43pm

You described what you want to do and it makes sense. but what’s the question? Which part is not clear? Create the scheduled job, create the back-end function that’s makes it. And that’s all.

rinshulgoel · October 31, 2020, 5:27pm

I don’t know How to do it.
@jonatandor35 In the last 1 hours I have checked all the articles about how to schedule jobs and I have understood it, the thing I don’t know what to do with the dataset code.
I don’t know what to write in the .jsw file.

J.D · October 31, 2020, 5:41pm

See here how to create a scheduled job:

Velo: Scheduling Recurring Jobs | Help Center | Wix.com.

Create an additional database of replacing executives with a priority rank field where you set the replacing executives and their order.

Create a backend function that checks for missed appointments and assign them to replacing executives.

rinshulgoel · October 31, 2020, 6:23pm

@jonatandor35 Please guide me further. I hope the written one is correct.

import wixData from 'wix-data';

export function change_executives () {
 return wixData.query("Try")
    .find()
    .then( results => {
        const today = new Date();
        today.setDate(today.getDate() - 2)
        let missed_appointments = results.items.filter(item => {
        if (item.followUpDate < today) {
             // Chnage the Executive (No Idea)
            }
        })
    })
}

J.D · October 31, 2020, 9:25pm

@rinshulgoel You can do it in the query itself:


//   backend/reassign-appointments.js
import wixData from 'wix-data';
export function change_executives () {
let dateToCheck = new Date();
dateToCheck.setDate(dateToCheck.getDate() - 2)
return Promise.all([
wixData.query("Try")
.lt("_upadtedDate", dateToCheck)
.limit(1000)
.find(),
wixData.query("ReplacingExecutives")
.decending("rank")
.limit(1000)
.find()
])
.then( res => {
if(res[0].items.length === 0 || res[1].items.length === 0){return Promise.reject();}
let pendingAppoinments = res[0].items;
let replacingExecutives = res[1].items;
let toUpdate = pendingAppoinments.map((e,i) => {
e.executive = replacingExecutives[Math.ceil(i/5) % replacingExecutives.length].executive;
return e;
})
return wixData.bulkUpdate("Try", toUpdate)
    }).catch(err => err)
}

[FIXED]

rinshulgoel · November 2, 2020, 5:11am

@jonatandor35
This my code - and it has done nothing to my database.

import wixData from 'wix-data';
export function change_executives () {
 let dateToCheck = new Date();
    dateToCheck.setDate(dateToCheck.getDate() - 2)
 return Promise.all([
    wixData.query("Try")
    .lt("nextServiceDate", dateToCheck)
    .limit(1000)
    .find(),
    wixData.query("RepalcingExecutives")
    .decending("rank")
    .limit(1000)
    .find()
    ])
    .then( res => {
 if(res[0].items.length === 0 || res[1].items.length === 0){return Promise.reject();}
 let pendingAppoinments = res[0].items;
 let replacingExecutives = res[1].items;
 let toUpdate = pendingAppoinments.map((e,i) => {
    e.salesExecutive = replacingExecutives[Math.ceil(i/5) % replacingExecutives.length].executive;
 return e;
    })
 return wixData.bulkUpdate("Try", toUpdate)
        }).catch(err => err)
}

Pics-
Try Dataset-

Reassign Executive-

Do I have to make the Job scheduler of this code as it is .js file so I thought it must not be.

J.D · November 2, 2020, 3:13pm

@rinshulgoel of course you have to create a new job in the jobs.config file

rinshulgoel · November 3, 2020, 6:44am

@jonatandor35 This is my jobs.config code but nothing happened this time too I have schedule it to 6: 37 UTC to test it

{
 "jobs" : [
    {
       "functionLocation": "/reassign-appointments.js",
       "functionName": "change_executives",
       "description": "Reassigns Members",
       "executionConfig": {
              "time": "06:37"
      }
    }
  ]
}

J.D · November 3, 2020, 10:33am

@rinshulgoel the rank field type should be “Number”.
You’ll have to debug the code and figure out what’s not working.
The goal of my code above was only to show a possible direction, but I can’t guarantee there’s no error/problem there, You’ll have to investigate (use the site monitoring tools) and adjust in accordance.

J.D · November 3, 2020, 10:38am

And any way I can see “RepalcingExecutives” is misspelled once.

for the screenshot you used “Reassign Executive”.

rinshulgoel · November 5, 2020, 7:58am

@jonatandor35 Yes you were right there were two problems in the code and now it is resolved

import wixData from 'wix-data';
export function change_executives () {
    console.log("It is used!!")
    
    let dateToCheck = new Date();
    console.log("Date 1" + dateToCheck)
    dateToCheck.setDate(dateToCheck.getDate() - 2)
    console.log("Date 2" + dateToCheck)
    
    return Promise.all([
    wixData.query("Try")
    .lt("nextServiceDate", dateToCheck)
    .limit(1000)
    .find(),
    
    wixData.query("ReplacingExecutives") // Earlier Spelling Was Wrong
    .descending("rank") // Earlier Spelling was wrong
    
    .limit(1000)
    .find()
    ])
    .then( res => {
         console.log("Returned")
         if(res[0].items.length === 0 || res[1].items.length === 0) {
             return Promise.reject();
         }

        let pendingAppoinments = res[0].items;
        console.log("Pending App. : " + pendingAppoinments)
        let replacingExecutives = res[1].items;
        console.log("Replacing Exe. " + replacingExecutives)

       let toUpdate = pendingAppoinments.map((e,i) => {
            e.salesExecutive = replacingExecutives [Math.ceil(i/5) % 
 replacingExecutives.length].executive;
            console.log("e" + e)
             return e;
        })
        return wixData.bulkUpdate("Try", toUpdate)
    })
    .catch(err => {
        console.log("Err " + err)
        err
    })
}

So now the code is working -

But At last, I have two questions -

Will this code works only on the first 1,000 items of “Try” dataset as I Have 30,000 items in my dataset. (I have tried WixDataFilter earlier but never tried WixDataQuery before)
Will you please make me understand the logic of how it is changing the executives as -

Before -

After -

You see the “Before” and “after” after screenshots of datasets. In the “After” pic you see that the 8th item is unchanged but that should also be changed.

Looking Forward for a response.
I am highly obliged to you thank you.

J.D · November 5, 2020, 2:00pm

@rinshulgoel as for the first question. Yes it’ll only work for first 1000 matches.
If you want to make it on over then 1000 matches you should make it in several iterations. (see: here )
Also the bulkUpdate is maybe even more limited (I’m not sure what its limit).
As for your second question, I’ll try to find time to answer it later today.

J.D · November 5, 2020, 6:31pm

For you second question. I assumed that the executives in the ReplacingExecutives collection are those who can be available for sure, and those in the Try collection may now be available.

The logics (maybe it doesn’t suit to your needs) is like that:

Find the events that need to change
Find the Executives available for event assignment
The assignment order is by rank - the executive with the highest rank will get the assignment first.
Assign 5 events to the first executive and then move to the second highest rank, assign 5 and move to the next one. etc…
When you run out of available executives, go back to the first one and assign another 5 and then to the next etc…

rinshulgoel · November 6, 2020, 5:47am

@jonatandor35 In this can’t we do that if the executive matches then skip that.

Like -

if (present_executive === replacing executive) {
    move to next replacing_executive
    and then replace the executive
}else {
    we have written code for this condition
}

J.D · November 6, 2020, 11:43am

@rinshulgoel you can do it, but you should plan the exact logics. “Move to the next executive” and then what? go back to the previous one? keep proceeding? When you know exactly what you’d like to happen, you’ll be able to write the corresponded code.

rinshulgoel · November 6, 2020, 5:31pm

@jonatandor35 okay thanks!!

Topic		Replies	Views
How to update a dataset at a set time. Ask the community code , question	3	196	March 10, 2021
Job to Copy Data between Two Datasets Ask the community code , question	1	79	August 7, 2020
Resolved: loop thru dataset and update a field. Help cleaning up code would be appreciated though Ask the community code , wix-bookings , question	2	214	June 15, 2022
Scheduled job to change fields based on date. Ask the community code , question	0	161	April 18, 2020
How to get this week's data from a database? Ask the community code , question	2	154	April 16, 2020

How can we make a database collection, change its data itself??

Related topics