Many Many to Manys?

[mrpez1]

I have a project database and a contractor database. Each project has four different types of contractor (Master Planner, Engineer, Subcontractor 1, Subcontractor 2). Each contractor can be any one (or more than one) of the four types on any number of projects. I need to be able to see: 1) a break down of which project each contractor is on and what their role is in each project. I can do this using a join file that has four different join fields linked to four different portals in the contractor database; one for each type of contractor. What I can't do is display the courses each contractor worked on in one list (portal?). The are broken up depending on the different roles the contractor had in different projects. Basically, I want to merge the 4 portals into one.....is this possible? Is there another way to display which projects each contractor worked on without regard to what their role was?

[Ugo DI LUCA]

Hi,

I may be wrong but a Many Many To Many could be handled using a MultiLine Item.

Does each line in your Join File holds a name in each field or do you hold one line for each contact ?

[mrpez1]

I have one join file that has four names, one for each contractor. I don't have a seperate join file for each contractor.

[cjaeger]

make that contractors list a single, return separated list and use it instead of the join file....

[Ugo DI LUCA]

Hi Christian,

Not so simple I think if he wants to use this file further more.

Mrpez,

I didn't meant a separate line item for each titles. I mean a line in your Line Item for each contact...

[cjaeger]

yes, you are right. Line Items. Just dropped by on my way to bed, didn't read properly ...

[mrpez1]

Ugo,

I'm not sure I understand your question. Attached is a simplified version of what I have so far. I'm looking to be able to combine the two portals in contractors into one.

Contractors.zip

[Ugo DI LUCA]

Hi,

This should get you started. ProjectAssign is a Line Item with one line per employee as each employee can be affected to more than one project.

There is one main adjustment to make, but you should find out what to do according to your needs.

Project2.zip

[mrpez1]

Ugo,

I've adapted your setup to my needs but I keep running into a problem. I want to display in the project database related info about the employees BUT not in the portal (There's way too much info to fit in the portal). Ideally, I'd like to have a button next to the portal (in the portal?) that would move to a second layout that displays the corresponding employee's contact info. It seems that once data is in a portal it becomes very difficult to use. Any ideas?

[Ugo DI LUCA]

Hi,

Brent (Live Oak) and Ray(CobaltSky) just gave 2 awesome answers to this question....

Don't tell it's not a clean solution.

This was for one field, but you'll understand that this can work for more than on field obviously.

IMO (sorry Brent), Ray's solution could be more accurate as a text field (vs a global) would change when you will navigate from record to record in the main.

[Ugo DI LUCA]

Hi,

As you need to play with three files in that situation, I corrected the first sample and add this feature in the new attachment.

As the portal is also set to enter new records, I added a little yellow button to the right of each lines, for the script

Project3.zip

[mrpez1]

Ugo,

Is there any way to allow creation of employee records from the project database or will this system only work with a known group of employees?

[Ugo DI LUCA]

Hi,

Of course you can, but it obviously wouldn't be related to anything, so you wouldn't do it from a portal.

If you were dealing with a one to one relationship, you would create a portal with a relationship Project_ID::Project_ID. But as you've got a many projects for one employee, you can't do it that way.

What you could however do is create a set of global fields and attach a script that would create a record in employee and fill in the fields using the global fields, with "set field" steps.

Now, for certain interface reason, you may like to recreate exactly the same "layout" within the Employee file. It's tedious but I know some times where you would like to do it that way.

Lately and easier, simply create a new record in the employee file and fill in the Name field and title function in the Portal.

But you will still need a script that will pull the data outside from the related file to the main (as your relationship employee::assign project is a "one-way" realtionship, the records entered in the portal would not update in the employee file.

...And you certainly would have to fill in more fields in the employee File.

...So the best method IMO.

Script a go to layout new entry in employee

Loop

Go Back to Project when over...

[mrpez1]

Nevermind... I wrote a script that seems to be doing what I need it to.

[mrpez1]

Looks like I posted my last one just as you were. I went with the second option. Thanks for the info.