Complex relationships - can I use OR criteria?

[Guy Weymouth]

I currently have a global field (first_name_search) which relates to a field (first_name) in another table (individuals).

If you type "Bob" into first_name_search and there happens to be a first_name = "Bob" in the individuals table, then the portal displays "Bob". This is basically used to obviate the need for Find Mode and restrict users to searching for data in certain fields.

Now, I'd like the user to be able to "search" for a first_name OR a last_name.

I could have two global fields where you type in data into first_name_search to search for a first_name or type into the last_name_search to search for a last_name - and there would be two relationships; one for first_name and one for last_name.

Surely there's a way to have a relationship which says:

first_name_search = first_name

OR

last_name_search = last_name

It seems you can only use the AND operator in defining a relationship, not an OR operator.

Any thoughts?

[QuinTech]

Hi Widey. If you multi-line the keys; that is, if you separate each search term with ; it works as an OR in the relationship. Maybe you could do this:

Instruct users to use AND and OR in their "search" terms. Then build the relationship off a calc field equal to

substitute(substitute(theGlobalField," AND "," ")," OR ",":paragraph:")

It could get much more complex if there are more than two booleans -- for example, what do you do with "(Bob AND Wiley) OR Betty"? But this should give you a start.

HTH,

Jerry

[Søren Dyhr]

Yes it's a little dissapointing that when stacking realtionships under 7.0 the separator is always AND ...well I'm at least still trying to "de morgan" me out of it looking here and there in associative- and distributive laws to get my head around it. So return delimited list or repeating fields of keys as it has been since 3.0 ...is probably the only way???

--sd

[-Queue-]

You can nest the Substitutes in 7.

Substitute( theGlobalField; [" AND "; " "]; [" OR ";

[Fenton]

Since this is a relationship all you have to do is include the last name in the calculation field in Individuals.

Left(First Name; 1) & "

[-Queue-]

Or Left( First Name; Get(CalculationRepetitionNumber) ), a repeating calc with the desired number of repetitions.

[Ugo DI LUCA]

Hi,

And then trying again I found my way through without the refresh window step, involving nested ValueListItems...

For the kind of search in this thread, the Clairvoyance technique suggested by Fenton would be more accurate, but when you're looking for exact match in 2 possible ways...

MultipleSearch.zip

[-Queue-]

Nice job! I thought you were going on vacation.

One little tweak, make gsearchInput an auto-enter calculation with 'Do not replace existing value for field' deselected, equal to Substitute( g_searchInput; ", ";

[Ugo DI LUCA]

neat.

Well, got the project finally done on time, so waiting for the snow to come, I test and test and test and test and test and test and......

[Søren Dyhr]

Then you can enter a comma-delimited list into the search field and not be forced to use a

[-Queue-]

No, it would still behave the same, but users would be forced to enter carriage returns instead of commas or some other character that is more natural to them when making a list.

[comment]

I don't think this is of any practical value, but at least in theory:

blue OR red = not (not blue AND not red)

[Søren Dyhr]

I don't think this is of any practical value, but at least in theory:

blue OR red = not (not blue AND not red)

It escapes me what "de Morgan"'s rule has to do with it. It's another issue here since 1NF dictates atomicity of a table where ugo's template with JT's tweak denormalizes by allowing both name and surname be written next to each other in the same field.

lets se what the truth table says:

A | B || A + B

0 | 0 || 0

0 | 1 || 1

1 | 0 || 1

1 | 1 || 1

....Where XOR is identical except for last line where the result is 0, and if you type in the intire name of a person no related records exists.. JT's tweak encourage the user to type an entry which should have been validated against in the first place. But some issues are tough to handle via a validation - because some naming conventions allow ...say O'Brian or de Morgan you indirectly were hinting at repesents challenges to the developer....

--sd

[comment]

I was referring to something you said earlier:

it's a little dissapointing that when stacking realtionships under 7.0 the separator is always AND

Of course "a OR b" is possible within Find (multiple requests) and over a single relationship (multiline key).

But, as you said, when "stacking relationships", you're stuck with AND.

In theory, one could convert an OR statement into an AND statement (using de Morgan's laws) but, as I said, I doubt it would yield practical benefits.

[Søren Dyhr]

blue OR red = not (not blue AND not red)

I might be daft but how do you make outer "not" ??

--sd

[comment]

how do you make outer "not" ?

On a piece of paper, it's really easy.

In FMP, you probably don't - that's why I said it doesn't seem practical.