Jump to content
Sign in to follow this  
Jason H

Help with Self-Join needed

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 !

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


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

 

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?

Share this post


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.

Share this post


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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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