How do I lookup a field value and then put it (not a lookup) into my table

[Nigel_Wood]

Imagine this scenario - I want to create an invoice line. I have a product table of product_code and sales_price. I have the product code and quantity in the invoice line table and want to lookup the sales_price and copy it into the invoice line table field sold_price_each. I can do it with a lookup but that has a problem. If the price of the item changes in future, then the invoice will change after it’s been issued, which is no good at all!

I am new to Airtable and cant see a way forward on this one. Any help appreciated.

[ScottWorld]

Yeah, sadly, that’s one of the drawbacks of Airtable’s lookup fields.

Your easiest solution would probably be to put 2 fields next to each other on your Invoice table:

1. The lookup field that is called “Current Price”.
2. An empty currency field that is just called “Price”.

Then, manually copy and paste the current price into the price field on your invoices.

By the way, if this is an important feature request to you (like it is to me), please add your voice into the comments of this product suggestion that I created: IMPROVING LOOKUP FIELDS: option for lookup fields to NOT get overwritten with new values

[JonathanBowen]

Hi @Nigel_Wood - have a look at this post which deals with a similar issue:

Old entries in invoice table become inaccurate if I update my rates Ask the community

I have a base which I use for generating invoices for clients. I have a table for Line Items, a table for Invoices, and a table for Services. The services tab has rates for all services provided. The Line Items tab has all the line items for each invoice, with the rates populated via a lookup from the services tab. The invoices tab totals all the relevant line items into a single invoice and uses a Pages block to turn that into a nice looking invoice. My issue is this: if I update my rates,…

[JonathanBowen]

@ScottWorld’s solutions also nice (and arguably simpler)

[ScottWorld]

JonathanBowen:

@ScottWorld’s solutions also nice (and arguably simpler)

Lol, thanks! :slightly_smiling_face:

I was actually just going to type up that I like your solution on this one as well! Very clever! And with the new conditional linked fields, you could even limit the invoice’s selection of items to just the ones that are in the most recent price book.