Conditional Relationship?

[mvincenti]

My terminology isn't all there but... when setting up relationships, I can have a one to many relationship. When looking at the details, it is always "x = b" AND "x = c" AND "x = d" AND "x = e"... Is there any way to change the "And" to an "OR"?

Does anyone know what I'm talking about?

[comment]

An OR relationship is done by using a multi-line key field, i.e. a field containing multiple values separated by carriage returns. A record is related if it matches any of the values in the multi-line key.

[mvincenti]

I'm sorry, I don't understand. I'm anxious though, as it doesn't sound difficult. Can I give you my example?

I have 2 tables: Contacts and Jobs. A contact may be a "Broker", an "adjuster" a "referral" or an "agent". A job may have one of each. I want to relate the Contact record with the job. However, some jobs may have the same person as the broker and the referral.

I have a field in the Conatct record from which you select their role: "broker, adjuster, referral, etc". (checkboxes will allow multiple roles to be selected). However, the way i'm relating the tables, I can selected any contact to be included on the job BUT, I can't pull up each job for that contact because filemaker is looking to fulfill all of the relational criteria or none at all (hence, the need for an OR).

Attached is a copy of the database if you are so inclined. Any help is most graciously appreciated. It is more robust than the example just given.

(the file includes a work around which sort of fills the need. but, if you go to the "jobs" tab of the contact record, I'd like a portal to display all jobs that relate to this person in ANY fashion- be it a referral, broker, etc. once that is worked out, i'll apply the same scheme to the Companies record. This would also do away with the need for created duplicate contacts with simply different "roles".)

aft8.zip

[comment]

I'm afraid your file needs a LOT of work.

First of all, you should change all your relationships to be based on IDs, instead of names. You have the fields in place, but you're not using them, for some reason.

Next, if a person is capable of playing more than one role, you would indicate this in the Kind/Title field, by changing the field's display to checkboxes. Duplicating the record is really not the way.

There's a lot more, but to address the original issue: I have added the required multi-line field and the portal. But it is not a good solution. First, it too should be based on ID's, not names. And I suspect that what you really need is a join table between Contacts and Jobs, where each record would be an assignment of a single Person to a single Job, recording the role he plays in this job.

aft8.1.fp7.zip

[mvincenti]

Wow... thank you so much. Now I see what you mean. I usually do use IDs but I opted against it in this case. Not sure why...

And you are right- assigning the role in the Job record is a much smarter way of doing things. And now I see what you mean with the "OR" relationship. Much appreciated. I learned a lot. Thanks so much.