8 Replies Latest reply on Jul 9, 2012 6:42 PM by JonXS

    Event management - template enhancement

    JonXS

      Title

      Event management - template enhancement

      Post

      I am building an event management solution. I have found it simpler to start from scratch and use parts of the Filemaker events template, rather than try to modify that file.

      My solution has tables for events, contacts and invitees (not all contacts are invited to attend every event)

      Basically, where I'm stuck is on the need to track invitees by their response status (invited, not attending, maybe, attending etc) and across multiple events.

      That would seem to imply some kind of dual find, or a calc field (If event = event1 and if (response status = attending), 1, 0) etc... but that all seems rather clunky.

      I'm sure someone has been here before and has a more elegant approach....

      I want to be able to update individual contacts' and invitees' records, and list each event's numbers at each status stage.

        • 1. Re: Event management - template enhancement
          philmodjunk

          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----<Invitees>-----Events

          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.)

          • 2. Re: Event management - template enhancement
            JonXS

            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.

            • 3. Re: Event management - template enhancement
              Sorbsbuster

              "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.

              • 4. Re: Event management - template enhancement
                JonXS

                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?

                • 5. Re: Event management - template enhancement
                  Sorbsbuster

                  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

                  • 6. Re: Event management - template enhancement
                    philmodjunk

                    If Sorbsbuster hadn't posted this I would have.

                    • 7. Re: Event management - template enhancement
                      philmodjunk

                      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.

                      • 8. Re: Event management - template enhancement
                        JonXS

                        Thanks guys. Much appreciated.