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');
