8 Replies Latest reply on Aug 21, 2011 4:39 PM by ZandriBanks

    Inventory calculation help

    ZandriBanks

      Title

      Inventory calculation help

      Post

      I have created an inventory database to control stock of various aircraft parts. Each item has an unique id but many of the part numbers repeat throughout the inventory. I would like to calculate the quantity available for each part number separately. I am a complete novice & any help would be greatly appreciated. Thanks!

        • 1. Re: Inventory calculation help
          philmodjunk

          Do you know how to use a check register?

          You can use a similar approach here where parts received are your deposits and parts consumed/lost/discontinued are your withdrawals. Calculation and Summary fields can then compute a running balance on each part to show you both the quantity on hand and also how your levels change over time--which can help you fine tune re-order points.

          Define at least the following fields:

          Date, PartNumber, In, Out, cBal, sBalance

          In and Out are the number fields where you create a new record and then record either the quantity added or removed from inventory.

          cBal is a calculation field: In - Out

          sBalance is a summary field that computes the total fo cBal. Set it up as a running balance, restart total when grouped by PartNumber.

          Set up a layout of these fields as either a list or table view layout. cBal can be left off the layout.

          As long as you keep your records sorted by PartNumber, then by Date, you'll see the inventory totals for every part in your inventory. You can perform a find for a specific partNumber and then sort your records to see the total for just that part.

          • 2. Re: Inventory calculation help
            ZandriBanks

            Thanks so much for your fast reponse. I have defined the fields you specified and have given them the appropriate values. First question:  Why do the records need to be sorted by part number & then date? (I'm specifically wondering about the date requirement as each part number has a unique id number as stock was received. Second question: After making the changes you suggested, the calculations for each unique id number is correct, but not for the same part numbers. I must be doing something wrong. Thanks again for your help! 

            • 3. Re: Inventory calculation help
              philmodjunk

              Why do the records need to be sorted by part number & then date?

              They don't actually. The quantity on hand for the last entry for each part will be the same whether or not you sort by date as long as you keep the enties sorted by partnumber. This just makes sure that you always have the correct running balance at each point in time.

              After making the changes you suggested, the calculations for each unique id number is correct, but not for the same part numbers. I must be doing something wrong. Thanks again for your help!

              I assumed that the part number and the unique ID would be one and the same. What "unique ID" are you describing here? Your sort order has to include the same field as the "group by" field specified in sBalance. And then it computes totals correctly only when the records are sorted to correctly group the records by that specified field.

              • 4. Re: Inventory calculation help
                ZandriBanks

                Each part number gets assigned a new id when it's received into stock. Thus after a while you could have several parts of the same part number but with different quantities & different id's. 

                eg: BHP10142 (id)    212-340-113-101 (pn)    2 (qty)

                     BHP11407 (id)    212-340-113-101 (pn)    6 (qty)

                     BHP10109 (id)    212-340-113-101 (pn)    1 (qty)

                I would like the total quantity for that pn to display as 9. Does that make more sense? My sort order includes the part number & so does the "group by" field in sBalance. 

                • 5. Re: Inventory calculation help
                  philmodjunk

                  Ok, so you need to sort your records by part number and set up the running total field to restart totals when grouped by part number. What I am suggesting ignores any value in the unique ID field. If you re-read the post where I first laid this out, you'll find that I made no mention of such a field. (Didn't know you had one.)

                  • 6. Re: Inventory calculation help
                    ZandriBanks

                    Thanks for your help. The sBalance seems to be working somewhat. Interestingly though, when viewing the record list or record detail, the first of say 2 /3 parts will have only it's own qty and it will increase with every part. Thus only the last of the specific part number shows actual total qty? 

                    eg: BHP10142 (id)    212-340-113-101 (pn)    2 (qty) (received qty 2)

                         BHP11407 (id)    212-340-113-101 (pn)    6 (qty) (received qty 4 + 2)

                         BHP10109 (id)    212-340-113-101 (pn)    9 (qty) (received qty 3 + 4 + 2)

                    • 7. Re: Inventory calculation help
                      philmodjunk

                      Yes, that's how a running total field works.

                      This should be a useful feature for you. This gives you a "history" of how your available inventory levels change over time. If you see that the level for one part is frequently reaching zero or a count near zero, this can be an indicator that you need to re-order this part more often by setting a higher "re-order point" (The inventory count that tells you it's time to order more.) If you see that available balance is always fairly high or is trending towards larger and larger balances, you might skip a re-order and/or reduce your re-order point for that part.

                      You may also find it useful to set up a "Parts Catalog" table where you have one record for each part you keep in invenory that you link by part number to this "ledger" table. If you do, you can place a copy of this summary field on a list or table view layout of your parts based on this Parts catalog and you'll be able to check current inventory counts at a glance.

                      • 8. Re: Inventory calculation help
                        ZandriBanks

                        Thanks so much! You've been a great help.