Hi, Iโm making an inventory App that uses one table as a record for what items moved from and to what locations. However Iโm trying to make an automated system that detects if someone creates a movement that makes the quantities at that location negative (moved more than were there). Once it detects that, it deletes that movement record and tells the user why it was an error and to make another one. Basically, I want a workflow that whenever a row is added to one table, to look at the values in another table and if they become less than zero, to delete that row and tell the user an error message. Any advice?
Solved! Go to Solution.
SELECT(Movement Record[Key],AND([Item]=[_THISROW].[Item],[Date/Time]=TODAY()))
@Luke_Vancleave
You need to set up 2 different workflows:
1.) An email workflow - to send the notification email
2.) A datachange workflow - to delete the row data
And you need to specify a conditional rule to trigger them i.e.
LOOKUP([Product Name],"Products","Name","Quantity") < 0
I donโt have any idea about your table structure and how you might set up your app, but just shouting out loud to express the idea. You can construct many similar expressions as per your app structure.
Hi, thanks for getting back to me. Iโve thrown in a couple pictures to maybe help explain what Iโm talking about. So in the first picture thereโs the table that uses the unique Item ID, From ID, To ID and how many I am moving. I only have one record in this case. This tells columns in tool list To sum the quantity if the movement record row is that Item, and if it was From or To. So in the second picture, my example movement record row said to move 5 of that item out of storage 1 which we can see reflected in the โmoved from storage 1 columnโ on the right. But since there were none of that item there to begin with, the quantity is now -5.
So Iโm trying to make a workflow that says, โWhen a new row in Movement Record is made, check the Quantity at Storage 1 column in the Tool List sheet if it is now less than zero, and if it is, delete the newly added row from movement recordโ
I seem to be having a lot of trouble with the workflowโs conditional formula syntax because I canโt figure out how to get it to read from the other sheet.
Thanks for the input!
@Luke_Vancleave
[A] Email Workflow
WHEN THIS HAPPENSโฆ
Target data > Movement Record
Update event > ADDS_ONLY
IF THIS IS TRUEโฆ
Condition >
LOOKUP([_THISROW].[Item],"Item List","Tool ID","Quantity at Storage 1") < 0
Set the the rest of the Email workflow as requiredโฆ
[B] Datachange Workflow
Step 1: Create a Behaviour Action
Create a Behavior Action for your Movement Record table and give a name as you like i.e. Delete Movement Record
In the image where you describe making the action that deletes the last row in the Movement Record, would you mind copy pasting what you wrote in the referenced rows formula?
SELECT(Movement Record[Key],AND([Item]=[_THISROW].[Item],[Date/Time]=TODAY()))
Works great! Thank you
Youโre welcome @Luke_Vancleave. Provided you can mark my post as the solution, it might be beneficial for other members as well, when they search for the solution tag. Thank you.
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |