Help with Self-Join needed

[Jason H]

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 !

[Mark Scott]

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

[Mark Scott]

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

[mikedr]

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

[Lee Smith]

Mike, 

 

Please learn how to search for answers to your questions.

 

There has to be a dozen examples of this technique. Start with this Google result. Hide blank portal row

[Mark Scott]

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

[mikedr]

Sorry, I should've been more descriptive. What I do now is have a button that has a popover to create a new related record. I was more curious about your preferred technique than anything else.

[Mark Scott]

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

[Jason H]

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?

[carlosnorvik]

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.

[Lee Smith]

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