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
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.
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.
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.
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.
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.
I thought that was clear from my previous advice to add a TO, but if it
was not clear it is now.
It was clear to me, but I was not sure if it was clear to the OP.
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??
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!
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.
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??
Everyone should have a hostID. You will appreciate it later.
You can limit the value list you see in other ways.
Okay, I was wondering if it would make things a little screwy in the future... good to know - thanks!