Error: SUM function is used incorrectly

[DianeT]

I have these columns in a table named Downtime Log:

Downtime Category, Total Hours, Planned Downtime Duration, Unplanned Downtime Duration, Remarks
 - Downtime Category is chosen by app user in a form
- Total Hours (in duration format) is calculated by clocking in and out
- Planned Downtime Duration copies the duration in Total Hours if the Downtime Category is "Planned Downtime" in the same row
- Unplanned Downtime Duration column copies the duration in Total Hours if the Downtime Category is "Unplanned Downtime" in the same row
- Remarks is either "Transferred" or " "

I want sum up the Planned Downtime Duration column when the "Remarks" column is blank in a virtual column, and then pass this value into another table's column when a form for that table is submitted. 


I have pasted the following expression in the virtual column: 

IF(
AND(
ISBLANK([Remarks]),
[Category] = "Change over"
),
SUM(
FILTER("Changeover Downtime Record", AND(ISBLANK([Remarks]), [Category] = "Change over")),
[Total Hours]
),
""
)

It does not work and says "SUM function is used incorrectly". 

I have tried to put LIST() but it does not work either. 

What could be the problem here? 


P.S. I want to calculate the total downtime according to category during a shift (ex: 7 am to 7pm) in a day and put them in another table that states other details regarding the shift. Once the values are put in the other table, I want to mark all downtime duration involved as "Transferred" in the Remarks column.

[Landan_QREW]

I would try this:

IF(
AND(
ISBLANK([Remarks]),
[Category] = "Change over"
),
SUM(
SELECT(Downtime Log[Total Hours], AND(ISBLANK([Remarks]), [Category] = "Change over"))
),
NUMBER("")
)

[DianeT]

I tried and this error pops out: 

IF function is used incorrectly:the second input (value-if-true) and third input (value-if-false) should have the same type.

[Aurelien · Accepted solution]

You need to adapt the content provided by @Landan_QREW to your expected type, using the error provided by AppSheet.

You said:

---

@DianeT wrote:

- Total Hours (in duration format) is calculated by clocking in and out

---

According to this, can you try:

"000:00:00"

instead of 

NUMBER("")

For reference:

Date and time expressions - AppSheet Help

The notions of TIME and DURATION and how to use in... - Google Cloud Community

[Landan_QREW]

Ah! Sorry, I missed the data type on your [total hours] column. To Aurelien's point, I think you can put '' instead of NUMBER('') or '000:00:00'.

[DianeT]

Guys, it worked! Thank you so much. 

Also, I would like to ask if the dataset gets big, will this expression cause lagging?  

And this is probably a question best suited under another topic, but here it is anyway. Any idea how I can execute an action that marks the Remark column to "Transferred" after a form (for another table) containing the sum of downtime duration is submitted? It seems that I cant execute a group of actions on two separate tables.

[Landan_QREW]

Depends on the size of the dataset. Supposing your downtime log table stays under 1000-3000 rows, I don't envision a tremendous impact, but larger than that, and I would consider a slightly different architecture.

Regarding your second question, you will need two actions that execute an action on a set of rows that are both linked with the table you plan to trigger the actions on. You will then need a grouped action that performs both preceding actions.

[DianeT]

I see, could you explain a little more on the two actions?

[Landan_QREW]

Difficult to elaborate over a chat. 

If you want to try to cover it over a quick web call, you can reach out to my email at (PII Removed by Staff). 

[DianeT]

Actually i just noticed a problem, the expression returns the sum of previous sums in the same column, any idea how i can overcome that?