You can use a match field in your Rate Cards table that is a calculation field that produces a return separated list of values when the rate card is a member of the "common" category.
Let's assume that your current relationship is based on the text values of "A" or "B" being present in the match fields of your defined relationship.
Then you can update your relationship from: rate cards::category = Order::order type to become: rate cards::cCategories = Order::order type
Define cCategories as:
If ( Category = "Common" ; List ( "A" ; "B" ) ; Category )
And now rate card data in the "common" category will always appear in your value list whether "A" or "B" is selected in the order type field.
Thank you so much. You just saved me a lot of time. It works great.
Although I still don't quite understand:
when I did debug and I am in the order layout for an "A" order, I tried to see if below expression will return 1, but it actually returns 0 !
List("A";"B") = Ord::Order Type
then I think the "common" item is NOT related to the order, how come it is able to show in the drop-down list?
Please point me out.
How a relationship matches values and how a boolean expression such as List("A";"B") = Ord::Order Type evaluates is not the same.
A return separated list of values ( list of values separated by the return character) can be produced with the List function, a check box formatted field or just by typing the values in and entering the returns manually. When used in a relationship, all that need match a value in the related table is any one of the listed values. The "matching logic" is "or" rather than "and". Thus if you have "A" <Return>"B" in the match field (what my calculation produces for "common" rate cards), it will match to records with an operator type of "A" or "B".
That solved my puzzle.
So FileMaker is using List function to implement "OR" logic when we need to build relationship based that. From the relationship GUI, I never found a way to do that.
It's one of two ways. It's quick and easy, but less flexible than the alternative. But it works well for this particular issue.
The other way to get "or" matching is with a third "join" table to which both of the other tables are linked in a many to many relationship.
In many ways, a return separated list of values is really a kind of "special join table" of just one field.