Rollup or Joint Table or Formula?

[Erika_Mijlin]

Hi -

I'm trying to figure out the best way to display, in an interface, how many events are occurring on any given date, so I can calculate staff needs based on this count.

Tables I am working with:
Events (Events are made up a series of Sessions)
Sessions (Sessions might be 2-3 in one day, and each one has date info, and is linked to an Event)

Events overlap a lot, and a given day may have more than one Event occurring, so I want to have a way to look at any given date and see how many Events are occurring. Sessions need to be staffed by one or more people depending on what kind of Event, etc.

I know how to group records in various ways in various grids and see the sum count by group, but I can't do anything with this number because it doesn't live in any field. Ideally, I would like to display this "Number of Events per Date" number in a pivot table style dashboard.

Am I missing some really obvious way to do this?  Would appreciate shortcuts!

Rollup? Formula? Joint Table?  Pivot table magic? 😉 

Thanks!

[TheTimeSavingCo]

Try creating a table called "Dates" and linking it to the "Sessions" table, and you can populate this automatically with an automation to paste the date field value into the linked field to the "Date" table, so it'd look like this:

 

Populating your existing data is a bit trickier.  To do that you'd create a formula field that outputs the date as "YYYY-MM-DD" with DATETIME_FORMAT:

You can then click the field header for this formula field to select the entire column, copy it, and then click the field header for the linked field and paste it.  This'll auto link everything up for you as needed

Link to base