I have an Action that “adds an new row to another table using data from this row”.
I am trying to tally data from a Timesheet table using the below expression, but it is not aggregating the data because it just displays “0”.
SUM(
SELECT(Timesheet[Total Hours], ([_THISROW].[WorkorderID] = [WorkorderID])
))
Any suggestions on how to get this data to do the proper math?
It is difficult to assist as no data is provided from the select nor the datatype.
What is [Total Hours] data type ? Number or Datetime or Hours ?
To debug go into the expression assistant and just do the SELECT and then evaluate the select and see what data is being returned ? It should be LIST().
SELECT(Timesheet[Total Hours], ([_THISROW].[WorkorderID] = [WorkorderID]))
The select should return a list and must be defined as type LIST
SUM(LIST) on the other hand should be defined as type NUMBER, HOURS ?
I have tried it as various data types, Number, Duration, Text.
I did try and debug it by entering the expression in the ValidIF box. I tested every column to see if i can pull the data and for some reason I am able to pull data from every single column except the [Total Hours] column. I will say that the Timesheet column [Total Hours] has the hours in this format, 000:00:00. Could that be the cause?
Yes the data format is key. Note that hours in time format may never get greater than 23 so one cannot add them as say 12:00:00 + 12:00:00 + 12:00:00 = 1 day + 12:00:00
So what you want to do is extract the hours part only, which by default is in NUMBER format.
Maybe create a virtual column HOURSNUM = HOUR(TIME(Timesheet[Total Hours]) - TIME(“00:00:00”)) and this should return hours only in number format.
I actually tested the above method and it worked fine.
You can use SUM() with Duration-type values, so your SUM(SELECT(...)) should work fine so long as the SELECT() expression is producing a list of Duration values. Likewise, the column receiving the result of the SUM(SELECT(...)) should be the Duration type to correctly display the result.
Specifically, this:
SUM(
SELECT(Timesheet[Total Hours], ([_THISROW].[WorkorderID] = [WorkorderID])
))
should produce the desired total time as long as the Total Hours is of type Duration.
If you instead prefer to record whole hours, use Number-type columns rather than Duration; or use Decimal-type columns if you prefer fractional hours.
I did end up going the route of the whole hours by extracting the HOUR() and MINUTE() from the Duration. Thank you.
Now that I was able to produce the correct data I am ready to sum it up. Now the issue that I am having is I am not able to filter the data.
The tally just ends up as zero when I try to specifically filter by WorkorderID
Im using:
SELECT(Timesheet[Number Of Hours], ([_THISROW].[WorkorderID] = [WorkorderID]))
If I just use this then it pulls the Total Hours of every single entry in the Timesheet table.
Timesheet[Number Of Hours]
So basically when I filter it to just included the matching WorkorderID it fails.
Ok, I was able to get everything to work. This is a bit strange but all I had to do was add “[_THISROW]” in front of [WorkorderID] inside the action used to create the report (see image).
About the only thing that looks a bit off is the fact that it is no longer filling the WorkorderID field with anything, just blank, but everything is calculating correctly. (see image)
In your guys opinions do you think im ok to let it stay blank?
I have had this same blank column issue before and I had to make the spreadsheet column type format and APPSHEET column type format different. When I made the spreadsheet type Plain Text it populated the column?
Why this works I am not sure but I head bashed and found it worked fine.
