Relationships with OR?

June 14, 200619 yr

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, 200619 yr by Guest

June 14, 200619 yr

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.