14 Replies Latest reply on Jul 28, 2016 7:59 AM by rockchez

    Inventory Stock Reduction Problem with Repeating Record File

    rockchez

      I need the ability to temporarily show the reduction in available inventory for items ordered, but not yet delivered.

       

      The system was started on a 512E computer in the 1980's when my wholesale cheese business began, using example files that came with Filemaker.  Though expanded greatly (there are more buttons and little files than you could shake a script at), Filemaker in all its versions has never failed.  The Filemaker Community's posts have often solved problems I've encountered.  I thank you all.

       

      After these decades, I am making my first post to the Filemaker Community.  I know I'm a dinosaur for using files, not tables and for using repeating records for the Current Invoices file, but we're planning a system wide redesign in two years.

       

      Overview of Operation.

           OS:  FM 14.0.5 Advanced on iMacs with OS X 10.10.5

           Magnitude:  900 Products, 60,000 invoiced items per year

       

      Pertinent Files and Pertinent Data Elements:

           Current Invoices - Each record is one invoice containing multiple items :

                      _pkCustomerCode

                    w/Repeating data for each item ordered:

                              _fkItemCode

                                sBal   (the amount of stock that is available for sale for each _fkItemCode at the end of the previous week.  It is a looked up value from Products)

                               QtyOrdered   

                               QtyDelivered     (may be different from QtyOrdered and reflects the realities of customer rejection, staff errors in pulling stock, etc. )         

       

           Products - One Record for each item:

                      _pkItemCode  

                     sBal     (Many thanks to philmodjunk ) (File is updated weekly from Inventory File)

       

           Inventory - One Record for each line item sold:

                     _pkItemCode   

                     QtyIN   

                     QtyDelivered    

                     sBal = Summary field reflecting the running total of (QtyIN - QtyDelivered ) that restarts "when grouped by _pkItemCode"    (Repeating records from Current Invoices are split when added to this file weekly after delivery of items.)

       

      Considerations:

           Staff appreciate having sBal available in Current Invoices when entering new orders, but as weekly orders are added to Current Invoices, sBal does not reflect commitments made by previously entered orders.  They desire inventory values that reflect new orders in Current Invoices  ( e.g. by Item Code,   QtyAvail  =  sBal - sum of QtyOrdered)

       

      Problem:

           How to calculate and display in real time the QtyAvail value for each item in Current Invoices.

           I've looked at creating a TemporaryTransactionLog file on the fly, but am stumped as to how to do this.

       

      Fini:  Any thoughts short of eliminating the repeating record nature of the Current Invoices file would be appreciated.  Thank you.

        • 1. Re: Inventory Stock Reduction Problem with Repeating Record File
          planteg

          Hi rockchez,

           

          what do you mean here:

           

          sBal = Summary field reflecting the running total of (QtyIN - QtyDelivered ) that restarts "when grouped by _pkItemCode" (Repeating records from Current Invoices are split when added to this file weekly after delivery of items.)

          ?

          Usually when maintaining a permanent inventory, there is a table that stores all of the inventory movements:

          • acquisition - increase quantity
          • sale - decrease quantity
          • lost or damaged - decrease quantity

           

          I don,t know though if you need to get all these details. Regarding having a current balance, it's always kind of complicated, because you need to do calculation and if you have loads of movements, that can be lengthy.

           

          One way things are done in an ERP I know (SAP BusinessOne), is that they maintain calculated results in a table: that is each time there is a transaction, they add or subtract quantities. This way, the total is readily available. But that needs to be bulletproof... thing is once in a while, the total is wrong and we need to start a process that recalculates the totals, and sometimes there are bugs that causes invalid numbers.

          • 2. Re: Inventory Stock Reduction Problem with Repeating Record File
            rockchez

            Thanks for your remarks.

             

            The Inventory file has many records - one for each item in each invoice plus stock acquisition records.  The small volume of stock acquisition records contain _pfItemCode and amounts (QtyIN) which increases quantity, while most are invoiced items records containing _pfItemCode and QtyDelivered which decreases quantity.  Each record has a field that calculates the difference (QtyIN - QtyDelivered), actually named cBal.

             

            sBal is defined to provide the total of cBal (QtyIN - QtyDelivered) when sorted by _pfItemCode.  sBal from Inventory is copied into Products weekly.

             

            Here is a reference to a post made by philmodjunk a while back.  https://community.filemaker.com/thread/112621

            • 3. Re: Inventory Stock Reduction Problem with Repeating Record File
              rockchez

              I should further explain my question to the Community. 

               

              Current Invoices is a repeating record file containing new invoices for the current week.  Item codes ( _pkItemCode) are entered on each line which looks up the amount available in inventory at the end of the previous week ( sBal) for that item.  When an order is entered, staff can determine if there is enough stock available to fill the order.

               

              However, as orders continue to be entered during the week, sBal becomes less meaningfull because there may have been orders previously entered for a particular item code.  Now they have to manually find the total of already entered customer's orders.

               

              When staff enter an Item Code, I would like them to be able to see how much has been previously ordered for that item.   i.e., a continuous summary of QtyOrdered by Item Code  e.g. sQtyOrdered

               

              Having this continuous summary available allows real time calculation of the amount of stock available as orders are entered. QtyAvail = sBal minus sQtyOrdered.

               

              Any and all advice and counsel is welcome.

              • 4. Re: Inventory Stock Reduction Problem with Repeating Record File
                greatgrey

                I’m a little puzzled as to why do your order enterers need to know how much has been ordered and by who versa just needing to know how much stock you have on hand that is available for sale.

                It seems at the very least when the sBal is updated you copy it to QtyAvail and then subtract from QtyAvail each customer’s ordered amount when the order is made. Of course add any new stock received.

                • 5. Re: Inventory Stock Reduction Problem with Repeating Record File
                  siplus

                  IMHO any adjustment to your repeating field-based system is a bit a waste of time, given that you want to redesign.

                   

                  Why wait 2 years ? Start NOW creating your system as it will be and beginning Jan 1st, 2017 use it. Your old system will run on a second track, dealing with open orders etc. until it ceases to be feed with info and becomes an archive of past operations.

                   

                  However, without wreaking havoc into your actual structure, you can begin by creating some ExecuteSQLs that will sum up stuff on demand and deliver the results in $$vars, set as merge fields on specific layouts (or popovers).

                  • 6. Re: Inventory Stock Reduction Problem with Repeating Record File
                    electon

                    If you manage orders and invoices with the same table you need to decide how to distinguish an order from an invoice. Either a status or some other field.

                    Then get the sum of qtyOrdered by product code from the filtered order items table and subtract from sBal.

                     

                    I've never figured out how to do this easy in Relationship Graph without creating funky relationships.

                    Better to do it with ExecuteSQL.

                     

                    Let's say you defined an invoice by a number field "isInvoiced" that you set to 1 when order is converted to invoice.

                    I'll call your repeating data table : "OrderItems" for now.

                    The current inventory quantity is looked up from products, sBal

                    You could place this in an unstored calculation field in OrderItems or use some other means like a button bar ( allows you to specify calculations )

                     

                    Let ([

                         ~itemCode = OrderItems::fk_ItemCode ;

                         ~currentQty = OrderItems::sBal ;

                     

                         ~sql =

                              "SELECT SUM ( OrderItems.qtyOrdered ) FROM OrderItems

                               INNER JOIN \"Current Invoices\" ON  OrderItems.\"_fkInvoiceID\" = \"Current Invoices\".\"_pkInvoiceID\"

                               WHERE \"Current Invoices\".isInvoiced <> 1 AND OrderItems.\"_fkItemCode =?"

                     

                         ~sqlData = ExecuteSQL ( ~sql ; "" ; "" ; ~itemCode )

                     

                         ];

                     

                    //  now the result. sql results are text so I like to wrap them in filemaker data type.

                    //  if you want to see the results of sql for testing use the ~sqlData as return parameter

                     

                         ~currentQty - GetAsNumber ( ~sqlData )

                     

                    )

                     

                    It's a workaround for now until you rewrite the solution.

                    • 7. Re: Inventory Stock Reduction Problem with Repeating Record File
                      karina

                      Hi,

                       

                      I made you a sample file.

                      Hope it helps you.

                       

                      Greetz,

                      Karina

                      • 8. Re: Inventory Stock Reduction Problem with Repeating Record File
                        rockchez

                        Thanks for the sample file Karina.  I tested it a bit and it works well with the order table, but when I redefined several field to be repeating records, it doesn't seem to work.

                         

                        Thanks for your efforts.  They are much appreciated.

                         

                        Bill Rock

                        • 9. Re: Inventory Stock Reduction Problem with Repeating Record File
                          greatgrey

                          You just found out why repeating fields are not recommended for most data tracking but, sub records key to the master order record. There are others better able to explain it.

                          • 10. Re: Inventory Stock Reduction Problem with Repeating Record File
                            rockchez

                            Indeed so greatgrey - the file was started in the late 1980’s, before FM had tables. It was a blessing at the time to easily create invoice records.  It has served me well.  Cleaning it up before final departure will be a huge job.  I might need professional help.

                             

                            I want to thank everyone who has commented and provided help with this problem.  Its not the world’s biggest issue, but I’d like to tackle it (for function and as a challenge).

                             

                            Bill Rock

                            • 11. Re: Inventory Stock Reduction Problem with Repeating Record File
                              greatgrey

                              Each file you have is equal to a table. But it not necessary to combine them into one file to take advantage of portals and subrecords. As you say you a two year time frame I think learning to use subrecords would be beneficial

                              Given that you want to remake the system.

                              Start with one area such as inputting customer data then getting the right customer, then inventory  and then add basic ordering then add details such as canceled orders or miss filled orders, etc. What I'm saying is work on one step at a time also try not have One script do everything. i.e. one script gets the customer, one adds a new customer, one get the order, one cancels orders, one add to the inventory, one removes from the inventory, etc. It makes for much easier debugging. Now you may have one or mores scripts that calls them as necessary for updating inventory and other bookkeeping.

                              Now that is a lot of scripts and why you need to use folders and separator in your manage scripts window even though most or all won't show in the Script Menu.

                              • 12. Re: Inventory Stock Reduction Problem with Repeating Record File
                                rockchez

                                Hello Again:  I've tried the sql solution without success.  Here is my problem boiled down as much as possible should there be any interest in solving.  Thanking in advance.  Bill Rock

                                 

                                File Name:  Current Invoices  (contains about 200 invoices for the current week)

                                Current Invoices - Data elements.

                                     _fkInvoiceID is unique ID for each invoice.

                                 

                                     Repeating Data Elements (30 repetitions)

                                          _pkItemCode

                                          QtyOrdered

                                          sBal - Looked up Inventory amount for _pkItemCode at end of the previous week.

                                 

                                I wish to have an unstored QtyAvail calculation ( for each _pkItemCode)   QtyAvail = sBal - QtyOrdered 

                                 

                                after the First Invoice is entered, its information will look like:

                                  _fkInvoiceID = 157634

                                _pkItem Code  QtyOrdered  sBal  QtyAvail

                                2499                 10             100       90

                                2501                 10              200       190

                                 

                                after the Second Invoice is entered, its information will look like:

                                  _fkInvoiceID = 157635

                                _pkItem Code  QtyOrdered  sBal  QtyAvail

                                2501                 20            200       170

                                2499                 20            100       70

                                • 13. Re: Inventory Stock Reduction Problem with Repeating Record File
                                  greatgrey

                                  you may need a script and a couple script triggers do it. One trigger when you open the record and one to reflect the change once the order amount is entered.

                                  • 14. Re: Inventory Stock Reduction Problem with Repeating Record File
                                    rockchez

                                    Thanks for the suggestion greatgrey.  Any help with the script steps to solve the problem will be appreciated.  Bill