It’s awesome that lookup fields automatically bring in data from other tables, and that these lookup fields always keep their data constantly updated in real-time between the multiple tables.
However, sometimes we want to lookup data from Table A into Table B, and KEEP THAT DATA STATIC in Table B. In other words, we DON’T want the lookup data in Table B to be constantly updated.
The #1 classic example of this is creating an invoice in an invoice table that looks up prices from a products table. For historical accounting purposes & historical tax reporting purposes, it is very important that each invoice ALWAYS stays at the EXACT SAME PRICE after submitting the invoice to a client.
But with Airtable’s lookup fields, whenever we increase prices in our products table, all of our old historical invoices also increase their prices as well. In reality, we only want the new prices to affect NEW INVOICES moving forward. We still want to keep all of our old invoices locked down to their old prices for historical purposes.
In other words, new prices should only affect new invoices moving forward… but should leave old invoices alone.
We got some workarounds in this thread, but it would be significantly easier if this functionality was directly built into Airtable.
We are coming to Airtable from the FileMaker world, where a “lookup field” in FileMaker copies & pastes the data (such as price) from Table A into Table B in a STATIC FASHION, so that prices always remain static on old invoices for historical purposes.
FileMaker, of course, also gives us Airtable’s ability to have lookup fields that automatically update in real time as well (but these are called “related fields” in FileMaker).
So we have both options in FileMaker. Airtable only gives us one option.
We would really love Airtable to give us the ability to have lookup fields that do “static copying & pasting” of data instead of real-time linking of data.
Just go ahead & steal FileMaker’s methodology of doing this!