Skip to main content
Answer

Resource planner - Split job into workweeks

  • December 19, 2022
  • 8 replies
  • 70 views
N
Forum|alt.badge.img+3

Hey,

Im currently working on a resource planning tool and im struggeling to make it work like intended.

The way it works is, im creating some jobs with a resource linked and a start and end date.

What i want is a script to check every new record and if the dateinterval is overlapping several workweeks(Monday-friday) it's splitting the record into several other records with same resource and name but date is a maximum of 5 days inside the same workweek.

E.g. record "job1" with start date 15 december 2022 and end date 22 december 2022 with resource George is split into:

Record "job2" start= 15 december 2022, end = 16 december

Record "job3" start=19 december 2022, end = 22 december

"job1" is supposed to be deleted then.

If someone could send me in the right direction, i would be grateful!

Best answer by kuovonne

Are you hoping to 

- write the script yourself?
- hire someone to write the script?
- find a free script that does what you want?
- have someone volunteer to write the script for you for free?

If you are interested in writing the script yourself, what is your coding background? If you do not have a coding background, this script may be too difficult for a first project for someone completely new to coding. If you have experience coding, feel free to post more details on the specifics that are giving you problems.

8 replies

J
Forum|alt.badge.img+9
  • John_B2
  • Inspiring
  • December 19, 2022

How about a script that loops from start date to end date, incrementing by 1 day. Inside the loop check the day and if it's a Monday then create a new record.

N
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • December 19, 2022

Are you hoping to 

- write the script yourself?
- hire someone to write the script?
- find a free script that does what you want?
- have someone volunteer to write the script for you for free?

If you are interested in writing the script yourself, what is your coding background? If you do not have a coding background, this script may be too difficult for a first project for someone completely new to coding. If you have experience coding, feel free to post more details on the specifics that are giving you problems.

N
Andrey_Kovalev
Forum|alt.badge.img+20

@NFGK I wrote a script that might solve your task of splitting jobs through work days.

//collect input let Rec = input.config() //load record let table = base.getTable("Jobs") let record = await table.selectRecordAsync(Rec.RecID) //Declare variables let start = new Date(Rec.start) let end = new Date(Rec.end) let job = Rec.job let range = [] //keeps dates let rangeDays = [] //keeps week days let foundRange = 0 //length of current subrange //Check if the range is correct if(end >= start) { //The loop to build an array of dates excluding Saturdays and Sundays for(let d = start; d <= end; d.setDate(d.getDate() + 1)) { if(d.getDay() == 6 || d.getDay() == 7) { d.setDate(d.getDate() + 1) continue } range.push(d.toISOString().substr(0,10)) rangeDays.push(d.getDay()) } //Loop through the array, create new records, and splice while(range.length !== 0) { if(rangeDays.indexOf(5) !== -1) { //range has Friday foundRange = rangeDays.indexOf(5)+1 console.warn(foundRange) await table.createRecordAsync({ "Job": job, "Start": range[0], "End": range[range.slice(0, foundRange).length - 1], }) } else { // range doesn't have Friday foundRange = rangeDays.length await table.createRecordAsync({ "Job": job, "Start": range[0], "End": range[range.length-1], }) } //splice ranges range.splice(0, range.slice(0, foundRange).length) rangeDays.splice(0, rangeDays.slice(0, foundRange).length) } //delete initial record await table.deleteRecordAsync(Rec.RecID) }

In my table I'm using 3 fields:

  • Job - the name of the job,
  • Start - starting date
  • End - ending date

Script uses 4 variables: 3 reflecting the fields, and RecordID()

Hope this will be useful.

N
Andrey_Kovalev
Forum|alt.badge.img+20

@NFGK I wrote a script that might solve your task of splitting jobs through work days.

//collect input let Rec = input.config() //load record let table = base.getTable("Jobs") let record = await table.selectRecordAsync(Rec.RecID) //Declare variables let start = new Date(Rec.start) let end = new Date(Rec.end) let job = Rec.job let range = [] //keeps dates let rangeDays = [] //keeps week days let foundRange = 0 //length of current subrange //Check if the range is correct if(end >= start) { //The loop to build an array of dates excluding Saturdays and Sundays for(let d = start; d <= end; d.setDate(d.getDate() + 1)) { if(d.getDay() == 6 || d.getDay() == 7) { d.setDate(d.getDate() + 1) continue } range.push(d.toISOString().substr(0,10)) rangeDays.push(d.getDay()) } //Loop through the array, create new records, and splice while(range.length !== 0) { if(rangeDays.indexOf(5) !== -1) { //range has Friday foundRange = rangeDays.indexOf(5)+1 console.warn(foundRange) await table.createRecordAsync({ "Job": job, "Start": range[0], "End": range[range.slice(0, foundRange).length - 1], }) } else { // range doesn't have Friday foundRange = rangeDays.length await table.createRecordAsync({ "Job": job, "Start": range[0], "End": range[range.length-1], }) } //splice ranges range.splice(0, range.slice(0, foundRange).length) rangeDays.splice(0, rangeDays.slice(0, foundRange).length) } //delete initial record await table.deleteRecordAsync(Rec.RecID) }

In my table I'm using 3 fields:

  • Job - the name of the job,
  • Start - starting date
  • End - ending date

Script uses 4 variables: 3 reflecting the fields, and RecordID()

Hope this will be useful.


One more thing. The automation script can be triggered by a check box field.

N
Forum|alt.badge.img+3
  • NFGK
  • Author
  • New Participant
  • December 21, 2022

Are you hoping to 

- write the script yourself?
- hire someone to write the script?
- find a free script that does what you want?
- have someone volunteer to write the script for you for free?

If you are interested in writing the script yourself, what is your coding background? If you do not have a coding background, this script may be too difficult for a first project for someone completely new to coding. If you have experience coding, feel free to post more details on the specifics that are giving you problems.


Get some inspiration to write the script myself was the main goal and see if anybody played with something similar.

I know basic coding, and can read most of it.

 

Andrey_Kovalev
N
Forum|alt.badge.img+3
  • NFGK
  • Author
  • New Participant
  • December 21, 2022

@NFGK I wrote a script that might solve your task of splitting jobs through work days.

//collect input let Rec = input.config() //load record let table = base.getTable("Jobs") let record = await table.selectRecordAsync(Rec.RecID) //Declare variables let start = new Date(Rec.start) let end = new Date(Rec.end) let job = Rec.job let range = [] //keeps dates let rangeDays = [] //keeps week days let foundRange = 0 //length of current subrange //Check if the range is correct if(end >= start) { //The loop to build an array of dates excluding Saturdays and Sundays for(let d = start; d <= end; d.setDate(d.getDate() + 1)) { if(d.getDay() == 6 || d.getDay() == 7) { d.setDate(d.getDate() + 1) continue } range.push(d.toISOString().substr(0,10)) rangeDays.push(d.getDay()) } //Loop through the array, create new records, and splice while(range.length !== 0) { if(rangeDays.indexOf(5) !== -1) { //range has Friday foundRange = rangeDays.indexOf(5)+1 console.warn(foundRange) await table.createRecordAsync({ "Job": job, "Start": range[0], "End": range[range.slice(0, foundRange).length - 1], }) } else { // range doesn't have Friday foundRange = rangeDays.length await table.createRecordAsync({ "Job": job, "Start": range[0], "End": range[range.length-1], }) } //splice ranges range.splice(0, range.slice(0, foundRange).length) rangeDays.splice(0, rangeDays.slice(0, foundRange).length) } //delete initial record await table.deleteRecordAsync(Rec.RecID) }

In my table I'm using 3 fields:

  • Job - the name of the job,
  • Start - starting date
  • End - ending date

Script uses 4 variables: 3 reflecting the fields, and RecordID()

Hope this will be useful.


Amazing! I'll take a look at it 🙂

 

Andrey_Kovalev
Andrey_Kovalev
Forum|alt.badge.img+20

Amazing! I'll take a look at it 🙂

 


I forgot to mention that I used ISO format for dates: YYYY-MM-DD, just in case. That is why dates converted to strings did not require any backward conversion. If you are using a different format you may need to adjust the script accordingly.

N
Forum|alt.badge.img+3
  • NFGK
  • Author
  • New Participant
  • January 13, 2023

@NFGK I wrote a script that might solve your task of splitting jobs through work days.

//collect input let Rec = input.config() //load record let table = base.getTable("Jobs") let record = await table.selectRecordAsync(Rec.RecID) //Declare variables let start = new Date(Rec.start) let end = new Date(Rec.end) let job = Rec.job let range = [] //keeps dates let rangeDays = [] //keeps week days let foundRange = 0 //length of current subrange //Check if the range is correct if(end >= start) { //The loop to build an array of dates excluding Saturdays and Sundays for(let d = start; d <= end; d.setDate(d.getDate() + 1)) { if(d.getDay() == 6 || d.getDay() == 7) { d.setDate(d.getDate() + 1) continue } range.push(d.toISOString().substr(0,10)) rangeDays.push(d.getDay()) } //Loop through the array, create new records, and splice while(range.length !== 0) { if(rangeDays.indexOf(5) !== -1) { //range has Friday foundRange = rangeDays.indexOf(5)+1 console.warn(foundRange) await table.createRecordAsync({ "Job": job, "Start": range[0], "End": range[range.slice(0, foundRange).length - 1], }) } else { // range doesn't have Friday foundRange = rangeDays.length await table.createRecordAsync({ "Job": job, "Start": range[0], "End": range[range.length-1], }) } //splice ranges range.splice(0, range.slice(0, foundRange).length) rangeDays.splice(0, rangeDays.slice(0, foundRange).length) } //delete initial record await table.deleteRecordAsync(Rec.RecID) }

In my table I'm using 3 fields:

  • Job - the name of the job,
  • Start - starting date
  • End - ending date

Script uses 4 variables: 3 reflecting the fields, and RecordID()

Hope this will be useful.


Sorry for the delay! just had time to test and it works as intended. I do have a little bonus question tho.

What if some of the jobs containts link to another record?

In my case i have a table with jobs, projects, resources(Manpower), and each job i split need the same project and resource.

I tried integrate them like the start, end and jobname, but the linked fields is "read only" when scripting.

Do you know any fix?

I guess i need to get the linked record itself, instead of just getting the String with the value from the linked record.

 

Edit: Allright, i fixed it myself! Ty for your assistance! 🙂

 

 

 

 

Terms & ConditionsAccessibility statement