10 Replies Latest reply on Mar 15, 2016 11:18 AM by ndveitch

    Using ExecuteSQL to count records in Table

    ndveitch

      Hi there,

       

      I was wondering, I need to count a certain amount of records based on various fields and normally I would end up making a few global fields, then a TO to link the tables and then work out my count value that way. I am trying to get my head around ExecuteSQL() as I believe it could be the answer.

       

      I have a table with Static Activities and File Activities. When a job is created, the system copies all the Static records into the File Activities table and marks them as locked. Senior management may modify these records if need be, depending on the job. Then the File Activities are displayed on the Job layout in a portal with the static records at the top and then below the user gets to add any new activities should any arise.

       

      Now I have to make sure that at least 2 new activities are added to the File Activity portal before the user can leave the layout. I was thinking of creating a count field in the File Activities Table and then count the records where the Job number equals and the records are not locked. Then as long as the count is over 2, the user can proceed to the next section. Im not keen on creating more TO's but will if I have to, as my DB needs a major clean up because I started this program when I first started using FileMaker, so as you can guess, there are already way to many TO's and some that were added and never really used.

       

      I was hoping to be able to use the ExecuteSQL(count) to count the records, but i am unsure of how to do it.

        • 1. Re: Using ExecuteSQL to count records in Table
          schwjm

          Based on the way you wrote your post, I assume you have some experience with ExecuteSQL already. Count(*) is a simple aggregation function that will return the number of rows that would otherwise be returned individually in a Select query. "Select Count(*) From TABLEOCCURRENCE Where CONDITIONS" will give you what you're looking for. Just write the query as if you were going to select the File Activities records under the same conditions as the relationship, then use Select Count(*) instead of Select * or whatever.

           

          I won't make a claim whether that's the best solution in this case, but getting more comfortable with SQL is a good thing regardless.

          • 2. Re: Using ExecuteSQL to count records in Table
            ndveitch

            Hi There,

             

            Thank-you for the quick response. To be honest my SQL is very limited, as you will see now. I tired creating a Count Field in the File Activity table,

             

            ExecuteSQL ( "SELECT COUNT ( DISTINCT __pk_ActivityID ) WHERE IsEmpty ( Locked )"  ; "" ; "" ) and as you can guess my result was a "?".

             

            I have 4 fields which I feel would be important to the count in the File Activity table:

            1. __pk_ActivityID - unique ( get(UUID) )

            2. JobNumber - this links back to the Job Table

            3. Locked - Set to "Yes" if the record can not be edited by a normal user

            4. Count - My attempt at a SQL statement.

             

            In my mind the statement would read something like, count all the pk's where JobNumber matches and Locked does not equal "Yes" . Usual I would have created a global field set to "Yes" and then a self join TO based on JobNumber and where Locked does not equal globalYes, but I am trying to see if the SQL statement would be better.

            • 3. Re: Using ExecuteSQL to count records in Table
              schwjm

              No problem. The main thing is that regular FileMaker functions don't exist in the FMSQL space, only SQL functions.

               

              The equivalent of "IsEmpty(Locked)" in SQL would be "Locked IS NULL"

               

              There are reserved keywords in SQL and anything with a name like that, fields/tables with spaces in the name, or fields/tables beginning with underscore (_) and probably other special characters must be quoted. So it is not bad practice to just quote everything. You'll have to use \" to do a quote within your expression.

               

              You also must specify where things are being selected from (what table occurrence.) ExecuteSQL does not use regular FileMaker context, it is very powerful and can do queries from anywhere in the database. If your table occurrence is "File Activities" then you'd add FROM \"File Activities\". Note that this means your field definition probably belongs in your job table, if you're trying to count related File Activities.

               

              Count(DISTINCT PK) is not strictly necessary. Count(*) will accomplish the same goal as primary keys should be unique anyway.

               

              For example: ExecuteSQL( "SELECT COUNT(*) FROM \"File Activities\" WHERE Locked IS NULL"; ""; "")

               

              One thing your query is missing, is to further limit your query results to only related records. You can use AND to create additional conditions. For example: WHERE Locked IS NULL AND JobNumber=?

               

              Then you pass in the JobNumber you want to match on as the 4th parameter to ExecuteSQL.

              • 4. Re: Using ExecuteSQL to count records in Table
                nicolai

                1  You do not need DISTINCT on unique key

                2  you have to escape a field if it starts with "_"

                3  You are missing a FROM clause

                4  IsEmpty() is not a FQL Function


                Try this:

                 

                ExecuteSQL (

                    "SELECT COUNT ( \"__pk_ActivityID\" )

                    FROM \"Static Activities\"

                    WHERE Locked IS NULL"

                ; "" ; "" )


                Edit:


                Justin Schwartz posted an answer with more detail and before me.

                • 5. Re: Using ExecuteSQL to count records in Table
                  ndveitch

                  Thank-you for the advice. I have gotten so far,

                   

                  ExecuteSQL( "SELECT COUNT(\"__pk_ActivityID\") FROM \"File Activity\" WHERE \"_JobNumber\" = \"_JobNumber\" AND \"Locked\" IS NULL "; ""; "")

                   

                  And I am getting the count of all the records where the Locked field is blank. When I tried the SELECT COUNT (*) I didnt get any result, and when I dont have the _JobNumber in I get a count off all the records in the table.

                   

                  Thinking about it, maybe I should have just done the global field and self join TO.

                  • 6. Re: Using ExecuteSQL to count records in Table
                    ndveitch

                    After all that, I create a global Yes field, a new self join TO with relationships JobNumber=JobNumber and Locked ≠ GlobalYes, then do a count on the PK. All done in 5min, instead of 2 hours of searching the Net and forums just to try and use the ExecuteSQL().

                     

                    Oh well, live and learn I guess.

                    • 7. Re: Using ExecuteSQL to count records in Table
                      nicolai

                      You've semi-answered your own question! In your SQL you need to create a JOIN with another table by JobNumber.

                      • 8. Re: Using ExecuteSQL to count records in Table
                        schwjm

                        It's fine to use the table occurrence solution. Your query was close though.

                         

                        \"_JobNumber\" = \"_JobNumber\" doesn't make sense because you are telling it to match records on a condition that will always be true, so effectively you are only matching on Locked IS NULL as you found. You want to do \"_JobNumber\" = ? and then pass the actual _JobNumber field as the 4th parameter to the ExecuteSQL function. Then it will match things only on the value of the _JobNumber field from that record.

                        • 9. Re: Using ExecuteSQL to count records in Table
                          beverly

                          You don't need to create a field, create a variable that tests in a script trigger before allowing exist from the record.

                           

                          beverly

                          • 10. Re: Using ExecuteSQL to count records in Table
                            ndveitch

                            Thank you everyone for the input.