# Re: Table and field formules

Solved
Jump to Solution
373 0
cancel
Showing results for
Search instead for
Did you mean:
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:

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
6 - Interface Innovator

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

2 Replies 2
12 - Earth

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

6 - Interface Innovator

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