14 Replies Latest reply on Jun 2, 2016 8:14 AM by DanielShanahan

    MRP best practice

    pfroelicher

      We use an in-house MRP system for our production of cosmetics. I wrote the system some five years ago.

      Since the beginning I always wondered how systems like SAP or other MRP ERP solutions handle this question.

      The question is: Every product you have in your database has a count, lets call it "number_in_stock".  It is everything you ever bought of this item minus everything you ever sold plus everything you ever produced minus everything you used in the production process.

      Our 5 year old system handles this very well.

      However the other day I deleted some 5 year old invoices of purchases which had some obsolete data on them.... and... obviously out "number_in_stock" figure showed negative.. because the purchase orders five years old got deleted.

      I thought I should come up with a way to make this system more secure.

      We check our inventory each month and correct the numbers, sometimes something gets typed wrongly, something gets lost/stolen/missing or whatever..hehe.

      Would there be an elegant way to make this inventory dates be new " set points" for the system, so that older events, purchase orders, sales orders, production orders or productions order items would NOT counted anymore.

      How do they do in SAP, which is designed for BIIIG companies.

      Any ideas? Was I even clear :-)

      Thanks Pierre

        • 1. Re: MRP best practice
          beverly

          this may be like one solution where I don't have a summary (or calc) Total for invoices, but a Set Field (triggered) script. Change qty (or add/remove) in line items and the total is 're-set'.

           

          Perhaps this with backups of what you've archived?

           

          If you are "adjusting" by doing inventory (as many companies do), then this can be scripted to change the value, too.

           

          beverly

          1 of 1 people found this helpful
          • 2. Re: MRP best practice
            pfroelicher

            Hi Beverly,

            thanks for your answer.

            I think it is more complex that scripting summary with Set(field) commands.
            I am looking for a way to set fixed points in time in a historically growing set of records, so that older records do not get contemplated anymore.

            Yours

            Pierre

            • 3. Re: MRP best practice
              beverly

              Yes, and placing data into field(s) rather than relying on calculations or related data or any "non-stored" values is the way to have "fixed" data.

               

              beverly

              • 4. Re: MRP best practice
                pfroelicher

                Beverly, Thank you

                 

                I agree that this is the way to fix data.

                However I thought that you, or someone could ponder a bit on HOW these things are done on a bigger scale.

                 

                As I said, my solution, basically an Inventory system MRP would have to have set points in 5-10 tables where records before the "inventory control" date would have to be "archived", "made unchangeable" or similar.

                 

                For example in a Bank. There you basically only have one "file"... your account. It starts on a certain date, some years ago with the payment of your father that puts in 5 USD to start the account. 20 years later I am sure that if the bank looses that data point of +5 USD@ 10/01/1973 I am sure that your balance as of today would not get -5USD.. why? Because I assume Banks make something like "montly closing",

                How is this done? In a MRP?

                Thanks

                P

                • 5. Re: MRP best practice
                  robmertens

                  In the system i built (also about five years ago) i opted for a few extra fields with an entry date, when i want to see totals i filter them in a portal only showing from mmddyyyy to mmddyyyy, that way a summarize only shows totals for a certain period.

                  • 6. Re: MRP best practice
                    DanielShanahan

                    Do you have a Transaction table?  That would be the place to store 5 years worth of movements.  The quantity fields (on hand, available, allocated, etc.) would be updated each time a Transaction record is made.  That keeps the quantity fields current and indexable.

                     

                    You would also be able to remove old purchase orders and invoices as their values would not be needed to update the quantity fields.

                    1 of 1 people found this helpful
                    • 7. Re: MRP best practice
                      pfroelicher

                      Hi Daniel,

                      thanks for you answer. I just saw it today.. sorry.

                      Well.. as you might see I have about 5 transaction tables, purchase_items, production_orders_items, sales_items and some more that can influence the "number_of_itens_on_hand".

                       

                      Now... If I delete a Purchase order which is five years old... the items sold on that order will go missing in my "number_of_itens_on_hand calculation...

                      Is there a way to do it better?

                      Yours

                      Pierre

                      • 8. Re: MRP best practice
                        electon

                        Banks usually store previous and current balance with every transaction. If old data is deleted, you still have the totals.

                         

                        I don't know exactly how things are done on a bigger scale but those are SQL engines. Far more flexible than filemaker is. you have full transactional approach, triggers, stored procedures etc.

                         

                        I have at least two tables for simple inventory:

                        inventory log where I log every change ( qtyChange, qtyOnHand [after change], timestamp, type of transaction [ receive, ship, ... ]

                        inventory ( product id, qtyOnHand [stored] , qtyAllocated [unstored], qtyOrdered[unstored], .... )

                         

                        The log gives me inventory movement tracking ( graphs ) and history.

                         

                        The unstored fields have filtered relationships setup to purchases and orders based on their status.

                        When PO is received, the qtyOnHand is increased and status changed so it no longer takes part in the calculation.

                         

                        The way it's set up, I only ever look at these unstored fields one record at a time.

                        The more records, the more calculations and I've figured I will deal with inventory item by item anyway.

                         

                        It's all scripted with specific transactional workflows and a lot of error checking. It must be really tight to keep it in sync, especially in multi user environment.

                        If specific inventory drops to zero I delete the inventory record.

                         

                        HTH.

                        1 of 1 people found this helpful
                        • 9. Re: MRP best practice
                          DanielShanahan

                          pfroelicher wrote:

                          ...I have about 5 transaction tables, purchase_items, production_orders_items, sales_items and some more that can influence the "number_of_itens_on_hand"...Is there a way to do it better?

                          I suggest having one table for transactions.  I've also started to keep the item quantities (On Hand, Allocated, Available, etc.) in a separate table with a 1:1 relationship to the ITEM table.

                           

                          See if the attached file helps.

                          1 of 1 people found this helpful
                          • 10. Re: MRP best practice
                            karina

                            Hi,

                             

                            We use a table stock and de PK = lot number and a table stock transactions with an PK = stocktransactionID and SK=Lot number.

                            We have an inventory script that creates a record in the table stock transactions if there's an difference between the stock and the inventory.

                            The lot number can also be an article number if you use one record for each article in the stock table. We use lot number an article number separately because we can have articles in several locations.

                             

                            We also use this in an ERP system  (all Filemaker) with sales orders, invoices, pick lists, production orders etc. etc.

                             

                            To solve the deletion problem you can make an export from a backup in excel sheet with the old data fields you need for example Article, quantity, transaction date, Production number, Sales order number , Invoice number and then import it in the new table stock transactions.

                            Do the same with your current stock.

                             

                            Hope this helps.

                            • 11. Re: MRP best practice
                              pfroelicher

                              Daniel,

                              I am analyzing your file. Thanks a lot.

                              Hmm.. Do not yet know why making script in purchases PO, sale SO and so forth, writing into an TRANSACTION table and the reading that table is much more easier than reading directly the SUM of PO and SUM of SO.. as it is is now in my Solution. . ..

                               

                              Or do you update the qty's periodically by script reading the totals of the transaction table?

                               

                              But...hey...great stuff.. (Did you write this all for me??)) :-).

                               

                              Thanks

                              Pierre

                              • 12. Re: MRP best practice
                                DanielShanahan

                                pfroelicher wrote:

                                Do not yet know why making script in purchases PO, sale SO and so forth, writing into an TRANSACTION table and the reading that table is much more easier than reading directly the SUM of PO and SUM of SO.. as it is is now in my Solution. . ..

                                It is a standard practice in inventory management systems to have a Transaction table to monitor the movement of items from the time of entry through the time of departure (including assembly, if the organization is a manufacturer or creates kits).

                                 

                                I use a script to update the quantities rather than a field calculation.  That way the quantity fields (On Hand, Allocated, Available, On Order, Backorder, etc.) can be indexed and quickly queried.

                                 

                                One other thing about the file is that the 1:1 relationship with Quantity table (where the quantity fields live) means that there is minimal chance of human record locking.  That is, if someone is editing a field, then takes a phone call, then goes to lunch, etc. then that field cannot be updated.  The 1:1 Quantity table only updates via a script so I loop through to check if the record is locked (by a previously run script) and run the loop until the record is free.  If the record is locked from a previous script I suspect it is milliseconds until is it available (but I haven't measured this).

                                 

                                pfroelicher wrote:

                                 

                                But...hey...great stuff.. (Did you write this all for me??)) :-).

                                Thanks - I'm glad you like it.  I do sometimes create files for discussion threads, but not this one.  Rather, this file came as a result of this discussion thread where I asked other developers to describe their method for updating inventory.  I still find that thread helpful.

                                • 13. Re: MRP best practice
                                  pfroelicher

                                  Daniel,

                                  Yes..liked it a lot.. .actually I should give your answer the "correct answer" tag.. It is one hell of a step into the right direction..

                                   

                                  Your "this discussion thread"does somehow not work.. could you indicate the right discussion please.

                                   

                                  Yours

                                  Pierre

                                  • 14. Re: MRP best practice
                                    DanielShanahan

                                    pfroelicher wrote:

                                     

                                    Your "this discussion thread"does somehow not work.. could you indicate the right discussion please.

                                    Sorry Pierre.  My link failed in another thread as well.  Sloppy copying on my part!

                                     

                                    Try this: Updating Inventory