Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Table and field formules

Topic Labels: Formulas
Solved
Jump to Solution
892 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Tiaan_Terblanch
6 - Interface Innovator
6 - Interface Innovator

Hi All - I would appreciate any support

In one base, I have two tables (Master and Prices).
I have a list of products on the master table with a field (column) named costs, and in Prices, I have Product and new Price fields. I need assistance (not sure if this is possible) with a formula that will help me update the costs in the Master table if the product in the Master table is also available in the Prices table.

Example:

Snip20210811_2

In this example, the Master table will only update products A and C’s Cost fields because it has not picked up a product on the Price table.

This means that in Table 1, product A will change automatically to 150 and C to 250. Nothing will happen to B because there is not a unit for it in Table 2.

Any help will be much appreciated.

Thanks

1 Solution

Accepted Solutions

You are the best - thank you so much. It worked perfectly.

See Solution in Thread

2 Replies 2

Hi,
Assuming your products in Table2 are unique and Product is primary field,
in table 1 you should add linked field (right after products, you can move/hide it then). Then copy/paste whole Products to linked field.
Warning!: if you trying to add the link by copy/paste, and that value is absent in the linked table, airtables will add them as new records to table2.
You should remove these redundant values afterwards manually (by ‘group by price’ or whatever).
If you considering long-term existence of this schema, you may not remove them, but set price to 0, for example.
Then return to table 1, add “Prices from table 2” as lookup field to linked you created before.
Now you have 2 price fields in table 2 - and you can make some resullt formula field like IF({Prices from table 2}>0, {Prices from table 2}, Prices)

note: using formula for lookup results may add some unexpected output, so double check it

You are the best - thank you so much. It worked perfectly.