filter value list on value in separate related table
I'm struggling to figure out how to do something that seems relatively simple. I want a value filtered value list but the aspect that I want to filter on is in a third table.
the three tables I'm working with are:
Participants, Groups, and Programs
Programs is the a parent to Groups. There's a fourth, many-to-many table that links Participants to Groups. (see attached image)
I want users to be able to add Participants to Groups via a Participants layout. I want this to happen using a drop down menu that uses a value list populated with Groups. The thing is, I want to filter the value list so that it only shows Groups that are children to active Programs. So, the flag that I'm attempting filtering by is in the Programs table.
This must be possible, right? I've read a number of forum posts, examined sample files and watched several videos trying to figure out what I might do to get the desired result.
EDIT: 25 views with no answer leads me to think that something is unclear in my question or my approach? If that's the case, let me know and I'll fix it. Thanks!