Help with creating totals in a complicated table
I created a solution that tracks clients, employees and hours spent by each employee on their assigned accounts.
I've been tasked with creating a report, which would be exported to Excel, which would have the clients in rows and employees in columns, show in each cell how many hours each employee spent on each client.
Since both clients and employees are ever-changing, and the data is pulled from separate tables, the only way I could think to do this was by creating looping through the relationship-found-set of employees and getting their names and then hours to populate the cells for each row/client. Maybe there's an easier way (definitely open to it), but I got this to work.
So basically I have generic fields, numbered 1-100 (we currently have < 50 employees). My script changes the relationship based on a variable counter to populate each field (user01, user02, user03, ...) using Set Field By Name in a loop to traverse across the "user" fields.
The problem is, for the Excel export, the totals need to show up as the last row, after the last client, with the totals populating those same generic User01 fields.
I've set up a loop and tried to use the same process, but it's not working. I've tried using the Sum function, along with the GetFieldName function (so that I can use the same variable to get the sum for each field), but it won't allow an "expression." I've tried setting that same with a matching summary field (the summary field has the right number), but I just get a question mark. Any thoughts? Am I doing this fundamentally wrong?