Workflow add rows effecting sum select

Hi all,
Can anyone assist?

I have created a payroll system which has 6 tables

Employee table (owns the salary table as children)
Salary table

Benefits table
Time Sheet table
Sick leave table
Holiday leave table

On the salary table i have the below formula aggregating the values of the Timesheet table

TOTALHOURS(
SUM(
SELECT(
TimeSheet[Shift Hours],
AND(
[Employee] = [_THISROW].[Employee],
([WorkDate] >= [_THISROW].[Period Start]),
([WorkDate] < [_THISROW].[Period End])
)
)
)
)

  • All the columns are structured (not VC)

  • All tables are referencing the Employee table

  • The 4 accrual tables are not referenced to salary table

  • The duration columns (Time In, Time Out, Shift Hours) are all set to ignore seconds

  • The spreadsheet column format duration columns is set to [h]:mm i have also tried [h]:mm:ss

  • The formulas is testing out fine but the tables never update

  • I have a sliced view setup of the salaries table but i have looked at the un-sliced view and it’s still not working

  • I have setup a Workflow that fires on updates to the Employee table, basically when a new employee is added the Workflow creates a number of rows in the Salary table for that employee, populating spreadsheet column A through D, funny though, the application calculates the TimeSheet table values fine when the Workflow fires, but any update made to the TimeSheet table after the rows are added is not showing in the Salary tables

  • But the sum select formula is still testing out fine :face_with_raised_eyebrow:

  • All of the 4 tables i am trying to sum are doing this

TimeSheet Table

Salaries Table
salarytable

Do i need to reference these 4 tables to the salary table to make this work? It shouldn’t make a difference right…

A non-virtual column will only recompute when the row is updated, as when saved in a form or modified by an action. A virtual column, by contrast, updates whenever the app syncs or when its row is updated,but a virtual column value isn’t saved to the data source.

1 Like

Hi Steve,

thanks for that one, i think this might be the problem i’m having but i checked all the columns in the formula, they’re all non-virtual columns, the data should be getting aggregated into the salary spreadsheet tab.

Could you assist any further?