Jump to content

Portal get records from many to many relation


This topic is 2758 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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. 


Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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....


Link to comment
Share on other sites

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.

whichpeople 1.png

whichpeople 2.png

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

This topic is 2758 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.