You do not need the quotes around Kp_GroupID
Yes, you are right - that's not what I'm using... I copied the wrong field (that one was from experimentation).
Here's what I'm using:
SELECT Kp_GroupID FROM Groups
WHERE NameActiveList = 'Yes'
Using quotes or not won't change the result of the query. They just aren't needed in this case.
On which table occurrence is your layout based? J_ParticipantGroup?
Is this a stored or unstored calculation field?
For those interested in seeing examples of ExecuteSQL controlled value lists, Adventures In FileMaking #1 has several. You can download this file for free from my FaceBook page.
Phil's example is what I used to create this file - it's an excellent resource.
This is calculation is in J_ParticipantGroup in the cActiveFilter field.
Indexing was set to "Minimal", I changed it to "All" after reading your comment, no change in behavior though.
After reviewing the example, it ought to be unstored.
If it is not unstored, it won't automatically update when data referenced in the query changes. We can use unstored fields in relationships if we use them with caution.
In this relationship:
LayoutTable::MatchFieldA = ValuesTable::MatchFieldB
where "LayoutTable" is specified in "Show Records From" in layout setup for our layout and "valuesTable" is the table occurrence that we specify in Manage | Values Lists as the source of values for a conditional value list,
MatchFieldB must be an indexed field and thus must be stored, not unstored. FieldFieldA, on the other hand, needs neither to be stored nor indexed. From what I can "reverse engineer" from observed behavior, FileMaker takes the value of MatchFieldA for the current record on the layout and matches it against the index of values from MatchFieldB.