IMPROVING LOOKUP FIELDS: option for lookup fields to NOT get overwritten with new values

[ScottWorld]

One of the best things about lookup fields is that they always update with the latest values from the linked table.

BUT — one of the absolute worst things about lookup fields is that they always update with the latest values from the linked table.

We need the optional ability to have lookup fields that DON’T always auto-update with the latest values — but will only update when we WANT them to update.

This would enable us to have accurate information from the past that DOESN’T get inadvertently overwritten with inaccurate information in the future.

This is ESPECIALLY mission critical for reporting end-of-year sales and other financial reports.

There are literally thousands of examples of why people would NOT want lookup fields to keep updating with new information, but here are just a few of them:

1. You have an invoice system in Airtable where it looks up sales tax based on zip code from a sales tax table. Next year, the sales tax changes for all of your zip codes. BUT — if you update the sales tax in your sales tax table, ALL OF YOUR OLD ARCHIVED INVOICES GET UPDATED WITH THE NEW SALES TAX! So now, all of your old invoices have inaccurate prices on them.

2. You have a purchase order system in Airtable where you pay the salesperson on the purchase order a commission % for their sales. Their commission % is pulled into the purchase order table by looking up their commission % in the salesperson table. BUT — if you increase their commission % next year in the salesperson table, ALL OF YOUR OLD ARCHIVED PURCHASED ORDERS GET UPDATED WITH THE HIGHER COMMISSION %! So now, all of your old purchase orders have inaccurate sales commissions on them.

3. You have an invoicing system in Airtable which lookups line item pricing from a price table. However, when you increase prices items in the price table next year, ALL OF YOUR OLD ARCHIVED INVOICES GET UPDATED WITH THE NEW PRICING. So now, all of your old invoices have inaccurate totals on them.

HOWEVER — going back to #1 above:

1. Sometimes, you might have typed in the wrong zip code for an invoice, so the sales tax % is actually wrong. So sometimes, you might actually WANT the sales tax % to update when you type in the right zip code. We should be able to MANUALLY “relookup” a sales tax % when retyping in the zip code. IN OTHER WORDS: CHANGING DATA IN THE “TRIGGER FIELD” SHOULD RE-TRIGGER THE LOOKUP.

Please please please take a look at how FileMaker Pro implemented lookups — everyone in the database world knows that FileMaker is the absolute gold standard of desktop database programs, and best of all, FileMaker is also the gold standard of how to implement lookups. They’ve nailed it absolutely perfectly. The way that FileMaker handles lookups is brilliant, simple, perfect, beautiful, and works exactly how people would want them to work.

FileMaker gives you 3 options:

1. Lookups will ALWAYS overwrite their existing data in the current table AND update the data in the linked table. (This is actually called a “related field” in FileMaker.) <-- THIS IS THE ONLY OPTION THAT AIRTABLE GIVES US RIGHT NOW. THIS IS HOW AIRTABLE BEHAVES, AND WE HAVE NO OTHER OPTIONS.

2. Lookups WILL overwrite their existing data whenever you change the “trigger field”. It will pull in the updated information from the linked table into the current table, but only when you change the “trigger field”. It’s not “automatic” like it is in #1 above.

3. Lookups will NOT overwrite existing data in the current table, even if you change the “trigger field” in the current table. Any information that has been updated in the linked table will NOT be brought into the current table.

So those are the 3 options we have in FileMaker.

Yes, I know that there are clunky workarounds in Airtable — but all of these are clunky workarounds that require extra manual work on the users’ end, and they end up with cluttered tables, too. Check out how clunky & cluttered some of these workarounds are, even though they will “technically” work.

This product suggestion for improving lookups is one of the Top 10 Features that we need to truly build powerful business solutions with Airtable.

[Jack_Derrick]

Yes this feature would solve a lot of issues for me