Switch query

[JamesWB]

I want to evaluate possible values of a field, "Edit_count", which is of type ChangeCounter. Based on those, I then change the value or don't change it, of another field.

I am trying a switch statement like this, but it throws the error "sequence contains no elements". Where am I going wrong?

SWITCH([Edit_count], < 1, COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1, 1, COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 0, > 1, COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 0)

[Suvrutt_Gurjar]

Please use IFS() instead.

With SWITCH() you cannot have comparison or equality operators in the expected values.

So, just for testing, if you test your expression with something like 

SWITCH([Edit_count],  1, COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1, 2, COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 0,  3, COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 0) 

it will work without error but obviously will not give correct results expected by you.

Please try to use IFS() instead.

[JamesWB]

Hi Suvrutt, thanks for your answer. I didn't realise you couldn't do comparisons with Switch, that's very helpful.

I'm trying to use IFS but the logic of evaluating a ChangeCounter that starts off blank and then increments when edited seems very difficult. Here's what I've got to so far:

IFS(([Edit_count] <>1) ,COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1)

Strangely, when adding an entirely new record, it removes the Count field from the form altogether! The initial value is something like Blank or Null but I don't know how to use that in the IFS statement, clearly there's something odd about ChangeCounter because things like ISNULL, ISBLANK and "" all don't work. Would love to hear an explanation of how to test against a field of ChangeCounter type using IFS.

What I'm trying to do is test:

- If Edit-count is empty, then start the Count field at numeric 1 when adding the new record.

- If Edit-count is 1, then start the Count field at existing Count number + 1 when adding the new record.

- If Edit_count is more than 1 when editing and then saving, don't change the Count number.

At the moment, I can't achieve the first of these.

[JamesWB]

This almost works, but it removes the counter field from the App!

IFS(([Edit_count] <1) ,COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1)
([Edit_count] = 1) , COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1
([Edit_count] > 1) , COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 0

[Suvrutt_Gurjar]

Could you please elaborate  your second post?  What do you mean by almost works? Which Counter field gets removed? 

Your shared IFS() expression in general does not seem to be having a final closing parenthesis. 

[JamesWB]

The code checker accepts that! Maybe it's still wrong though.

So when a new record is added, the Counter appears in the form and is correctly set at 1, which is the first counted record.

But when Saved, the Counter field shows no value in the sheet and disappears in the Detail and Inline views.

I suspect the IFS statement needs something like a start value and end value, but I don't know how to do that.

[Suvrutt_Gurjar · Accepted solution]

It is difficult to opine without knowing more details, but it sounds your entire expression should be as follows

IFS(

[Edit_count] <=1  ,COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1,
[Edit_count] > 1 , COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 0

)

I think your expression was not throwing error based on first part of the expression which seemed to be syntactically correct and I think remaining expression was ignored by the editor.

IFS(([Edit_count] <1) ,COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1)
([Edit_count] = 1) , COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1
([Edit_count] > 1) , COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 0

[JamesWB]

Oh brilliant, that worked, thanks so much.

[JamesWB]

Actually I was wrong, the expression works but it doesn't solve the problem, sigh.

The issue is that when editing a record, the Counter still increments.

[Steve]

COUNT(FILTER("Hazard", [_THISROW].[Machine] = [Machine]))
+ IFS(([Edit_Count] + 0) <= 1), 1)

[JamesWB]

Hi Steve, thanks very much for responding to my problem.

That code says too many parentheses.

Is the + sign in line meant to be within an overall bracketing of Count, eg, is it all part of the first Count statement? If I remove the final ) it says it's not a valid IFS statement.

[graham_howe]

Try the following, I don't know if it gives what was intended, but it look valid syntax to me:

COUNT(
  FILTER(
    "Hazard", [_THISROW].[Machine] = [Machine]
  )
)
+ 
IFS(
  ([Edit_Count] + 0) <= 1
  , 1
)

[JamesWB]

Thanks Graham. It accepted the code, but it didn't work - Counter was still incremented on an edit.

I'm giving up now!

[Suvrutt_Gurjar]

Very compact @Steve 👍