AnsweredAssumed Answered

Inventory Stock Reduction Problem with Repeating Record File

Question asked by rockchez on May 20, 2016
Latest reply on Jul 28, 2016 by 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.

Outcomes