6 Replies Latest reply on Apr 20, 2016 6:04 PM by sumaschu

    How to group and count

    lredding

      Title

      How to group and count

      Post

      I have read through so many posts on this issue, but I can't figure it out and would really appreciate some help. What I am trying to do is similar to my SQL days when you could create a group by query that counted the number of records on one or more fields. In FileMaker I need to be able count the number of different alert types I have created. It is dynamic and changes all the time. What I really would like to do is get these values and then insert the unique rows with unique counts into a separate table - then with that table I can use it on a dashboard type concept.

        • 1. Re: How to group and count
          philmodjunk

          But are you counting the VALUES or the number of RECORDS?

          example:

          apple
          Orange
          apple
          pear
          Orange

          If you are counting unique values, there's a count of 3 here. If you want a record count, you'd get:

          Apple   2
          Orange 2
          Pear  1

          Both are possible in FileMaker And SQL is even an option for getting either count though not the only option.

          It also can make difference as to whether you want this count over a) all the records in your table, b) all the records in your current found set or c) a set of related records in the table (such as all records you might list in a portal).

          • 2. Re: How to group and count
            lredding

            Thanks PhilModJunk for your quick response. Let me answer your questions and then refer to an attachment that I will upload.

            I am wanting counts of records, so in your example I want to display on a layout what you posted in your reply:

            Apple   2
            Orange 2
            Pear  1

            This count comes from a related table to the layout that displays the list and would be only those related records. My attachment displays a dashboard. On the dashboard highlighted in yellow is the area where I want this list to appear. The list will be dynamic and different from each user's dashboard to the next. I can't find a way to make this work and would appreciate your help. The related table has the userid and the name of the alert as fields. Just don't know how to summarize those counts and only display those summarized results on the dashboard.

            • 3. Re: How to group and count
              philmodjunk

              And what is the relationship used?

              This sounds like an approach best implemented with the ExecuteSQL function and then set up your chart with the Delimited data source option charting the delimited data from the SQL query. This should put you on familiar territory given your reference to wanting to use SQL in your original post.

              You can check out the specific details of how FileMaker SQL works here: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

              • 4. Re: How to group and count
                lredding

                No not wanting to build a chart - I want to build a list (see my previous post). Ok ExecuteSQL function might be the way to go, but not having much success with that. I know SQL and can build the correct query. But this query will produce a list of rows - having difficulty knowing how to (1) execute it in Filemaker, and (2) displaying the results of the query on the layout in my attached file in the yellow area. That's my issue.

                • 5. Re: How to group and count
                  philmodjunk

                  Just define the ExecuteSQL() function call in an unstored calculation field and put it on your layout. You can specify a return (¶) as the Record separator and the circled box on your example can be the one single field.

                  This is not the only option given your comment that this is related data.

                  • 6. Re: How to group and count
                    sumaschu

                    Philmodjunk,

                     

                    Have a very similar question, but unfortunately, do not know SQL.

                     

                    Have a table about residential homes that have sold.  Each record contains data about a single home.  Need to grab:

                    1. counts (number of homes sold) by year sold,
                    2. average square footage by year sold, and
                    3. average price/square foot by year sold. 

                     

                    To that end, have the following fields for each record:

                    • COEYear (Close of Escrow Year) - calculated from Close of Escrow date [ Year ( COEDate ) ]
                    • SalesPrice - number
                    • SquareFoot - number
                    • PricePerSquareFoot - calculated = SalesPrice / SquareFoot

                     

                    It is really the "by year sold" that is giving me challenges.  Know how to do simple counts and averages using the "Summary" type calculator.

                     

                    I know this must be really easy, but having trouble getting it started.  As always, really appreciate your help!

                     

                    Thanks,

                    Mark <><