Skip to main content

I am from a company selling STEM supplies to schools. I have three tables:

  • A “Material List” table with a list of materials we have in inventory
  • An “Activity List” table with a list of our STEM activities. Each activity has a linked record field which is populated with linked records in the “Material List” table

  •  

  • An “Activity Material” junction table with the quantities of each material for each activity listed. 

 

Desired Outcome of the Script:

I’ve written a script that is intended to:

  1. Retrieve an activity record from the "Activity List" table using a passed record ID, whenever the list of linked Material records is changed.

  2. Get the linked materials for that activity.

  3. Compare the linked materials with existing records in the "Activity Materials" table.

  4. Delete outdated records of materials no longer linked to the activity.

  5. Create new records for materials that are newly linked to the activity, including setting the Activity and Material fields with appropriate record references.

Here’s the script:

let activitiesTable = base.getTable("Activity List");
let materialsTable = base.getTable("Material List");
let activityMaterialsTable = base.getTable("Activity Materials");

// Get the record ID passed from the automation trigger
let inputConfig = input.config();
let activityRecordId = inputConfig.activityRecord; // This should be the Record ID passed from the trigger

// Fetch the full record from the Activity List table
let activityRecord = await activitiesTable.selectRecordsAsync().then(result => {
    return result.records.find(record => record.id === activityRecordId);
});

// Check if the activity record exists
if (!activityRecord) {
    throw new Error("Activity record not found.");
}

// Fetch the linked materials from the Activity List record
let linkedMaterials = activityRecord.getCellValue("Material Needed");

// Check if linked materials exist
if (!linkedMaterials) {
    throw new Error("No materials linked to the activity.");
}

// Fetch all existing activity materials for this activity
let activityMaterialsQuery = await activityMaterialsTable.selectRecordsAsync();
let existingActivityMaterials = activityMaterialsQuery.records.filter(record => record.getCellValue("Activity").0].id === activityRecord.id);

// Delete old records for materials no longer linked
for (let existingRecord of existingActivityMaterials) {
    let materialId = existingRecord.getCellValue("Material").0].id;
    if (!linkedMaterials.some(material => material.id === materialId)) {
        // Delete the record if the material is no longer linked
        await activityMaterialsTable.deleteRecordAsync(existingRecord.id);
    }
}

// Add new records for materials that are newly linked
for (let materialRecord of linkedMaterials) {
    let existingMaterial = existingActivityMaterials.find(record => record.getCellValue("Material").0].id === materialRecord.id);

    if (!existingMaterial) {
        // Create a new record if it doesn't already exist
        await activityMaterialsTable.createRecordAsync({
            "Activity": >{ id: activityRecord.id }],
            "Material": >{ id: materialRecord.id }],
            "Quantity": 1 // Set quantity or retrieve it from another field if needed
        });
    }
}

Issue:

  • In the script, line 47 is causing an error: “Type '{ id: string; }' is not assignable to type 'string'.”

  • Am I using the deprecated selectRecordsAsync function? Any troubleshooting tips here would be much appreciated...

 

In the ‘Activity Materials’ table, the ‘Activity’ field is a text field based on your screenshot.  However, in the script, you’re treating it as a linked field and trying to give it a record ID

 

To fix this, you could either point your script at the linked field to the ‘Activity List’ table, or put in the name of the record into the ‘Activity’ field instead

 Hi,

It is recommended to add object {fields: array of field names] } parameter to selectRecordsAsync( )
to avoid querying the whole table. without it, function works but displays unpleasant deprecation warning. 
You should use command to query a single record, it doesn’t require fields parameter,
Also, I would not add so much useless vertcal indents because throw new Error  noticeable by the difference in colors. 

I would rewrite the first half of your script (from first 3 table declaration lines, not included,... to // Fetch all existing activity materials)  as
 

// Get the record ID passed from the automation trigger
let {activityRecordId} = input.config()

// Fetch the full record from the Activity List table
let activityRecord = await activitiesTable.selectRecordAsync(activityRecordId)
if (!activityRecord) throw new Error("Activity record not found.")

// Fetch the linked materials from the Activity List record
let linkedMaterials = activityRecord.getCellValue("Material Needed");
if (!linkedMaterials) throw new Error("No materials linked to the activity.")

but then I realized -  when you have trigger record, you could use not just id, but all values of this record, Including links And even ‘Select records in table C, matching value from table B, linked/not linked to something in a Table A..
Indeed, more than 50% of your script can be done via standard Automation functions. The only thing where you cannot avoid scripting is record delete.
(I can show you, if needed) 

Well, it was interesting. I tried to add any trigger and did it from checkbox.
Since Msterials table linked to Junction, you need lookup from it. Activity (from Activity Material)
That’s all.

(Material N - trigger value of link field)


(record ID, Makecratd Activity - from trigger,  Activity (from Activity Material) - added lookup)


I suppose no need to break del operation to batches - 50 records at a time.

 


 


 



I think it’s good to add demo of test Results 

 

 

Thank you all for the help!! i must admit i was struggling to sort it out through regular automations. Alexy, I’ll try out what you’re suggesting now and report back

Btw, when using checkbox for trigger, after you set up and check that all OK, I would recommend to add easy and not mandatory step that make it looks ‘more automatic’. Usually I add it in the end, but since Repeating group not allows steps after it, it’s ok to set it before.

 


 

Reply


Terms & Conditions