Upcoming Event Amount

[Thomas_Feith]

Hi,
I’ve been trying to set up an Event Table, with a certain category attached to it, and I’d like to know how many Events of each category are upcoming or in the past.

Current Table situation is that I have an Event Tab and a Category Tab.
The Event Tab has a column “category” which links to the Category Tab.
So I am able to count the number of categories in total, but how can I limit the Events that are shown in the Category Tab to a certain criteria?
If I try to turn on “limit to view” in the “event” column, it does nothing.

Maybe someone has an idea for a workaround.

Thank You

[Justin_Barrett]

You describe the basic setup clearly enough:

Thomas_Feith:

Current Table situation is that I have an Event Tab and a Category Tab.

To put this into Airtable terms, you have two tables: [Event] and [Category]

Thomas_Feith:

The Event Tab has a column “category” which links to the Category Tab.

So the [Event] table has a {Category} field, which is used to link each event to a specific category from the [Category] table. Got it.

Thomas_Feith:

So I am able to count the number of categories in total, but how can I limit the Events that are shown in the Category Tab to a certain criteria?

The way I see it, you shouldn’t be viewing events in the [Category] table. That’s what you made the [Event] table for: containing and viewing events. The preferred way to view subsets of that full list is to make new views in the [Event] table, with each view using a combination of filters, groups, and sorting to restrict and/or organize your event records.

For example, let’s say that one of your event categories is “Wedding.” You could make a grid view—again in the [Event] table—that uses a filter to only show records where {Category} is set to “Wedding.” Rename this view to “Weddings,” and you have a quick way of viewing all of your wedding events. Go back to your initial view, and you see all of the records again. That’s the power of views: you use one “master” view to enter and edit all of the raw data, while using other views to control the display of subsets of that data.

[Thomas_Feith]

Thank You for the quick response Justin,
this is a great suggestion and I will use it, however it does not give me the number of Events in one view which is the main concern of this.
It’s basically a way to see how many events have been happening so far in comparison to all the other events, to balance them out and for that I’d need them in one place and not one by one.

Hope this makes sense.

[Justin_Barrett]

It kind of makes sense, and my gut says that there’s probably a way to work with some combination of filters, groups, and/or sorting to make that happen.

Can you provide a more detailed example of the kind of view you’d like to see?

[Thomas_Feith]

Thank You again for your help!
It just needs to be a Table with the Eventtypes and two other Columns with the numbers of past and upcoming Events of that type.

[Justin_Barrett]

Here’s what I came up with. Let me know if this works for what you need.

In your [Events] table, make a new formula field named something like {Before}, using the following formula:

IF(Date <= TODAY(), Date, 0)

Right-click on the {Before} field, change its name to {After}, and change its formula to this:

IF(Date > TODAY(), Date, 0)

Feel free to hide these fields, as they’re only going to be reference for the next step:

In your [Category] table, add a new Rollup field named {Before Today} that points to the {Before} field in your [Events] table, using the COUNTA(values) aggregation function.

Duplicate this field, rename it {After Today}, and change the rollup to point to the {After} field in the [Events] table.

Now you can look at the list of categories and see how many fall before today, and how many fall after today. Here’s how my example looks (some fields are hidden/cropped):