1 2 Previous Next 26 Replies Latest reply on Jan 22, 2016 5:13 AM by mikebeargie

    Need a new relationship


      Hello, for our annual conference, our database is people-oriented: Our main table, PATRONS, tracks the people who attend and all of the relationships are established around the events that people attend and the things that people do.We create relationships to various other tables, such as ACTIVITIES, SEMINARS, and DEMOGRAPHICS.


      As you would expect, one of the fields in PATRONS is Company, and when we sign on a sponsor, there might be several people attending from that organization. That presents us with a challenge: for these purposes, Company becomes the central organizing unit (instead of Last Name), and we would like to be able to view and manipulate our records that way. I would like to create a relationship (and a layout) whereby the other fields could revolve around Company: where we could add a record in which first we add the Company, define its role in the conference, and then add the people who will be attending.


      The attached image shows two of our primary views: on the left shows the PATRONS table and on the right is the ACTIVITIES table. As you can see, Company is just one of the garden-variety fields in PATRONS.


      I'm good with scripts and calculations; less so with relationships, as I am unable to see them in my head. I would be grateful for some help in visualizing and creating the relationship(s) necessary to make this happen.



      Rick A.

      Pleasanton CA


      FMPA14 running under Windows 10.

        • 1. Re: Need a new relationship

          So, first you're looking at a new table, companies. Companies will then get it's own layout, where you can display a portal of related Patrons.


          To set that up, you need to establish a relationship between Patrons and Companies. Think of Companies to Patrons in the same way you have Patrons to Activities setup. This is a one-to-many relationship. One Patron has many activities. One Company has many Patrons.


          I'm not sure of your key structure given that you haven't done a screenshot of your relationship graph, but you need to establish a primary key in companies, and then a foreign key in Patrons that relates to that primary.


          This is the good way to do it. There is also a hack-job way of doing it, where you do a self-relation of Patrons based on Company Name, and designate one of the Patrons as the "parent" record. However if you ever have two companies with the same name, it will bleed over, since Company Name is not a unique key value. The only advantage to this is not having to create an entirely separate table and view.

          • 2. Re: Need a new relationship

            Many thanks, Mike. I am attaching what our relationship schematic looks like. If I create a new table called COMPANIES, how does it relate to the field in PATRONS by the same name? Do I move all of that data in that field into the new table? That's the part I'm not clear on.relationships.png

            • 3. Re: Need a new relationship

              You would need to create a script that seeded your companies data with values from your patrons data. Do this after you've created a Companies table with Company Name and Company ID (auto-enter serial number is fine), and relate Company ID to a Company ID field in the patrons table.


              This is pretty much it, but you'll have to adjust your field names:


              Go To Layout [ Patrons ]

              Show All Records

              Go To Record/Request/Page [ First ]


                Set Variable [ $count ; ExecuteSQL("SELECT COUNT(*) FROM Companies WHERE Name = ?" ; "" ; "" ; Patrons::Company Name) ]

                Set Variable [ $name ; Patrons::Company Name ]

                if [ $count > 0 ]

                   Enter Find Mode [ uncheck pause ]

                   Go To Layout [ Companies ]

                    Set Field [ Companies::Name ; "==" & $name ]

                    Perform Find

                    Set Variable [ $id ; Companies::Company ID ]


                   Go To Layout [ Companies ]

                   New Record/Request

                   Set Field [ Companies::Name ; $name ]

                   Set Variable [ $id ; Companies::Company ID ]

                End If

                Go To Layout [ Patrons ]

                Set Field [ Patrons::Company ID ; $id ]

                Go To Record/Request/Page [ Next + exit after last ]

              End Loop

              This script checks to see if a company exists based on matching company name. Then if it exists, it goes and grabs that company's ID, if it does not exist it creates the company with the name, and then grabs the ID. Finally it returns and marks the ID of the company to the Patron, relating it to the companies table. It's inside a loop so it will take care of all of your Patron records.

              • 4. Re: Need a new relationship

                I think I understand. I would normally do a whole export-import thing to migrate the data, but your script is much better. Can I take the code you have crafted here and somehow import it into a new script and then massage it to fit?

                • 5. Re: Need a new relationship

                  nope. I typed out all of that from memory, so it should be easy enough for you to follow through it and recreate in your own solution!

                  • 6. Re: Need a new relationship

                    I will attempt! Many thanks...

                    • 7. Re: Need a new relationship

                      I'm like you and would have done an export import thing. Perhaps to save some headaches create a value list in Patrons for Company Names and then scan the list for mis-spelled duplicates before running Mike's script. My guess is not everyone enters the company name in exactly the same way e.g. LTD vs LTD. Inc vs Inc.  an & vs 'and'. That way if you do need to change it you can make corrections by find and replace.

                      • 8. Re: Need a new relationship

                        Export and import will create duplicates, and you'd need to create a dedupe script that would update all of the related patrons of both companies before removing the duplicate. Much less painful to do it in a way that checks first.


                        And yes, the recommendation to scan for similar or misspelled names is good.

                        • 9. Re: Need a new relationship

                          Just to flesh out the conversation, I'd like to regard the hack-job self-relation technique. I recognize the value of not having to make a fundamental change to my data structure. Beyond that, what would be the reason to consider that route and what would be the argument against it?

                          • 10. Re: Need a new relationship

                            The argument for it is convenience and ease of not having to split your data.


                            The argument against it is the high chance for false positives, where people accidentally get cross-joined in two companies of the same name. OR false negatives, where misspellings cause two records to not be joined.


                            Using Unique Ids is considered the best practice. But databases often allow for less desirable matches as well.

                            • 11. Re: Need a new relationship

                              Hmm, could you address the false positive issue with a match field that is a calculation of Company + Full Name? That would have a much higher chance of always being unique. I hope that isn't a foolish supposition; like I said, my ability to visualize relationships and their meanings nears zero.

                              • 12. Re: Need a new relationship

                                If you do that though, you won't be able to use it for a join. The whole point of relating on company name is that you can see others related by that company name (ideally, other patrons from the same company). If you make it unique by company name + patron name, then it will no longer join to anyone else in the company. Yes, you reduce the chance for false positives, but it is not COMPLETELY eliminated unless you integrate a unique identifier like an auto-enter serial number or UUID.

                                • 13. Re: Need a new relationship

                                  Okay, so I had a major score here just by messing around with self-joining. I simply created the relationship and then created a portal from that relationship. Here is the result:partners.png


                                  This was exactly what I wanted to accomplish: a view of reps from that company. So for my pedestrian needs and neanderthal understanding, the hack-job strategy was the perfect Rx. I will save this entire conversation, in the hopes of graduating to the more kosher solution. Many thanks.

                                  • 14. Re: Need a new relationship

                                    I think you will still have company spelling issues with this and may not get all the related Patrons to show.

                                    1 2 Previous Next