Formula for calculating if someone attended an event or not

[Matt_Hill]

HI, all! Looking forward to your kind help.

My end goal is to sort out people who have attended 3+ workshops.

I have a workshop business, and we have three main ticket types: Deposit, Remaining Balance and Pay in Full.

I am working on a way to automagically sort our orders table to show only people that have attended 3+ workshops. That means either Deposit + Balance OR Paid in Full.

I figured if I can make a formula that uses logic to see if Deposit and Remaining Balance fields both return “checked” then… something. And this is where I get hung up writing the formula.

I’m sure I can then use another field or view to examine that result and the Paid in full to find a total quantity to be 3 or greater. Some people sometime pay in full, and sometimes put down a deposit and then their remaining balance. So I have to examine both fields to determine their total attendance count.

Essentially, I am having a hard time achieving my goal of determining if they attended 3+ workshops. Each order is a new record, so I have to compare two record against each other sometimes to see if someone attended a workshop, without double counting them for the separate records.

Regards,
Matt

[Matt_Hill]

Or should I consider using rollups?

[Justin_Barrett]

Welcome to the community, Matt! :grinning_face_with_big_eyes: Rollups would definitely help. Here’s what I suggest…

Create three tables: [Attendees], [Events], and [Orders].

Orders would connect attendees with events, and would be where you track payments. Here’s how I set mine up:

The formula in the {Attended?} field is this:

IF(OR(AND(Deposit, {Remaining Balance}), {Paid in Full}), "✅" )

Back in the [Attendees] table, I’ll roll up that {Attended} field using ARRAYCOMPACT(values) as the aggregation function. In case someone doesn’t meet the criteria to count as having attended, those blank responses won’t appear in the rollup.

I’ll then add a formula field to count the number of check marks rolled up, and display another emoji if it’s 3 or more.

That final formula is:

IF(LEN({All Attended} & "") >= 3, "😀")

[Matt_Hill]

Gratitude Justin - you are amazing. Thank you.

I muddled my way pretty close to what you suggested. I have two tables - Orders and Customers. I am building the Events one now. I used rollups from Orders to Customers in nearly the same manner. I’ll closely look at how you did it to see if I missed anything essential.

But you unlocked a way to get to the finish line. Eternally grateful. THANK YOU! I’ll report back with success (or perhaps questions… but I feel confident this is exactly what will work).

Love the community support here. Awesomesauce.

[Matt_Hill]

I implemented your suggestions - thanks!

OK, so here is the rub. The transactions for Deposit [A] and Remaining Balance [B] are in separate records.

Each transaction will be a separate record.

Pay in Full [C] does not affect this, but I still need to be able to compare, by event, if a customer qualified for a check in attendance for [A] + [B] or [C] by comparing separate transactions.

Getting closer to being not confused, but not out of the woods yet :slightly_smiling_face:

I tried to upload a screenshot, or a link to it, but I am blocked from both. Must be new user syndrome…

[Justin_Barrett]

Having things in separate records definitely makes it trickier, but not impossible. Before diving into the changes, I want to explain the logic I applied.

You’ve been saying so far that you want to count [A] + [B] (deposit payment + balance payment) as a single attendance indicator. However, unless I’m missing something, there will never be a situation where someone makes balance payment [B] without having made deposit payment [A] first. Therefore, we can effectively ignore the deposits and only count the balance payments. In other words, all balance payments plus all full payments equals the number of times that person attended.

With that in mind, here’s an alternate setup for the Orders table:

The new formula for the last field, now named {Status} is:

IF(OR({Remaining Balance}, {Paid in Full}), "✅")

That field is still rolled up in the {Customers} table, but with a different aggregation formula. Adding ARRAYCOMPACT into the mix removes those blank values for the deposit records, leaving only those for balances or full payments:

ARRAYJOIN(ARRAYCOMPACT(values), "")

With that, the final formula field changes to this:

IF(LEN({All Attended}) >= 3, "😀")

[BillFrench]

@Matt_Hill, @Justin_Barrett:

I believe this is yet another example of a multi-pass process across the data that I discussed here.

One of the big challenges in a near-code-free environment like Airtable is that we tend to search for workarounds to multi-pass computational problems. We do this by creating adjacent tables, hidden fields, and relational indices that tend to complicate the data model. Based on my other post, you can imagine I’m not a big fan of complicated data models to achieve what appears to be a simple computational algorithm. This is just me and my opinion. :winking_face:

I’m okay with workarounds to a point, and that point is when a system becomes very difficult for the domain expert who created it (i.e., you) to understand it long after it’s working. And with each new step “forward”, in workaround mode, the solution becomes increasingly more complex because - well - workarounds tend to require other workarounds. :winking_face:

The entire point of Aitrable is to make information management simple and accessible to people who just want to get work done. I often need to remind myself of this because workarounds are very low-hanging fruit indeed.

I’m sorry I can’t offer you an answer to your specific problem, but I believe it requires an external (API-driven) process that can perform the multi-record steps you sought in your initial assessment and requirements. That approach would allow you to sustain the elegant data architecture that got you to this point and it’s likely what you and your workers want anyway - a unified table that is just simple and easy to build reports and analyses from.

Consider using an API process running on a serverless scripting model that performs these algorithms and writes results back into your table. Your process will work better, it will be more elegant, and likely more sustainable.

[Justin_Barrett]

I appreciate your thoughts, Bill, but I’m afraid I must take issue with your reference to solutions like the above as “workarounds.” To me, they’re still solutions. They solve the problem and get the user to the desired end result. Are they elegant? Definitely not. Are they efficient? Not likely. But for those who may not have the means or desire to go outside of Airtable to tackle (or hire someone like yourself to tackle) a code-based solution, they meet a need.

FWIW, I’m also a coder at my core (perhaps more of a coder-dabbler, but I’ve been messing with code in some form or another for the past 30-odd years). However, I haven’t had the time (for many reasons) to delve that deeply into Airtable’s API to figure out how to engineer code-based solutions. Even if I had, though, I would probably still throw these cross-table solutions out there just so people know what’s possible natively within Airtable. Just like with coding, Airtable’s features are like a set of tools. While everyone hopes for those tools to become more plentiful and robust, I’m determined in the meantime to see how many solutions I can come up with using the tools that already exist.

[Bill_French]

@Justin_Barrett,

BillFrench:

… we tend to search for workarounds to multi-pass computational problems.

As my quote attempted to make clear, it is not an indictment of glue-like services such as Zapier; rather, it is a cautionary reminder that using them to overcome certain types of challenges in any solution - specifically where multi-pass algorithms are required - come at a cost and sometimes a very steep cost. That is the point where many users find themselves going around the barn many times. The price of a “working solution” often becomes expensive in terms of added complexity. And added complexity in tables often results in an erosion of usability.

I think we can both agree that there are limits to what can be done without code. I’m simply suggesting it’s good to assess the nature of the solution to make sure the accommodations we must make to avoid writing code are well worth the outcome.

Indeed, it’s all about solutions as you point out - we both certainly agree that something working is generally better than nothing. And in many cases, a working model is an ideal stepping stone that may someday transition into a sustainable solution with fewer moving parts, better reliability, and scalability without added services costs. But we must also be mindful of solutions that are brittle.

I think - as consultants - we also share an obligation to advise those less experienced concerning the possible downsides of any implementation approach. There are some cases where these tools are simply a bad choice, and I think members of the community would like to be aware of potential pitfalls.

Lastly, solutions that are created using third-party adhesives tend to muddy the waters in small and large companies and for many reasons.

• Dependencies that have hidden costs.
• Dependencies that may change without adequate notice.
• Dependencies that create security risks. (identities flowing through Zapier is not advised; some CSOs consider this a security breach)
• Dependencies that need to be maintained and fully understood long after the creator has left the project.
• Dependencies that create copyright and patent constraints.

Surely, there’s no point in debating the contribution that third-party integration services provide; they have their place in the API economy just as SDKs do, and just as development libraries do. But like many implementation approaches, sometimes they can make matters worse, not better.

I too have been playing with code for a few decades - almost five to be exact. Yes, I’m 67, a really old cantankerous dude! I created LapLink when I was twenty-something (53m users), dBrief (4m users) when I was thirty-something, QuikSite (3m users) when I was 40-something, MyST (16m users) when I was fifty-something, and Stream It (1 user, $4.3m contract) when I was sixty-something. As you might guess, I’m biased toward solving problems with code. :winking_face: