That is the method I use. The portal with the use of error trapping allows me to roll back the changes if there is a problem caused by a locked record, etc. Todd Geist's video on transactions got me started. https://www.geistinteractive.com/2010/04/21/inventory-transactions/
basically the same transactional model. But I don't do things on the same layout with a portal off-screen. I use a dedicated transaction layout based on a transaction table (the parent to the transaction), so that I have some good info on the # of transactions and info on who runs them when, which ones failed, etc.
Thanks Wim. I also have a transactions table that tracks the movement of the inventory.
In your model, do you keep the inventory quantity fields solely in the transaction table or do you have them in the inventory table? If you have them in the inventory table, what method do you use to update those quantities when you are on another layout/table such as Sales Order Line, Work Order Line, Invoice Line, etc.?
Thanks Todd (both Todds).
In the inventory table.
It gets a little more complex if you have multiple warehouses, then your transaction has to update the warehouse count and the overall inventory count.
The workflow is such that there is a "submit" or "post" point in that workflow. That takes care of the scripted navigation to the transaction table, updates what needs updating and takes the user to the next point in the workflow.
The transactions table is purely to record and execute the transaction so that I can have a history of the "movements".
Wim, I'm still a bit confused.
It looks like you go to the Transactions table to update movements. But where/how do you update the Inventory and Warehouse quantity fields? It looks like you don't use a portal off screen - does that mean your script (submit/post) navigates to the Inventory and Warehouse layouts to update those quantities?
No, not update movements.
Say that a sales order is created, proper line items for products sold, qties filled in. At some stage the sales order gets confirmed. That is the point in the workflow where I go to my transactions layout, create a new record to be the transaction parent, create the links to the sales order and the inventory to populate the portals on that layout and start updating the 'qty committed' and 'qty available' in the inventory (not the 'qty on hand' -> that one gets updated when the order gets picked in the warehouse).
If I don't have enough on hand for one of the products that is when the sales order gets marked with a certain qty back-ordered.
Got it. Thanks Wim.
One of the things I like about the portal method of updating inventory is that I don't have to change context until the transaction is complete. That is, I can stay on the Sales Order or Invoice layout and continue to add line items without having to navigate to three other windows (Lot, Item, and Transaction).
However, with FMP 13, it seems that Perform Script on Server could be employed to navigate to the various tables/layouts and update data (Lot and Item) as well as add data (Transaction) without the complexity of window management on the client.
Of course, this doesn't have the ability to Revert Record in the event to record lock. Nonetheless, I'm curious if anyone is using this method. Likewise, I'd like to hear the reasons for not using this method.
A couple of issues with PSoS:
1) the server deployment needs to be able to handle it - this is a very serious consideration that needs to be analyzed and quantified by doing a baseline performance monitoring to see what the current numbers are and extrapolate from that.
2) FMS has a setting for the max # of PSoS sessions that can get exceeded
3) the FMS scripting engine may fail
In both 2 and 3, the PSoS call will generate an error and you need to have code at the client to handle it; for instance proceed with the transaction at the client...
One of the things I like about the portal method of updating inventory is that I don't have to change context until the transaction is complete
This has not been an issue for me; changing the context to the transaction table fits in nicely with the scripted workflow; and it has the benefit of using the same context when coming in from different places.
I suggest you decide depending on whether you need one or both of these abilities:
- Search by current quantity
- Report historical quantity at arbitrary time in the past
We need the latter so we store quantities (and also total value) in transactions.
We have fields for
- id of the stock this transaction is for
- new current quantity per stock
- new current quantity total
- new current value per stock
- new current value total
To report quantities and value for specific timestamp we simply have to find last transaction preceding that timestamp for each item. Here's how to do that fast: Marvelous Optimization #1 | HOnza's Bits @ 24U
I appreciate hearing more about how other developers approach inventory quantity. You make a good point about obtaining the historical value of the quantity. It does seem to me, however, that you can search by the current quantity by keeping the those fields in the inventory table. Nonetheless, its a different approach I hadn't considered. Thanks.
The third item in your list is confirmation for me. If I need to have a backup process on the client I might as well always do the process on the client. I suppose if the process was really slow I could consider PSoS but that hasn't been the case so far.