Comparing records from different tables

[leandro_medeiro]

please help me.

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.

[Kamille_Parks]

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.

[leandro_medeiro]

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. ;(

[Kamille_Parks]

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.

[leandro_medeiro]

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.

[Kamille_Parks · Accepted solution]

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")

[leandro_medeiro]

Kamile,

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

[Kamille_Parks]

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.

[leandro_medeiro]

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

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