Find related (child) records from checkbox'd value list in parent

[Rich S]

This is a little convoluted so my apologies in advance.

I have a parent table (DISTRICT_STAFF) where the teachers' grade levels are captured in a child table (GRADE_LEVEL); they're entered using the portal circled below. (As an FYI, I use the List function to list them in the Grade_Levels field by the red arrow head.)  To look up teachers and to create lists/reports, I created a checkbox'd value list so users can pick which grades they're interested in finding out which teachers teach them. Mind you, their selections may not be in sequential order, e.g., K, 3, 5 , 12.

Here's where it gets tricky: How do I find (related) records from a checkbox'd value list? As far as I can tell, I'll need to perform a Find (in the GRADE_LEVEL table) for each ticked grade level  (using a loop) and aggregate the found records in the GRADE_LEVEL table where I can then create a sub-summarized list of teachers (by grade) and/or GTRR back to DISTRICT_STAFF to see which teachers teach those grades. 

As an alternative, could I relate the value list to the grades levels in GRADE_LEVEL so I can GTRR it? (I could replace the value list with a portal if that'll help.) 

Methinks I need a Costco-size bottle of Advil for this one. : P

TIA for your help.

[comment]

I am assuming you want to do an OR find - i.e. find teachers that teach at least one of the selected grades. The most straightforward way to do this, IMHO, is to search the related field in the GRADE_LEVEL child table and create a separate find request for each selected grade.

However, if you perform this find from a layout  of the DISTRICT_STAFF parent table, you won't be able to show the results as a list of teachers by grade. If you need such report, you should do the same find from a layout of the child table and summarize the found set by the FK field to the parent table.

In both of these scenarios the search will be performed in the original, indexable, data fields and not depend on any calculation fields.

--
Alternatively you could use GTRR to locate the matching child records (and another GTRR back to the parent table, if that's where you want to end up). But I am not in favor of adding an auxiliary relationship for something that can be accomplished by performing a find. 

 

[Rich S]

>The most straightforward way to do this, IMHO, is to search the related field in the GRADE_LEVEL child table and create a separate find request for each selected grade.<

Sounds like a plan, but how do I grab each, ticked checkbox's grade level from the value list and perform a Find for each? I imagine I'll use GetValue(listOfValues;valueNumber) somehow in a loop, just that I don't know how to set it up so it increments.

[comment]

Something along the lines of:

If [ not IsEmpty ( SomeTable::gSearchValues ) ] 
Enter Find Mode []
Go to Layout [ TableToSearch ]
Loop 
   Set Field [ TableToSearch::FieldToSearch; GetValue ( SomeTable::gSearchValues ; Get ( RequestCount ) ) ] 
   Exit Loop If [ Get ( RequestCount ) ≥ ValueCount ( SomeTable::gSearchValues ) ] 
   New Record/Request 
End Loop 
Perform Find []