Help

Resource planner - Split job into workweeks

Topic Labels: Scripting
Solved
Jump to Solution
2080 8
cancel
Showing results for 
Search instead for 
Did you mean: 
NFGK
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

2 Solutions

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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.

See Solution in Thread

Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

@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
John_B2
6 - Interface Innovator
6 - Interface Innovator

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.

kuovonne
18 - Pluto
18 - Pluto

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.

Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

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

NFGK
5 - Automation Enthusiast
5 - Automation Enthusiast

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