Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 12, 2022 03:51 PM
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(),
})
}
Solved! Go to Solution.
Sep 14, 2022 08:20 PM
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
Sep 12, 2022 07:27 PM
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
Sep 13, 2022 02:46 PM
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,
})
}
Sep 13, 2022 04:22 PM
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.
Sep 13, 2022 06:22 PM
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?
Sep 14, 2022 06:57 AM
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.
Sep 14, 2022 07:32 AM
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?
Sep 14, 2022 10:58 AM
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.
Sep 14, 2022 08:20 PM
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
Sep 15, 2022 11:05 AM
That worked! Thank you so much for all of your help!!!