Search via multi-criteria relationship

[Mandu]

I'm enjoying the power and simplicity of "searching" a database using a few value-list checkbox fields, a multi-criteria relationship, and a portal in which to show results. It's much more satisfyig and immediate than FindMode / PerformFind for iteratively refining criteria. I don't know what people call this technique; I stumbled across it by accident. I'd like to learn more about it.

The problem I'm having is in implementing the "any" criterion. Let me describe the database first:

The "Meetings" table has records containing searchable fields Location, Day, Time, and Qualifiers. Each of these fields is value-listed; Location/Day/Time each take exactly one value, and Qualifier may take zero or more values.

To specify "any" for the first three fields, I simply store the entire value list into the global fields that holds the match criteria (there's an "any" button beneath the checkboxes that does just that). But for Qualifier, this won't work - because Qualifier is not required to contain a value at all.

I thought about adding the value "Any" to the value list for Qualifiers, and then making sure that every Meeting record's Qualifier field contained, at minimum, the value "Any". But it's a bit of a hack, and would require more hackery to hide it from the end user.

Has anyone encountered and solved this problem?

Thanks,

Chap

[Ender]

Since the relationship can't match on blank values, you need to use a calculated key on both the parent and the child side. On the child side, the key would be something like:

cMatchField (calculation, text result) = field & "zzzAll"

and on the parent side, the key would be something like:

cParentFilter (calculation, text result) = case(isempty(gFilterField); "zzzAll"; gFilterField)

[Mandu]

So that's what a "filtered portal" is!

Great! Thanks so much. Works exactly like I wanted.