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.
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.
@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.
@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.
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.
@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 🙂
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.
@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! 🙂
