Help

Link multiple records with specific multiplier for each record

Topic Labels: Base design Formulas
233 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Giacomo_Lanzi
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all, 

I am building a base that should help me build diets for my customer. 

In one of the tables, there are all the foods I can use (800+ records), each of them with nutritional data, as the nutritional tables on the foods boxes, to be clear. 

In another table, I would like to link records from the food table, and summarize nutritional facts from each of them. This is easy, also with multiple records linked, using the rollup field. 

The problem is: I would like to add a specific amount of each food (a quantity, so to speak), and calculate nutritional facts from each linked records and summarize the results. 

For example: 

I would like to add an apple to my diet, but the food table has nutritional fact for 100g of apple and I would like to add 250g of apple in the diet table.

 

If I have to link just one record, it would be simple, I add another numeric field and use it as a multiplier for the nutritional facts. But since I am not sure how many food items will be in each meal, I would like to link multiple records from the food table to the diet table into one field that represents the specific meal, and for each record add the multipler and then calculate the nutritional facts for the amount of food I selected. 

Any idea on how to do it without creating a linked record field for each food? Is this possibile, somehow? 

Thank you

3 Replies 3

The only way that you can do this is to restructure your database as a “many-to-many relationship”, which is a more complicated type of relationship that requires 3 tables (instead of 2 tables) for your record linking.

You can read more about many-to-many relationships in Airtable’s support document here: https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable

I also give a demonstration of many-to-many relationships on this Airtable podcast episode: https://www.youtube.com/watch?v=9tXHLU3i5GY&list=PLqssva4liHRwHhQIpTXekG8WObEoyC2F1

p.s. If you need to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consultant — ScottWorld

Thank you @ScottWorld, you gave me a direction! 

I will check the podcast and the documentation, hope that it will serve my purpose. 

You'd need a junction table for that I'm afraid, and I've set something up for you here to check out!

Screenshot 2024-03-29 at 10.21.09 AM.png

Screenshot 2024-03-29 at 10.21.20 AM.png

Screenshot 2024-03-29 at 10.21.22 AM.png