The only reason i ask is that i like to avoid execute sql if its easy to do so for the only reason that if field names change then its hard to remember to change it everywhere..
Many people have gone away from using hardcoded ExecuteSQL statements. They use Custom Functions (FileMaker Pro Advanced) to create a function that grabs the field name of the field you specify and the build the SQL statement. Here's one I use:
_Field1 = GFN ( fieldRef );
_Table = GTN ( fieldRef );
_WHERE = GFN ( fieldRef );
_Query = "SELECT " & _Field1 & " FROM " & _Table &" WHERE " & _WHERE & " = ?"
ExecuteSQL(_Query ; "";""; )
GFN - get field name
GFN [for GetFieldName] returns only the name of the field, without the relationship portion. As opposed to GetFieldName() which also returns the relationship. This is to be used in SQL queries to protect the field references from breaking if the field is renamed.
The Quote() function allows us to reference "SQL reserved" field names without having to manually escape them.
Let ( [ a = GetFieldName ( field ) ; b = Substitute ( a ; "::" ; ¶ ) ] ; Quote ( GetValue ( b ; 2 ) ) ) // end let
GTN - get table name
Let ( [ a = GetFieldName ( field ) ; b = Substitute ( a ; "::" ; ¶ ) ] ; Quote ( GetValue ( b ; 1 ) ) ) // end let
This will always be safe when you change field names. You don't have to remember to go and change them anywhere
If you already have the relationship from customer to projects you could put an unstored calculation field into customers that gets a list of project statuses with List ( project::Status ), apply a filter to remove not open FilterValues ( List ( project::status ) ; "open" ) and count the results ValueCount ( FilterValues ( List ( project::status ) ; "open" ) ) )
This will work if project::status is never empty.
If it can be empty then you'll have to remove the empty values before counting them.
Can be done with a Custom Function.
I'm sure there are other ways but this is the first thing that came to mind that would not require creation of a new relationship.
THANKS to both of you.
This has been very helpful