4 Replies Latest reply on Aug 19, 2014 8:36 AM by philmodjunk

    Inventory Totals by Status

    Badam

      Title

      Inventory Totals by Status

      Post

           I have an inventory section of a solution and I am trying to get totals for particular part numbers based on the parts status.

           The different parts statuses are “On-order”, “On-Hand” and “Assigned” (to an order).

           The result I am looking for would be:

           For part number – 123 there are:

           5 - On-order

           10 - On-Hand

           7 – Assigned to orders

           I am looking for these to be 3 different fields that would appear in an inventory record when the user is looking at it. It would act as a summary of the inventory for that given part number. In other words, if you were looking at any inventory record for part number 123 you would know how many appear in the total inventory grouped by their status.

        • 1. Re: Inventory Totals by Status
          philmodjunk

               Are you using a transaction table to track inventory changes? (each time you receive or manufacture new product you add a record to log the increase in order, each time you ship out the product, or any other event that reduces inventory, you add a record that logs that reduction in inventory...)

          • 2. Re: Inventory Totals by Status
            Badam

                 Yes I am using a Transaction table (I call it "inventory history")

            • 3. Re: Inventory Totals by Status
              Badam

                   One thing that might be somewhat unique about this solution is that I need to keep history on each Item in the inventory such as all testing that has been performed on an item and things like that. Given that when I add items to inventory, if there were 3 items ordered I do not add one inventory record with a quantity of 3, instead I add 3 records with a quantity of one each. This allows me to track each items separately, and the items have serial numbers that we need to track as well.

              • 4. Re: Inventory Totals by Status
                philmodjunk

                     As long as you have "in" and "out" fields that log the addition and removal, whether you use one record for three items or 3 records doesn't make much difference.

                     First a very simple approach:

                     You can set up a summary field on the transactions table on a layout based on this table that does not have a body layout part but rather two sub summary layout parts. The first would be sorted by product name or ID to group your records by ID and provide a "sub header" for each part. A second sub summary layout part would be sorted by this status field. Summary fields can be placed inside both sub summary layout parts to show sub total type values for each group of records.

                     From your parts table, you can use ExecuteSQL to produce these totals: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                     Without ExecuteSQL, there are several other approaches that combine the product ID in one field with this status value to match to only records of a given status and product ID in order to compute a total.