How to create an "OR" relationship?

[Markus Musik]

I am adding a new table to a system. The purpose of the new table is to be a personalized gateway into this rather large existing system. I am trying to define a relationship where the current projects for the user are presented in a portal on the gateway layout. So far so good, and it is working… with a catch.

 

The problem is the user might be assigned to one of three different functions in the existing file the data is pulled from. The user's name could appear in one of three discrete fields, in any of thousands of records. 

 

I have it working based on a relationship to one of these three fields. But I can only find a way to have an "AND" relationship to the other two fields. What I need is an "OR" relationship: the user name in the gateway table related to function 1, OR function 2, OR function three in the second table. 

 

After I got things working with a single relationship I created two more occurrences of the second table in the relationship chart and related the remaining two function fields to the user name in the gateway table, but it still only pulls data from the first relationship I established.

 

Any ideas?

[Josh Ormond]

To retrieve the data from each of the table occurrences:

List ( TO1::fieldName1 ; TO2::fieldName2 ; TO3::fieldName3 )

I'm not sure this is the most efficient approach for your structure. But that's for a different discussion, I think.

Edited August 4, 2015 by Josh Ormond

[comment]

The user's name could appear in one of three discrete fields

A possible solution would add a calculation field (result is Text) =

List ( Field ; Field 2 ; Field 3 )

then use this field as the matchfield opposite UserName in the "gateway" table.

 

A better solution would probably replace the three fields with a join table of Roles.

[mr_vodka]

You really should restruture your design.  You shouldnt have 3 different fields for this but rather a related table of assigned members.

Project --< Team

In the team table there should be:  user, role

[Markus Musik]

mr_vodka, I don't believe that would work because one individual's role will vary from project to project, and one individual could be assigned two different rolls on one project. But perhaps I am missing something...

[Josh Ormond]

You may need to include the project in the relationship, but that's the general idea.

Your solution can then pick out the role for the specific project/task they are trying to accomplish. It's much more dynamic...for example, what happens when you have a person that has 4 roles on a project???

[comment]

one individual's role will vary from project to project, and one individual could be assigned two different rolls on one project.

You are describing a many-to-many relationship between Individuals and Projects. As I have already mentioned, the proper solution is to add a third table to join the two:

Individuals -< Roles >- Projects

The Roles join table has (at least) these fields:

• IndividualID
• ProjectID
• Role

This allows you to assign an individual to any number of roles in any project.

 

Edited August 4, 2015 by comment