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 :
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
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.
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 ?
Is there a ref between your Line Item and Inventory?
Hi @LeventK
Yes, this is the “barcode” column
I’ve been adopting another strategy since we started to discuss:
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):
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |