7 Replies Latest reply on Jan 13, 2010 9:35 AM by philmodjunk

    Calculation thru relation or subset of records?



      Calculation thru relation or subset of records?


      I'm new to FM and I'm creating a multiple location database. I'm currently working on showing totals for each item in each location.

      I have 3 tables that I think the data should relate from.


      My Inventory list (which shows the general description of my items)

      Item ID


      cQtyonHand (which ads up the cItemBal from the Item_transaction table) 


      Item Transaction Table 






      cItemBal (QtyAdd-QtyRmvd)


      Location Table 


      Location Description 


      I currently have a lay out based on the Inventory list which shows Item information total on hand and has a portal to the location table and displays where the items are. What I want to add to that portal is how many of each item are in each location. It should be a calculation that totals the cItemBal, but only for the item on that layout and for each location it is in. So I think that its somehow but a subset and relational, but I'm not getting it to work.



        • 1. Re: Calculation thru relation or subset of records?

          I think a summary report with sub-summary parts based on Location will give you the same information in a more flexible format.


          Add a subsummary part with fk_ItemID as its break field ("sorted by")

          Add a subsummary part with fk_locationID selected as its break field  


          Place fields about the specific Item in the first subsummary part.

          Place your location field and your summary field in the second subsummary part.

          Delete the body part.


          Sort your records by fk_ItemID, fk_LocationID


          and you should get the report you want.


          If you'd like to learn more, here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial


          Edit note: just made the above link live so folks don't have to copy and paste to pull it up.


          Message Edited by PhilModJunk on 01-12-2010 12:31 PM

          • 2. Re: Calculation thru relation or subset of records?

            Ok, I'm not getting this to work at all. I think I'm missing something. First off I should create the sub summary parts on the Location table? When I create the layout for this which type of layout should it be? standard form or List/report? How where do I place my calculation so that for each item I can see how many are in each of the multiple locations.

            Thanks for you help something isn't clicking right.


            • 3. Re: Calculation thru relation or subset of records?

              The layout parts are created on a layout not a table. In this case, it would be a layout based on your item transaction table.


              There are several ways to create this report. You can start with a standard blank layout and add the parts. That's how I wrote my instructions. I've found that the wizard for creating this type of layout to be more trouble than its worth in most cases, but you can get the same results through that approach.


              In either case, make sure you've selected list view for your layout when finished. You can limit the view to this option in Layout Setup... if you want.


              The calculation is the same summary field you've already defined. Simply by placing it in the sub-summary part turns it into a sub-total for a given item stored at a given location. That looks a bit like a smoke and mirrors job when you first create this kind of approach, but it does work.


              I'd suggest playing around with the tutorial I mentioned in my previous post. It should be a good way to figure out how this type of report works and some of the basic options you can take advantage of with this type of report.

              • 4. Re: Calculation thru relation or subset of records?
                   My problem is that if I delete the body part I don't get any data. My fields need to be in the body part in order for the fields to display data. Is there a setting that I missed?
                • 5. Re: Calculation thru relation or subset of records?

                  Actually, all your fields will be in the sub-summary parts and your data will thus still be visible. You can keep the body part if you want to also include each transaction record as a separate row on your report but this is not strictly necessary. You do need an additional summary field that summarizes cItemBal. I assumed you had such a field, but don't see it in your original post now that I double-check. Define a summary field, Balance, as "total of" cItemBal in your Item Transaction table. Specify the running total options you need if you are also going to show a running total on one layout or another.


                  Heres the "no body" version:


                  ---Sub summary when sorted by fk_ItemID---

                  [fk_ItemID]  [InventoryList::Name]

                  ---Sub Summary when sorted by fk_LocationID---

                  [Location::Location Description] [Balance]


                  This gives you a report like this:


                  1234 Acme Yellow Widget

                  Warehouse #1    5,000

                  Warehouse #2    6,000


                  1235 Acme Red Widget

                  Warehouse #1   500

                  Warehouse #3   700


                  If this isn't what you had in mind. Post an example like the above that shows what you want.

                  • 6. Re: Calculation thru relation or subset of records?

                    Thanks I don't know what I was thinking yesterday. I didn't sort the report properly for the sub-summaries to behave correctly! Whoops!

                    One thing I am seeing and I'm sure it's user error. Is that when I have an item with multiple transactions there is a larger space between the different locations. There are records with no visible data in-between. Any Idea of what I'm doing wrong there?

                    • 7. Re: Calculation thru relation or subset of records?

                      Are the records "with no visible data" supposed to be there? I have no way of knowing why such records would exist in this type of table.


                      You can set up a find that omits these records from the found set--that's simplest.


                      You could use sliding fields, but that's more work for the same result.