14 Replies Latest reply on Jan 26, 2014 6:01 PM by slayden@msn.com

    Showing multiple records from a table on a layout

    slayden@msn.com

      I'm trying to show distinct Contacts by role on a layout based on Transaction that I need to get distinct data out of a JOIN table onto a layout

       

      Are the only ways to do this multiples portals on the layout or multiple Contacts table instances?

      What's best and why?

       

      1. Transaction (fields I need to fill)
        1. Borrower
        2. Realtor
        3. Referral Person
        4. Loan Officer
      2. Contacts
        1. ContactID (match to Join_ContactID)
        2. FullName
      3. JoinContacts_Roles_Transactions
        1. ContactID (joins to contactID)
        2. RoleID (joins to roleID)
        3. TransactionID (joins to TransactionID)
      4. Roles
        1. RoleID
        2. RoleName

       

      Thanks in advance for any help.

        • 1. Re: Showing multiple records from a table on a layout
          wimdecorte

          On a Transactions layout, the portal to "JoinContacts_Roles_Transactions" would show all roles for that transaction.

           

          What is your understanding of "Distinct" in this case?  If the same person has more than one role for a given transaction he should be listed only once?  Seems strange to me.  I'd rather see all the roles even if it lists the same person multiple times?

          • 2. Re: Showing multiple records from a table on a layout
            Vyke

            I was thinking the same thing but then I can understand if you have a situation where someone could be both a vendor and a customer or if someone purchases for themself and for someone else (ie company purchaser). The real question I would have would be, why would a single transaction have a contact listed more than once?

             

            I'm guessing the way he wants to pull it off would require calculation fields with ExecuteSQL statements using SELECT DISCTINT() otherwise you would need to create a calc that pulls a list of contact ids to the Transactions table, then have TO's related via Contact ID & Role assuming you create a field in Transactions storing that role you want to see.

            • 3. Re: Showing multiple records from a table on a layout
              wimdecorte

              I would almost certainly not use calculated fields for this but gather the data through a scripted workflow as roles are defined / set / recorded.  Should be a lot more efficient than having an unstored calc that fires every time a layout is drawn even if the roles have not changed.

              • 4. Re: Showing multiple records from a table on a layout
                slayden@msn.com

                By scripted are you thinking I should pick up the ids in the join table, and set fields in the transaction table?

                 

                I have a similar solution where I set the person ID in the field I want, then overlay the fullName related field.

                It's fast, but it requires multiple instances of the contacts table in graphs.

                 

                I have another solution that has a portal, but with 39,000 contact records, it's slow....

                 

                thanks for hanging in there with me on this

                • 5. Re: Showing multiple records from a table on a layout
                  wimdecorte

                  You haven't explained yet what you have in mind for "distinct":

                  - show only one person if that person has multiple roles

                  or

                  - show only one role if that role is shared between multiple people

                   

                  As to the mechanism: I am most definitely NOT saying to set those fields in the transaction table.  Leave them in the join table.  That way you will have flexibility to add roles in the future.

                   

                  You mention a portal at the end and 39,000 contact records: that's not right.  The portal on the transaction layout would only show the x number of join records, not the contact records.

                   

                  I'm also not convinced about the multiple instances of the contacts table that you'd need for this.

                  • 6. Re: Showing multiple records from a table on a layout
                    Vyke

                    Yeah, I am really not a fan of doing it that way either, but it's just one possible (bad) solution that would "work". I'm interested in seeing the answers to what you've asked as well.

                    • 7. Re: Showing multiple records from a table on a layout
                      usbc

                      Hi,

                      Your question begins with, "I'm trying to show distinct Contacts...".

                      Your possible solutions may broaden or narrow based on what, exactly, the end user needs to do from this "showing".

                      Do the need to modify the content ? Do they need to search on it ? Or do they just need to see it ?

                      • 8. Re: Showing multiple records from a table on a layout
                        slayden@msn.com

                        My initial mistake was to use the word "distinct" in it's english use, forgetting that it's a SQL statement.  Poor communication of the issue in that way.  I apologize.

                         

                        I'm script to put the ID's of the transaction, contact and Role into the join table for each person involved in that transaction)

                        Where I'm struggling is how to best bring it back to the layout the best way in filemaker.

                         

                        Should I

                        1. use a single portal to list "Transaction People and their roles)?
                        2. Multiple portals with one "role person" per portal
                        3. Populate indivual fields in the transaction layout with appurtenant fields like BorrowerID, REALTORID, AppraiserID, ReferralSourceID, etc., and overlay the field Fullname from the Contacts tabl

                         

                        re #3 above: 

                        1. Is there a problem overlaying a calc field, as that seems to just show blank, when in the Contacts table it's showing data.
                        • 9. Re: Showing multiple records from a table on a layout
                          erolst

                          Why are these fields (of which I assume they are roles) in Transactions …

                          slayden@msn.com wrote:

                           

                          1. Transaction (fields I need to fill)
                            1. Borrower
                            2. Realtor
                            3. Referral Person
                            4. Loan Officer

                          … when you have a RoleNames table and a roleID foreign key in your join table?

                          • 10. Re: Showing multiple records from a table on a layout
                            wimdecorte

                            slayden@msn.com wrote:

                             

                             

                            Should I

                            1. use a single portal to list "Transaction People and their roles)?

                             

                            This is the way to go.

                             

                             

                            slayden@msn.com wrote:

                             

                             

                            1. Is there a problem overlaying a calc field, as that seems to just show blank, when in the Contacts table it's showing data.

                             

                            No real problem, so something is wrong with your relationship it would seem.

                             

                            One thing to consider: you may want to store the contact's full name in the join table.  Sometimes people's names change (married women for instance) so if you want to capture the name of the person at the time of the transaction you need to store it as data with the transaction's join instead of linking to the current name.

                            • 11. Re: Showing multiple records from a table on a layout
                              slayden@msn.com

                              RoleProblem2.pngRoleProblem.pngRoleProblem3_JoinTable.png

                              In the join table, I add a join record, set the contactID, transactionID, and roleID.  Any idea why in the portal whenever I change the roleID ALL of them change?  The PEOPLE and their records are correct, but I only get the FIRST row selected for ROLEID, and then it replicates every role -- the join table looks correct, so I'm baffled

                              • 12. Re: Showing multiple records from a table on a layout
                                steve_ssh

                                Hello Slayden,

                                 

                                The symptom you describe is typical of a case where the Base TO for the roleID field in the portal has been set incorrectly.

                                 

                                It's difficult for me to know whether I have a good understanding of your situation, as the image of the Relationship Graph is not what I'd expect to see given the text of your post.

                                 

                                Nonetheless:

                                 

                                I'd suggest taking a look at the offending field in the portal, and make sure that the Base TO for that field has been properly set.

                                 

                                If the layout is based on [Transactions], and the portal is displaying from [TransactionJOIN], where [TransactionJOIN] stores contactID, transactionID, and roleID, then my guess is that you want the display of roleID to be pulled from the [TransactionJOIN] TO.

                                 

                                I'd start with that, and see if it yields the results that you are expecting.

                                 

                                Best regards,

                                 

                                -steve

                                • 13. Re: Showing multiple records from a table on a layout
                                  slayden@msn.com

                                  The actual name for the transactions table in this case is "opportunities," for a CRM database for a mortgage company.  the screen shots are actual

                                  1. Persons:PersonID_pk TO Join_OpportunitiesPersonRole:PersonID_fk seems to work just fine
                                  2. Join_OpportunitiesPersonRole:OpportunityID_fk TO Opportunities:ID_pk seems to work just fine
                                  3. It's the Join_OpportunitiesPersonRole:RoleID_fk TO List_OpportunitiesRolese that is the problem.  No matter what, each portal row shows only the first record's number EVEN THOUGH as you can see above, the RoleID_fk fields. 
                                  4. What is the proper way to get each row showing its own number in a portal?

                                  Thanks again,

                                  Scott

                                  • 14. Re: Showing multiple records from a table on a layout
                                    slayden@msn.com

                                    I think I just figured it out.  More in a bit

                                     

                                     

                                    So....he sheephisly admits:  Using the portal based on Persons instead of the Join table.  Sheesh  - 6 hours....

                                    Hopefully,  I can add value answering others' questions as penance.....