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.
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.
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.
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
"SELECT COUNT ( \"__pk_ActivityID\" )
FROM \"Static Activities\"
WHERE Locked IS NULL"
; "" ; "" )
Justin Schwartz posted an answer with more detail and before me.
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.
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.
You've semi-answered your own question! In your SQL you need to create a JOIN with another table by JobNumber.
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.
You don't need to create a field, create a variable that tests in a script trigger before allowing exist from the record.
Thank you everyone for the input.