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

    Execute SQL function problem

    arnojansen1

      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

      Cheers

      Arno

        • 1. Re: Execute SQL function problem
          beverly

          You may need to escape-quote the field

          _kp_ToursPricesTable

          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:

          \"_kp_ToursPricesTable\"

           

          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

          beverly

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

            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.

            Arno

            • 3. Re: Execute SQL function problem
              beverly

              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

              beverly

              • 4. Re: Execute SQL function problem
                arnojansen1

                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
                  beverly

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

                  beverly