Jump to content

Help with Self-Join needed


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

Recommended Posts

I'm trying to add a portal to my PEOPLE layout that shows a Person's MENTOR. The Mentors exist in the PEOPLE table just as those they mentor do.

 

So when I go to the PEOPLE layout, I want to see the person's information and a portal that contains their mentor. Also, when a Mentor's record is viewed, I need to see all the people they Mentor in the portal. Would this require 2 portals?

 

Creating the layout and portal are not the issue. I need to know how to properly create the relationship for this scenario.

 

It seems straight forward that a Self-Join is needed but I'm not sure how to setup the key fields and the join itself to make this work.

 

Thanks !

Link to post
Share on other sites

Hi Jason,

A quick question: while a mentor can have many students, do I presume a mentored person/student can have only one mentor?

If so, then you don't need a portal to show the mentor's name or details on a mentored person's layout, just the related fields, sans portal, will do. Use a portal when there is a 1:M relationship. Thus you will need a portal to show a mentor's students.

As this is a 1:M self relationship, each record should have, in addition to the record's primary key (e.g., "id") field, a foreign key field holding the primary key of the person's single mentor (e.g., "id_Mentor", or your preferred naming scheme). Then you can use that relationship both ways to show both the mentor (outside of a portal) and their students (inside a portal). You'll need 3 TOs, all based on the single PEOPLE base table: one providing the context for the layout, one "PEOPLE » MENTOR" (where the join is id_Mentor [foreign Key] = MENTOR::id), and one "PEOPLE » STUDENT" (where the join is id = STUDENT::id_MENTOR) for the portal.

I can mock up a quick example for you this evening, unless someone beats me to it.

hth,

Mark

 

[Edited to use "Student" for the mentored person, for clarity.]

Link to post
Share on other sites

Hi Jason,

 

Here is a file that illustrates the self joins I described.  In this example, you'd add students in the portal, which creates new records and establishes the relationship (I don't like the "blank row" method of creating 1:M records, but it's quick to implement for a demo).  In a more common scenario, you'd probably already have some records populated and then wish to either (i) assign each a mentor from among the existing records, or (ii) assign students to the portal from among existing records.  I didn't implement those for the demo.

 

hth,

 

Mark

Mentors and Students Demo.fmp12.zip

Link to post
Share on other sites

This caught my eye -- I don't like the blank row method either in a portal . . . how do you typically do this instead?

 

(I don't like the "blank row" method of creating 1:M records, but it's quick to implement for a demo).
Link to post
Share on other sites

Mike,

 

Agree with Lee's advice: basic stuff like this you'll remember better if you search it out yourself.  

 

That said, after you Google (or DuckDuckGo) the common techniques, also check out Kevin Frank's (attributed originally to Ugo Di Luca) slightly more advanced Magic Key technique.

 

hth,

 

Mark

Link to post
Share on other sites

Apologies, Mike.  It seems I misinterpreted your question.  

 

I really like the Magic Key technique, as it doesn't involve changing to another context to directly add records to the related table.  Check it out and see what you think.

 

Mark

Link to post
Share on other sites
  • 4 weeks later...

Hi Jason,

A quick question: while a mentor can have many students, do I presume a mentored person/student can have only one mentor?

If so, then you don't need a portal to show the mentor's name or details on a mentored person's layout, just the related fields, sans portal, will do. Use a portal when there is a 1:M relationship. Thus you will need a portal to show a mentor's students.

As this is a 1:M self relationship, each record should have, in addition to the record's primary key (e.g., "id") field, a foreign key field holding the primary key of the person's single mentor (e.g., "id_Mentor", or your preferred naming scheme). Then you can use that relationship both ways to show both the mentor (outside of a portal) and their students (inside a portal). You'll need 3 TOs, all based on the single PEOPLE base table: one providing the context for the layout, one "PEOPLE » MENTOR" (where the join is id_Mentor [foreign Key] = MENTOR::id), and one "PEOPLE » STUDENT" (where the join is id = STUDENT::id_MENTOR) for the portal.

I can mock up a quick example for you this evening, unless someone beats me to it.

hth,

Mark

 

[Edited to use "Student" for the mentored person, for clarity.]

 

I'm still having problems implementing this.

 

I have the Student part (portal and TO) working. I understand that.

However, the Mentor part is confusing.

 

Just to clarify the requirements:

-a Mentor can have Many Students

-A student can have ONE Mentor

-A Mentor can NOT be a Student

 

Looking at your example, the Person to Person>>Mentor relationship does not create records so why have the TO at all?

Link to post
Share on other sites

Hi Lee:

I don't know if you noticed, but there is a link where you say: "Hide blank portal row"

The link's address is: h t t p : / / site / (I double spaced it so it won't be a reallnk here).

If I click it on IE or Chrome it just takes me to "page cannot be diplayed" which is logical.

But on Firefox it took to direct to Salesforce page, very strange.

I even typed it myself, and went also to Salesforce.

I know this is unrelated to this topic, but still very strange.

Link to post
Share on other sites

I don't know if you noticed, but there is a link where you say: "Hide blank portal row"

The link's address is: h t t p : / / site /.....

I’m not sure why this link broke, but all it was, was just a search of Google using the keywords i.e. site:fmforums.com keywords.

 

I don’t have the specific search booked marked, but anyone should be able to take this approach and have success.

 

Lee

Link to post
Share on other sites

This topic is 2109 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.