If two fields in different tables match, update record in first table

[Wendy_Crews]

Hello,

I have a Client Management base that has a main table called “Accounts” with a list of accounts by name. I have a second table called “Online Presence Builder,” where new records are automatically created using the Jotform integration.

When clients fill out the Jotform form, they choose the type of project they want to be completed.

The trouble I am having is creating an automation/script that will compare the company name in the Online Presence Builder table to the account name in Accounts.

If they match, I want the automation to add the project named in the automation to the projects listed under the client.

If they don’t match I want to create a new account to the Accounts table.

I found a few examples that were close but I couldn’t figure out how to make them work for me.

Does anyone have any ideas? It would really help!

Thanks!

[Simon_H]

I had a similar use case where i needed to track new items added to a table and find a corresponding approval record on a different table and link it if it exists, or create a new one if not. Here is a simplified version if the script I used, you should be able to be modify it to fit your use case as well.

//Items table to track for new entries
const table1 = base.getTable(“Items”);
//Approvals table where all new items need to be added
const table2 = base.getTable(“Item Approvals”);

async function main() {
const item = await input.recordAsync(‘Please choose an item’,table1);
await approvals(item);
}

async function approvals(item) {

let digitStyle = item.getCellValue(‘5 Digit Style Number’);
let approvalItems = await table2.selectRecordsAsync();
let matchingItems = approvalItems.records.filter(item => item.getCellValue(‘5 Digit Style Number’) && item.getCellValue(‘5 Digit Style Number’) == digitStyle )

if (!matchingItems.length){
//approval does not exist, create new record
await table2.createRecordAsync({
‘Items’: [item]
})
}else{
//approval already exists, add new item to existing approval
approvalItem = matchingItems[0];
approvalItemId = approvalItem.id;
let linkedItems = approvalItem.getCellValue(‘Items’);

if(!linkedItems.some( itm => itm.id === item.id)){
    await table2.updateRecordAsync(approvalItemId, {
        'Items' : [ ...linkedItems, item]
    })
}

}

}
await main()