Data Validity Before Sync

[Luke_Vancleave]

Hi I’ve got an issue I’m looking for any advice on. I’m making an inventory database app. I have a list of Items and a list of Locations. I then have a list of Items At A Location and their quantity based on a list of Movement records. I have a workflow make a new Item At A Location row if there was no quantity of that Item there and then have a virtual column calculate the quantity by adding or subtracting the quantities of that item based on the Movement records to and from that location. Then if the quantity of an Item At A Location reaches zero, a workflow deletes that row. I also have a data validity check when making a new Movement record row that doesn’t allow for more quantity to be moved FROM that Location than currently are in it.

This allows me to have an unlimited number of things move to an unlimited number of places and have there quantity divided any number of times between them. I have a problem, however.

To create a new Movement row, I have a grouped action attached to my Item At A Location row where I press it and it creates a new Movement row saying THIS Item from THIS Location and then takes the user to further edit that Movement row by finishing where it is going to and the quantity. This means there are two actions that need to be synced before the virtual column that calculates quantity on the Item At A Location row. This means that with Delayed Sync on, the user can press the button to execute that same grouped action again and create a duplicate Movement record before it has had time to sync the change in quantity. This means that if the user is fast enough, they can create quantity of an Item that don’t exist.

I’m wondering if there is any way to effectively stop this without turning on the Forced Sync option because if I do this, it has to sync FIRST when the new movement row is created and then AGAIN after the user finished the form with the destination and quantity.

[Luke_Vancleave · Accepted solution]

I added another column to my Item At A Location row that acts as a kind of quantity checker. Now there is an action that adds up the quantity similarly to the virtual column but does so by an action (I basically just copy pasted the same expression from the virtual column). I made this action first of the grouped actions that happen when I made a new Movement record row to give it the most up to date data which the data validity from the Movement record form checks against.

Basically, having the Movement form check against the Item At A Location’s virtual column meant that it only had the actual quantity after all changes had been synced. This new method forces the Movement form to check against an Item At A Location’s quantity INCLUDING all un-synced Movement record rows.