13 Replies Latest reply on Jan 16, 2014 9:42 AM by philmodjunk

    Multiple inventory management

    StephanToppinger

      Title

      Multiple inventory management

      Post

           HI!

           I am looking for some help regarding the following task, that's proven too difficult for me :(

           I have products in different shops in commission. I have one product list and all the shops has the same list. Some of them has some products from the list that others dont. However the prices can be different in these shops. 
           From time to time I have to restock these shops and sometimes I have to make the Invoice for the goods they've sold. 

           So I made these tables so far:

           SHOPS
           -SHOP_ID
           -name
           -address

           PRODUCTS
           -PRODUCT_ID
           -name
           -design date

           A joint table of these so I know which shop has what kind of products on what price:

           INVENTORY (in different shops)
           -SHOP_ID
           -PRODUCT_ID
           -price
           -current stock

           I have to make transactions (resupply and invoice). The products in the shops are not invoiced only if they sell it. Later if I want i should be able to search back by transaction. so:

           TRANSACTIONS (related to inventory through shop_id)
           -SHOP_ID
           -TRANSACTION_ID
           -Date
           -Kind (resupply or sell)
           -Total
           -Vat
           -Gross
            

           TRANSACTION_DETAILS (related to transactions and an other product table)
           -TRANSACTION_ID
           -PRODUCT_ID
           -quantity
           -price (i want to store the price cause if I change it in the Inventory I can still review it later)
           -total price

           So this is where Iam stuck Completely. I want to calculate the current stock in Inventory according to transactions. My Idea was to SUM up all the "total price" fields by shop_id and product_id and deduct (sale/invoice) or add (resupply) to my current stock in inventory table. But I dont have the slightest idea how. Or my database design is the problem.

           Please help me out...

        • 1. Re: Multiple inventory management
          philmodjunk

               What's the difference between Transactions and Transaction_Details?

               It looks like you could use one table instead of two--simplifying your design slightly.

               I've suggested something similar for inventory management here: Managing Inventory using a Transactions Ledger

               You might be able to glean some ideas from it.

          • 2. Re: Multiple inventory management
            StephanToppinger

                 It is needed since I have to give a number per transaction, like resupply form number or invoice number that contains the related products. This way I could look it up later. Same way as in Invoices starter solution (Invoice - Invoice details).

                 I have found your suggestion earlier but couldn't implement it.

            • 3. Re: Multiple inventory management
              philmodjunk

                   But why can't that be a field in Transactions? What info will you put into Transaction Details that you can't put directly into Transactions?

                    

              • 4. Re: Multiple inventory management
                StephanToppinger

                     But one transaction can have multiple products. That is to be deducted or added to Inventory by product and shop id. How can I group the products by transaction in any other way?

                • 5. Re: Multiple inventory management
                  philmodjunk

                       OK, I was reading "transaction" differently. If you look at the thread that I recommended. I called my version of Transaction "Invoice" and my version of transaction details, "transactions".

                       Thus, except for the name difference, what you have is very similar to what I set up in that other thread. I recommend reading it over to see how it computes inventory levels.

                       The main added detail in your case is to specify the location of each transaction, and via relationship to transaction details, to show inventory counts that can be specific to a particular store.

                       And yes, a transaction in your transaction table could add a list of different products to your inventory as well as to remove it.

                  • 6. Re: Multiple inventory management
                    StephanToppinger

                    I have rewritten the hole thing according to your tutorial (img attached). I'm afraid I still don't get the whole pic. How do I list the available stock in a shop layout? 

                    • 7. Re: Multiple inventory management
                      philmodjunk

                           Apologies. I wasn't actually suggesting that you redesign to match your database to mine. I was suggesting that some of the methods used in it could be applied to your system.

                           Take a look at the other thread's description of how you can group records by Product to get a group of transactions for each product and a running total of quantities on hand. If you first sort by ShopID, then by Product ID, you'll get a "ledger style" report showing all the transaction details for all the products at each store. You can also perform a find or use Go to Related records to pull up all transaction details for a given store to limit your view to just the transactions for one store. You could also pull up a found set of just a single product for a single store.

                           You still need that Inventory table where you have one record for each product sold at each store. Not only do you need that to track store specific pricing, but by linking an Tutorial: What are Table Occurrences? of it to TransactionDetails by both store and product ID, a calculation field can compute the total on hand for each product at that store. Sum ( TransactionDetails::NetAmount)

                           Performance Note:

                           The biggest draw back to this method for computing total on hand is that the time it takes to compute these totals gets longer and longer as more and more transaction detail records are added to the table. There are two methods you can use to improve performance:

                           Set up a scheduled "housekeeping" script that periodically "condenses" your transactions. Such a script can take a group of transaction detail records for a given product/store location compute the total NetAmount and then replace those multiple records with a single record with that computed total as the QtyIn. Often, such a script would first import those records into a different table so that you can still research past patterns in your inventory levels should that be needed.

                           Or use a script that takes the totals from these unstored calculation or summary fields and stores them in a simple number field in your Inventory table. Layouts that refer to this number field instead of summary or unstored calculation fields will display very quickly. The trick with this is to set up all the needed scripts/triggers in all the needed places so that any change to TransactionDetails automatically performs the right script to correctly update the number field.

                           And these methods are not mutually exclusive. You could employ both.

                      • 8. Re: Multiple inventory management
                        StephanToppinger

                             First of all thank you for taking time helping us all out.

                             The approach that you mentioned in the other thread is much better then mine was, so I chose to copy it :).

                             I ended up using this database design. Actually I only have problems with define the relationships to have the correct reports/lists.
                             I assume I am close.

                        • 9. Re: Multiple inventory management
                          philmodjunk

                               Sorry for not getting back to you more quickly. My boss had a death in the family and he closed his business for the funeral. That gave me a day off and I chose not to spend much of it accessing this forum. The result was that a lot of older posts got "buried" and I only now have had the time to check on them.

                               I guess that the only issue here come back to the Transactions table. I would probably add another Tutorial: What are Table Occurrences? of Shops to link to Transactions by Shop ID. I'd probably find that added relationship useful when setting up my layouts and scripts to work with this data model as it would let me display data about the shop right on the transactions model without needing to "tunnel" through the different occurrences to do so.

                          • 10. Re: Multiple inventory management
                            StephanToppinger

                                 Well I ended up using these relationships. I find it a bit too complicated but this is the way I can use conditional value lists and reports properly. I cant really find a way to simplify it.

                            • 11. Re: Multiple inventory management
                              philmodjunk

                                   One way to reduce the clutter is to use the anchor buoy method of organizing your table occurrences into groups.

                                   You can also use ExecuteSQL in FileMaker 12 or later to reduce the needed number of TO's.

                              • 12. Re: Multiple inventory management
                                StephanToppinger

                                     Hi thanks for your help I tried executeSQL but I cant make it work. I posted the a new post about it:

                                     http://forums.filemaker.com/posts/fbd72a96da

                                     Thanks

                                • 13. Re: Multiple inventory management
                                  philmodjunk

                                       To very good resources to refer to:

                                       Get SeedCode's SQL Explorer. It can actually create the needed SQL for you so it reduces the amount of ? results you get.

                                       See this SQL Reference put out by FileMaker Inc.: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                                       Only the stuff that applies to a SELECT query can be used with ExecuteSQL in that PDF document.