No, you don’t need to use a script, but you will need to create an automation that essentially “copies and pastes” the current price from your lookup field into a normal number field. Instead of using the lookup field for your actual prices, you would use the number field instead. The lookup field would just be used for the automation’s purposes.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with anything that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Hey ​@Graham_Lindsay,

Sharing some additional details on what Scott mentioned above.

You can get this solved in multiple ways. E.g. (i) you could have a Products table, have Price as a field on the Products table, and overwrite Price each time pricing changes; or (ii) you could have a Products table, and Price List table and keep all price updates, with their dates, for each product, as a new records.

My understanding is that your current setup is (i) above. I’ll assume that you have a Products table, an Orders table, and an Order Line Items table, and that your Order Line Items table links back to the Products table. For last, I assume that you have a lookup on the Order Line Items table that looks up current price from Product.

What Scott is suggesting is that you have a completely new filed on the Line Items table that should be called smth like “Actual Price”. This field should be of type Currency. Whenever Product and Current Price (lookups) are not empty, an automation should get triggered that will:

1. Find Records from the Products table where Order Line Item has any of the record id of the line item that triggered the automation.
2. Update record from the Order Line Item table that triggered the automation. Field to be updated is Actual Price (which will be currently blank). To such field you’ll map the value of the Current Price lookup. (so that is how the “copy and paste” would work automatically.

Given that the Actual Price is now a static value on a “free text” Currency field, even if your price for the product changes, and in turn that affects the lookup, Actual Price will be completely independent.

Edge case you might want to consider:

Product Updates. If given a mistake, or any other reason you need to change the product on the order line item, and you change the product without making the Product field blank first, then your automation will not trigger (if set to run when a record matches conditions). Reason for it being that the condition is already met (product and actual price are still not empty). This edge case would require an additional automation to get triggered “when a record gets updated” and then watch the Product field.

I’d be happy to show you around on a quick call. Feel free to grab a slot using this link!

Mike, Consultant @ Automatic Nation 
YouTube Channel