How to Keep Old Sales Records Accurate When Product Prices Change in Airtable

You run a clothing store. Every month you sell products like shirts, trousers, and jackets, and you record the sales in Airtable.

You keep a product table where each product has its price. When you create a sales record, you link it to a product, and through a lookup field the price comes into your sales table.

Everything works as expected.

But the price of shirts, trousers, and jackets does not stay the same forever. Prices change monthly or seasonally.

When that happens, you go into the product table and update the price. The problem is that the lookup field on your past sales records also updates.

Now suddenly those old records no longer show the values they had at the time of sale.

That is not what you want.

What you really want is for history to stay fixed. If you sold shirts for 30 dollars in March, that record should always stay the same, even if today’s prices and costs are different.

Store Prices as Static Values

The issue comes from relying only on lookup fields. Lookups always pull the latest value from your product table, so whenever you update your prices, all your old sales records change too.

The fix is to store the price as a permanent value at the moment of sale.

In your sales table, create a regular number field called Price at Sale. This field will hold the actual transaction price.

Typing the price manually each time is not reliable and wastes time. Instead, set up an automation in Airtable.

When a new sales record is created, the automation copies the lookup price into the static Price at Sale field. Once stored, those numbers never change, no matter how you update your product table later.

Now you can update your product prices whenever you need. New sales will use the latest price, while old sales keep their original values.

This approach works not only for clothing stores but for any business where prices or costs change over time. Whether you sell food, services, or subscriptions, storing static values at the time of sale ensures your records are always accurate.