Skip to main content
Solved

Best practice for data reference

  • December 10, 2024
  • 5 replies
  • 4 views
F
Forum|alt.badge.img+5

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!

Best answer by 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!

View original
Did this topic help you find an answer to your question?

5 replies

ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8795 replies
  • December 10, 2024

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
Forum|alt.badge.img+28

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!

Adam, Consultant @ thetimesaving.company
matthew_b
F
2 people like this
  • matthew_b
    matthew_b
  • F
    Fire315
matthew_b
Forum|alt.badge.img+15
  • matthew_b
  • Known Participant
  • 62 replies
  • December 11, 2024
TheTimeSavingCo wrote:

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!


+1 - agreed with this solution!

F
Forum|alt.badge.img+5
  • Fire315
  • Author
  • New Participant
  • 2 replies
  • January 25, 2025
TheTimeSavingCo wrote:

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!


Thank you for your response TheTimeSavingCo.

After some tinkering, I settled with your first suggestion of using the reference table for now.
The gross price doesn't really change for us and if any, it would change once a year. 

But you made a great point about the problem I'll face if the gross price ever changes. I'll look into automation and see what I can do. If I need some help, I'll reach out to you!

Thank you for your help!

TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo
TheTimeSavingCo
Forum|alt.badge.img+28
Fire315 wrote:

Thank you for your response TheTimeSavingCo.

After some tinkering, I settled with your first suggestion of using the reference table for now.
The gross price doesn't really change for us and if any, it would change once a year. 

But you made a great point about the problem I'll face if the gross price ever changes. I'll look into automation and see what I can do. If I need some help, I'll reach out to you!

Thank you for your help!


Ha yeah that problem happened to me and unraveling it was...not fun heh

Glad I could help! 

Adam, Consultant @ thetimesaving.company

Reply


Most helpful members this week

Terms & Conditions