Rollup/ Formula to deduct # tickets claimed from tickets available

[Shirley_Kawafuc]

Hello Airtable Community,

I have 2 tables: Attendance and Performances. 

People will fill out my ticket request form, submitting their preferred number of tickets for Performance Dates 1,2,3 etc.  This gets recorded in the Attendance table under their school name and quantity goes under "Number of Tickets." 

I also have a Performances tab that tracks total number of tickets available for the performance. I want to use a rollup or formula to connect the two tables and deduct the total SUM of tickets claimed from a Total Tickets Available number that I set (right now it is 1,000 tickets.)

Ideally I am able to make this appear in both tables: Attendance and Performances. Can you help me?  

[TheTimeSavingCo · Accepted solution]

In your "Performances" table, create a rollup field with the formula "SUM(values)" and point it at the "Number of tickets" field so that it sums up the total number of tickets requested

After that, create a formula field that will deduct that value from the value in Total Tickets Available" and you should be good to go. 

After that, you can create a lookup field in the "Attendance" table to display that formula field's value

If you could invite me to your base I could set it up for you real quick too!

[Shirley_Kawafuc]

Thank you so much Adam!! Your steps worked. The only thing that remains wonky is now my lookup field in my Attendance table. It's doing what it's supposed to (displaying the value of Total tickets remaining), but displaying it within each entry is a bit confusing. By chance do you have any tips? 

[Shirley_Kawafuc]

Performances tab is solved, but I just did another test submission and am definitely having something weird going on with my lookup formula in Attendance.  For one of the performances, it's returning 576,776 instead of just 576. Not sure where the other number came from. I've attached a screenshot of the weird value and how it's appearing.

[TheTimeSavingCo]

Hmm, in the picture "Attendance Error 2", we've got two records linked to "Shirley Test School".  As the lookup field displays values from all the linked records, we end up with two numbers displaying, does that make sense?

[Shirley_Kawafuc]

@TheTimeSavingCo , I did some final tests and I'm having trouble with my Rollup for "Sum of Tickets Requested" because "number of tickets" is listed in separate fields, by date. I feel like there is an extra linking step I need to do. Can I invite you to my base to take a look? 

[TheTimeSavingCo]

Yeap sure, feel free to direct message me an invite link!

[Shirley_Kawafuc]

Just sent! Please note it is a LIVE form with responses already coming in. Thank you for your help and please feel free to direct message me with any questions.