Change field value of linked record without changing original value

[Alex_Varlamov]

Hi, I have 2 tables: Table1 and Table2.
In Table2 I have the field “SUM”, which I use as the linked field in Table1. For some linked records, within the Table1, I want to change the value of field “SUM” leaving the original value not changed in Table2. I know, I can create a new record in Table2 with the needed value in field “SUM”, but I want to avoid duplicated records with only “SUM” field changed.

Any help would be appreciated.
Thank in advance!

[Olly_L]

Just to clarify: in Table 2 when you say you have a field labelled “SUM” that is a “linked field”, what type of linked field is this? Is it a: Link to another record, Lookup, Rollup or Count?

[Alex_Varlamov]

Thanks for your reply. Sorry, maybe my question is not clear enough.
The “SUM” field from the TABLE2 has the field type “Currency”.
And in TABLE1 I have the field “TOTAL”, which is of type “Rollup” and shows the sum of all records linked from TABLE2.
Now, in some cases, when linking a new record from TABLE2 to TABLE1 I want to change the value of field “SUM” (from TABLE2), leaving the original value, for this record in TABLE2, not changed.

[Olly_L]

So you have:

• TABLE2, with a field “SUM” that is a user inputted field
• TABLE2 links to multiple records from TABLE1 (and by default vice versa)
• TABLE1 has a Rollup field called “TOTAL” which sums the “SUM” field from all the linked records in TABLE2
• In some instances you want to link to multiple records in TABLE1 from a record in TABLE2 but use one value for some records and another for others?

Sorry if I’m still not getting it!

[Alex_Varlamov]

Almost right :slightly_smiling_face: Well, I think it’s better to see something once than to hear about it a thousand times.
Here is a link to sample table: https://airtable.com/shrUitLKclYVQQlCj

With such tables structure, I want to add the “Product 2” to “Order 2” with a custom “Price”, let say, $20, keeping the original price, $10, not changed. So that the “Total” for “Order 2” would be $70 instead of $60.

[Justin_Barrett]

I suggest creating a junction table named something like [Line Items]. You would use link fields to select an order from the [Orders] table, and a product from the [Products] table. A rollup field would then pull the normal price for the product. A currency field named something like {Override} would allow you to override the price of that product for the associated order. Finally, a formula field would look for an override price and use it if present; if not, it would use the normal price. The order in the [Orders] table would then roll up the price from the formula field in [Line Items].

[Alex_Varlamov]

Justin, thanks for your answer! It sounds like a good workaround, so for now I’m ending up with this solution, hoping that airtable developers will extend their functionality in the future.

[Justin_Barrett]

Junction tables are actually a preferred way of operating in certain circumstances. Not every database design requires them, but in a case like this I wouldn’t consider it a workaround, or something that needs to be fixed by adding other functionality down the road.

[Alex_Varlamov]

I agree with you on junction tables, it’s really useful, especially for many-to-many relations when designing databases. Initially I did not consider the airtable as the traditional relational database. And it really makes sense now when looking at airtable from this point of view.
But considering this situation from the point of view of a simple user, who may not know anything about databases, it would be really more convenient to deal with this situation without need to support additional junction tables. And as a simple user, I really don’t care if the airtable uses junction tables or whatever to handle this requirement under the hood.