I think this is a question of an OR relationship?

[chasm24]

Sorry for being so clueless everyone, but I promise i've come a long way in a short time. I've just gotten into relationships and portals and am trying to set something up that i think is an OR relationship. Some guidance would be fantastic! I'm attaching two shots of relationship graphs.

Here's the deal: I have two tables: Projects and contacts (maindatabase). my first portal was very simple. if the company field in the contact database matched the firm field in the project database, then the portal listed the related records. great.

But in the projects table, there is another field called "financial firm". i would like to make a portal so that if the company field in the contacts database matches the "firm" field OR the "financial firm" field from the projects table, that both records are listed. I know this must be possible--how do i do it?

when i tried to make a separate relationship, FMP told me i had to create a new table occurrence but i have the sense that i'm making something that's dysfunctional. please see the attached relationship graphs from the contacts table (maindatabase) and projects table (projects or cglaprojectsupplement)

thanks for all your help. this forum is a life-saver.

*MainDatabase1(contacts)relationshipgraph.pdf

[chasm24]

here's the other rel'p graph from the projects table

Projectsrelationshipgraph.pdf

[Rob 7 Collins]

I'm no FMP pro myself, but maybe I can help. You want a portal from the Projects layout to the Contacts data, right?

FileMaker does not support multicriteria OR relationships, apparently (from what I read in the help files). If that's the case, you'll need a third table (this get's a little messy to impliment).

In it you can have fields for "firm" (a foreign key to the Contact record) and "type.of.firm" (like financial firm or client), "project", and a primary key to keep it fast (type number, options: auto enter serial number, always validate, required unuqie value, indexing on all). You would relate the "project" field to a projects table's field that uniquely identifies each project (often called a "primary key" field, like the company field in the contacts table). And, relate the "firm" field in this new table to the Contacts table's company field.

Now you've got to populate the new table: I'd do it by script if there are more than a handful of projects:

1. error capture on,

2. goto the first record,

3. while not error (loop I think)

4.a. if the project's firm field is not empty

4.b.i. create a new recrd in the new table

4.b.ii. copy the project primary key to the new table's project field,

4.b.iii. copy the project firm to the new table's firm field and

4.b.iv. set firm to "client",

4.b.v. commit the record in the new table,

4.c. if the project's financial firm field is not empty

4.c.i. create a new record ... (copy project prime key, fin. firm, set to "financial firm", commit

... and

4.d. in the project layout, goto the next record.

5.-end loop

After you run that your new table should have all the firm and financial firms, and associate them via the project field to each relevant project. Check to be sure, then print your script and delete it so it can't accidentally be triggered again.

Now, you can have your portal: Add a portal to the new table. Add the "type" field so the portal shows whether the record is a financial firm or a client, and add the fields you would like from the related Contacts table.

Attached is a sample file to demonstrate. Best luck

ORportal.zip

[Fenton]

Another way you can do this is to create a multi-line field, which simulates a join table. Create a calculation field, result text:

Firm &