Retrieve Value From Lookup Table and Write to New Record

[Dominic_Rannie]

Hi,

I have two tables. “Margins” and “Orders”. I need a script that would be triggered by an “New Record in the Orders table” automation that would use the value in the “Product” field to lookup a “Margin” value in the “Margins” table, then record this against the new order record, assuming a match is found.

In doing this, I hope to preserve the current margin against a new order, something that would not happen with a normal lookup filed as it would simply update all the linked order records when a margin is altered.

Would anybody be able to nudge me in the right directly here. I have no experience in Javascript, although I have basic knowledge of Python.

How do I select the “Product” value from the latest record(s) in the Orders table and only update those records that have not been already updated previously be the script?

Are there examples of similar scripts that anybody is aware of that I could work from?

I am guessing that this example posted by @Sam_Cederwall could be a good starting point.

Many thanks in advance!

[Sam_Cederwall]

Hello Dominic,

I believe I have also done something similar to this in a few of my different bases. It sounds to me like you want to isolate a value at a point and time and preserve it to a record since that value will change as records are created.

To do this properly you will have to do this as an automation with a scripting action. A little hard to replicate your case exactly but this is what I would offer you to get you started and hopefully it captures what you need. You will have to probably adjust some of the field and table names based on your system.

//Inputs
var inputs = input.config();
var recordId = inputs.recordId;
var product = inputs.product;

//Tables
var orderTbl = base.getTable("Orders");
var marginTbl = base.getTable("Margins");

//Queries
var marginQry = await marginTbl.selectRecordsAsync();

//Filter to find product
var productMatch = marginQry.records.filter(x=>x.getCellValueAsString("Product") == product)[0];

//If a match is found
if(productMatch !== undefined){

    //Get the margin from the associated margin record
    var productMargin = productMatch.getCellValueAsString("Margin");

    //Update the record
    await orderTbl.updateRecordAsync(recordId, {
        "Margin": productMargin
    });
}

For the inputs portion, you will need to call inputs from the first step in the automation like so, once again, I had to somewhat force this into an already existing base, so yours will have to look a bit different.

Hope that this helps. Best of luck!

[Dominic_Rannie]

Hi Sam,

Many thanks for your response. I will give your code a go. I have also been looking at Retrieve a Record api call as a solution.

On top top of this, as long as I have the margin value as a lookup field in the orders base (have have synced the Margins base to Orders), I have been able to use the Airtable Automations functionality to write this value to a new field “sale margin” on each new record created. This provides a completely codeless method.

[Sam_Cederwall]

A good point! My mind just immediately went to the more complex solution. Whoops.

Well all I’ve got for you now is if you didn’t want to use lookups but it looks like you got it handled.

Good luck.