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!

//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


    });


}

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.

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.