12 Replies Latest reply on Jan 20, 2014 8:03 PM by user19752

    executeSQL

    gwinzeler

      using the following for conditional formatting and works fine but when I try to use in a field as a calculation it doesn't work...

       

      conditional formatting

      0 < ExecuteSQL ( "select count (*) from tableA where rowID = ?" ; "" ; "" ; $$rowD)

       

      field calculation

      fieldtest = ExecuteSQL ( "select count (*) from tableA where rowID = ?" ; "" ; "" ; $$rowD)

        • 1. Re: executeSQL
          wimdecorte

          What is your result in the field: ? or nothing.

           

          Could it be a typo in your global variable?  Logically I would expect an I before the D.

          • 2. Re: executeSQL
            johan

            If your table occurrence isn't named exactly "tableA", but something with invalid characters like space, for instance "table A", then you must quote the table occurrence name in the calculation. The same goes for the field namn "rowID".

             

            Also, remember that you have to escape any quotes in the calculation. " should be typed as \"

             

            The following may work:

            ExecuteSQL ( "select count (*) from \"tableA\" where \"rowID\" = ?" ; "" ; "" ; $$rowD)

            • 3. Re: executeSQL
              gwinzeler

              It is returning a blank

               

              again works in script or conditional formating but not in a field calculation ...

              • 4. Re: executeSQL
                gwinzeler

                again works in script or conditional formating but not in a field calculation ...

                ??

                • 5. Re: executeSQL
                  ch0c0halic

                  gwinzeler may be staring at nothing,

                   

                  I've been getting this a lot too. Works in the Data Viewer but not in the calculation.

                   

                  A few things I've found to review.

                   

                  1. Calculations do not 'use' Global Variables very well. If the value is in the 'local' table then use the field instead of a variable. I think this is the problem. I'd suggest changing this to either a custom function or a scripted data entry so you have more control over it. One note if the local field is stored as a global it won't necessarily force an update of all records. A global field isn't in the dependency tree the way a record level field is. Entering a global will sometimes only update the record it was entered on. One trick is to use the "Replace" command on the global to force every record to update.

                  2. Calculations rely on the Dependency tree to evaluate. Make sure it is unstored or you have some other 'trigger' field to make it update or after the first calc it may never change.

                  3. Changes in related field do not reside in the dependency tree so they do not trigger an update. In order to get it to change you have to 'trigger' the calculation. I suggest using an "auto-enter modification date field = to itself" may work for you.

                  4. Make sure there really is related data for your query. I've done this often enough. With multiple parameters its especially important to check all the values actually have records to count. I once spent over an hour trying to debug my calculation only to find there were no related records. Ugh!?!?!

                  5. To troubleshoot try temporarily wrapping the calculation inside the EvaluationError ( expression ) function. This will give you an error code. You'll have to search the SQL sites for error codes to figure out what's wrong as it probably won't return an FMP error code.

                   

                   

                  §=) Thanks for reading, hope this help in some small way. §=)

                  • 6. Re: executeSQL
                    gwinzeler

                    not trying to use global variable, and all other suggestions didn't pay off.

                    there is related records as the same cut and pasted code works as a variable in a scrpt but not in a field calculation.  Is this a known bug????

                    • 7. Re: executeSQL
                      jormond

                      How are you setting the global variable in your calculation?

                      • 8. Re: executeSQL
                        Paul Jansen

                        Hi,

                         

                        Just a thought, but this is not a data separation issue is it?   What you describe would be the case if the field calc is in the data file and the $$rowD exists only in the UI file.

                         

                        Paul Jansen

                        • 9. Re: executeSQL
                          gwinzeler

                          global variable is set in a previous script

                          • 10. Re: executeSQL
                            gwinzeler

                            not sure I follow Paul -- $$rowD is a global variable set prior

                            • 11. Re: executeSQL
                              gwinzeler

                              WOW not sure how to explain or understand this - but here is what I did:

                              removed where clause and worked ok

                              put where clause back in and worked ok

                               

                              strange - I cut and pasted prior to stripping so I am sure there wasn't any typo  ....  ???

                              • 12. Re: executeSQL
                                user19752

                                Sorry for replying old article.

                                Your calculation contains only static value, so if define as 'saved'

                                calculated only commiting field definition.

                                change to 'unsaved' or use some field may solve it.