8 Replies Latest reply on Nov 17, 2016 2:01 PM by beverly

    ExecuteSQL Found No Record


      How can I identify that an ExecuteSQL function has not found any record in the table with the proper value in the field selected ? What is the function returning then ?

        • 1. Re: ExecuteSQL Found No Record

          If you got "?" as return, it means there is error (most case in syntax), not "not found".

          • 2. Re: ExecuteSQL Found No Record

            You get an empty string.


            Isempty ( executeSQL  (. ). )


            would be true if no records were returned.

            • 3. Re: ExecuteSQL Found No Record

              That is what I thought, but I was unsure.


              Thanks very much.

              • 4. Re: ExecuteSQL Found No Record

                To test:

                Put EvaluationError() around the ExecuteSQL().

                If you get 0 no error.


                If you have FMPAdvanced you can error check this way:



                Sent from miPhone

                • 5. Re: ExecuteSQL Found No Record

                  Do yourself a HUGE favor and get a real SQL tool. The maddening and totally unhelpful "?" drives me nuts.

                  Using a real SQL tool, like RazorSQL to connect to your LIVE FMP database you would get:


                  1. SQL content assist - query pop-up "tool tips"/"intellinsense" as you type your query.

                  2. ACTUAL ERROR MESSAGES!

                  3. Ability to actually "View Data" in columns! (not just a few little rows at the bottom where you have to scroll)

                  4. Be able to resize respective windows (horizontally, vertically) , change fonts, etc.

                  5. Do INSERTs, UPDATEs, and DELETEs as well as SQL

                  6. No need (initially) to use ExecuteSQL. Just type regular select statements.

                  7. Browse tables and edit data directly.

                  8. Ability to connect to ANY JDBC-enabled (read: all of them) databases

                  9. A well supported developer product.

                  10. Other stuff....


                  Once you get your query working against your LIVE FMP database, THEN move it back to FMP and add replaceable parameters and such.


                  IMHO, for a multi-hundred dollar tool (>$500 for FMPA), the data viewer is really more of a calculation checker. FMP should have an actual capability to view data. The "Data Viewer's" teeny tiny window with non-resizable windows, non-changable fonts, no error messages, no content assist is (again, IMHO) a weak part of the product and needs a major re-write. (That's why I call it a "Calculation Checker").


                  (Many Java IDEs have a "Data pane" where you can connect to your FMP database too in real time). FOR. FREE.


                  Hopefully FMP 16, or 17 will get a much needed rewrite of the "Data Viewer".


                  HOPE THIS HELPS.

                  1 of 1 people found this helpful
                  • 6. Re: ExecuteSQL Found No Record
                    the data viewer is really more of a calculation checker.

                    In actual fact, that's exactly what the data viewer is when using the watch expression. It was created before ExecuteSQL was an option and was for the purpose of checking calculations. It's very handy for that purpose, though you've mentioned some improvements here that would be very nice. For something like ExecuteSQL, it's not as useful. They really need a native SQLBuilder utility that does pretty much what you have spelled out, plus:


                    1. Graphic relationship building in order to define joins. (Not an actual change to the relationships in Manage | database, but just within the tool to define what's needed for the join.)
                    2. The ability to drop in real field references that are referenced by their ID numbers (created internally when the field, table and table occurrences are created) rather than as a text based name reference. Such a query then would not break should a developer rename something via Manage | Database at a later date.
                    • 7. Re: ExecuteSQL Found No Record

                      Phil ... you're the best!



                      • 8. Re: ExecuteSQL Found No Record

                        well stated pmj!