I have drivers doing jobs (logistics : pickup → drop off). The drivers are paid a fixed daily rate. We expect 4 jobs for that, any job after the 4th job earns an additional commission.
How should I approach this. How do I create a counter for each person, and then I need to create a counter per day for each person.
Thanks
How are you storing each person’s job? Do you have a dedicated table for that?
You’d want a table of jobs that consist of a pickup (time/location?) and dropoff. When there’s a drop-off, then a job is “complete”. The only tricky part is developing a calendar that group all jobs per day for each person.
One way to do it is with a calendar helper table.
All the jobs are in one table called JOB_TABLE, in each row I have all the info needed (created date, job date, pickup date, completion date, job type, driver_assigned)
I made some progress.
I used a maths formula to create a unique day (YYYYMMDD) and then I use template to add on the login-email of the driver.
I am then using a relation (multi-match) and then doing a rolloup count on that to give me the number of jobs each driver has done on each day.
BUT 
as the commission paid on the jobs after the first 4 varies depending on the type of job, I still have an issue. I need to order the jobs based on a completion data and then on the fifth job look at the type of job and give the appropriate bonus, that is where I am stuck. I somehow need to have a daily counter per driver that I use to label the job using an action at time the job completes.
Might need to index your jobs table per person/day. If index > 3 (starts with 0), then look at job type and provide compensation (through another relation/lookup).
Here’s how to index:
Superb technique and solution Bob. Thanks for posting!
Hi Robert,
That is an excellent suggestion and an awesome algo, I can use this for another project.
However, it may not work for me, I will see if I can make it work. This is assuming that the jobs are in order. My issue is that sometimes the jobs are not done in the order they are inserted into the table, so I would need to order it by job_start_datestamp
Then you just need to query it first, sort by the job start time, return the rowIDs, and then get the index.
Sorry going to request a little more hand holding/directing here pls…
query = you talking about an actual query column ?
yes
I have the query, it has the multiple rowIDs sorted by the time, next step is to find the index/position.
When I try to use ARRAY - Find Index, I am not able to select any columns, I assume it is looking for an ARRAY column.
When I try to use MAKE ARRAY, I cannot select the query column, its not in the drop down
How do I convert it to an array or get the index of the rowid’s
I got. Not sure if it is the right thing to do, but I created a LOOKUP colulmn pointed it to the query and that gave me an ARRAY, FIND ELEMENT INDEX on the LOOKUP and I have my index.
Is that correct ?
That’s correct.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.