If you mean list of IDs as values delimited by line breaks, it is not "normalized" data, bad for SQL.
There is workaround not using ? like
WHERE a. "PartDailyGoal_ID" IN (ID1,ID2,ID3...)
If ID is text then need quoted. IN ('ID1','ID2','ID3'...)
Assuming you have a found set and the Primary Key field is a number and it is the PartDailyGoal_ID in the other table, would this work?
Let ( [
F1 = TableName::SummaryFieldOfPrimaryKeyAsList ;
F2 = Substitute ( F1 ; ¶ ; ", " ) ;
F3 = "SELECT Area_ID FROM FieldManagement WHERE PartDailyGoal_ID IN ( " & F2 & " ) " ;
F4 = ExecuteSQL ( F3 ; Char ( 9 ) ; ¶ )
] ; F4 )
I took a look at all of the suggestions and think it may be one of those times the SQL is not the best answer. Easy FileMaker solution. Create Global field with relation to TableOccurence::PartDailyGoal_ID
Enter Value in to global field, use list function to get list of values required.
Down side adding something extra to the relationship graph. Upside this greatly simplifies the scripting involved.
All thanks very much for the helpful suggestions.
Could you use instead:
FROM "FieldManagement" a
WHERE a."PartDailyGoal_ID" LIKE ?" ?
Without some sample data, it's difficult to understand exactly what you mean.
Have you considered getting a SQL plug-in for FileMaker or are you already using one? There are lots of them. Beverly recently listed three different ones. I do SQL remotely against the FM database (anything to get away from the Data Viewer), but that seems to be quite rare.
So, anything that would let you experiment freely to test your queries would be helpful.
SQL is amazingly powerful and if you can't do something with it, it may point to a design issue (but of course, depending on what you're actually trying to do).
I agree with Bruce to go with a relationship. Because we have a feature in FM that's called "multi-line" key, a LIST of return delimited values (a¶b¶c) can relate to a field in another table that has a OR b OR c.
It's difficult to use this kind of data in SQL as it relies on the LIKE and the values are case-sensitive and just plain not as fast as "native FileMaker". Not even a plug-in would be as beneficial. You have to worry about getting the quote right because the ? does not work with lists like this (return delimited).
Here is an article about ExecuteSQL and this kind of "data":
The multi-line key is a unique FM feature (in relationships), AFAIK.
If you have only one "list of IDs field", you can use it instead of global field in relation (using another TO if need).
If there are two or more, using global decrease "extra relationships".