7 Replies Latest reply on Feb 19, 2014 8:27 AM by beverly

    FileMaker Count Function

    tm9

      Is it possible to make the count function only count the fields that meet a certain condition?

       

      ie: Count ( grid_PERSON::status = "Extended" )

       

      This keeps giving me an error.

       

      Thanks,

        • 1. Re: FileMaker Count Function
          beverly

          This is where ExecuteSQL can be used:

               ExecuteSQL ( " SELECT COUNT(status) FROM grid_PERSON WHERE status = 'Extended' " ; "" ; "" )

           

          or you create a calculated field status_Extended_c

               = if ( status = "Extended" ; 1 ; )

           

          Then you have a summary field that is :

               = Count ( status_Extended_c )

           

          I guess that mostly it depends upon where you want to use the value. If it's a report, perhaps the calculation and summary field are needed ( based on found set or sorted by some category, for example ).

           

          Beverly

          1 of 1 people found this helpful
          • 2. Re: FileMaker Count Function
            tm9

            Thanks! I will need it for reports but what I am currently using it for is not a report.

             

            I thought about the ExecuteSQL but I have never used it before.  I would like to use it.

             

            The thing is that my GRID is related to an ORGANIZATION table.  Each Organization has a record in the GRID table for each year.  This grid defines requirements etc for that organization each year. 

             

            The PERSON table is related to the ORGANIZATION table with a many-to-many relationship.  What I want to be able to do is count the #of people who meet the "Extended Criteria" for each year, however it needs to be per organization.  Could I something in the arguement of the SQL to accomplish this?

            • 4. Re: FileMaker Count Function
              coherentkris

              If you are looking for a detailed solution to your specific issue you should probably post the file and a thorough description of what you are trying to accomplish.

               

              You can do most but not all of http://www.w3schools.com/sql/ in FileMaker via ExecuteSQL().

               

              Their are great resources at www.beezwax.net, www.filemakerhacks.org and other sites for FM SQL implementation.

              1 of 1 people found this helpful
              • 5. Re: FileMaker Count Function
                tm9

                Thanks!

                 

                Is it possible to make one of the WHERE conditions a dynamic field?

                ie

                WHERE status='extended' AND year= adynamicfieldfromrelatedgridtable?

                • 6. Re: FileMaker Count Function
                  coherentkris

                  ExecuteSQL ( "SELECT field FROM table WHERE field=? AND field=?" ; "" ; "" ; firstquestionmarkvalue ; secondquestionmarkvalue ; nthquestionmarkvalue )

                   

                  Use of the ? is the SQL equivalent of a variable.

                   

                  first/second/nthquestionmarkvalue can be either a text value in quites OR a fully qualified field reference toname::fieldname

                   

                  AND can also be OR... check out the syntax i sent at w3schools for more possible clauses and combinations that you can write, test, and use.

                   

                  I strongly encourage looking for the list of SELECT clauses that do not work in FileMaker to explore the limitations of ExecuteSQL()

                  Clint Eastwood said it best when he said "a mans got to know his limitations".. same with ExecuteSQL... : )

                  • 7. Re: FileMaker Count Function
                    beverly

                    Hmmm, need to check your linkage, co.

                     

                    <http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/>

                    It sums up links to the other sites. There's a PDF (more info than in the blog) and example file(s). Yes it's "12", but except for the new functions in "13", it's the same!

                     

                    Beverly