Portal get records from many to many relation

[amg78]

Hello i'm new in FM, i making a data for my company, and i have a problem i been like to 2 days try to solved with out any luck, i have 3 tables:

the company(table "company") can have many projects (table "projects") and the projects can have many person in charge (table "in_charge") and i have a 4th table "project_incharge" in other to add specific  person to specific project so far i got the picture clear the problems is wen i add a portal to the projects table, and to a a record to it from the people in charge i just want to see the people charge for the current client, but i see the list from all the client in charge.

so the idea asigne some in charge people to a specific  project  that work for a specific client, not all the in charge for all the clients i hope i make my self clear. 

 

[comment]

35 minutes ago, amg78 said:

i hope i make my self clear. 

Not really. Where exactly do you have a many-to-many relationship? Can a person be "in-charge' on more than one project? If so, I would expect a structure of:

Companies -< Projects -< ProjectRoles >- People

I don't see in your description that you have a table of People (so how can you "add specific  person to specific project"), and I don't understand what the "project_incharge" table represents in real life.

 

 

[amg78]

Thanks i just attach a diagram of what i want to accomplish i hope is much clear now, thanks again

 

[comment]

Do I understand this correctly:

• You are on a layout of Projects table;
• You want to assign another person to the current project;
• You want to use a value list showing only persons that have already been assigned to any project of the client that owns the current project.
   

 

[amg78]

yes  !!!! thats it 

[comment]

There are several ways you could go about it - I believe the simplest one would be this:

1. In the Client table, define a calculation field cPersonIDs (result is Text) =

List ( Person::Person PK )

 

2. Add a new occurrence of the Person table to the relationships graph and link it to Client as:

Client::cPersonIDs >- Person 2::Person PK

 

3. Define a value list using values from Person 2:::Person PK, include only related values starting from Project.

Edited October 3, 2016 by comment

[amg78]

18 hours ago, comment said:

3. Define a value list using values from Person 2:::Person PK, include only related values starting from Client.

Sorry in which table i device this 

[comment]

Value lists are defined in the file, not in any particular table - see:
http://www.filemaker.com/help/14/fmp/en/html/edit_layout.10.49.html#1198115

[amg78]

i try what you say with out any luck , attack you will see, thanks for your patience  

test.fmp12

[LaRetta]

You've skipped item #3.  Also, if a person can hold different roles depending upon the Project then the field 'Role' should be in the join table (ProjectRoles) along with the ProjectID and the PersonID instead of in the Persons table.

[comment]

I am afraid I cannot look at your file, but I see I have made an error in my description: the value list needs to be defined to start from Project, not from Client. Otherwise it may work only with existing records in the portal, not new ones.

[amg78]

OK i solve the issue of showing only related person from a current company but in the portal i just can add one row, how can just select one client and let me any more than one person to the portal 

test.fmp12

[bruceR]

1. You didn't follow any of Comment's directions. You created no list of existing project people.

2. Your planned approach will work; but it does not allow you to create project roles for a client that has never had any projects before.

3. It does not allow you to add new people to a project; people who have never before worked for any project of this client.

testMODBFR.fmp12

[amg78]

thanks so much for the advice and the file is really helpful but  i still can get the result the i want 

[amg78]

i think  solve it .... i
 

i add i dropdown menu from client_pk with this i add to the project the client that ones the project 

then i add (+) BUTTON in my portal  with this script 

Go to Object [ Object Name: "Portal" ] Go to Portal Row [ Select; Last ]
Set Field [ ProjectRoles::client_name; project::_client_fk ]
Commit Records/Requests [ No dialog ] 

so i add new row with just the person from current client ...

may be is not the best way, i'm open to improvements  thanks all for you help and ideas....

test.fmp12

[bruceR]

Doesn't work at all.

Doesn't properly present people in value list.

Also: Role field does NOT belong in Person table. 

If the Role field is in the Person table that that person has the same Role for every project they are assigned to.

[amg78]

thanks I will double test it, sorry i made a mistake using role is more like  the person position in the company for example i will have in company one the same IT Manager, and he can be assign to any project from the company one...

thanks again 

[bruceR]

See updated example, attached.

testMODBFR.fmp12

[amg78]

Wow!!!! thanks a lote, work really really nice, is perfect, thanks Bruce