Preventing old invoices from updating their totals

[scotty321]

I have an invoice table that looks up prices from my inventory table. However, when I increase prices in my inventory table, all of my old invoices automatically inherit the newly increased prices. How do I keep my 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.

[JonathanBowen]

Hi @scotty321 - you can’t fix these values directly. What I would recommend is that your invoice table has two price columns - one is the price lookup you have now (the “current price”) and another, a currency field, called “invoice price”. When you create the invoice, you’ll get the lookup price. The copy and paste the value into the invoice price field. When the "current price changes because you have update prices in your products table, the invoice price will be unaffected.

JB

[JonathanBowen]

The other way would be to have a Prices table which shows the dates that a price for a product applies.

Then, when you create the invoice, choose the latest/current price for your invoice line:

[ScottWorld]

HI @JonathanBowen,

Thanks so much for these 2 clever workarounds! I greatly appreciate it! :grinning_face_with_smiling_eyes:

They are both equally excellent solutions! :grinning_face_with_big_eyes:

p.s. I’m coming from the FileMaker world, where a “lookup field” automatically copies & pastes the data (such as price) from one table into another table, so the prices always remain static on an old invoice. FileMaker ALSO gives us the Airtable ability to have linked fields update in real time, too (which is called a “related field” in FileMaker). So we have both options in FileMaker. I wonder if Airtable will ever give us the ability in the future to have lookup fields that do static copying & pasting of data instead of real-time linking of data? Do you know where I can suggest this feature to Airtable?

[Justin_Barrett]

ScottWorld:

Do you know where I can suggest this feature to Airtable?

The Product Suggestions category is designed just for that. Before starting a new thread, though, do a quick search to see if one already exists.

To add to Jonathan’s solution, you could create a view for that [Prices] table that only shows the latest prices for products based on their dates, and limit your selection to that view when selecting a price on new invoices. That way you have a historical record of all prices for all products, but only see the latest ones when making new invoices.

[ScottWorld]

Thanks, @Justin_Barrett! That’s a really clever solution as well. I didn’t even realize that lookup choices could be restricted to a particular view, so that’s really awesome.

I could even allow users to put a checkmark next to the “current” version of the product — and then only “current products” would show up in that view that I link to the invoices.

I suppose another concern would be that users might accidentally delete old products from the products table that are no longer current — which would then accidentally remove those products from the old invoices as well. I suppose that I could change those users’ access privileges for the products table to prevent deletion of records there.

[scotty321]

Ah, I guess that there is no way to allow a user to edit a record but NOT delete a record. Would be nice to prevent people from deleting records.

[Nathalie_Collin]

scotty321:

there is no way to allow a user to edit a record but NOT delete a record.

Been saying that for a long time. Actually, having the ability to create a more defined permission in general would be optimal.

[scotty321]

Thanks, @Nathalie_Collins!

[Agustin]

Hi @JonathanBowen ,

Thanks for you solutions!
Do you know how can i automatize the copy and paste step?

Thanks you so much in advance.