Help

Update Multiple Linked Records w/ Timestamp in Table 1, When a Button Clicked in Table 2

Topic Labels: Automations
560 3
cancel
Showing results for 
Search instead for 
Did you mean: 
daverad
6 - Interface Innovator
6 - Interface Innovator

I am looking to update multiple linked records w/ the current time/date in one table when a button is clicked in another table.

Here is a screenshot (shows checkbox, but a button would be my preference).

CleanShot 2024-01-31 at 15.47.14.jpeg

I tried creating an automation but if I understand correctly it is not possible to run an automation on multiple records. So I believe I may need a script.

I know enough to be I can make small edits to a script but if someone could help get me started and confirm if this is possible it would be a big help.

Any and all guidance welcome and appreciated. Thanks!

3 Replies 3
Les_Brown
5 - Automation Enthusiast
5 - Automation Enthusiast

Would something like this work?

  • Create a table called Time.
  • In that table create a Date Field to indicate the time
  • Create an automation to update that single record with current time.
  • Link back to that table in your other tables
  • Create lookups for that record in your other tables such that whenever the automation runs, all of those tables get updated automatically.

Something like that?

Thanks for thinking it through it and suggesting an idea! I wasn't sure I fully understood, but it did encourage me to explore automations and scripts more to better understand.

I ended up collabing with ChatGPT to get some sort of script. It took a while but here is where I landed and so far it seems to be working!

let affiliatesTable = base.getTable('Affiliates');
let revenueCatEventsTable = base.getTable('RevenueCat_Events');

// Get the record from Affiliates table
let record = await input.recordAsync('Select a record from Affiliates table', affiliatesTable);

// Get the linked record IDs from the selected record
let linkedRecordIDsString = record.getCellValueAsString('Linked RecordIDs');
let linkedRecordIDs = linkedRecordIDsString ? linkedRecordIDsString.split(',') : [];

// Check if linkedRecordIDs is not empty and is an array
if (linkedRecordIDs.length > 0) {
    console.log('Linked RecordIDs:', linkedRecordIDs);

    // Fetch all records from the RevenueCat_Events table
    let queryResult = await revenueCatEventsTable.selectRecordsAsync();

    // Loop through each linked record ID
    for (let linkedRecordID of linkedRecordIDs) {
        linkedRecordID = linkedRecordID.trim(); // Trim any leading/trailing spaces

        // Find the linked record in the fetched records
        let linkedRecord = queryResult.records.find(record => record.id === linkedRecordID);

        // Check if the linked record is found
        if (linkedRecord) {
            console.log(`Linked record found with ID ${linkedRecordID}:`, linkedRecord);

            // Update the linked record with the current timestamp
            await revenueCatEventsTable.updateRecordAsync(linkedRecord, {
                'Payment Processed Time': new Date().toISOString()
            });

            console.log(`Updated record with Linked Record ID ${linkedRecordID}.`);
        } else {
            console.log(`Linked record with ID ${linkedRecordID} not found in "RevenueCat_Events" table.`);
        }
    }

    console.log('Linked records updated successfully.');
} else {
    console.log('No Linked RecordIDs found or invalid format.');
}

Ah, congratulations on solving your problem!  In the future, you could use the repeating group automation action to help you with stuff like this, as it allows you to run an automation on multiple records

You could also try the following if you don't want any automations I think:
1. Create a new "Last Modified" field and point it at the "Paid" checkbox
2. In your other table, create a lookup field to display the date from that "Last Modified" field, and give it a conditional to only display from records where "Paid" is checked

Should work I think