5 Replies Latest reply on Aug 12, 2011 10:22 AM by philmodjunk

    Very Basic Summary Field Question



      Very Basic Summary Field Question


      I'm a FMP beginner. (That will be painfully apparent in just a moment.) I'm using FMP11 for Mac.


      I'm working on a database that contains information about various communities. There are currently 1,300 records that contain about 250 data fields. I've created a "dashboard" layout that I hope to use to return various statistics, (summaries and caculations), on the table's data.



      Some of the summaries elude me. My FMP knowledge is sub-rudimentary.

      Desire: To return the total number of records in the table. I've accomplished this by creating a summary field called "total_entries" that computes "count of ID". (ID is an automatically generated field that all new records get.) This summary field works perfectly.

      Desire: To return the total number of records that contain specific data in one field. I'd like the field in my dashboard layout to show the total number of records in the table that contain "Y" in a specific field. In the dashboard view, for example, the narrative would read, "Total number of applicatants that have a high school education: <number>"

      I have not been successful with the second challenge. I'd thought it might be as easy as creating a calculation like "count of highschoolgrad = Y" or something like that. Clearly that's not the correct syntax. (I wish it were.)

      For reasons beyond my understanding I cannot find clear instruction on how to return the count of records for a field containing a specific bit of text.

      Any guidance or advice (beyond RTFM) would be hugely appreciated!

        • 1. Re: Very Basic Summary Field Question

          There are a lot of ways to count the records that store a specific value in a specific field. Which solution is best can depend on the structure of your database.

          Here's a very simple way to count them:

          Enter find mode by clicking the find button at the top of your screen.

          Enter y in the field

          Click the perform find button.

          In the status area, you'll see what looks like a fraction   x/y where y is the total records in your table and x are the number in your found set. This total is the count of all records in your database matching the specified criteria.

          Here's a more sophisticated approach that only works  in FileMaker 11:

          Open Manage | Database | Relationships and find the table occurrence "box" for this table. Select it and click the button with two green plus signs. This creates a new "occurrence" of the same table. You have not created a new table. Drag from any field in the one box to any field in the other so that they are connected by a relationship line. Double click this line and use the operator drop down to change the = to X so that you get a relationship that looks a bit like this:

          YourOriginalTable::somefield X YourOriginalTable 2::somefield

          Now place a portal to YourOriginalTable 2 on your layout. Make it a one row portal and click the portal filter option in Portal Setup... Enter this expression (modify it to fit your table occurrence and field names): YourOriginaltable 2::FieldwithY = "Y"

          Put the total_entries summary field inside this one row portal and it will report the total records with "y" in the field you specify in the portal filter expression.

          • 2. Re: Very Basic Summary Field Question

            Okay, terrific, Phil. I'll approach this using a portal / occurence method.

            I frequently use the "search *" and sort functions to report these types of totals. The Dashboard layout I'm working on now will (hopefully) provide a top-level view of these common searches and sorts. In other words, I'm spending 40 hours to save 10 minutes. (But hopefully I learn enough through this exercise to save another 10 minutes sometime in the future.)

            After I explore your excellent solution I'll be facing the sum of records found based on specific text data from TWO or more fields. For example, "Total number of high school graduates that are Hispanic: <number>" I'm going to shelve that inquiry until I fully grok this first step.

            Thanks, Phil! I appreciate your help.

            • 3. Re: Very Basic Summary Field Question

              Once you can filter by a value in one field, you can use more complex filter expressions to filter on values in more than one field, by using And, Or or even a Case function.

              You can even include a reference to a field on your layout so that selecting a value in this field controls what value is used in the filter so that one portal can dynamically report different totals based on how the user manipulates the value in this field.

              • 4. Re: Very Basic Summary Field Question

                Maybe relevant, maybe not. The field I am trying to return a sum of records for either contains a Y or is blank.

                Ran into a few obstacles. I'll replicate it step-by-step:

                1. In my dashboard layout I open the "Manage Database" panel and go to the Relationships tab.

                2. I click on the double-plus button to create an occurence of the table.

                3. I select the Graduate field in my OriginalTable and drag it to the matching Graduate field in the OriginalTable 2 occurence.

                4. I change the default "=" symbol to an "X" symbol in the link between those two fields.

                5. I click OK to close that panel.

                6. I go into Edit Layout mode and from the drop-down Insert menu, click on "Portal."

                7. I change the default number of rows (5) to "1", confirm the portal is showing related records from OriginalTable2, then toggle the box for "Filter portal records". This action automatically opens the "Specify / Options" panel.

                (NOTE: I can OK at this stage and exit layout mode to view the results. The portal returns nothing...a blank box.)

                9. I manually add " = Y" to the end of the summary / calculation so that it now reads, "OriginalTable 2::graduate = Y.

                10. When I click OK to accept this I get an error that reads "The specified table cannot be found."

                11. Undeterred, I remove the "= Y" from the calculation and just close that portal control panel.

                12. Within the portal shape, I insert a field that returns my (previously created) "graduate_count" summary field. It returns the results of a "Count of graduate".

                I feel like I'm missing a critical step here. Up until now I have not successfully specified that I am looking only for the sum of "Y" data.

                13. I click Okay and exit layout mode to preview my handiwork.

                I have a beautiful gray box that would make my mother proud... but it is empty.

                Phil, do you see my critical error?


                • 5. Re: Very Basic Summary Field Question

                  Put the Y inside quotation marks so that FileMaker knows this is a literal value, not the name of a field.