Getting started: event attendance / training log

[jackvinson]

Hello - I'm VERY new to Airtable, though I think I have a basic understanding of databases.  I'm looking for examples, templates, pointers to further help.  Or even the right terminology to use.

While I want to create a larger base, I got started with a simple event tracker (types of events, different leaders, different sponsors, etc.).  Now I'd like to expand this to keep track of who has attended AND enable some options for adding the list of attendees and querying.

Adding the attendees seems fairly "obvious" - table of people; table of events; table of attendees (people + event).

The thing I don't know as is how to make this easy to use - how to get data in easily AND how to query easily.  The obvious thing for data entry is to type into the table or a form.  But what I'd love to be able to do is send an (structured?) email with the event information and list of attendees.  Maybe even forward the meeting invite?

And on the query side, I know how to search for individual names or look for specific events.  But I'd like to be able to take a list of names and ask "what sessions have these people been in" and get a useful response back.  Again, could I send an email and get an email response back?

Thanks for any guidance or suggestions!

[Sistema_Aotearo]

Hiya Jack! Welcome to the community.

I run a music education programme with multiple tutors, and hundreds of students, with over a hundred lessons a week. I feel like we may have similar needs. From being able to track attendances, to communication to the community.

For attendance, this is how I manage mine. I use an interface to display the list of lessons, or events in your case. I use a Calendar visualisation for this. I then "allow users to open record details". This means that I can click on each event and build a page that I can use to mark attendance. How this looks can range from very simple, to very complicated!

• Simple: Display your attendance record linked to your event. Then make it so you can add records from the table of people.
• Intermediate: Using dynamic linking conditions, you can set it to only allow people to be linked if they are also set to attend the event in the first place (with another link field).
• Advanced: Display a list of all the scheduled people. Add a button that runs an Automation that automatically adds selected people to the attendance record.

For querying statistics, I'd also use an interface. Viewing all your people in a list, you can open up their details and display their attended events in a list or as a calendar. There is so much more functionality but hopefully, this will help you get started!

Let me know if you have anymore questions!

[TheTimeSavingCo]

re: But what I'd love to be able to do is send an (structured?) email with the event information and list of attendees.

Hmm, try:
1. In your "Events" base, have a checkbox field called "Send event summary" or something
2. Create an automation that'll trigger when that checkbox field is marked
3. In the automation, add a "Find record" step to grab all the records from the "Attendees" table that are going to the Event that triggered the automation
4. Add a "Send email" action and add the records found by the previous step as a table and any other event details you want to include from the triggering record

Basically a variation of the email digests tutorial: https://support.airtable.com/docs/how-to-create-email-digests-with-automations

---
re: Maybe even forward the meeting invite?

1. In "Attendees", create a lookup field to grab each person's email
2. In your "Events" base, have a checkbox field called "Send invites" or something
3. Create an automation that'll trigger when that checkbox field is marked
4. In the automation, add a "Find record" step to grab all the records from the "Attendees" table that are going to the Event that triggered the automation
5. Add a repeating group that'll use the records found from the previous step
6. In the repeating group, add a "Send an email" action to send each person an invite, using the lookup field from step 1

---
re: But I'd like to be able to take a list of names and ask "what sessions have these people been in" and get a useful response back. Again, could I send an email and get an email response back?

Ha this is super interesting.  Yeap you could do this; try:
1. Send a structured email to that email account, e.g.
  - Subject: "Airtable automation session request: [Person Name]"
  - Body: "[Person Name]"
2. In Outlook, create a filter that'll grab all the emails where the subject includes "Airtable automation session request" and dump them in a specific folder
3. Create an automation that triggers when an Outlook email is received in that folder from the previous step (Airtable automations only supports a trigger from Outlook)
4. Give it a "Find Record" step and look for all the records in "Attendees" that have the same name as the name in the body of the email
5. Add a "Send email" action that'll send you an email as a reply, and insert the list of found attendees into the email

[jackvinson]

Thanks for the assistance so far.  As I make attempts here, I am discovering that my assumptions don't all make sense in reality.  Maybe I should design the input forms first? And output interfaces?   

Let's say, I have a base of Events (name, start date/end date, location, leader).  And I have a base of People.  In order to have a This Person attended That Event, I thought I needed another table that linked the two, but really, it just seems I need a lookup field in the Events base for Attendees.  My knowledge of the forms is pretty limited, but I would like to simply paste a list of names into the "attendees" field and have it automatically link the existing people or create new ones.  But my first attempts at a form make it look like I need to paste in each name individually.  (I assume I'm wrong again...)

I think I will worry about sending the base email or sending email from the base later.

[TheTimeSavingCo]

Hmm, if you paste a comma separated list of values into a linked field, Airtable will treat them as separate values and attempt to link them as needed.  So what you could try is have a long text field in your form to let your users paste the names into, and then use an automation to paste the value from that long text field into the linked field to "People".  This'll either auto-link to existing records or create new records in "People" as needed, does that make sense?

Having just the "Events" and "People" table works fine, yeah. Having the "Attendees" table allows you to add in details specific to their attendance, such as what time they arrived, whether they're a no show etc

[jackvinson]

Cool!  Pasting into the "attendees" field in Grid view does exactly what you describe.  But if I try to do the same in a "form" (either the default form that appears in the Calendar view, or a form that I attempt to create for "New Event"), Airtable treats the paste as a single entry.

[TheTimeSavingCo]

Hmm, could I confirm you've added a long text field to your form for users to paste the names in, and have created an automation that will run when that form is submitted to paste those names into the "People" linked field? 

[jackvinson]

Okay you have now reached the limits of my knowledge.  I have no idea how I would create an automation in this case.  And why would I need an automation? It clearly works in the context of the Grid view, just not this particular form - maybe I need to customize the form somehow?  (But this form pops up by default from the Calendar view, and the only thing I can change is what fields are hidden or not.)

Let's see if I can do some screen shots....  Here's what I get when I create a new entry from the Calendar view of the Events. 

Clicking the "+" next to attendees brings up a Search field that will auto-complete to a known person or let me add them if they aren't in the database.  But if I paste a comma-separated list into the "attendees" field, it tries to create a new attendee with the entire string. 

[TheTimeSavingCo]

Unfortunately Airtable doesn't support pasting comma separated values into linked fields in a Form view or in the Calendar view

If you put those comma separated values into a text field and use an automation to paste those values into a linked field it'll work the same way as you pasting it into a linked field in the Grid view, does that make sense?

[jackvinson]

It makes sense (to build an automation that processes data input), but I have no idea how one would do that.  I would like that to work either from the Calendar view or from a Form.  And it should probably clear the temporary value after processing it.  

Where would I look for some guidance on this?