Can a Portal selection be used to filter another portal "through" a Join Table?

[ratherbsailing]

Basically the setup is as follows:

3 Tables: People, Join-PeopleTask, and Tasks

The Join-Table relationships are working well to manage individual people attached to a Task. Adding new Tasks happens in a Form Layout based on the Tasks Table. It shows a detail view of each Task and then the people attached to it (in a portal). We are curious if we can add a way to click on (select) a person from this portal and based on that selection, have another portal below display what other tasks that person is attached to. Logically, if we click off the portal ("no selection"), we'd like that portal to be empty.

This will help in quickly determining a persons work load after they are assigned to a new task, without having to go to the Person's Detail page and looking at the portal that displays all the tasks they are associated with from there.

I have a global variable $$SelectedPerson that holds the UID of the Person we click on in the first portal, that was a simple script. I think I am stuck on understanding the relationship I need for the 2nd Portal to achieve this, if it's even possible. I've tried using a few of the Self-Self TO's that I already have setup for other stuff, but they haven't worked yet, and I realized I probably just don't have the correct relationship or context setup. Before going relationship TOG crazy I figured I'd ask for advice.

Again, I want to be on the Form Layout for the Tasks TO, thumbing through random records there, and when I click on a person displayed in the Join-PeopleTask Portal, to have a 2nd Portal display info not only from the Tasks table (like Id/Task Description/Status/Priority) but also from the People table (Id/FullName/Schedule - yes this stuff would repeat in every portal row). Obviously it cannot only display info from the Task I'm looking at, but from all of the Tasks the Person is attached to.

Any suggestions on the cleanest (relationship) way to set this up?

[comment]

You could do this without adding anything to your relationships graph, by using the ExecuteSQL() function to get a list of tasks associated with the person clicked. 
And to keep this even simpler, you could display this list in a popover placed in your existing portal.

Otherwise you would need to populate a global field (not a variable) with the ID of the clicked person, and add a relationship to (another occurrence of) the join table based on matching the global field. And possibly tack another occurrence of the Tasks table on the other end. And you would also need a script triggered by exiting the portal to empty the global field.

Note also that if your purpose is to determine a person's work load, you could have some kind of calculation field in the People table quantifying the load ( e.g. the sum of all hours this person has been tasked to), and display it right in your existing portal. 

 

[ratherbsailing]

It sounds like using SQL is the way to go, since I'd love to avoid adding more TO's with more TO's tacked on... etc. 

That being said, it sounds like I need to move on from using a portal to display the info and actually just use a new field? SQL is completely new to me, so I'm just working off what I've researched today. It sounds like what you're describing is adding a new field that would be an auto-enter calculation using an ExecuteSQL() function, and I could display that field (list?) either below the main Portal where I've selected a person, or as a popover? (The popover is a great idea, by the way, thanks for that!)

 Is that correct? Thanks again, comment!

[comment]

57 minutes ago, ratherbsailing said:

It sounds like what you're describing is adding a new field that would be an auto-enter calculation using an ExecuteSQL() function

Actually, what I meant is run a script that would set a global field to the result of ExecuteSQL(). You could also use a variable instead of a field.

 

1 hour ago, ratherbsailing said:

It sounds like using SQL is the way to go

SQL is the way to go if you want to display only a single value or a simple list (e.g. a list of task names). If you want to present tabular information - as you would in a portal - then not so much.

 

[ratherbsailing]

On 7/20/2020 at 5:05 AM, comment said:

Otherwise you would need to populate a global field (not a variable) with the ID of the clicked person, and add a relationship to (another occurrence of) the join table based on matching the global field. And possibly tack another occurrence of the Tasks table on the other end.

Thanks for all this, Comment! I did end up figuring out the SQL within a script, which was quite clean within a popover. Your clarification really helped.

However, after that I decided that I'd want to be able to click on that info and go to the related record on another layout... so I ended up making a portal how you described above. It's still relatively clean and gives me the additional formatting options that portals provide. Thanks again for your assistance !