13 Replies Latest reply on Apr 16, 2017 8:50 PM by philmodjunk

    Using a portal to reference other records in same table based on criteria

    velocitytino

      Hello everyone. Bit of a Filemaker noob, so if this question has been answered elsewhere I'd appreciate some help.

       

      I've got a filmmaker networking database made up of People, Films, Companies, and Events, and all of them are connected by many to many relationships through join tables (i.e. one person can have multiple roles in a Film, one person can be in multiple films, etc.) On each record, I have a checkbox that the user clicks to mark whether this is one of their "Goal" people (or companies, or films, etc.) What I want to do is have portal views on each record's layout that display whether they are connected to another record that has a checked box, and if so, how they are connected.

       

      For sake of example, lets say I enter information for Michael Bay, Stephen Spielberg, and Megan Fox in 'people', then I enter information for Saving Private Ryan, Transformers, and Pearl Harbor in 'films', and I also enter information for 20th Century Fox, Paramount, and Dreamworks in 'companies'. Maybe I'm not too terribly crazy about Megan Fox, but I'd like to get to work with Spielberg and Bay. And maybe I'm more interested in working with Dreamworks than with Paramount and 20th Century, so I click the checkboxes for Spielberg, Bay, and Dreamworks in their respective records. If for some reason I know Megan Fox, I look at the portal on her record and I see that she is connected to Michael Bay because he directed Transformers, which she had a lead role in. And she is connected to Spielberg because he Executive Produced Transformers. She is also connected to Dreamworks because Dreamworks was one of the distributors for Transformers. So then if I want to contact any of these people/companies, I know that I can use my acquaintance with Megan as a starting point, and if she's nice she could even introduce us.

       

      I've got this working about 75% of the way - the problem is that the portals end up referencing the same record that they're originating from. So if I look at Spielberg's record, instead of seeing that he worked with Michael Bay and Dreamworks for Transformers, I'll instead see that he worked on Transformers and Saving Private Ryan, which is information that already exists in another portal on the same layout. Is there a way to use portals in the way I'm describing? Is there a special relationship or filtering method that I have to use? I can attach screenshots if this needs to be clearer.

        • 1. Re: Using a portal to reference other records in same table based on criteria
          philmodjunk

          A screen shot of your relationships graph would be helpful.

          • 2. Re: Using a portal to reference other records in same table based on criteria
            velocitytino

            database relationships.jpg

             

            Sorry it took me so long to reply. I had just gone to work when I got the reply.

             

            btw, this is the chart in its current incomplete state. I'm going to add more tables as I add more features, but I want to make sure that what I do have works before I add more in.

            • 3. Re: Using a portal to reference other records in same table based on criteria
              philmodjunk

              If I understand you, You are trying to make connections between one person record and another by looking at a portal to persons from a layout based on persons. And that won't work.

               

              What will work, is to create a "self join" between two occurrences of your person table. Then you can base the layout on one occurrence while selecting the second occurrence for the portal. The relationship that you specify between the two occurrences will control what you see in that portal.

               

              I think that you want to see all persons that are somehow, someway connected to the current person on the layout that also have the check box selected in GoalPerson. That "connection" might be a common project, common event or common company.

               

              Is that a correct restatement of what you want to do?

              • 4. Re: Using a portal to reference other records in same table based on criteria
                velocitytino

                Yes! That's exactly what I'm trying to do. About half an hour ago I figured out that I would have to use a self-join relationship, so I gave that a try, but the portal in my layout was still returning the same person that I was currently looking at. It would only return that record if the checkbox was selected, so I know I at least got that part right.

                 

                If possible, I'd like to display one record for each project/event/company had in common with the goal person, not just one record per goal person. Right now, it's only displaying one record per person.

                 

                Here's what my relationships graph currently looks like:

                 

                database relationships.jpg

                • 5. Re: Using a portal to reference other records in same table based on criteria
                  philmodjunk

                  To repeat:

                  The relationship that you specify between the two occurrences will control what you see in that portal.

                   

                  Since you chose to match records by person ID, the current record matches to itself and you get only one record in your portal, the same record as is current on your layout. It's done exactly what you designed it to do, not what you want it to do.

                   

                  What complicates this is that you have three different types of connections from three different join tables. It's not impossible to do this from a single portal, but to keep it simpler, let's put three portals on your layout, one for each type of connection. That might be a useful approach in any case.

                   

                  So create three occurrences of Person: Person|Project, Person|Event, Person|Company. link each of these to one of the three join tables by PersonID.

                   

                  Now you can place portals to these three new occurrences of person and you'll see all people connected to the current person record by one of those three possible connections. That gets you close to what you want, but you'll have two groups of Person records that you want to exclude--the same person as the current person record and any persons where the "GoalPerson" check box is not selected. We can set up a portal filter to exclude them from these three filters. Here's the filter expression for a portal to Person|Project:

                   

                  Person::personID_pk <> Person|Project::personID_pk AND

                  Not IsEmpty ( Person|Project::GoalPerson )

                   

                  You can copy/paste this filter expression and just change the table occurrence names for each portal.

                  • 6. Re: Using a portal to reference other records in same table based on criteria
                    velocitytino

                    Okay, I created a Person|Project table occurrence and linked it to the PersonProject_Join table by personID, but I'm getting the same problem. When I don't include the portal filter calculation, the portal simply does the same thing it did before, only showing one record, and showing the same record as the one I'm currently on. When I use the portal filter, nothing shows up in the portal.

                     

                    I'm getting the impression that for this to work, I shouldn't connect the PersonID_PK in the Person|Project table to the PersonID_FK in the PersonProject Join table, but maybe I'm wrong.

                     

                    Here's what the relationship graph looks like for the first portal.

                     

                    database design image.jpg

                    • 7. Re: Using a portal to reference other records in same table based on criteria
                      philmodjunk

                      Apologies

                       

                      I've spelled out the wrong relationships.

                       

                      We are going to need a different approach. ExecuteSQL is probably the most direct method but since you probably are not to speed on SQL queries, let's try this method:

                       

                      Define a calculation field, cProjectIDlist, in Person with a text result type and this expression:

                       

                      List ( person_projectjoin::projectID_fk )

                       

                      set up this relationship using a new occurrence of the join table:

                       

                      Person::cProjectIDList = person_projectSameProject

                       

                      disconnect person|project and connect it to this new occurrence of the join table by personID. Your portal should now work.

                      1 of 1 people found this helpful
                      • 8. Re: Using a portal to reference other records in same table based on criteria
                        velocitytino

                        Okay, so if I understand you correctly, my relationships diagram should now look like this:

                         

                        problem 4.jpg

                        We're getting close, but now a new problem cropped up, and I'm not sure if I entered a calculation wrong or constructed a relationship wrong. I'm only getting cross-references for a single one of my checked records. The cross-references will appear and disappear in the other people's portals depending on whether "Goal Person" is checked, but only for the first record. None of the others are cross-referenced in any layouts for some reason. I'll try to explain with three pictures:

                         

                        problem 1.jpg

                        This is the second record. In the Goal People Projects is the portal we're trying to construct. It references Jim Vasil, who also has a checkbox, and excludes Amanda, which is exactly what I want. But then...

                        problem 2.jpg

                        We go to Jim's record. Amanda should be there too, since her record is also checked and they both worked together on "Ben Hurrpaderp." (It was late and I was making up stupid project names.) But nothing shows up. Again, in a third record...

                         

                        problem 3.jpg

                        We have Maria, who isn't checked as a goal person, so she shouldn't show up on anyone else's record. But she worked with both Jim and Amanda on "Ben Hurrpaderp," so they should both be listed. But for some reason it's just Jim. If I uncheck Jim, he disappears from both Amanda and Maria's portals, so at least that bit is working.

                         

                        Just to try something out, I deleted Jim's record to see what would happen to Maria's Goal People Projects portal. Now instead of Jim, Amanda is listed on that portal. I recreated Jim's record and checked his "goal people" box, but he doesn't show up on the Amanda or Maria's portals. Now, Amanda shows up in his.

                         

                        So it seems to me that the current setup is only evaluating the first record in the table for some reason.


                        I'm not 100% sure where the problem is. I've included a dropbox link to my project if that helps.

                         

                        https://www.dropbox.com/sh/kfi0g0uv9nsx7jn/AAAccuETLFgCdoPKuQkwrIQ-a?dl=0

                         

                        And by the way, THANK YOU SO MUCH for your help so far! Even though the problem is still there, it's 95% of the way to being solved, and it's thanks to you! I would have been totally lost trying to figure this out.

                        • 9. Re: Using a portal to reference other records in same table based on criteria
                          philmodjunk

                          The portal on the Goal People Projects tab should be a portal to Person|Project, not a portal to Project.

                          1 of 1 people found this helpful
                          • 10. Re: Using a portal to reference other records in same table based on criteria
                            velocitytino

                            Ah yes! The portal is now working!

                             

                            There's only one other thing that I want it to do, but this is the core of what I was looking for, so I'm happy with this. My next question is just for some extra-useful functionality, but it might change the whole setup.

                             

                            Currently, the portal is displaying one record for each person who worked on a project in common with the current person, which is exactly what the relationships and calculations have set it up to do. Is there any way to list all the projects that the current record had in common with each person (such as a portal within a portal or a list calculation.) i.e. Maria and Amanda both worked on "Ben Hurrpaderp" and "Space Fights 1990" together, so in the portal for Maria's goal people connections, it would be useful to see both "Ben Hurrpaderp" and "Space Fights 1990" next to Amanda's name.

                             

                            I tried fiddling around with this earlier, but couldn't get it to work (again.) I can probably get rid of the "Job" and "Character Name" fields from the portal, since getting that to work would result in a number of records that would duplicate both the person and the project in the display, and that would just get cluttered.

                             

                            Am I making any sense?

                             

                            If that would require too much of a rework, then I can consider the question answered. But it would be useful to see not only who the connections are, but which projects they worked on together (beyond just the first record they have in common) at a glance.

                            • 11. Re: Using a portal to reference other records in same table based on criteria
                              philmodjunk

                              You could base the portal on personProject_sameProject and include fields from both the join and person occurrences.

                              2 of 2 people found this helpful
                              • 12. Re: Using a portal to reference other records in same table based on criteria
                                velocitytino

                                Thank you! That solves it! Just an FYI - I figured out I had to create a new instance of the project table to pull the info from the jobs in common that I was looking for. It's below.

                                 

                                But this accomplishes what I wanted the portal to do! Thank you very very much!

                                solved.jpg

                                • 13. Re: Using a portal to reference other records in same table based on criteria
                                  philmodjunk

                                  When you can take a suggestion and extend it successfully, that's excellent confirmation that you not only followed the directions, but understood them.