AND matching?

[Saubs]

Hello, all--

I'm using a return-separated list in a primary key to do portal filtering. It correctly shows matching child records when a word in the foreign key (a concatenated return-separated list) exists in the primary key.

Q: Is it possible to do an "AND" or "EXACT" match? iow, if my primary key is this...

blue

green

red

...how can I get the portal to show child records whose foreign key is exactly blue¶green¶red (not blue OR green OR red)?

Hope that makes sense. Many thanks--

[TheTominator]

I think your question is technically the same as the one in this thread. Maybe the solution presented there will help you.

http://fmforums.com/forum/showtopic.php?tid/213894/post/352789/#352789

[comment]

Is it possible to do an "AND" or "EXACT" match?

AND is quite different from EXACT: in your example, a value of "blue¶green¶red¶white" would match by AND but not by EXACT. There is also the problem of order, i.e. should "red¶blue¶green" be considered a match?

In version 11, these problems can be solved quite easily by portal filtering. For previous versions, see:

http://fmforums.com/forum/showtopic.php?tid/171912/

http://fmforums.com/forum/showtopic.php?tid/172518/

[Saubs]

In version 11, these problems can be solved quite easily by portal filtering.

I installed v11 a couple days ago, haven't worked with all the new features yet. I've always used a gSelection key field to filter portals. Generally speaking, is the new 'Filter Portal Records' in the Portal Setup window intended to replace the use of that global key, or work in conjunction with it? (Perhaps it all depends on what the programmer's trying to accomplish?)

[comment]

Generally speaking, portals are layout objects and portal filtering works on the layout level. Among other things, this means that you don't need another relationship to DISPLAY a filtered related set - OTOH, calculations that work on the data level, e.g. Sum ( Related::Value ), will ignore the filtering.

[Saubs]

AND is quite different from EXACT: in your example, a value of "blue¶green¶red¶white" would match by AND but not by EXACT. There is also the problem of order, i.e. should "red¶blue¶green" be considered a match?

Order doesn't matter. I want "red¶blue¶green" to match, but I don't want "blue¶green¶red¶white" to match.

I'd like to try to solve this using v11's Filter Portal Records function if possible. It's starting to get a bit tricky for me as I think about this, because I'd need EXACT in order to filter out 'white', but EXACT would be too restrictive if the order wasn't exactly the same in both key fields (iow I still want "red¶blue¶green" to match!) Thanks so much-- Any suggestions greatly appreciated.

[comment]

I have posted a demo file here:

http://fmforums.com/forum/showtopic.php?tid/215008/

[Saubs]

That's great, Michael. Let me see if I understand what's going on under the hood:

You converted return-separated (vertical) lists in the parent and child records into (horizontal) text strings, with each value separated by a pipe; and you then used FilterValues and ValueListItems to alphabetize the values within the strings on both sides of the relationship. And that's how you get the keys to match, regardless of the order in which the values originally appear in the checkbox field.

Is that about right? Thank you so much for that, it was very helpful.

EDIT: I should mention that my comments are based on your MatchAnyOrAll demo file, not your MatchAnyOrAll11.

Edited May 28, 2010 by Guest
Clarification needed.

[comment]

Yes, that's roughly correct (apart from the order of operations). But this actually produces an EXACT match, not AND. There was another file that attempted to do that by using wild cards for unchecked values.