Which version of FileMaker are you using?
If you are using FileMaker 10, you could setup a Script Trigger on you inbound_order_status field that will perform a script doing the following
If [ inbound_order_status = "Completed" ]
Set Field [ current_stock ; current_stock + inbound_order_quantity ]
This will then add the two quantities together for you only if the status is "Completed"
To set a Script Trigger, in Layout Mode select your field and go: 'Format > Set Script Trigger...' and OnObjectModify is probably the best one for the job, or OnObjectExit, and I would also suggest the field be a pop-up menu of radio button field, if not already.
Hope this helps
Thanks very much - works perfectly!
And yes, I'm using FM 10
…And what happens if the user accidentally selects status "Completed" a second time?
I suppose that would all depend on how the record is treated after it has been marked as "Completed" and whether or not it is locked down and made read-only.
Which can be done any number of ways. Using Account & Privileges to lock the record.
An OnObjectEnter Trigger applied to the field that runs a script to stop the user being able to enter into the 'inbound_order_status' field if it is marked as complete.
Or the user views completed records on a different layout that does not allow data entry into that or any field for the record.
Let me know if you want more detail on any of the options.
Thanks for your analysis.
Having worked with OE systems for some time (decades), I have learned that a lot can happen here. (User completes the wrong order-item; customer cancels the order; customer changes the ordered quantity; order is shorted; portion of ordered quantity is cross-shipped to a higher priority customer; etc., etc.)
Locking down the record is probably too restrictive depending on how robust the design requirements are.
I suggest updating a new field (quantity completed) when the status is changed to completed so that it can be backed out (reversed) if need be.