- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2022 12:36 AM
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!
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2022 12:29 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 21, 2022 12:22 AM
@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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2022 11:30 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2022 12:29 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 21, 2022 12:22 AM
@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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 21, 2022 12:29 AM
One more thing. The automation script can be triggered by a check box field.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 21, 2022 01:10 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 21, 2022 01:11 AM
Amazing! I'll take a look at it 🙂

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 21, 2022 01:52 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 12, 2023 10:49 PM - edited ‎Jan 13, 2023 04:34 AM
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! 🙂
