I am trying to create a solution for a colleague, which based on the availability and integration of Workforce, seems like a simple task; however it's proving somewhat elusive. I've been asked to set up Workforce for our Volunteer Coordinator to track volunteers' availability, location and hours worked. One of the primary requirements is to produce at least a view of hours worked for each volunteer.
I'm not sure that a dashboard is the best way to do this. I have been trying to beat a dashboard (v2) into submission; using a list display filtered for assignments in the last month (for now, would ideally prefer to set a date range) and then trying to write some Arcade that would at least provide a readout of each volunteer and their hours worked.
The complications seem to be:
- The time stamps are stored in the Assignments table, and related back to the Workers, not directly available in the Assignments table. The result ideally would be a displayed table/grid of workers and their hours worked for some given time period/range. The existence of only $datapoint globals in the Arcade editor for Dashboard modules suggests that maybe other tables aren't available to Arcade here?
- Getting the expression to read all of the features in multiple columns (Can't figure out which modules Feature Sets are actually available in)
- Constructing a strategy to arrive at the desired result
Other things I've considered
- Creating fields using Arcade calculations that would store the calculation of hours worked for whatever time period, however this kind of dynamic solution wouldn't allow historical data to be retrieved / calculated.
- Creating a new table to hold workers, their assignments and time worked... inserting a record upon assignment completion. Just thought of this, haven't looked into its feasibility. Not sure how this would work if an assignment is re-opened or edited unless more advanced functions are implemented.
- I have accepted filtering assignments by date completed (say, "Last Month") as a way to define the time period. Setting the date range manually would be more flexible, or the option to do either would be even better; but the predefined date filters will probably be OK for now.
What would be your solution for understanding/displaying cumulative hours per worker across some time period?
