# Re: Comparing records from different tables

Solved
2153 0
cancel
Showing results for
Did you mean:
6 - Interface Innovator

I have 2 tables, one for sales and the other for stock.

The inventory table receives data (customer id and product) that is informed via a bot every time the customer goes through a certain flow.

however, I need this product to be counted only if the sale is made.

In the sales table you will find the customer’s id. and this table is only fed if the customer makes the purchase.

So basically I need a column in the inventory table that asks the following question: if the sales table has an ID equal to the inventory table then 1 if not 0.

1 Solution

Accepted Solutions
16 - Uranus

Try doing the following:

1. Add a `Link to another record`-type field to the `[Estoque]` table to connect to the `[pedidos]` table.
2. I am assuming a possible `{Status}` in the `[pedidos]` table is “sold”. If that’s the case, set up an Automation with a `when record matches condition` trigger for the `[pedidos]` table with the condition being `{Status} = "sold"`.
3. Add a `Find records` step that looks for records in the `[Estoque]` table that have a value in `{nrpedido}` that equals the trigger record’s value in `{nropedido}`. (or pick some other pair of fields will match some unique value for the product sold/in inventory)
4. Add an `Update record` step that updates the trigger record in the `[pedidos]` table by inserting the `list of record ids` returned from the step above into the `Link to another record` field you created in step 1.
5. Make the `{purchased}` field in the `[Estoque]` table a formula: `IF({name of the link field to the pedidos table}, "no", "yes")`
8 Replies 8
16 - Uranus

A formula can’t calculate based on records from another table unless the record on which the formula is applied is linked to one or more records in another table.

So, are you linking records together? Based on your description it sounds like you should be, but aren’t.

6 - Interface Innovator

I am not linking the records. because as they are imputed in the table by an external platform, there is no way to predict the id that will appear and link it previously. ;(

16 - Uranus

Is every single table imputed by this external source? Is it possible to post screenshots of your base setup. You almost certainly can link your records together, manually or via an automation.

6 - Interface Innovator

each item you select is inserted in that table. however, these items are not counted as sold. will only be counted when the customer completes the purchase.

When the customer completes the purchase, the summary is computed in this table.

Therefore, after confirming the purchase, I want the string “yes” to appear in the stock table in the purchased column.

16 - Uranus

Try doing the following:

1. Add a `Link to another record`-type field to the `[Estoque]` table to connect to the `[pedidos]` table.
2. I am assuming a possible `{Status}` in the `[pedidos]` table is “sold”. If that’s the case, set up an Automation with a `when record matches condition` trigger for the `[pedidos]` table with the condition being `{Status} = "sold"`.
3. Add a `Find records` step that looks for records in the `[Estoque]` table that have a value in `{nrpedido}` that equals the trigger record’s value in `{nropedido}`. (or pick some other pair of fields will match some unique value for the product sold/in inventory)
4. Add an `Update record` step that updates the trigger record in the `[pedidos]` table by inserting the `list of record ids` returned from the step above into the `Link to another record` field you created in step 1.
5. Make the `{purchased}` field in the `[Estoque]` table a formula: `IF({name of the link field to the pedidos table}, "no", "yes")`
6 - Interface Innovator

Kamile,

until the third stage, I managed to do it, but I didn’t quite complete the fourth stage. Could you explain it better?

16 - Uranus

An update record step will ask which table and which record to update. Select the `[pedidos]` table. In the box for record ID, click the plus button then click `Record (from Step 1)`, then click `insert` next to `Airtable record ID`.

Then under `Choose field`, select the `Link` field that connects to the `[Estoque]` table. Click the plus button and select `Records (from Step #: Find records)`, then click `continue` next to `Records`, then under `Make new list of...` select `Airtable record ID`.

6 - Interface Innovator

Wow!! it looks like witchcraft. it worked very well!

Thanks for all your support and patience, Kamille. :heart_eyes: