[D]oes scripting with executeSQL allow you to just specify fields without having the table prefix beforehand even for fields in different tables?
No SQL query allows you to bypass specifying table names, with the exception that you can omit them if all the fields in the query come from the same table. I usually use table aliases to make the queries shorter and easier to read, like this:
SELECT so.ToolID, so.DIX_Num, so.Model, so.Category, so.Serial_Number FROM SignOut so WHERE so.Name = ?
Of course, if you are coming from different tables, then you have to create a join between them in the query. You can do this either explicitly or implicitly. The latter is simpler, but only works for simple joins (like where there are only two tables). That might look something like this:
SELECT so.ToolID, so.DIX_Num, m.Model, m.Category, so.Serial_Number FROM SignOut so, Models m WHERE so.ModelID = m.ModelID AND m.Name = ?
If you need additional tables included, it usually gets a bit more complex. We would need to see your table structure to know how to help.
P.S. It's usually better to post your screen shots as images (little camera icon) rather than sticking them in a Word document. That way, people can see the image without having to download and open the document.
1. It's difficult to say, but sometimes a word in a table or column name is "reserved" or otherwise needs to be "escaped".
WHERE \"Name\" = ?
And there may be other places that need to be escaped.
2. If you've changed something, you might verify that you have the correct T.O. (table occurrence) on the RG (relationship graph) in the file/db from which you are calling the ExecuteSQL().
3. I don't understand what the Set Field is doing either. It would surely over-write each loop.
In the list of reserved words there is names but not name so does my field name, Name, need to be escaped?
Thanks, It looks like I need a join then in that currentAssignedItems variable as the WHERE name part references different table!
If in doubt, escape.
FM sql reserved words
Name" should be safe but...
Does the SignOut table have fields ToolID, DIX_Num, Model, Category, Serial_Number and Name?
Can Set Variable ( $currentName ; GetValue ( $nameID ; $counter ) ever return ""?
Because $currentName is used as an argument for ExecuteSQL() $currentName = "" may cause error although i have never tested null/empty string args in ExecuteSQL().
Changed it to tools as its the tools table that contains those fields, I was confusing myself because i put them in the SignOut layout and thought they were in there.
Sorry name field is in staff. The name returns ok, literally the only part that doesnt appear is the items in the email.
For your table aliases do you specify an earlier script line saying select a,b,c from tools as to etc? or on the same line?
Oh I see it now from your reply
Select * FROM Table1 will only return values from Table1.. to retrieve values from anything but Table1 you have to use SQL Joins or other SQL methods.
As Mike said, the joins can be implied.
" SELECT field1, field2, field3, 'constant'
FROM table1, table2
WHERE table1.field1 = table2.field2
AND table1.field3 = ? "
may be perfectly ok. I depends on what you need returned. An explicit JOIN may be LEFT OUTER JOIN, for example, to get all parents whether they have children or not.
All tables must be on the RG, but need not be related to be used in ExecuteSQL().
ALIAS (table AS ...) need not be used IF the columns/fields are uniquely named. But placing the table name (or alias) with the dot+column name can provide clarity in the SQL statement.
How do you write a join from 2 tables away. I have staff >---- Location -----< Tools. Most of the fields are from tools but the name field is pulled from staff.
what fields do you need? from which tables? and what fields are related?
I agree, your screen shot was difficult to view. Can you answer the above in text and the query can be written.