Find by one or more ticked Checkbox'd values

[Rich S]

I'm stuck (again.) Temporarily, I created this goofy setup where DISTRICT_STAFF is the parent to the GRADE_LEVEL table (hence, the portal on the left.) As is, the user clicks the Perform Find button after entering either one grade level or a range of grade levels, but that's no good because what if the user wants to find grades 2, 6, and 10 (and no others)?

On a lark I thought of using a checkbox'd value list but I can't even think of how to go about coding a script to find the ticked grade levels. Any help with figuring out the schema, or a better way of doing this, would be greatly appreciated.

[comment]

4 hours ago, WF7A said:

what if the user wants to find grades 2, 6, and 10 (and no others)?

This was discussed (again) just a few days ago:
https://fmforums.com/topic/107782-searching-for-students-by-belt-rank-color/

The "and no others" part can be read in more than one way. The method proposed there will find staff that have at least one of the checked grades - regardless of any other grades they might have. If you want something else, please clarify.

 

[Rich S]

Sorry, my poor Engfish (English that stinks) strikes again. What I was referring to is that most of the teachers in our district teach more than one grade, so if I want to find teachers that teach only one grade--say, 1st grade--and no other grades, their records will be the only ones to pull up if "1st" is chosen as a search criterion, not teachers that teach 1st and other grades.

I'm sure it exists somewhere for FileMaker Pro, but it would be cool to perform a find for records identical to the same way used when queuing pages to print, e.g., 4,5, 7-10.

Thanks for the post reference--your script will work great. (No surprise there!)

 

Edited April 21, 2021 by WF7A

[comment]

43 minutes ago, WF7A said:

if I want to find teachers that teach only one grade--say, 1st grade--and no other grades, their records will be the only ones to pull up if "1st" is chosen as a search criterion, not teachers that teach 1st and other grades.

That would be quite difficult to accomplish with your current structure. Filemaker finds records that satisfy the given criterion - not records that satisfy the given criterion and no other. In order to perform such find, you would have to find teachers that teach 1st grade, then omit those that teach any other grade.

It would be easier if you used a checkbox field in the Staff table instead of records in a child table (do you actually have any real information in that table?). Then you could search for an exact match of the search checkbox - provided that both are sorted in the same order.

 

54 minutes ago, WF7A said:

it would be cool to perform a find for records identical to the same way used when queuing pages to print, e.g., 4,5, 7-10.

It's possible, but it would require work to break the entry into individual requests. And it would fail if the user did not follow the required pattern for the entry.

 

[Rich S]

Agreed, and I just created a field in the parent/DISTRICT_STAFF table and created a few quick calcs to copy the data into a text field so I'm good. I'll create a field to convert the text values, Preschool and K, into numeric values so when called upon to do anything with sorting, the records will appear in grade ascending order, e.g., Preschool could be -1; K = 0 ; 1...12 would stay the same.

Edited April 21, 2021 by WF7A

[comment]

6 minutes ago, WF7A said:

created a few quick calcs to copy the data into a text field

The problem with that is that performing the find on an unstored calculation field will be slower.

 

6 minutes ago, WF7A said:

convert the text values, Preschool and K, into numeric values

That's not really necessary. You can use the value list to sort the entered values. For example, if you have a value list of:

Preschool
K
1st
2nd
3rd

And you enter (in this order) into a checkbox field:

2nd
1st
K

you can use:

FilterValues ( ValueListItems ( "" ; "Grades" ) ; YourCheckboxField )

to get:

K
1st
2nd

 

[Rich S]

Brilliant, as usual!