Matching Records Put Price From Table 1 to Table 2

[RampartOnline]

Hi all,

Just asking for a kick in the right direction for a formula, extension/script and/or automation that'll put the PriceInput value from my Price List table into the Database1 table (whether it's displayed in the Lookup or thrown into the PriceOutput field) when the "Agency, Board Type, Size, Type" match.

I can see the option's available to select the suitable record via manually selecting it in the linked cell (as pictured in attachment) but this is obviously the part I need automated when a new record gets put in or updated.

Database1 : https://airtable.com/apprcSs2FuTW9JKPJ/shrX6iv9CyKfOFLN3
Price List : https://airtable.com/apprcSs2FuTW9JKPJ/shrMo95QtDPC7n3Hq


Greatly appreciated in advance 🙏

[Arthur_Tutt · Accepted solution]

Hey @RampartOnline ! This one was a little tricky based on how the data is structured, but I put together an automation you can use to make it work. I recorded a short Loom Video to take you through step by step exactly how I'd set it up. 

Table setup and desired output:

Pricing Table:

 Automation Setup:

Automation Script (remember to change my table / field names to match your own):

 

// get address table

let addressTable = base.getTable("Addresses")
let addressQuery = await addressTable.selectRecordsAsync({fields: ["Summary"]})
let addressRecords = addressQuery.records;


// get input data

let inputConfig = input.config();
let recordId = inputConfig.recordId;
let summary = inputConfig.summary;
console.log(summary)


// get pricing table

let pricingTable = base.getTable("Price List")
let pricingQuery = await pricingTable.selectRecordsAsync({fields: ["Summary", "Price"]})
let pricingRecords = pricingQuery.records;
console.log(pricingRecords)


for (let i = 0; i < pricingRecords.length; i++) {
    if (pricingRecords[i].getCellValue("Summary") == summary) {
        
        var updates = [{
                "id": recordId,
                        fields: {
                            "Price": pricingRecords[i].getCellValue("Price"),
                        }
                }]
                console.log(updates)

                await addressTable.updateRecordsAsync(updates);

    }
}

[RampartOnline]

@Arthur_Tutt you are an absolute weapon.
I knew this was beyond my capacity but you've made this infinitely easier to understand.
Thank you heaps for your time, you've saved countless hours of mine! 💸