Summing Meal Choices in Registration Table

[John_Dlouhy]

Greetings! I am trying to track event registrations. My table has these fields:

Primary First Name
Primary Last Name
Event #1 Status (Single Select: Yes, No, Cancel, Didn’t Attend, Hasn’t Replied–applies to attendee coming alone or to attendee bringing a guest)
Event #1 Meal for Primary First (Single Select: Meat, Veg)
Guest First Name
Guest Last Name
Event #1 Meal for Primary Guest (Single Select: Meat, Veg)

How best to see a total sum of all the meat meals and all the veg meals?

Thank you–any assistance is greatly appreciated!

[AlliAlosa]

Hi there!

You’ll first want to create a second table, maybe call it [Events], and link every record on your first table to the same event on the second table.

Next, add a formula field to your first table with the formula…

SUM(IF({Event #1 Meal for Primary First} = “Meat”, 1, 0), IF({Event #1 Meal for Primary Guest} = “Meat”, 1, 0))

Let’s say you call it {Meat Count}. Next, add a rollup field to the [Events] table, and point it at the {Meat Count} field on your first table. Use the formula function:

SUM(values)

This should give you the total number of meals for Meat. Repeat the same steps for “Veg” :slightly_smiling_face:

Hope this helps!

[John_Dlouhy]

Brilliant! Thank you so very much!

[John_Dlouhy]

Hello again! I Changed the names of my meal fields to simplify matters:

Meal (single select filed: Meat, Veg) - this is primary person’s meal
GMeal (single select filed: Meat, Veg) - this is the guest’s meal

Here’s the formula I tried to enter, without success.

SUM(IF({Meal} = “Meat”, 1, 0), IF({GMeal} = “Meat”, 1, 0))

Any idea what I might be doing wrong?

[AlliAlosa]

The formula appears correct, but it looks like you’ve got smart quotes - the quotes are angled/curved when airtable will only accept straight quotes. This can happen when copying and pasting sometimes. Try deleting the quotations and replacing them - the formula should work then :slightly_smiling_face:

EDIT: I just realized that the formula I gave you had smart quotes to begin with - my apologies! I wrote this from my phone so I suspect that’s why :grinning_face_with_sweat:

[John_Dlouhy]

Ah—makes sense! I will try again using the preferred style of quotation marks. You’ve been soo helpful! Thanks so much, greatly appreciated!

John

[ I sent this on the go from my iPhone, so please excuse my brevity and any typing errors. ] :pray:

[John_Dlouhy]

Hello again! I am hoping to prevail on your expertise with another formula question.

First = Joe
Last = Smith
With First = Susan
With Last = Jensen
S1 Status = Yes or Yes FUP or Yes Staff

Formula needed for S1 RSVP #: If any of the above S1 Status are selected, and With Last is NOT empty (i.e. registrant is brining a guest) --then return a value of 2. If With Last IS empty, return a value of 1.

Hoping this is doable!

Thanks very much.

John

[AlliAlosa]

Hi there John!

Yes, this is definitely doable. Try this:

IF(OR({S1 Status} = "Yes", {S1 status} = "Yes FUP", {S1 Status} = "Yes Staff"), IF({With Last}, 2, 1)) 

Hope this helps! :slightly_smiling_face:

[John_Dlouhy]

Hello Neads! The formula worked like a charm. I am hoping you might be able to point me in the right direction on a challenge I’m having.

Per your excellent suggestion, my current set up has a table for Events linked to a table for People. It works great for tracking an event with only one session (say a reception or a dinner). The Events table has look up fields that now
accurately tally total RSVP and meal totals from the People table.

I am trying to figure out how to expand this set up to allow tracking for an event with multiple sessions:

[AlliAlosa]

Hi John! Are you offering meal choices for each session? i.e. does a person choose what meal they want for each individual session?

If so, this is how I would set it up. Add two more tables, one for [Sessions], and one for [People in Sessions].

[Sessions] should link to [Events], and [People in Sessions].
[People] should also link to [People in Sessions].

Move your meal selections to [People in Sessions]. This allows you you to assign a different meal choice for each session, and total them up on the [Sessions] table. You could then roll them up on the [Events] table to get a total for the entire event.

I’ve provided a sample base link below. Click the “Copy Base” button in the top right hand corner to copy the base to your workspace and check out how the tables are linked.

Airtable

Events (Sample Base) - Airtable

Explore the "Events (Sample Base)" base on Airtable.

I hope this helps to point you in the right direction!