Update multiple date type records from a different table and field

[Kim_Zhou]

Hi, I am trying to automate an update of the “Date of Last Interaction” field in multiple linked companies (records) in my ‘Companies’ table with information from the “Date of Interaction” field in my ‘Interactions’ table when an interaction (a record) is added into ‘Interactions’. I was able to successfully update "Date of Last Interaction with the day the record is created/edited following this forum post. However, I want to update this with information pulled from the “Date of Interaction” field in my ‘Interactions’ table instead. I have attached some pictures and the code I used.

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

[TheTimeSavingCo]

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

[Kim_Zhou]

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, 
    })
}

[Kim_Zhou]

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.

[TheTimeSavingCo]

Ahh, sorry about that error in my previous reply

Kim_Zhou:

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)?

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

Kim_Zhou:

I tried to create a new input variable but I can’t seem to access the other table in the input variables.

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

[Kim_Zhou]

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.

[TheTimeSavingCo]

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?

[Kim_Zhou]

I see, how would I go about grabbing those interaction dates and comparing to new interaction date in the second option you listed? I would prefer to not make more fields unless I have to!

I did try the first option you listed and came up with this:

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

But have run into some errors since lastDate and interactionDates are strings. Also, I get “no overload matches this call” when I hover over the if statement.

[TheTimeSavingCo · Accepted solution]

Kim_Zhou:

I see, how would I go about grabbing those interaction dates and comparing to new interaction date in the second option you listed? I would prefer to not make more fields unless I have to!

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

Kim_Zhou:

I did try the first option you listed and came up with this:

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

[Kim_Zhou]

Adam_TheTimeSavingCo:

base

That worked! Thank you so much for all of your help!!!