How to use workflow to verify data

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 Solved
0 7 841
1 ACCEPTED SOLUTION

@Luke_Vancleave

SELECT(Movement Record[Key],AND([Item]=[_THISROW].[Item],[Date/Time]=TODAY()))

View solution in original post

7 REPLIES 7

LeventK
Participant V

@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


Step 2: Create the Datachange Workflow


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?

@Luke_Vancleave

SELECT(Movement Record[Key],AND([Item]=[_THISROW].[Item],[Date/Time]=TODAY()))

Works great! Thank you

LeventK
Participant V

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.

Top Labels in this Space