Struggling to shape and aggregate data from related tables
This has me stumped 😞 I have been working on a registration app and need an efficient way to summarise registration events for reporting. There seem to be many paths and I'm struggling to envisage the overall approach.
I would massively appreciate any steer, ideas or just encouragement please 🙂
My solution comprises the following tables: -
A 'people' table with a 'personName' (Primary Name Column).
A 'groups' table which includes columns for 'groupName' (PNC), 'timeStart', and 'timeEnd'.
An 'attendance' table with 'id' (autonumber PNC), 'groupName', 'personName', regDate', 'regAction' (In or Out), and 'regTime'.
The 'people' and 'groups' table are in a Many to Many relationship. Check In and Check out events are (time)stamped into the attendance' table (from a Canvas app).
I'd like to end up with an aggregated 'register' (table &/or spreadsheet) by person and group with the following columns: -
jo bloggs, 14/04/2021, reception, 08:27, 18:45, 10:18
jo bloggs, 14/04/2021, chess, 14:21, 15:00, 00:39
jo bloggs, 14/04/2021, tennis, 17:35, 18:42, 01:07
I can see there are multiple transformation steps. I could use Power BI but I'm cognisant of additional licencing cost if not absolutely necessary. My thoughts are different table structure(!), rollups, calculated fields, powerFX code, flow...
Further thoughts. 300 people checking in and out of 2 or 3 groups daily generates a lot of data so performance and delegation are key considerations but the data shaping is not overly time critical.
This suggests a scheduled flow to me to shape, calculate, move, and manage data. I'm thinking along the following lines: -
1. aggregate 'attendance' records to a 'personName' and 'groupName' level with Min('regTimeIn'), Max('regTimeOut'),'group.timeStart','group.timeEnd'. 2. write aggregated table to 'register' spreadsheet (OneDrive) with calculations for duration & cost, etc.. 3. clear down the 'attendance' records.
Am I on the right track? or am I missing something simple e,g. calculated fields and rollups...?