See what Events were attended by whom and vice versa

[Thomas_Feith]

Hi everyone,

I’ve been trying and searching for hours, but can’t seem to find a solution.
My Events Database should be giving out the information of which Member of my group has attended which Event and in return also the information which Event was attended by which Member.

So far so easy, but I get the attendance lists in a very large complicated table and it is a hustle to put the names in manually for every new event.

Is there any way, that I can link an event to a new table which contains all the names attending and make it pull them so they can be compared with the third Members table?

Any help appreciated!

Thank You

Thomas

[Justin_Barrett]

One way to “merge” the attendee list with your list of members is to let Airtable do the merging for you.

Make a third dummy table, as you said, and drop the list of event attendees into a field set as Single line text. Once all of the names are in that field column (one name per record), change the field type to Link, and point it to your [Members] table. Airtable will then go through every name in your dummy table field, and look for it on your [Members] table. If it finds a match, it will simply link to it. If it doesn’t find a match, it will add a new record for it at the bottom of the [Members] table, and link to that new record.

So for example, say you have a list of 200 people who’ve attended a given event. After making a dummy table, dropping the names into a Single line text field, then changing that to a Link that targets your [Members] table, Airtable finds 150 matching names, and 50 that don’t match. Those 50 will be added at the bottom of the [Members] table. Now, Airtable won’t alert you and say “I made 50 new records.” It’ll just do it, so you’ll have to go to [Members] and see how many new names are at the bottom of the list. However, this is the easiest way I know of to merge the full attendee list with your list of members and compare the two.

If you’re worried about mixing the non-members with members, perhaps rethink the purpose of the [Members] table. Maybe rename it to “People”, and add a field (easily copied and pasted across existing members) that tags the members as members. Any new additions won’t have that tag, and you can then group the records to see how many have the “member” tag vs how many don’t.

A trickier task is linking those 150 members—who could be scattered throughout a member list that’s much larger—to the event record en masse. I haven’t had to do anything like that yet, so I’ll have to think over it a bit. Perhaps someone else knows of a solution?

[Justin_Barrett]

Okay, I think I’ve figured out a way to make it work. There’s still some manual effort involved, but not nearly as much as it would be to link users to events one-by-one.

Here’s my sample starting [People] table, with links to various events that they’ve attended:

After an event, I get a list of attendees, and drop that into an [Attendees] table for processing. Some of these are members, others aren’t, and not all members attended the event.

After putting all of their names into the primary field, I copy them to the next field over, then convert that field into a Link type, pointing it to my [People] table.

Now my [People] table looks like this. I can easily tell members from non-members, and can also easily see who attended the new event.

Now I add (or un-hide if already added) a Link field that points to the new event in the first record, and drag-fill that so that every record has that link.

That’s the link that I want to add, but only to those who attended, which I can see in the linked {Attendees} field. With that, I can build a formula field that adds that new event to the existing events for all attendees:

Events & IF({Attendees}, ", " & {New event})

The full table with that field added now looks like this:

Now I can copy the contents of the {Added Event} field across all records (click the field header once and the entire column will be selected), and paste it into the {Events} field, which will a) keep the existing links, and b) only add links to Event 3 for those who attended Event 3. Once that’s done, I can hide the extra fields, and I’m left with this:

I can then reuse the [Attendees] table for the next event.

[Thomas_Feith]

Thank You, this is a great workaround.
Nevertheless, I believe Airtable should be able to make cross-references.