How to freeze linked record values at the time of sale in Airtable?

​@nahuel 

Yeah, unfortunately, that’s one of the larger problems in Airtable that other database apps don’t have.

There are a few different ways to workaround that problem, but these are the 2 best ways:

1. In your Service Catalog table, never update the pricing on pre-existing items. Instead, when it’s time to make a price change, create a brand new item in your Service Catalog table with the new pricing. To help ensure that nobody accidentally adds the old pricing to a new Service record in the Service table, you can add a single-select field in your Service Catalog table called “Status”, with the options of “Current” or “Old”. Then, in your Service table, you can filter your linked record field to only let people choose prices that are marked “Current”.
    
2.  Keep only one record for each item in your Service Catalog table. Then, in your Service table, you can setup multiple automations. Each automation would copy and paste the current amount from one of the lookup fields (which will always change on you) into a normal number field (which will never change on you). Your automation should be triggered by “when a record is updated” and it should monitor the lookup field. Then, it would take the lookup field’s value and put it into a number field.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Hey @nahuel, 

As mentioned by ​@ScottWorld , you cannot truly “freeze” a lookup / rollup in Airtable, so you’ll want to to copy (automatically) those values into normal currency fields at the moment of sale.

Keep in mind that whilst Scott above mentioned that the trigger for the automation should be “when a record is updated”, this will not trigger the automation if you are creating the record via a form or integration where the service gets pre-populated. This would only tirgger if you make a change on the service field.

Therefore, you might want to have 2 automations. One that gets triggered when the record is created, an done that gets triggered when the record gets updated.

Feel free to grab a slot if you need any help setting this up.

Mike, Consultant @ Automatic Nation 
YouTube Channel

Of the options you presented, really only this one is feasible:
 

• copy these values at the time of the sale

Automations can do that quite easily. Your existing cost fields should essentially become helper fields that shouldn’t really be exposed to humans. Create new currency static currency fields for each of those costs, and then create an automation that copies the value from the helper field into those static fields when the status changes to “Closed Won” or whatever signals the moment of sale in your table.

The helper field values will continue to change, but the static field values will remain, well, static.

If you also need to be able to see the costs before the sale closes, there are a couple ways to accomplish that, but I would probably create a third formula field for each cost. That formula should take the static value if it’s filled and, if it’s empty and/or the status isn’t Closed Won, take the helper field value. That way you won’t need to be looking at multiple fields depending on where in the funnel a deal is.

+1 for copying the values at the time of sale

For me, I have two tables, one for Products and one for Orders.  Both of these tables have a field called ‘Price’

When a Product is ordered, I create a record in the Orders table and link it to the Product record.  I then have an automation that copies the Price value from the Product record and pastes it into the Price field in the Order record, and this way price changes don’t affect my historical data, does that make sense?