Script to Conditionally Link a Record

[Karen_Hamilton]

I have multiple tables - one is the master table, the other tables are child tables. Records from the master table should be automatically linked to the child tables when they are created, based on a field that contains a matching value for the child table.

For example, I have tables:
Master, Project 1, Project 2, Project 3.
Master project contains a list of companies and field that lists their projects.
Project 1's table is supposed to contain *only* companies that are associated with Project 1

Within the master table there is a field labeled "Projects", which contains zero, one or more of "Project 1", "Project 2", "Project 3".

If the field contains "Project 1", it should be linked to the table named "Project 1"

If the field contains "Project 1" and "Project 2", it should be linked to both "Project 1" and "Project 2" tables.

Hoping to do this within Airtable Scripting, so that it is dynamic and able to update based on the table names.
My current code looks like this (using a button to create a new table for a project, and link records associated):

let projectTable = base.getTable("ALL PROJECTS");

let companyTable = base.getTable("Company");

let project = await input.recordAsync("selectRecordAsync",projectTable);

let newTableName = project.name;

let companyList = await companyTable.selectRecordsAsync();

let newTable = await base.createTableAsync(newTableName, [{name: "ID", type: "singleLineText"}]);

let newTable1 = base.getTable(newTableName);

let companyName = company.getCellValue("Company Name");

let projName = company.getCellValueAsString("Project");

await newTable1.createFieldAsync(name: "Linked Field", type: "multipleRecordLinks", id: companyName); 

for (let company of companyList.records) {

        if( Find( projName, newTableName), // this is supposed to match project name to table names, but I am not doing this correctly 😞
await newTable1.createRecordAsync({"Linked Field": companyName})

}

Any help would be sooo appreciated!

[TheTimeSavingCo]

Hi Karen, is the intent to basically duplicate the entire table except records that don't match the project?

E.g. if the table has Field 1, Field 2, Field 3, two records of "Project 1" and one record of "Project 2", when clicking said button for one of the records of "Project 1" your desired output would be the entire table plus those two "Project 1" records?

[Karen_Hamilton]

Hey Adam, I hadn't thought about it, but that's a good way to frame it. I think the catch is that there are some records that are tied to multiple projects, so they would need to be pulled over into both tables.

[TheTimeSavingCo · Accepted solution]

Ah okay.  I've put something together here

It looks for records that match the current record's project.  It doesn't handle multiple projects, but should serve as an example for you to modify I believe

[Karen_Hamilton]

This is awesome, thank you so much!! To confirm, in the following line, which table are you trying to call?

await base.createTableAsync(newTableName, [{name: "ID", type: "singleLineText"},

    {name: "Linked Field", type: "multipleRecordLinks", options: {linkedTableId: "TABLE??"}}]);

[TheTimeSavingCo]

Ah that refers to the "ALL PROJECTS" table.  I've updated line 21 and 24 to be dynamic instead of hardcoded so that should help

[Karen_Hamilton]

Amazing - thank you so much!!