Update Values from another table after a new raw was added

I need a little guidance

I have one table called “Final Products” with following fields:

UniqueID,Date,Maker Name,Final Product Type,Quantity,Damage

I am trying to update Values in another table called “Inventory” with following fields:

UniqueID,Reference,Date,Maker Name,Type Of Material,Final Product Type,Initial Stock,Product,Damage,Shipment,Low Inventory Alert

Reference is a reference to a UniqueID in “Final Products”

I am having a hard time to come-up with a solution to update “Inventory” on the event of new entries been added to “Final Products” . Once example would be super helpful

For example how would I make [Inventory][Product] equal to [Final Products][Quantity]

where [Final Products][Reference]=[Final Products][UniqueID] if both [Inventory][Product] and [Inventory][Reference] are calculated in the same time

I can possibly use two actions .

  1. add a new raw to “Inventory” with “Reference” and some dummy values
  2. update “Inventory” with values from “Final Products” somehow using “Reference” from the step #1

This is #covid-19 effort. Any help would be appreciated.

I tried to update value of Product in “Inventory” by

select(Final products[Quantity],[UniqueID] = [_THISROW].[Reference])

but getting an error :

The expression is valid but its result type ‘List’ is not one of the expected types: Number

The best way to accomplish this would be to create the following 2 actions and a workflow:

  1. Create an action of type “Data: set the values of some columns in this row” for the table “Inventory”. This is where you would set the formula for the column to be what you described above. ie. you would select the Product column with a formula, “any(select(Final Products[Quantity], [UniqueID]=[_thisrow].[Reference]))”. This tells appsheet to set the value of he Product column to the refrenced quantity in the Final products table.

  2. Create another action of type “Data: Execute an action on a set of rows”. This would be for the record in the “Final Products table” and the referenced table would be Inventory. The referenced action would be the action created in step1.

  3. Create a workflow that triggers on ADDS to the Final product table. Then under “do this” section of the workflow select “Change Data” and choose the action you created in step 2.

Following those 3 steps above should give you what you need. Let me know if you have any other qs.

1 Like

The select statement itself returns a list type, even if its a list of 1 value. To convert this to a single value, wrap the function with any(). This selects any value from the list. In your case it would select any value from a list of 1.

You can also use the lookup() function too which returns a single value.

1 Like

Thank you Rich. I came to the same solution.

Thank you
Constantin

1 Like