Summing Meal Choices in Registration Table

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!

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!

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?

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:

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

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:

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:

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!

Wow! Thank you Neads!! This is so cool—I will need to spend time with it to understand the set up.

Yes—meals for all sessions. Future events would be a combo of sessions with meals and without.

I’ll need to see how to handle the scenario when someone brings a different guest to one or more sessions, etc.

OK to follow up with questions as they come up?

Hope you had a great Labor Day weekend.

Cheers—

John

Hi John,

You’re entirely welcome and of course! Feel free to reach out with any questions!

To handle different guests, I would add them into the [People in Sessions] table. You could pull the original guest’s name over from the [People] table with a lookup, and use a formula field to override it if a different guest is attending. :slightly_smiling_face:

Hello again! Hoping you can help again with a quick question.

I am trying to create a formula field to return the following result:

• If ‘Donors YES’ is not empty/is not blank, enter ‘SCHOLARSHIP’

My attempt is doing the exact opposite! It’s entering the SCHOLARSHIP name where Donors YES is blank.

Here’s my formula attempt:

• IF({Donors YES}=BLANK(),{SCHOLARSHIP })

And a screen shot of the results:

Hi @John_Dlouhy,

Just remove the =BLANK() from your formula.

IF({Donor YES}, {SCHOLARSHIP})

That will fill the field with the {SCHOLARSHIP} if there is anything at all in the {Donor YES} field.

Thank you Jeremy!

I am realizing I have to factor in another field as well,

If Scholar YES is not blank, and Donor YES is not blank – return SCHOLARSHIP.

Is this an IF/AND or IF/Or?

IF(
   AND(
      {Donor YES},
      {Scholar YES}
   ),
   {SCHOLARSHIP}
)