Error: SUM function is used incorrectly

AppSheet AppSheet Q&A
,
1

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.

2

I would try this:

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

1 Like
3

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.

4

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

You said:

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

1 Like
5

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’.

2 Likes
6

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.

2 Likes
7

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.

8

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

9

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?

10

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).