How to create script (or automation) that creates multiple linked records for different date ranges

I have been helped by so many of your community posts, thought I’d post one of my own for a problem I’ve been trying to solve.

I use Airtable as a resource management tool to staff designers onto projects. However, I want to also quickly pull data on who is on “open space” (not on a project) each week. (I’m often trying to answer the question "who’s available the week of i.e. 9/5?)

My Airtable base is set up with the following tables and links:

  • Designers Table: holds all of our designers
  • Projects Table: holds all of our projects (that designers need to be staffed on)
  • Availability Table: created via linked records between Designers and Projects

Unless there’s a simple way I’m completely missing, here’s the work around scenarios I’ve come up with:

Create a record for each week that’s labeled “Open Space” and set an automation for that record to be deleted if another record (a project record for a designer) overlaps with any of the open space records. That way I can set the filter to show records where Status is “Open Space” and Start Date is “9/5” to view all of the designers who are available the week of 9/5.

I’ve never written a script before so I’m borrowing this script by @Mike_Pennisi (thank you Mike!) to start off the “open space” record creation process. (see my version of the script pasted below)

Where I need your help!

  • How can I set the new records that are created to already have the status (single select field) selected as “Open Space”? (see my highlighted section on the script below where I’m having trouble setting this up)

const singleselectoption = hardCoded.singleselectoptionName
? childTable.getField(hardCoded.singleselectoptionName)
: await input.fieldAsync(‘Link field’, childTable);

  • How can I also have each of the records have a unique start and end date (should be every week, i.e. a record with a start date of 9/5 and end date of 9/9, then the next record should be 9/12-9/16, etc)

  • How/where should I set up the automation that removes these open space records whenever another record with a certain condition (Status = locked // this means that the designer is booked on a project) overlaps with the open space record?

  • I also want to set an automation to remove any open space records that have an end date before today since that information is no longer needed and I want to not exceed my record limit on my base

This would save my team SO much time, would appreciate ANY assistance with this. THANK YOU!

/**
 * Copyright 2020 Bocoup
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to
 * deal in the Software without restriction, including without limitation the
 * rights to use, copy, modify, merge, publish, distribute, sublicense, and/or
 * sell copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
 * IN THE SOFTWARE.
 */

/**
 * Create many links script
 *
 * Given a record in a "parent" table, create some number of "child" records in
 * another table, where each "child" references the "parent" through a Linked
 * Record field.
 *
 * **Notes on adapting this script.**
 *
 * The script prompts for input every time it is run. For some users, one or
 * more of these values may be the same with every execution. To streamline
 * their workflow, these users may modify this script by defining the constant
 * values in the first few lines. The values should be expressed as JavaScript
 * strings in the object named `hardCoded`.
 */
'use strict';

/**
 * Users may provide values for any of the properties in the following object
 * to streamline the script's startup.
 */
const hardCoded = {
    parentTableName: 'Designers',
    childTableName: 'NA-Availability',
    linkFieldName: 'Role/Designer',
    newRecordCount: '',
    singleselectfieldName: 'Status',
    singleselectoptionName: 'Open Space'
};

/**
 * Do not edit any code following this message.
 */

// Airtable limits batch operations to 50 records or fewer.
const maxRecordsPerCall = 50;

const parentTable = hardCoded.parentTableName
    ? base.getTable(hardCoded.parentTableName)
    : await input.tableAsync('Parent table name (holds the existing record)');
const parentRecord = await input.recordAsync('Parent record', parentTable);
const childTable = hardCoded.childTableName
    ? base.getTable(hardCoded.childTableName)
    : await input.tableAsync('Child table name (holds the new records)');
const linkField = hardCoded.linkFieldName
    ? childTable.getField(hardCoded.linkFieldName)
    : await input.fieldAsync('Link field', childTable);
const singleselectfield = hardCoded.singleselectfieldName
    ? childTable.getField(hardCoded.singleselectfieldName)
    : await input.fieldAsync('Link field', childTable);
const singleselectoption = hardCoded.singleselectoptionName
    ? childTable.getField(hardCoded.singleselectoptionName)
    : await input.fieldAsync('Link field', childTable);
const newRecordCount = hardCoded.newRecordCount
    ? parseInt(hardCoded.newRecordCount, 10)
    : parseInt(await input.textAsync('Number of records to create'), 10);

let newRecords = [];

// Part 1: Prepare the new records

for (let index = 0; index < newRecordCount; index += 1) {
    newRecords.push({
        fields: {
            [linkField.id]: [{id: parentRecord.id}], [singleselectfield.id]: [{id: singleselectoption.id}]
        }
    });
}

// Part 2: Perform the record creation operations in batches

while (newRecords.length > 0) {
    await childTable.createRecordsAsync(newRecords.slice(0, maxRecordsPerCall));
    newRecords = newRecords.slice(maxRecordsPerCall);
}

output.text('Done');

Hi,

I think you could create a 52 weeks timetable for the whole year using ‘week’ field with autonumber and DATEADD(zero_week_start, autonumber_field, ‘week’)

use for zero_week_start:

DATEADD(
  DATETIME_PARSE('01/01/'&YEAR(TODAY() )),
  0-WEEKDAY(DATETIME_PARSE('01/01/'&YEAR(TODAY() )))
  ,'day'
)

and for zw_end:

DATEADD(
  DATETIME_PARSE('01/01/'&YEAR(TODAY() )),
  6-WEEKDAY(DATETIME_PARSE('01/01/'&YEAR(TODAY() )))
  ,'day'
)

You may change 0 and 6 according to what day you consider as week start/end
image

indeed, with lookup/rollup and formulas you can achieve your goal (get list of available persons) without code and, I think, even without timetable (or do the small timetable for this week, maybe 1 or 2 forward.

regarding script, I don’t understand your goal at 100%. Example script is for wider use, and refactored, quite a large, and I’m too lazy to dig into it.
so I crafted a piece of code for task, how I understand it. No date, because I think it’s easier to do in AT.


const designers=base.getTable('Designers')
const assignee=await input.recordAsync('',designers)
const table=base.getTable('NA-Availability')
const [LINK,SELECT,OPTION]=['Role/Designer','Status','Open Space']
const newRow={[LINK]:[{'id':assignee?.id}],[SELECT]:{'name':OPTION}}
const content=new Array(10).fill(newRow)
const crt=content.map((row=>({fields:row})))
await table.createRecordsAsync(crt) // no batches, 10 hardcoded
1 Like

Wo yours is so much simpler, thank you @Alexey_Gusev !

Would you also happen to know how to identify records (with a certain condition) that overlaps with these Open Space records?

I was trying to set an automation like this:

  • Trigger: When a record is updated (Watching the “Status” field)

  • Find Records: Find records that match conditions (Status is Open Space and Role/Designer matches Role/Designer of Trigger Record) *

  • Conditional Action: If trigger record meets condition (“Weekly Dates” from Find Records contains “Weekly Dates” from Trigger Record ) update the Status of record from Find Records to “Remove” **

  • My tests were failing here. Comes out as “invalid Role/Designer filter”
    ** The Weekly Dates field is a formula field (included below) that strings together the Mondays of the project duration

REGEX_REPLACE(CONCATENATE(
  DATETIME_FORMAT(
    {Start Date 📅}, 
    "M/D/YY"
  ) & ", ",
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'weeks') >= 1,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 1, 'week'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'week') >= 2,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 2, 'week'),
        "M/D/YY"
    )  & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'weeks') >= 3,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 3, 'week'),
        "M/D/YY"
    )  & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'weeks') >= 4,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 4, 'week'),
        "M/D/YY"
    )& ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'weeks') >= 5,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 5, 'week'),
        "M/D/YY"
    ) & ", "
  )
), ", $", "")

Hi,
I suppose your tests are failing because Find Records always return array of records, even if you find 1 or 0 records. While Update record needs a single ID.

you can use scripting workaround and no-code workaround (even 2, i just ‘invented’ second, but both are quite absurd). They require additional little table with 1 record and 3 fields
first field might be formula RECORD_ID(). You need this value ‘recxxxxxxxx’ to hardcode the record into automation step ‘Update record’. It’s not mandatory to preserve it, you can even save this value in description and remove field.

  • 1st scenario: simple text field + formula field LEFT(textfield, 17)

example:
image

you put id_array of Find Records result into this text field by Update record

and then use value of formula field to perform your actual update.
I told, looks like absurd.

second scenario includes no formula but a link field (to the table where you searched by Find records), where you can put array of IDs. then second automation can do something via trigger ‘When record matches condition’ (link field not empty). It lets you loop through whole result of ‘Find records’, because if you put array of 10 IDs to link field, it creates 10 separate links ‘on the other side’

third, script way(no extra table needed). recommended:

1 Like

Thank you so much Alexey! Really appreciate your time and help on this!!