Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 24, 2021 09:26 AM
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.
Solved! Go to Solution.
Apr 24, 2021 06:18 PM
Try doing the following:
Link to another record
-type field to the [Estoque]
table to connect to the [pedidos]
table.{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"
.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)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.{purchased}
field in the [Estoque]
table a formula: IF({name of the link field to the pedidos table}, "no", "yes")
Apr 24, 2021 04:36 PM
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.
Apr 24, 2021 04:46 PM
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. ;(
Apr 24, 2021 05:15 PM
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.
Apr 24, 2021 05:24 PM
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.
Apr 24, 2021 06:18 PM
Try doing the following:
Link to another record
-type field to the [Estoque]
table to connect to the [pedidos]
table.{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"
.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)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.{purchased}
field in the [Estoque]
table a formula: IF({name of the link field to the pedidos table}, "no", "yes")
Apr 24, 2021 10:19 PM
Kamile,
until the third stage, I managed to do it, but I didn’t quite complete the fourth stage. Could you explain it better?
Apr 24, 2021 10:27 PM
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
.
Apr 25, 2021 07:16 AM
Wow!! it looks like witchcraft. it worked very well!
Thanks for all your support and patience, Kamille. :heart_eyes: