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 ?
You get an empty string.
Isempty ( executeSQL (. ). )
would be true if no records were returned.
If you got "?" as return, it means there is error (most case in syntax), not "not found".
That is what I thought, but I was unsure.
Thanks very much.
Put EvaluationError() around the ExecuteSQL().
If you get 0 no error.
If you have FMPAdvanced you can error check this way:
Sent from miPhone
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.
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:
Phil ... you're the best!
well stated pmj!
Retrieving data ...