8 Replies Latest reply on Jul 21, 2011 8:28 AM by Matty_1

    Subsummery calulation for inventory

    Matty_1

      Title

      Subsummery calulation for inventory

      Post

      Hello,

      I'm currently working on a grain inventory data base.  I'm having a hard time coming up with a "sumif" type function to properly track what comes into and leaves the system all while having a real time sum of the difference.

      My current design consists of an "Interim" table where all purchases are entered (Fields calculated: Type, Net_weight).

      On the flip side, I have a "Sales" table where all out going sales are done (Fields calculated: Type, Quantity).

      I'm trying to get an Inventory page working where I can have a real time idea of how much product we currently have in house.

      So far I was able to get the "TypeSumField" (here) trick working but this breaks when I delete a ticket.  The total is only correct when a change is made to the particular grain type either on a new ticket or an old one.

      Help will be GREATLY appreciated!

      M.

        • 1. Re: Subsummery calulation for inventory
          philmodjunk

          There are different ways to do this depending on how you structure your tables.

          If you have a third table where you have one record for each inventory category, you can establish relationships like this:

          Inventory::Type = Interim::Type

          Inventory::Type = Sales::Type

          Then you can define a calculation field in Inventory as:

          Sum ( Interim::Net_weight ) - Sum ( Sales::quantity )

          To compute the quantity on hand for each type of item listed in the inventory table.

          If you were to combine the records for Interim and sales in a single table, you could set this up as a ledger where inventory received is a 'debit' and inventory removed is a 'credit' with summary fields computing running totals for each type of grain.

          • 2. Re: Subsummery calulation for inventory
            Matty_1

            Thanks Phil, for some reasom I had it stuck in my mind that the two could not live on the same table but I can't seem to pin point the reason why.  they do have two different layouts but that doesn't matter.  I tall ya somtimes you're too focued on one task to pull yourself back and see the big pciture.  THANKS!!

            Know of a quick trick to have sub summeries in the form view?

            • 3. Re: Subsummery calulation for inventory
              philmodjunk

              With the first option I described, you don't need them. Each record in the Inventory table represents one "sub summary".

              In a merged table, you use "total of" summary fields for this, but put them in a sub summary layout part "when sorted by" the Type field.

              Then you find the records you want for your report and sort them by Type to group them and the sub summary parts show your sub totals.

              You can even delete the body layout part and just have sub summary parts if you want to see just the sub totals.

              • 4. Re: Subsummery calulation for inventory
                Matty_1

                Thank you Phil, I now remember why I needed to keep them seperate.  They need their own set of incrementing IDs which I use to keep track of transactions. My problem now is that my sales can sometimes contain multiple types so I created a repeating field for the type and of course quanity.  Is there a way to "extend" a relationship or is a portal my only way out?

                • 5. Re: Subsummery calulation for inventory
                  philmodjunk

                  Do not use a repeating field for this--that will produce nothing but trouble for you here.

                  Use a table of related records displayed in a portal.

                  See this invoicing demo produced by Comment as an example: http://fmforums.com/forum/showpost.php?post/309136/

                  This same basic structure works for both buying and selling inventory (and other inventory changes that are neither) and the LineItems table can be adaped to serve as your "ledger" table for listing all inventory changes

                  Don't really see any need for each type of line item transaction to have it's own ID series. I think this will be handled for you by adding Purchasing and Sales tables that link to this common "Ledger" or "line items" table. (The quantity of items removed from inventory is recorded in one field in this table and the quantity added is recored in a separate field--with calculation and summary fields used to compute a running balance that both shows the current amoung inventory and also shows you how your inventory levels have changed over time--which can often be helpful when evaluating adjustments to your inventory management methods.)

                  • 6. Re: Subsummery calulation for inventory
                    Matty_1

                    These tickets go out to the customers and venders much like a way bill or an invoice hense the ID series.  I've implemented the portal and everything is working as I need it.  Last question, much like a trouble ticket I have a field that displays whether the bill of sale is "Open" or "Closed" (sometimes we can't complete a bill of sale till a later date) and I'd like an on going count of "Open" tickets.  Can't seem to get the Count function working.

                    The fields name is "Record_status" on the table "Sales"

                    • 7. Re: Subsummery calulation for inventory
                      philmodjunk

                      These tickets go out to the customers and venders much like a way bill or an invoice hense the ID series. That's what I thought, but the inventory transaction table does not need to generate these numbers. That's done from the Invoice and Purchase order tables that each have their own portal to the Inventory Transaction (ledger) table. 

                      Count gives you a count of related records where the referenced field is not empty. To get a count of only "open" invoices, you can either perform a find for all Invoices with "Open" in your status field and count the reocrds found, set up a relationship that only matches to invoices with "open" in this field, or (FileMaker 11 only) use a one row portal to sales that uses an X operator in its relationship to related to all Sales, but a portal filter limits it to only those with a status of Open. A "count of" summary field counting some never empty field in the Sales table can then be put in the portal row to display the count of open sales.

                      To set up a relationship that only matches to open sales, define a caclulation field, constOpen to return the literal text: "Open". Then create a new occurrence of Sales and link it to Sales like this:

                      Sales::constOpen = OpenSales::Record_status

                      Then Count ( OpenSales::InvoiceID ) will give you a count of all open Invoices (any never empty field in Sales will work here).

                      You create OpenSales by selecting Sales in Manage | Database | Relationships then clicking the buttton with two green plus signs. (Double click the new occurence to open a dialog where you can rename it "OpenSales".)

                      • 8. Re: Subsummery calulation for inventory
                        Matty_1

                        That's what I thought, but the inventory transaction table does not need to generate these numbers. That's done from the Invoice and Purchase order tables that each have their own portal to the Inventory Transaction (ledger) table.  Agreed, I do have a serial ID running in the BG on my ledger just incase I would for some reason need to refer to it in the future but I'm not actually using it.

                        As for the counter, I used your X operator method and it works thank you!  Unfortunate that FMP doesn't have a "CountIf" function.  Seems it cold keep things slightly cleaner in relationship graph.  Ah well, still a great program.

                        Cheers!