My solution has tables for events, contacts and invitees
How have you related these three tables? Is Invitees a join table between contacts and events such as this?
Contacts::ContactID = Invitees::ContactID
Events::EventID = Invitees::EventID
This is the basic "many to many" structure of the original template and we need to know if you are still using that structure.
If so, a portal to Invitees placed on the contacts layout will list all events to which that contact has been invited. A portal to invitees on an Events layout will list all contacts that have been invited to that event. A filter on such a portal could filter the list down to just those that are attending.
There's really nothing wrong with a find on Invitees that specifies both an eventID and a response status, however. That's a very standard practice in database queries.
You could also use Go to Related records from the Events layout to pull up all invitees for that event, then enter find mode, specify the desired response status and constrain the found set to get your list.
All methods work and all of these can be automated with a script. (The filtered portal method may not even need a script.)
Thanks for that. I have double-checked the relationships in comparison to the template and now have it working - sort of.
The filtered portal works fine in the Events layout to display a list of those attending.
However, a portal in the Contacts layout shows only one event that person is attending. Will I have to make all the fields in Contacts which relate to events into repeating fields? Is there another way of doing it? I can see a risk of the number of events outgrowing the number of repetitions after a while.
I've looked again at the original template from the perspective of an individual invited to attend multiple events over time, and it doesn't seem to account for that.
"I have double-checked the relationships in comparison to the template" - you need to double-check your solution in comparison to Phil's suggestion. Then it will work the way you want.
I can see that the template provides for many-to-many relationships between contacts and events, but how can a contact's response to more than one event be recorded, other than in a repeating field?
You would have a Join Table as per Phil's suggestion. The Join Table will have one record per 'Invitation', with the ContactID, the EventID, and the Response. If you invited (or logged the status of) 5 Contacts to 2 Events the records might look like:
EventID Contact ID Response
001 100 Accepted
001 110 Accepted
001 120 Declined
001 130 Not invited
001 140 Accepted
002 100 Accepted
002 110 Not invited
002 120 Declined
002 130 Declined
002 140 Accepted
If Sorbsbuster hadn't posted this I would have.
However, a portal in the Contacts layout shows only one event that person is attending.
You'll need to check to see what you did differently there. It should list all the events that they are attending to which they have been invited. Make sure that the portal is to the Join table (Invitees), not the event table.
Thanks guys. Much appreciated.