I'm a complete newbie with SQL, just exploring now. This is probably a stupid simple question but here goes.

I have a table I want to query to extract a virtual value list. I want the WHERE condition to come from a value in a global table. I've tried lots of things but just don't have the knowledge of the syntax in SQL.

cList1 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = Cust_List " ; "" ; "¶" )

cList2 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = Cust_List[2] " ; "" ; "¶" )

cList3 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = Cust_List[3] " ; "" ; "¶" )

So in the above 'Label' is the field in the 'ListsCustom' table that provides the condition for generating the virtual value list and 'Cust_List' is the global repeating field in the 'Global' table that provides the three list labels.

If I put Cust_List in the ListCustom table then this works.

cList1 = ExecuteSQL ( "SELECT List FROM ListsCustom WHERE Label = Cust_List " ; "" ; "¶" )

...but for other reasons I need the Cust_List field in the Global table.

Hope someone can shortcut this for me.

There is no such thing as a global table.

There are global fields. But since they are global; the contain the same value and a where clause would make no sense.

Also, an ExecuteSQL query has a parameterized form:

ExecuteSQL ( "SELECT List FROM ListsCustom WHERE Label = ?" ; "" ; "¶" ; Cust_List[3] )

Edited May 31, 20169 yr by BruceR

Thanks Bruce

I call the table 'Global' only because it contains one record only. A lot of those fields are not actually global fields because a value list cannot come from an unindexed field. Maybe I should call it 'Utility' or something but that is not the point I was wishing to discuss. My point is more about how to refer to a value in another table in the SQL syntax i.e. ExecuteSQL ( "SELECT List FROM ListsCustom WHERE Label = ?" ; "" ; "¶" ; SomeOtherTable::Cust_List[3] ) in my NOOB speak. Of course that doesn't work.

I do understand the paramerterized form you suggest however when trying it that way it doesn't work.

I have gone ahead with having the Cust_List field directly in the ListCustom table and managed to get everything to work but I think my code is still clunky. You might be kind enough to have look at the rough file itself and see if you can point out how it could be improved? If it was robust enough I think it would be a good little module to post on Modular Filemaker as I have not seen anything like it out there and I believe it could be of use to many.

CustomListDemo.fmp12.zip

"I do understand the paramerterized form you suggest however when trying it that way it doesn't work."

Uh, no. That IS how ExecuteSQL works.

 ExecuteSQL ( "SELECT Name FROM ListsCustom WHERE Label = ?" ; "" ; "¶" ; ListsCustom::Cust_List)

=>

Apple
Peach
Pear
Orange
Banana
Pineapple
Mango
Plum
Nectrine

 ExecuteSQL ( "SELECT Name FROM ListsCustom WHERE Label = ?" ; "" ; "¶" ; ListsCustom::Cust_List[2])

=>

Bean
Pea
Lettuce
Cucumber
Onion
Cellery
Carrot
Zuccini
Mushroom

Edited May 31, 20169 yr by BruceR

You just turned on the light bulb I was looking for and answered my post precisely!

Thought I had tried that but hey... tested and works.

So I can relate the SQL query to ANY table using this syntax. Yes!

I can now delete a whole lot of relationships in my main solution and structure more value lists using SQL.

I really appreciate your input. Thank you. If you have any time I would still like you to have a look at my solution to date and tell me what you think. Don't hold back any punches. If it is of any merit I would like to 'give back' if I can.

Oh. One Other Thing. I have been used to using the pipe "|" symbol in field and table naming i.e. Lists|Custom. SQL seems to not like that. Am I correct?