4 Replies Latest reply on Jan 20, 2016 8:15 AM by malachydevlin

    alternative to simple execute SQL

    malachydevlin

      Hi,

      i have a customers table and i want to display a total count of all this customers projects that are "open".

      so a execute sql works well as it lets me specify a criteria for the projects.

      e.g select count(id) from projects where status='open' and customer_id=?thiscustomer.

       

      However i wondered is there a FM function that achieves the same thing, without making relationships etc.

       

      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..

       

      thanks

        • 1. Re: alternative to simple execute SQL
          mikebeargie

          So learn to write your ExecuteSQL() so that it is protected against field and table name changes.

           

          FM 12 ExecuteSQL: Robust Coding, part 1 | FileMakerHacks

          • 2. Re: alternative to simple execute SQL
            jbrown

            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:

            Let ([

            _Field1  = GFN ( fieldRef  );

            _Table = GTN ( fieldRef  );

            _WHERE = GFN ( fieldRef  );

            _Query = "SELECT " & _Field1 &  " FROM " & _Table &" WHERE " & _WHERE  & " = ?"

            ];

            ExecuteSQL(_Query ; "";""; )

            )

             

            Custom Functions:


            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.

             

            GFN (field)

            Let ( [
            a = GetFieldName ( field ) ;
            b = Substitute ( a ; "::" ; ¶ )
            ] ;
            
               Quote ( GetValue ( b ; 2 ) )
            
            
            )   //   end let
            

             

            GTN - get table name

             

            GTN (field)

            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

            • 3. Re: alternative to simple execute SQL
              coherentkris

              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.

              • 4. Re: alternative to simple execute SQL
                malachydevlin

                THANKS to both of you.

                This has been very helpful