Help

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

Comparing records from different tables

Topic Labels: Formulas
Solved
Jump to Solution
3982 8
cancel
Showing results for 
Search instead for 
Did you mean: 
leandro_medeiro
6 - Interface Innovator
6 - Interface Innovator

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.

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
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")

See Solution in Thread

8 Replies 8

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
6 - Interface Innovator
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. ;(

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

image

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

Kamile,

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

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

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

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