Ability to Reference One Record Multiple Times in One Cell

[Jonathan_Gribbo]

I am attempting to find a way to link one record multiple times. I’m trying to cost jobs and need to add the necessary material which I have in a separate table in the same base. I can link multiple records and have them add up however I cannot link one record more than once which is necessary because every record is in a quantity of 1. So for example one job may require 5 feet of wire where another would require 10. I cant change the original record to reflect these numbers because I would have to make a new record so they don’t conflict and by doing so would make the products page too large to be able to update as prices change. I attached an image of the basic setup in the table. I don’t think there are any current features to accomplish this.

[ScottWorld]

You would need to restructure your database to become a many-to-many relationship.

Here is Airtable’s guide on many-to-many relationships:

Airtable Support

Airtable's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

[Jonathan_Gribbo]

Hey Scott, I appreciate the response and had looked into many-to-many relationships, we currently use that setup but on top of many-to-many need one multiple times so that one record can be multiplied to find cost of it multiple times without disrupting the original so that stays as the base and can be used again with a different factor of multiplication in another record.

[Kamille_Parks]

Sounds like you need multiple records to represent change in “multiplication factor” over time. In my view having duplicate links between the same records would be a design flaw, not a feature.

[Jonathan_Gribbo]

I could add more records with different quantities in the table I’m linking to however this would make updating prices more tedious. For example if I make 4 records with different measuremnts for wire they would need to be updated separately. As I have it set now the material is in units of one for devices and 1ft for wire, PVC, conduit etc. So when I update prices I only have to do it once per type of material. Wire alone I have 72 records and 1800 total in that table separated by category so if I make multiple records for different quantities I would have too many records to update and too large of a table. That’s why I’m trying to link one record multiple times in this new table which is for flat rate jobs accounting for material used, the goal is to be able to update material price one time instead of multiple times.

[Kamille_Parks]

Jonathan_Gribbon:

I could add more records with different quantities in the table I’m linking to however this would make updating prices more tedious.

Would it? One table for the prices for each material, one table for quantities of each material ordered. If you update the material’s price per unit in the Materials table, the costs can be automatically reflected in your Quantities table using Lookups or Rollups. You’d still only need to update the price of wire once.