What’s needed here (as in many other parts of Airtable) is the ability to create some business logic. This use case is very common but is not really an example of where a lookup field is the right choice because the value should be the price when the product was selected not the current price whenever the record is viewed.
There is a way to achieve this at the moment (with no coding) and that is to use Zapier to look up the price of the product and then update the price in the order line table. However, this is pretty clunky at the moment given that a)the zap will not run immediately, b)the zap will only run once pre order line so if the product is changed the price wouldn’t and c)the Price field updated from zapper would be updatable by users which may not be very safe (it would be better to have a discount field).
I believe it would be possible to update the zapier integration to use instant (webhook based) zaps - and I think this also gets round the problem of them only running once for any given record as they don’t de-duplicate. The only remaining issue would be to be able to make fields read only (by user role ideally).
The combination of Zapier and Airtable would be pretty awesome if you were able to implement instant zaps in my view.
