1 2 Previous Next 20 Replies Latest reply on Jun 30, 2016 7:09 AM by RPeters

    Issue with Join Table Relationships

    RPeters

      Hi Everyone,

       

      I'm having some trouble with my table relationships.  I work for a non-profit music festival and I have patrons attending events.

       

      Here's my current set up, and it works great:

       

      Patrons ----|| Income ||---- Events

       

      So I can have patrons attending multiple events, and each event has multiple patrons.  This set up has worked really well for me so far.  I can use portals to display the information, and it's perfect!

       

      But now I need to add another element to it, and I'm having some trouble.

       

      One patron will be running each event, and I need to indicate which patron is in charge on each event page. (One patron can run multiple events.)  The tables are already related, and I just can't figure out how to differentiate a patron who is in charge of an event, versus a patron who is just attending an event as a paid customer.  The relationship that I need is this:

       

      Patrons ----|| Events

      but of course I already have a join table that eliminated that direct relationship.

       

      I'm feeling quite stuck, and any advice would be soooo greatly appreciated!

       

      Thanks!

      -Rachael

        • 1. Re: Issue with Join Table Relationships
          coherentkris

          The associative entity (join table) should probably not be named [income].

           

          What you actually have is [person/people], [events] and [attendance] where a patron is either a class of person/people or an attribute (think role here) of their attendance at an event

          • 2. Re: Issue with Join Table Relationships
            RPeters

            I kind of understand what you're saying, and I kind of don't.  Sorry - I'm fairly new to FMP!  I've called the join table "Income" because there's more than ticket sales being entered into the join table.  (Sorry - I'm realizing that was potentially an important piece of information to have left out! In trying to make my questions not to lengthy I try to isolate the piece of the database I'm having issues with, but perhaps I isolate too much!)

             

            Aside from ticket sales, I use the table to track payments from advertisers, corporate sponsorships, sales of gift cards, revenue from fundraising events etc.  So it's definitely a multi-use table.

             

            Any thoughts??

            • 3. Re: Issue with Join Table Relationships
              bigtom

              You could always make another table occurrence, but it sounds like you might just need a Coordinator field on the event table and set it with a value list using patron names. Not need for the relationship maybe.

              • 4. Re: Issue with Join Table Relationships
                RPeters

                Yes, I was considering that if I can't figure something else out.  I was hoping to set up a relationship though, so I could also include their phone and email, and keep a record of their involvement in hosting an event.

                • 5. Re: Issue with Join Table Relationships
                  bigtom

                  PatronIDfk in the event table to store the relationship.

                   

                  Popup with a value list for Patron::PatronIDpk and showing only PatronName as second field.  Super simple to use and will give you the coordinator details through the relationship.

                  • 6. Re: Issue with Join Table Relationships
                    flukey

                    Also, create a second TO of the Patron table and link its primary ID to the PatronIDfk field mentioned by bigtom.  This will let you pull in all of the information for the coordinator, independent of the other Patron TO.

                    • 7. Re: Issue with Join Table Relationships
                      bigtom

                      I thought that was clear from my previous advice to add a TO, but if it

                      was not clear it is now.

                      • 8. Re: Issue with Join Table Relationships
                        flukey

                        It was clear to me, but I was not sure if it was clear to the OP.

                        • 9. Re: Issue with Join Table Relationships
                          RPeters

                          Thanks for the ideas!  I'm going to try this, but I'd like to know more about table occurrences.  I've been looking into it online but I've yet to find a simple example that explains it simple terms.  (Maybe because it's not a simple concept?)  Is anyone able to explain it??

                          • 10. Re: Issue with Join Table Relationships
                            RPeters

                            Okay, so here's what I did:

                            1. New table occurrence of Patrons, creatively named "Patrons 2"

                            2. In Patrons 2, I created HostIDpk

                            3. In Events, I created HostIDfk

                            4. I created a relationship between the two

                             

                            ...Now I think I'm stuck again.  Right now the HostIDpk isn't on any layout (and therefore not populated) because I don't have any layouts based on "Patrons 2".  When I put the HostIDpk on "Patrons" (the original, not the second TO) it won't let me populate it with anything.

                             

                            I tried duplicating one of my layouts based on "Patrons" and make it based on "Patrons 2", but all the fields are blank.  I know I'm missing some really important steps, but I just can't figure out what's going on.

                             

                            I also tried going into a layout based on "Events" and placed the HostIDfk there... but I can't populate it with a value list because the PK is blank... I think I've put myself into a big mess!

                             

                            Any help would be unbelievably appreciated!

                            • 11. Re: Issue with Join Table Relationships
                              bigtom

                              In field options you need to set HostIDpk to auto enter calc of Get(UUID). If you already have records you will need to loop through setting the field so everything will work.

                               

                              The HostIDfk belongs in events table.

                               

                              I will see about doing a quick sample file for you.  It sounds like you are close and just missing the actual pk in your existing records.

                              • 12. Re: Issue with Join Table Relationships
                                RPeters

                                That would be amazing - thank you so much!

                                 

                                Question about setting the HostIDpk... I have about 3000 records, but there's only about 18 people that could be hosts.  The list could change over time, but I'd never be choosing from a group of people larger than about 20.  Do I need to give each Patron record a HostID, or only the 20ish records that I'd be choosing between??

                                • 13. Re: Issue with Join Table Relationships
                                  bigtom

                                  Everyone should have a hostID. You will appreciate it later.

                                   

                                  You can limit the value list you see in other ways.

                                  • 14. Re: Issue with Join Table Relationships
                                    RPeters

                                    Okay, I was wondering if it would make things a little screwy in the future... good to know - thanks!

                                    1 2 Previous Next