1 of 1 people found this helpful
Here is my approach : FM SQL Scratchpad
Instead of using the Let function approach, it abstracts table/field name using two simple custom functions (@t and @f) and you can auto-complete the field reference inside these custom functions.
It focuses on the efficiency to build a query incrementally
A big key is to wrap your fields in the Get (FieldName ) function instead of referencing the field name directly.
So instead of...
"Select FirstName from.."
"SELECT " & Quote ( GetValue ( Substitute ( GetFieldName ( Contacts::FirstName ) ; "::" ; ¶ ) ; 2 ) ) & " , FROM " & ...
That lets the calc you're writing survive field renamining, but can be a pain to type out. Our free query building will do this for you so you can paste the resulting queries right into your own work:
When you're using our tool and click "copy" select "copy abstracted calculation" for this option. ("Abstraction" is what folks call this not-hard-coding-field-names thing.)
(BTW, nice work, ave on /harawata/fmsqlscratchpad !)
Thank you, John.
A follow-up question: are their particular rules around using the "." in the middle of a table-field combination in an SQL Query? I've had trouble doing this consistently. It seems as though I can't get it to work if I put the whole reference within quotation marks (e.g. "tablename.fieldname"), and I can sometimes get it to work with "tablename"&.&"fieldname", but only inconsistently. I'm sure I'm doing something dumb, but I can't quite work out what.
I can sometimes get it to work with "tablename"&.&"fieldname", but only inconsistently.
If your table name or field name contains spaces or other "illegal" characters then you have to quote it. So this should work:
quote( "tablename" ) & "." & quote( "fieldname )
Thank you for that. Does that mean that even if I use the Quote (tablename) function, I should still include quotation marks (i.e. Quote(“table name”))? This is the sort of thing that has me confused.
The argument for Quote() has to be an expression. If you don't add the quotes Quote will try and find a field called "tablename" and return its contents in quotes.
it would save a ton of time if ExecuteSQl accepted "::" as well as "."