Skip to main content

I have a problem that I think should be solved through interfaces. I’m tracking attendance and need to be able to build rankings of attendees, and calculate attendance percentages, flag people below a certain percentage, etc.


I have a database with two tables: a list of employees and a list of events. The events table is a collection of records (date, attendee, type of event). It links to the employee list, so it rolls up the events and counts how many of those they attended. In fact there’s an extra record calculating the percentage over totals. So far so good, I can tell how many events and of which type everyone got.


Problem is, the list is growing and all the data I have is YTD. I need my boss to be able to select a date range and check the percentages, ie quarterly or whatever range they want.


So I built an interface, from which I’m able to make a filter (from date, to date) and even filter which type of event (meeting, conference, workshop), then show a number element that counts of how many attendees went to, for example, workshops during the summer. I can even show those filtered events in a grid, on the interface. I can also filter people individually and have that number show how many conferences that particular person attended the last month. Which is great, but we have 300 employees so checking them manually won’t do.


What I cannot do is create a grid that shows the top 10 employees with best attendance in the selected period. My employee table grid will show the YTD totals, with complete disregard to the selected dates. Rolled up or linked events cannot be filterable. And if I use the grid to show the events table, the employees are not rolled up so they cannot be ranked anyway.


After having that grid, I would love to be able to filter out people below certain thresholds, but I’m getting ahead of myself.


I guess it all comes down to: how can I create calculations in an interface? and how can I visualize grids that come from those calculations?


Thank you!

Hi Yago, the only solution I could think of when facing your issue involved a really clunky workaround that would break if two people were using the interface at once


Specifically, I ended up using a button click to trigger an automation that would update a helper record with the values I needed to filter by, and that helper record was linked to all of the records I needed to look through


I had lookup fields to pull the values from the helper record through, and formula fields to determine whether each record matched the filter


With reference to your setup, the helper record would be linked to all your event records, and, in your Employees table, you’d have a conditional rollup field that only calculated the attendance percentage for events that matched the filters in the helper record


The obvious downside would be that you’d have to set the date filters as well as have separate input fields to set the same date range, and you could probably overcome that with an automation I reckon


Hopefully someone else has a better idea!

Reply


Terms & Conditions