Lookup data that doesn't change after source data changes


#1

I store price data in a Products table, and link to it in an InvoiceLines table (Many InvoiceLines from One Product). However, when a price changes (is updated) in Products the original price of that item should not change in those records in InvoiceLines that have already been created.

Given the Lookup type doesn’t offer it, can you think of a way to achieve this?

At the moment the only way I can think of, is to export data from one table and import to another?


How about an example or templet that includes basic invoicing
#2

Yup. That’s the only way at the moment… :wink:


#3

If I’m understanding what you’re saying correctly, perhaps one solution would be to create the lookup field, pull in all of the data, then convert the lookup field into a currency field. This will sever the link between the two fields and it will no longer automatically update.


#4

Perhaps fix lookup and rollup results by copying the values and pasting them into a numeric archive field.

Perhaps this could be done automatically with a formula that references dates and only copies the value from the lookup or rollup field when the data is current … not sure if lack of an output on a formula would wipe out existing data in a field or not.

It does wipe out existing data in the field. I thought OK in the else condition just return self, or the same column that it is calculating, but this cannot be selected, yikes!


#5

Hi all, Has anyone resolved this issue.
I mean it it really necessary to have a way to fill out values in a field based on a relationship.
The Lookup should do that… but here in Airtable it is more like a fixed connection of the parent records value… Which clearly does not cut it in the Invoice Lineitem example.
I mean… Who wants all invoices to change when the product price changes???


#6

You could do it by creating a second field and populating it from the lookup field with Zapier.


#7

I realise there are workarounds but I wonder when this basic need, will be addressed in the Airtable structure.