Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Create a record for each date in a range of dates

Topic Labels: Scripting extentions
506 4
cancel
Showing results for 
Search instead for 
Did you mean: 

I have 2 tables. One called Booked jobs and one called Planning

I have a form from which a user can add one record at a time to the Booked jobs table.
Each booked job could be a service from a list of 3 services: Origin, Destination or Both.

For the Origin service there is a Start Date and an End Date.
For the Destination service there is a Start Date and an End Date.

For the Both service there is a Start Date Origin, End Date Origin, Start Date Destination and End Date Destination.

For each date in one of the ranges mentioned above a new record would need to be created in the Planning table with the respective date and some other details from the Booked job.

What I have until now is the following:

let table = base.getTable('Booked jobs');
let query = await table.selectRecordsAsync();
let destination = base.getTable('Planning');
let prev = input.config();
 
let typeService = prev.typeService;
let startOS = prev.StartOS;
let endOS = prev.EndOS;
let startDS = prev.StartDS;
let endDS = prev.EndDS;
 
const _MS_PER_DAY = 1000 * 60 * 60 * 24;
 
// a and b are javascript Date objects
function dateDiffInDays(a, b) {
// Discard the time and time-zone information.
     const utc1 = Date.UTC(a.getFullYear(), a.getMonth(), a.getDate());
     const utc2 = Date.UTC(b.getFullYear(), b.getMonth(), b.getDate());
     return Math.floor((utc2 - utc1) / _MS_PER_DAY);
 }
 
 switch (typeService) {
   case 'Origin':
     let os = [];
     let diff = dateDiffInDays(startOS,endOS);
     for (let i = 0; i < diff; i++) {
         let startDate = new Date(startOS);
         let newDate = new Date(startDate.setDate(startDate.getDate() +i));
         os.push(newDate.toISOString().substring(0,10));
     }
     console.log('Origin Case');
     if (os) {
         for (let day of os) {
             let recordId = await destination.createRecordAsync(
                 {
                     '2.Jobs': [{id: table.id}],
                     'Start Address': `address 1`,
                 },
             )
             console.log("Sucess!");
         }
    }
     break;
   case 'Destination':
     console.log('Destination');
     break;
  case 'Both':
     console.log('Both');
     break;
   default:
     console.log(`Sorry, we are out of ${typeService}.`);
}

I keep getting stuck somewhere.
How can I achieve this?

4 Replies 4

Hey

Did you ever soled this? I am also interested thanks

Hey Jack,

I’ve solved it through Make.com (ex Integromat).

Gorgan Mobility

Gorgan Mobility
Manager
address: 7, De Vunt, BE-3220
phone: +32 16 79 29 84
email: eduard@gorganmobility.co
website: gorganmobility.co

Here is also code solution based on a later post by @Jack_Tranckle

Hi @Eduard_Ichim I’m very interested to know how you managed to do this with Make :slightly_smiling_face:
How did you do to list all the days between your start and end dates ?