Summing Meal Choices in Registration Table

John_Dlouhy · ‎Aug 30, 2019

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 · ‎Aug 30, 2019

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 · ‎Aug 30, 2019

Brilliant! Thank you so very much!

John_Dlouhy · ‎Aug 30, 2019

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 · ‎Aug 30, 2019

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 · ‎Aug 30, 2019

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 · ‎Aug 31, 2019

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 · ‎Sep 01, 2019

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 · ‎Sep 01, 2019

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 · ‎Sep 02, 2019

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.

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