Automate recurring date from linked table

[Paul_Van_Dooren]

First things first, for all you airtable users, all the best for 2023!

Context:
I've got a newspaper that appaers 6 days of the week: monday, tuesday, wednesday, thursday, friday and saturday.
Every day i have in this newspaper 2 free spaces that needs to be filled up with an advertisement.
I would like to use airtable to make a kind of reservation tool so i can follow up when the correct advertisement must appear in the newspaper.

Through a form the user must be able to input:

• the name of the advertisement
• a start date of the apparitions (date selection from a linked table to exclude double bookings)
• a frequency of apparitions (1 = one day / 2 = two days / 3 = three days / ...)

Problem
I really don't have a clue how i can automate that airtable assigns the dates to the record when the frequency is more then 1.
And then for the icing on the cake... the frequency should be every other day => monday - wednesday - friday - (sunday) - tuesday - thursday - saturday

Example

Very much a scripting newbie so any help or pointers very much appreciated!

Allready a big thanks and any help is welcome

[Rupert_Hoffsch1]

Hi @Paul_Van_Dooren,

happy new year!

I would approach this with a form with your three mentioned fields: Name, start date (selection from linked table limited to a view which doesn't show dates with 2 bookings), frequency (number field?).

If I understand correctly, that one booking can now populate any number of available dates (given via frequency). For this I would write a script, also to incorporate alternating the days.

The script would actually be quite straightforward and would add a link to the booking record for the number given as frequency.

Are you interested in writing the script yourself? Happy to write a few lines of code in here to show how it could work.

Best,
Rupert

[Paul_Van_Dooren]

Hello Rupert,

Thank you for your quick reply to the post.
Your resume is spot on, and yes, one booking can populate any number of available dates.

I'm really less than a novice for scripting, so if you could give me a starting point, that would be much appreciated.

Big thanks Rupert!

Best,
Paul

[Rupert_Hoffsch1]

Sure, no problem!

So we would need a script automation for this (requires a pro plan). The trigger for the automation is a new form entry (every time a customer added something to the form).

The scripting automation could work like this (writing this in my head, so without debugging :P):

/* 
load input in scripting automation via input.config()(input defined on the left side)
let's just grab the record id here to reference the record that got created
*/
const inputConfig = input.config()

// loading tables + grabbing booking record (via inputConfig.recordId which got defined before) + all date records filtered for those that have a count of < 2 and sorted ascendingly by date
const bookingsTable = base.getTable("[bookings table name]");
const availDatesTable = base.getTable("[available dates table name"]);
const bookingRecord = await bookingsTable.selectRecordAsync(inputConfig.recordId)
const allDatesRecords = await availDatesTable.selectRecordsAsync(
sorts: [
       // sort by date in ascending order (to get next dates)
       {field: "Dates"},
    ]).then(result => result.records.filter(rec => rec.getCellValue("[count field name]") < 2))

// let's get n-amount (given by frequency) of date values now
const frequency = bookingRecord.getCellValue("[frequency field]")
cosnt nextDateRecords = allDatesRecords.slice(0, frequency);

// let's add these records to the bookingRecord
await bookingRecord.updateRecordAsync({
   "[avail dates field]": nextDateRecords.map(rec => ({id: rec.id}))
})

Does that help you / get you in the right direction? What's missing here is including the logic of skipping dates IF the next date is the next day. Basically means that the "nextDateRecords" part needs to get more sophisticated 🙂

Cheers
Rupert

[Paul_Van_Dooren]

Hello Rupert,

Thank you very much for your effort, but i'm not able to get the script working.

Could i maybe share my table with you?

Thanks,

Paul

[Rupert_Hoffsch1]

Hi Paul, definitely. Please reach out to rupert.hoffschmidt@gmail.com

Thank you!

[Paul_Van_Dooren]

Rupert,

Everything is working like a charm!

Thank you very much!

-> coffee is on its way.

Greetings,

Paul

[Rupert_Hoffsch1 · Accepted solution]

Thanks 🙂 Just posting the script here for reference if anyone else should want to use it...

/* 
load input in scripting automation via input.config()(input defined on the left side)
let's just grab the record id here to reference the record that got created
*/
const inputConfig = input.config()

// loading tables + grabbing booking record (via inputConfig.recordId which got defined before) + all date records filtered for those that have a count of < 2 and sorted ascendingly by date
const bookingsTable = base.getTable("Input");
const availDatesTable = base.getTable("Kalender");
const bookingRecord = await bookingsTable.selectRecordAsync(inputConfig.recordId)

if(bookingRecord) {
   const startDateRecord = await availDatesTable.selectRecordAsync(bookingRecord.getCellValue("Startdatum")[0].id)
   if(startDateRecord) {
      const startDate = new Date(startDateRecord.getCellValue("Date"));
      const allDatesRecords = await availDatesTable.selectRecordsAsync(
         {sorts: [
            // sort by date in ascending order (to get next dates)
            {
               field: "Date"
            },
         ]})
      .then(result => result.records.filter(rec => rec.getCellValue("DayCount") < 2 && new Date(rec.getCellValue("Date")) >= startDate))
   
       // let's get n-amount (given by frequency) of date values now
      const frequency = bookingRecord.getCellValue("Frequentie")
      // const nextDateRecords = allDatesRecords.slice(0, frequency);
      const nextDateRecords = allDatesRecords.filter((el, i) => i % 2 === 0).slice(0, frequency)
      console.log(nextDateRecords)

      // let's add these records to the bookingRecord
      await bookingsTable.updateRecordAsync(bookingRecord.id, {
         "All dates": nextDateRecords.map(rec => ({id: rec.id}))
      })
      console.log("Record updated!")
   }
}