Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Resource planner - Split job into workweeks

Topic Labels: Scripting
Solved
Jump to Solution
287 8
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

1 Solution

Accepted Solutions

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

See Solution in Thread

8 Replies 8

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.

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

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.

 

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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