6 Replies Latest reply on May 1, 2014 6:38 AM by Mike Duncan

    ExecuteSQL question

    ChrisPye

      I have a 'Clients' table with a related 'Contracts' table, there is a field in the 'Contracts' table 'Signed' (boolean) and a field 'Value' (the dollar amount), and a 'ContractDate' field.

       

      I want to show a Value for each Client record from their last signed Contract. I know how to do this via creating extra FM relationships, but in this case it is for a very infrequently used report, so I figure the ExecuteSQL may be a better option.

       

       

      Cheers,

      Chris...

        • 1. Re: ExecuteSQL question
          erolst

          ChrisPye wrote:

          I know how to do this via creating extra FM relationships

          You don't necessarily need a new TO/relationship; just create a new one-row portal using the existing Clients/Contracts relationship, sort it by contractDate descending, filter it with the expression 'Contracts::signed', and put the Contracts::Value field into the portal

           

          If you want to use SQL, the calculation would look like

           

          ExecuteSQL ( "

            SELECT Value

            FROM Contracts

            WHERE \"_fk_clientsID\" = ? AND Signed = 1

            ORDER BY ContractDate DESC

            " ; "" ; "" ; Clients::_pk_clientID

          )

           

          Use GetValue ( executeSQLcalculation ; 1 ) to get the value for the most recent date only. In FM 13, you can alternatively add a FETCH FIRST 1 ROWS ONLY to the SQL statement.

          • 2. Re: ExecuteSQL question
            TimDietrich

            Chris --

             

            The challenge with this is that you can't really do subqueries in FileMaker's SQL except for in the WHERE clause.

             

            However, here are a couple of different approaches that might help...

             

            ExecuteSQL ( "

            SELECT

                      Clients.Client_ID,

                      Client_Name,

                      Signed,

                      Contract_Value

            FROM

                      Clients

                      JOIN Contracts ON ( Contracts.Client_ID = Clients.Client_ID )

            WHERE

                      Contracts.Signed = 1

            ORDER BY

                      Client_Name,

                      Contract_ID DESC

            "; "|"; ¶ )

             

            That will show all contracts that have been signed, in descending order (based on contract ID - you could easily change this to "contract date" if you'd like), for all clients. The problem with this approach is that you won't just get the most recent contract - you'll get them all. But if you're using this in a scripted report, you could just loop over the results to show only the most recent contract for each client.

             

            Another approach would be to set this up as a calculation in the Clients table itself. For example...

             

            Recent_Signed_Contract_Value =

            ExecuteSQL ( "

            SELECT

                      Contract_Value

            FROM

                      Contracts

            WHERE

                      Contracts.Client_ID = ?

                      AND Contracts.Signed = 1

            ORDER BY

                      Contract_ID DESC

            FETCH FIRST 1 ROWS ONLY

            "; "|"; ¶; Clients::Client_ID )

             

            That works, but requires adding the calculated field to the Clients table. It also has the disadvantage of having to run the SQL for every client being included in a report.

             

            I hope this helps.

             

            -- Tim

            1 of 1 people found this helpful
            • 3. Re: ExecuteSQL question
              ChrisPye

              Thanks for your suggestion, I would never have thought of using a filtered portal like that.. It works fine for now, until the client asks to be able to sort on that field.

               

              Cheers,

              Chris

              • 4. Re: ExecuteSQL question
                ChrisPye

                Thanks Tim, if the client wishes to sort on the last contract value then I will try your second option.

                 

                Cheers,

                Chris

                • 5. Re: ExecuteSQL question
                  beverly

                  Chris, you've gotten some good answers. Instead of ExecuteSQL, might I suggest the Last() function. It will give you the last related record based on the sort for that relationship in the graph. If unsorted, it will more than likely be the last record by date as well, but you never know!

                   

                  You can do the related sorting one of these ways:

                       a. sort the relationship (I rarely do this)

                       b. sort the portal (one row portal, as other suggested) - test to see if this gives you the Last() you want.

                   

                  see help topic: (http://www.filemaker.com/13help/en/html/func_ref3.33.45.html)

                   

                  Beverly

                  • 6. Re: ExecuteSQL question
                    Mike Duncan

                    Beverly, thanks for that, totally forgot about this function. And I just spent ten minutes looking for a "first" function before realizing I didn't need it