Update multiple date type records from a different table and field

Hi Kim, I’m assuming the automation triggers when the record in Interactions is updated?

If so, you’ll need to add another input variable for the Date of Interaction field in the Interactions table, call it like “interactionDate” or something, update your script to grab it, and then use it

Something like:

let inputConfig = input.config()
let records = inputConfig.triggerRecords
let interactionDate = inputConfig.interactionDate
let table = base.getTable("Companies"); 
for (let record of records) {
    await table.updateRecordAsync(record, {
    "Date of Last Interaction": interactionDate, 
    })
}

Edit: Fixed error with input config in original code

Hi, thanks for your help! I’m new to Airtable scripting so this has been super great :slightly_smiling_face: This ended up being my final code used

let inputConfig = input.config();
let table = base.getTable("Companies"); 
let records = inputConfig.triggerRecords
let interactionDate = inputConfig.interactionDate
for (let record of records) {
    await table.updateRecordAsync(record, {
    "Date of Last Interaction": interactionDate, 
    })
}

I’ve run into another issue with this automation -
Is there any way I can compare interactionDate with information in a “Date of Last Interaction” field that lives in a different table and only update if interactionDate is after the info in “Date of Last Interaction” (note this field isn’t always populated)? I tried to create a new input variable but I can’t seem to access the other table in the input variables.

Ahh, sorry about that error in my previous reply

Hmm, yeah this sounds doable. You might not need to do the comparison in a script I think

A Find Record action to find that other “Date of Last Interaction” followed by a conditional checking whether interactionDate is after “Date of Last Interaction” could work

Hmm, could you provide some screenshots of the automation and relevant tables?

Yes, thank you in advance for your help! When I create an Interaction, I am trying to use the companies linked in #1 and find their Date of Last Interaction in #2. I then want to compare this to Date in #3. Finally, if #3 > #2, I want to update #2 with #3, else leave #2 as is.

Automation flow and Code here. Creating input lastDate was where I ran into trouble.

Ah, we’re triggering from Interactions and can only pull data from the triggering record, so you could try adding a lookup field with the interaction dates for each company, adding that as an input variable, and then checking against each of those within your script? From what I can tell, lookup field values display in the same order as the linked fields they’re looking up from, so this should work

You could also just grab the interaction dates for each company via scripting as well since you’ve got the record IDs of the relevant companies?

You’d need to do a selectRecordsAsync to grab the triggerRecords, and for each of them, do a getCellValue and compare them against interaction date

Here’s a base that does that; you should be able to see the code in the automation

Hmm, I don’t think you can use an IF like that in an object?

I assume lastDate is all the Date of Last Interaction values of the linked company records? If so, you’re going to need to use some sort of i++ loop to refer to the data in your triggerRecords and lastDate values I think

e.g.
(I haven’t tested the below chunk, but conceptually I think it’s right)

let interactionDate = new Date(inputConfig.interactionDate)
for (let i = 0; i < triggerRecords; i++){
  let companyInteractionDate = new Date(lastDate[i])
  if (companyInteractionDate.getTime() < interactionDate.getTime()){
     await table.updateRecordAsync(triggerRecords[i], { 
            "Date of Last Interaction": interactionDate 
    })
  }
  else... etc etc