or-type relationships

[mortee]

Hi,

can someone help me about how I can create a relationship with an OR-type criteria?

Let's assume I have customers and events. Events can be open, or associated to a set of customers. For each customer, I want to list all the open events, and also all the ones associated with that customer.

Is there a way I could do this in one relationship?

thx

[mr_vodka]

Take a look at this thread.

[mortee]

That's OK, but it doesn't help in many cases.

For example, I may have multiple independent relationships between the records in my tables (that is, the match fields of the relationships are different on both sides), and yet I'd like to collect the results in one related record set, with ORing the predicates.

The List() approach detailed in the referred topic works with multiple fileads on both sides, but it facilitates a match between ANY listed fields from the two sides, not only matches in corresponding fields.

Edited April 1, 2007 by Guest

[comment]

I am not sure I follow this correctly. In your first example, an open event needs a dummy parent key, e.g. zero or the string "Open". The matching field in Customers would be a calculation of:

CustomerID & ¶ & "Open"

In your second example, each "pair" of match fields should use a unique prefix (or any other unique characteristic) to prevent a false positive match.

[mortee]

Yeah, I coined unique prefixes up initially, too. The problem is, the List-approach seems to work only with numbers. Or maybe I did something (matbe indexing-related?) wrong - but the result was that the relationship didn't show anything when I worked with fields of type text.

Also, I'd like to avoid using structured IDs (as to the suggestion of using field values with some unique characteristic).

[comment]

The List() function accepts text arguments and the result returned is text. Make sure the calculation field's result type is also set to Text.

ID's prefixed with a string also need to be of type Text.

I am not sure what you mean by "structured IDs". A date value formatted as "yyyy-mm-dd" will never match a time value formatted as hh:mm. That's what I meant by 'unique characteristic'.

[mortee]

Yes, I know that I can use List() to combine textual values. The problem is, when both sides of a relationship is text resulting from a List(), then FM seems to simply match by the whole string value, as opposed to find a match when any line of the right value matches any other line of the left value.

By structured IDs I mean that I won't use any semantic information built into ID values - e.g. any IDs < 10000 represent employees, the others customers, or something like that. I hope I'm clear.

Also, this List() approach doesn't work if some of the predicates to be ORed together would use a different operator than the rest. That is, I can't freely combine any operator expressions with ANDs and ORs...

[comment]

FM seems to simply match by the whole string value, as opposed to find a match when any line of the right value matches any other line of the left value.

That shouldn't happen. Can you attach a small demo showing that?

I won't use any semantic information built into ID values - e.g. any IDs < 10000 represent employees

I agree.

I can't freely combine any operator expressions with ANDs and ORs...

A lot is possible, but it is difficult to discuss in abstract. I'd suggest you take a look at these two threads:

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

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

[mortee]

That shouldn't happen. Can you attach a small demo showing that?

I've figured it out since. Originally I've put the text constant for the global match value into a global field, and used that in the calculation to combine the match field together. Unfortunately, FM doesn't notify the user in this case that this disables indexing in the resulting calculation (because of the used global field), and thus it can't be used as the remote side of a relationship. So the portal just showed nothing, and I thought the whole concept just didn't work.

A lot is possible, but it is difficult to discuss in abstract. I'd suggest you take a look at these two threads:

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

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

I'll check them out. Thanks for the help.