3 Replies Latest reply on May 19, 2014 12:42 PM by KatelynLindsay

    Seeing two summaries from different tables



      Seeing two summaries from different tables



           So I am hoping someone here can set me straight! I have been pounding my head against this for almost 2 days and I am thinkin what I want to do may not be possible in Filemaker, but I have only been using it a month so there may be something I am missing!


           Here is my set up first:

            Popuation  >--- (countyCode) --- Counties ------ (countyCode) ----< Cases

           Population and Cases were also connected (in trying to get this darn thing to work) through:

      Population2 >------- (countyCode) ------< Cases


           My database needs to do 3 things:
           1. Sumamrize and show population data based on search criteria, summarized by sorting (it does this, see boxes 1 & 2 in the screen shot).

           2. Summarize and show workload data based on searchc riteria, summarized by sorting( It also does this though not pictured, it looks just like the first one).

           3. Summarize and show population AND workload data based on search criteria. (can't seem to get this to work).


           The problem seems to be in the searching, IE, it only searches based on one. I don't want to use portals as I only want the total number not the actual records. I did find one work around which might have to be my solution was searching based on Cases first then sticking that in a global field, then searching on population and just displaying the global field once. This works but I would like it to be broken down and summarized like everything else if possible (maybe it isn't?)

           I can provide any more information needed and thanks in advanced for any help!


        • 1. Re: Seeing two summaries from different tables

                    I don't want to use portals as I only want the total number not the actual records.

               Yet a one row portal can, in some cases, show a summary value for the set of records what would be visible in that portal if it had more rows and a scroll bar.

               That may not be the best option here, but it is one option available.

               What is not clear to me is what is meant by "work load". Is that the number of records in cases for a given specified set of criteria or is it a more complex computation from data in cases?

                "Year", "age", "Gender" and "Race" are all fields in which table? Population? Cases? Both?


          • 2. Re: Seeing two summaries from different tables

                 (I don't know how to reply to a specific post? If its so)


                 You know... I didn't think about using a single portal row (oddly cause I did that in another database) I will try that and see what happens.

                 The actual 'workload' is the count of cases returned by that search (where as population is a sum of the populations). So I was using a global field to hold the FoundCount in but I wanted it to be dynamic like the rest of the data so I was trying to find a new way, but I guess I'm okay with being less fancy :p.

                 I attached the design for you. The hearingTypes and Resons aren't in use right now but may be later which is why they are there, I am currently just working with the Population, Counties, and Cases. (Yes I realize the population table also has countyNames, when this was originally made it was JUST the population table and I just added all these new tabled on Friday so I haven't yet gone through and switched Population::countName for the County::countyName I will though!)

                 Thanks for your quick reply.

            • 3. Re: Seeing two summaries from different tables

                   I got it working.. Thank you so much PhilModJunk :D can't belive I didn't think of such a simple solution!