Change item value depending on another table event

[dev2]

Hi all,

I’m struggling with some workflow, didn’t find out how to solve it.
I would like to update a column value in an inventory list, depending on its status, which is calculated from another table.

I have a table1 : “Inventory”
==> barcode
==> name
==>…
==> initial stock
==> restock level
==> real “date since restock needed”
==> virtual “restock needed”

And a table2 : “Line Item” with material inputs and outputs
==> barcode
==> timestamp
==> quantity
==> input/output
==>…

On the inventory list, I set a “restock level”, so that if an output causes the current stock to drop below a certain level, I set the virtual column “restock needed” to True.
It’s basically a sum : initial stock, minus output, plus inputs ==> if thats sums<restock level ==> True

I would like to get the datetime for this event.

Here is what I think about :

• create a workflow:
  ==>based on “Line Item”
  ==> all_changes trigger
  ==> condition : Here is my issue.
  I’m aware this is not correct, but here where I need your help: I would like something like that:

AND([_THISROW_BEFORE].“Inventory”[Restock Needed]<>[_THISROW_AFTER].“Inventory”[Restock Needed];
[_THISROW].“Inventory”[Restock Needed])
==>do this : action “set current product restock need date to NOW()”
(normally OK with this part)

I tried to make a workflow based on the Inventory Table, so that if “Restock Needed” switches to “true” then I set current datetime. But because it’s a calculation, I guess the change of result of it is not considered as an update, which eventually does not do anything.

Many thanks in advance for your help

Aurelien

[Steve]

A workflow is triggered when a table change occurs. In your case, the table that’s changing is Line Item, so your workflow must be attached to Line Item, not Inventory. The Line Item workflow’s action then needs to reach over to the Inventory table to adjust it.

[dev2]

Hi Steve

Yes that’s what I guessed and that what I eventually started to do.
I’m struggling on the condition formula : do you have a clue ?

[LeventK]

Is there a ref between your Line Item and Inventory?

[dev2]

Hi @LeventK

Yes, this is the “barcode” column

[dev2]

HI @LeventK and @Steve,

I’ve been adopting another strategy since we started to discuss:

• use a workflow1 based on Line Item, with condition: if related “restock needed” status from Inventory is true, then set current date on a column “Trigger”, which I added on the Inventory Table
• and then, on google sheets use a formula to filter datas and get the expected date.
• use a worlflow2, based on Line Item, with condition: when the related “restock needed” status is false, then I fire another action that clears all trigger related to this item on “Line Item” Table.

It seems to work.
Maybe you have a clue to make it more efficient ?

Step by step, in case this might help, here is what I made:

The formula used on G-Sheets, on Produits/Inventory Table, is:
=array_constrain(FILTER('Line Item'!$E$2:$E,'Line Item'!$B$2:$B=A3),1,1)
Where column E2:E is the Trigger colmun, e.g. date set when restock needed is detected; B2:B and A3 are “Product Barcode”, respectively on Line Item and Produits (Inventory)

Workflow1 ==> On Change Value in 'Line Item" - setTriggerRestockNeeded
Condition :

ANY(
    SELECT(
           Produits[Restock Needed], 
           [Product Barcode]=[_THISROW].[Product Barcode]
          )
   )=true

Action:

setTriggerRestockNeeded

Which is:

Workflow2 ==> On Change Value in 'Line Item" - removeTriggerRestockNeeded

Condition:

ANY(
    SELECT(
           Produits[Restock Needed], 
           [Product Barcode]=[_THISROW].[Product Barcode]
          )
   )=true

Action (execute an action on a set of rows):
getTriggerRowsWithRestockNeededToDelete

Which is:

Formula within it is:

SELECT(
       Line Item[Key],
       AND([Product Barcode]= [_THISROW].[Product Barcode],
           NOT(ISBLANK([Trigger]))
          )
       )

And referenced action is (set the values of some columns in this row):