Relationships with OR?

[Virtus85]

Alright, I'm designing a solution that will auto-reconcile investment plans and find where discrepancies lie with a bunch of accounts.

Currently we do this by hand- using filemaker will save us GOBS of time. And hence make my boss happy. :

I've got it calculating differences no problem, but here's where I run into trouble:

There are 3 types of identifiers for each account; one table will have all 3, the other will have only 1 or 2 out of the 3.

IE:

Stock ticker name

Account ID type 1

Account ID type 2

when I put them into a relationship, filemaker only matches records that have the ticker AND ID1 AND ID2 all matching. Is there a way for me to make it match records with Ticker OR ID1 OR ID2 matching? (all of these identifiers are unique, and there arent going to be duplicate references)

hmm, to make it a little more clear:

Record 1 / Record 2

ATX / ATX

1020209 / *blank*

QECX2901 / *blank*

OR

Record 1 / Record 2

QED / *blank*

328243 / 328243

XIO2304 / *blank*

both of these examples must match, but currently only ones where all 3 are present will be matched.

Edited June 14, 2006 by Guest

[John Mark Osborne]

You can create OR relationships using multi-key relationships. A multi-key KEY field contains returns between each value. The return designates an entirely new match value. For example:

1¶

12¶

24

If the values above are placed in a key field, the parent record would match any related record that contained 1 or 12 or 24.

Your key is going to be a little more complicated to produce and will probably require a concatenation of some sort. This can be constructed using a calculation field.

[comment]

Here are two threads that you might find useful:

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

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