5 Replies Latest reply on May 30, 2016 4:49 AM by beverly

    Execute SQL function problem


      I am trying to get this to work but only get a ? mark as result so I know something is wrong.


      This is what I use in a calculation field in Filemaker 14 pro advanced for MAC

      calculation result is as text of number but it just doesn't work.


      I am looking for a NettPrice in a table named ToursPricesTable where the primary key matches: TourPrice0441, the primary key has a number and is defined as a number field.


      This query is in a table called itinerary


      ExecuteSQL (

      "SELECT NettPrice

      FROM ToursPricesTable

      where (_kp_ToursPricesTable)   =   TourPrice0441

      "; ",";""; ToursPricesTable::NettPrice;ToursPricesTable::_kp_ToursPricesTable)


      later I will make this more flexible with a selection on dates etc but first I need this to work.


      Your help is appreciated as always



        • 1. Re: Execute SQL function problem

          You may need to escape-quote the field


          underscore shouldn't be used in field & table names that will be used with SQL (or other "external" methods).

          however we have a work-around for ExecuteSQL() function:



          This may help you debug your eSQL:

          EvaluationError( ... your calc here ... ) // returns error code

          and/or this if you have FileMaker Pro Advanced (to use the Data Viewer):


          Unlocking Hidden Error Messages in the ExecuteSQL Function


          1 of 1 people found this helpful
          • 2. Re: Execute SQL function problem

            Thank you Beverly,


            i think i need some more work as it doesn't work yet. i have noticed that the reference fields only seem to work with number fields? i have combination between text and numbers for my primary keys. could that be a problem?  i have looked at the error codes. code is either 8909 or 8910.


            i have tried the example from filemaker and that works so it is not my settings.


            cheers and thank you for your help.


            • 3. Re: Execute SQL function problem

              kpToursPricesTable)  =  TourPrice0441


              Is the "value" you are trying to match this combination of text and numbers or is this another field?

              Generally you use the ? parameter


              _kp_ToursPricesTable)   =   ?

              then in the parameters:

              "TourPrice0441" for the value or

              TourPrice0441 for the field (which probably should have the table name


              • 4. Re: Execute SQL function problem

                Magic with the question mark I get the value I was looking for. This works however when there is one value not a range of values with different dates. Now I can try to refine the formula.

                what I was thinking of is to add the following:

                after the where statement


                and SS_travel_date  (this is the date I need the price for)

                between ToursPricesTable::StartDate and ToursPricesTable::EndDate  (every tour price has a range where the date is valid)


                all date fields are formatted as date field.


                somehow this is not working again.


                Thank you so much for your help Beverly.

                • 5. Re: Execute SQL function problem

                  repost you query (as changed) if you need further assistance!