1 2 Previous Next 20 Replies Latest reply on Dec 5, 2016 7:23 PM by BruceRobertson

    Count records that fit two criteria?

    ezeitgeist

      There was a post a while back (2014) similar, but got no response so I am posting to see if there is a solution.

       

      I want to count the number of records in a table that fit 2 specific criteria. Ie. Field A = "1" and Field B = "2".

       

      In my research it looks like Get(FoundCount) might do it. I just need to have the count of a few different sets of records displayed in an admin dashboard. The description notes: "If you specify the context for the current calculation, this function will be evaluated based on that context; otherwise, it will be evaluated based on the context of the current window." Which I do not fully understand -- so can I make a script or calculation that names the table and does a search for the 2 criteria matching?

       

      NB: Doing this on WebDirect, so if there are any WebDirect glitches related, I thought it would be good to note the delivery platform.

        • 1. Re: Count records that fit two criteria?
          erolst

          ezeitgeist wrote:

          The description notes: "If you specify the context for the current calculation, this function will be evaluated based on that context; otherwise, it will be evaluated based on the context of the current window." Which I do not fully understand -- so can I make a script or calculation that names the table and does a search for the 2 criteria matching?.

          You can only specify a context in a calculation field. In a script the context is determined by the current layout.

           

          A script would just be:

           

          Enter Find Mode

          Go to Layout [ YourTable ( YourTable ) ]

          Set Field [ YourTable::A ; 1 ]

          Set Field [ YourTable::B ; 2 ]

          Set Error Capture [ on ]

          Perform Find

          Set Variable [ $myFoundCount ; Get ( FoundCount ) ]

          Go to Layout [ original ]

          # [ do something with $myFoundCount ]

          1 of 1 people found this helpful
          • 2. Re: Count records that fit two criteria?
            philmodjunk

            There are multiple paths to that result.

             

            a relationship can be set up that matches only to those records by matching on two pairs of fields. In that case, the Count function, Count ( Relatedtable::neveremptyfield ) will give you the count.

             

            A filtered portal can show only the records meeting your criteria. This can be a one row portal and a summary field or the {FoundCount} layout object (see the insert menu while in layout mode) can show you the total records in that one row portal.

             

            ExecuteSQL can also return this count for you.

            1 of 1 people found this helpful
            • 3. Re: Count records that fit two criteria?
              ezeitgeist

              I've done the filtered portal method for a single criteria and a related record. Does it have to be related? The table I am counting from is the User table and my Dashboard is in that table.

              • 4. Re: Count records that fit two criteria?
                philmodjunk

                You can make it a self join relationship to another occurrence of the same table.

                 

                And ExecuteSQL, my last suggestion, does not require any relationships.

                1 of 1 people found this helpful
                • 5. Re: Count records that fit two criteria?
                  ezeitgeist

                  I will try a self-join! (ExecuteSQL is above my pay grade, as it were ... I have no experience with it thus far so it scares me. hehehe.)

                  • 6. Re: Count records that fit two criteria?
                    coherentkris

                    Using fields myTable::primaryKey, myTable::fieldOne, myTable::fieldTwo as an example...

                     

                    ExecuteSQL ( "SELECT COUNT ( primaryKey ) FROM myTable WHERE fieldOne=? AND fieldTwo=?" ; "" ; "" ; 1 ; 2 )

                     

                    should work.

                    If it returns a ? something in the syntax is wrong

                    1 of 1 people found this helpful
                    • 7. Re: Count records that fit two criteria?
                      ezeitgeist

                      Is there an advantage to a self-join filtered portal approach vs the ExecuteSQL? I did not realize that ExecuteSQL was just a command line. I thought it involved more backend database aspects.

                      • 8. Re: Count records that fit two criteria?
                        philmodjunk

                        The only "advantage" to the self join is that you don't need to learn SQL. The SQL query is actually simpler.

                        1 of 1 people found this helpful
                        • 9. Re: Count records that fit two criteria?
                          ezeitgeist

                          Wait, now you have me confused again. So to do the ExecuteSQL do I have to learn SQL and do something prior to the line coherentkris listed? Ahhh, probably in the "Using fields myTable::primaryKey, myTable::fieldOne, myTable::fieldTwo" part. I though that was just another command line.

                          • 10. Re: Count records that fit two criteria?
                            coherentkris

                            what i wrote was the complete, theoretical given the example, ExecuteSQL command that will produce the count.

                            This would be then inserted into a spot for specifying a calculation anywhere the calc engine is exposed.. setting of a field in a table, setting a variable in a script etc.

                            the advantage is that its not context sensitive and does not require any new relationships on your graph.

                            If the To exists and the fields exist then you will get a result no matter what context you execute the calculation from

                            1 of 1 people found this helpful
                            • 11. Re: Count records that fit two criteria?
                              ezeitgeist

                              Okay, I'm trying ExecuteSQL I put in this:

                              Screen Shot 2016-12-01 at 4.19.33 PM.png

                              Where the Table name is "Users by gSession 10" and field one is "Active Boolean" and field two is "Affiliation". For this one I need it to count when each is equal to "1".

                               

                              It returned a "?"

                               

                              I will try to research what I typed in wrong. If you have any thoughts, please let me know. Thanks.

                              • 12. Re: Count records that fit two criteria?
                                philmodjunk

                                SQL is very picky about details that FileMaker is not. That's part of what I meant by having to learn SQL. You are applying an example without knowing the details of what might keep it from working for you.

                                 

                                By inspection, it looks like you have a two word field name and SQL has a problem with them. They'll generate a syntax error and that is shown by a ? result. To correct for that, you have to enclose the field name in quotes, but because your field name is inside a quoted string, you have to "escape" the quote characters or FileMaker will interpret them as the beginning and end of some quoted text and show an error message for that.

                                 

                                Try this fragment in your query:

                                 

                                WHERE \"Active Boolean\" = ?

                                 

                                \ is the escape character you have to put in front of the quotes so that the character is treated as a regular character and not an operator indicating the end or beginning of quoted text.

                                 

                                Then you should consider that you have just enclosed field and table occurrence names in quotation marks in your query. Should you later rename any of those via manage | database, your query will once again return a syntax error ? result. There are ways to redesign this expression so that ExecuteSQL does not lock you to field and table occurrence names that can henceforth never be changed, but that's a whole new layer of complexity added to the proper construction and use of this function.

                                1 of 1 people found this helpful
                                • 13. Re: Count records that fit two criteria?
                                  ezeitgeist

                                  Just checking, I should not use \"Active Boolean"\ so both quotation marks are inside the \?

                                   

                                  Also given that the table is more than one word, should I change that also to \"Users by gSession 10"\? [or \"Users by gSession 10\"]

                                   

                                  UPDATE: \"Active Boolean\" and \"Users by gSession 10\" made it successful!

                                   

                                  THANK YOU!!!

                                  • 14. Re: Count records that fit two criteria?
                                    philmodjunk

                                    The escape character must be placed just before the quote character so you should use them exactly as shown. And any other field names with spaces in them should also be quoted in the same fashion.

                                     

                                    There are three cases where these are needed in SQL that I can recall:

                                    1. When the field name has spaces
                                    2. When the first character of the field name is the underscore
                                    3. When the field name is exactly the same as an SQL reserved word. Example: Date is a common field name that happens to be a reserved word in SQL.

                                     

                                    Most of us get into the habit of simply quoting all field and table occurrence names so we don't have to double check on this (particularly #3) while writing the query.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next