Best practice for data reference

[Fire315]

Hello,

I'm new to Airtable, and have been struggling to find the best way to solve my problem.
I apologize if there is a similar post for this problem, but I couldn't find any that helped me to solve mine.

I'm currently designing a base with multiple tables where there is a repeated and fixed information that needs to be reflected in different tables and different tables have different purpose and calculation. Thus, I would like to know if there is a way to create a reference table where I can update the data once and have it flow to other tables as needed.

To give more information about what I'm trying to accomplish, let's assume that there are 4 tables.
Table 1 - Reference Table
Table 2 - Invoice
Table 3 - Billing
Table 4 - Receipt

In tables 2, 3 and 4, there is a fixed amount that needs to be repeatedly reflected for different calculations called and let's assume that the field is named as 'Gross Item Price'. Instead of creating a formula field to reflect the 'Gross Item Price' of $500 (which is the best solution that I came across so far) in the 3 tables, I would like to set up the base so that once I update the gross item price in the 'Reference Table', the amount would update all the fields in tables 2, 3 and 4 that shows the 'Gross Item Price.'

In spreadsheets like Excel, this would be an easy solution as I would just have to refer to the 'Reference Table' like ='Refernece Table'!A2 or wherever the data that I'm trying to refer to is. 

If this practice is not recommended or possible in Airtable, I would like to know what would be the best practice.

Any help or suggestion would be much appreciated!

[ScottWorld]

In Airtable, you reference data in another table manually… by manually picking a record using a linked record field, and then using lookup fields to bring in the other data that you want to see.

I do a deep dive into linked record fields and lookup fields in my free Airtable training course, which you can take for free by signing up for a free 30-day trial with LinkedIn Learning.

My course is relatively outdated because it was created in 2020, but the core concepts remain the same, and the lessons on linked record fields and lookup fields are still valid.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

[TheTimeSavingCo]

Ah for this you'd use lookup fields: https://support.airtable.com/docs/lookup-field-overview

You'd create linked fields to the Reference Table in the Invoice, Billing and Receipt tables, link the appropriate record from the Reference Table, and then use the lookup field to display the desired value from the Reference Table.  To populate the linked field you'd need to use an automation I reckon, and how that's done is going to depend on how your data flows.  If you could talk more about how data gets created I'd be happy to help

---

For your use case I'm wondering whether a reference is a good idea though.  For example, let's say you've made a couple of sales and reference that Gross Item Price of 500 in a couple of Receipt records.  There's a change in the price, so you now update the Gross Item Price to be 600

If you're using a reference here, those Receipt records will now be updated to display the price of 600, which is incorrect since they were sold at 500 back then

As such, it might be a better idea to use the automation to stamp the Gross Item Price (or any other desired values) in, instead of linking to the Reference Table and using a lookup, does that make sense?

Apologies if you've already considered this and are fine with it!

[matthew_b]

+1 - agreed with this solution!