1 2 3 4 Previous Next 56 Replies Latest reply on Jul 1, 2015 8:19 AM by ekrj

    Updating Inventory

    DanielShanahan

      I’m curious how other developers manage inventory quantities.  For quantity fields, I have onHand, available, and allocated.  The db is a manufacturing db and each inventory item has multiple lots.  There are primarily three times the quantity fields for items and lots are updated:

       

      1. When a finished good item is manufactured from raw material items. (Finished good onHand and available are incremented while raw materials onHand and available are decremented).

       

      2. When a lot/item is entered into an invoice line item. (available is decremented and allocated is incremented).

       

      3. At night time to adjust for shipped items. (allocated and onHand are decremented).

       

      The way I am currently doing this is using a named portal object on the layout but off screen (see attached).  A script navigates to the appropriate portals and updates the quantity fields accordingly.  This works well.  However, I’m curious what other methods are employed.

        • 1. Re: Updating Inventory
          mtwalker

          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/

          • 2. Re: Updating Inventory
            wimdecorte

            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.

            • 3. Re: Updating Inventory
              Todd Geist

              Here is some more recent stuff I did on transactions that you might find helpful

              https://www.geistinteractive.com/filemaker-transactions/

               

              Todd

              • 4. Re: Updating Inventory
                DanielShanahan

                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.

                • 5. Re: Updating Inventory
                  DanielShanahan

                  Thanks Todd (both Todds).

                  • 6. Re: Updating Inventory
                    wimdecorte

                    Hi Daniel,

                     

                    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".

                    • 7. Re: Updating Inventory
                      DanielShanahan

                      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?

                      • 8. Re: Updating Inventory
                        wimdecorte

                        Hi Daniel,

                         

                        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.

                        • 9. Re: Updating Inventory
                          DanielShanahan

                          Got it.  Thanks Wim.

                          • 10. Re: Updating Inventory
                            DanielShanahan

                            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.

                            • 11. Re: Updating Inventory
                              wimdecorte

                              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...

                               

                              DanielShanahan wrote:

                               

                              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.

                              • 12. Re: Updating Inventory
                                HOnza

                                Hi Daniel,

                                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

                                 

                                HTH

                                • 13. Re: Updating Inventory
                                  DanielShanahan

                                  Thanks HOnza.

                                   

                                  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.

                                  • 14. Re: Updating Inventory
                                    DanielShanahan

                                    Thanks Wim.

                                     

                                    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.

                                     

                                    Thanks.

                                    1 2 3 4 Previous Next