Help

Matching Records Put Price From Table 1 to Table 2

Solved
Jump to Solution
500 2
cancel
Showing results for 
Search instead for 
Did you mean: 
RampartOnline
4 - Data Explorer
4 - Data Explorer

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 🙏

1 Solution

Accepted Solutions
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

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:

Screenshot 2023-11-11 193305.png

 

Pricing Table:

Screenshot 2023-11-11 193316.png

 

 Automation Setup:

Screenshot 2023-11-11 193414.png

 

Screenshot 2023-11-11 193502.png

 

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

    }
}

 

 

See Solution in Thread

2 Replies 2
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

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:

Screenshot 2023-11-11 193305.png

 

Pricing Table:

Screenshot 2023-11-11 193316.png

 

 Automation Setup:

Screenshot 2023-11-11 193414.png

 

Screenshot 2023-11-11 193502.png

 

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
4 - Data Explorer
4 - Data Explorer

@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! 💸