You'll need to set up a different field as your match field. Checkbox formatted fields build a list of values where each checkbox value that is selected is stored in the field and a return character separates it from the other selected values in the field. And when your match field has a return separated list of values, it will match to records where any one of the values matches to the value in the other field. (and if both are return separated lists, any one value need match to only one value in the other field.) This makes for a nice OR type match of values, but you want the logic to be AND.
One approach is to set up a calculation field that replaces the returns with another character, but you'll also need to make sure that selected values are specified in a consistent order as the values from a checkbox formatted field are ordinarily stored in the order that the boxes were clicked by the user.
Step 1, get the values stored in consistent order.
Open Field Options for your check box field and give it this auto-enter calculation:
FilterValues ( ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotesHere" ) ; self )
And be sure to clear the "do not replace existing values..." check box.
Step 2, define a calculation field that replaces the returns with a different character:
Define a new calculation field with this expression:
Substitute (YourCheckBoxFieldHere ; ¶ ; "|" )
Select Text as the result type for this calculation field and use it in place of your original checkbox formatted field as the match field in your relationship.
OMG! If I could buy you a drink I would!
I've been beating my head over this for the past week.
It works like a charm.
Thank you so much!