COUNT() not working

[Linus_Grupp]

Hello everyone,

since today some parts of expressions in our apps are not working anymore. To be more specific: We could narrow it down to the COUNT() function not working properly when used with a SELECT() inside.

Here is one example of code not working anymore:

NOT(AND(COUNT(UNIQUE(SELECT(APP_T_USER[USER_ALL_ROLE_ID],AND([USER_EMAIL] = USEREMAIL(),[USER_APP_ID] = "4u1jdaw")))) = 1,IN("e5216f58",SELECT(APP_T_USER[USER_ALL_ROLE_ID],AND([USER_EMAIL] = USEREMAIL(),[USER_APP_ID] = "4u1jdaw")))))

The result of this part is now always false. The goal here is to check whether the current user has only a single entry in column "USER_ALL_ROLE_ID" and if this entry has the value "e5216f58".

The column "USER_ALL_ROLE_ID" is a virtual column of type List. I know that the SELECT() returns a List of Lists but the filter on USEREMAIL() in the select returns only one row which until recently worked as intended.

We could already implement a workaround for the cases we know of. Nevertheless it would be appreciated if someone could give some insights on this topic.

Thank you and best regards!

[Marc_Dillon · Accepted solution]

Here is your expression, better formatted to be readable:

NOT(AND(
  COUNT(UNIQUE(SELECT(
    APP_T_USER[USER_ALL_ROLE_ID],
    AND(
      [USER_EMAIL] = USEREMAIL(),
      [USER_APP_ID] = "4u1jdaw"
    )
  ))) = 1,

  IN(
    "e5216f58",
    SELECT(
      APP_T_USER[USER_ALL_ROLE_ID],
      AND(
        [USER_EMAIL] = USEREMAIL(),
        [USER_APP_ID] = "4u1jdaw"
      )
    )
  )
))

First, I'd recommend creating a "Current_User" Slice. It'll be helpful in this case, to shorten your expression, as well as many other cases. Read more about that here. I'm not sure what the [user_app_id]="4uiljdaw" portion is about, but since it's hard-coded, you can maybe add that into your Slice as well.

That would shorten your expression to:

NOT(AND(
  COUNT(UNIQUE(Current_User[USER_ALL_ROLE_ID]))) = 1,

  IN(
    "e5216f58",
    Current_User[USER_ALL_ROLE_ID]
    )
  )
))

 Which is already much easier.

Next, the List of Lists is definitely an issue, even if you're only talking about a single record. I recommend always explicitly dealing with this with SPLIT(TEXT()). So then your expression turns into:

NOT(AND(
  COUNT(UNIQUE(
    SPLIT(TEXT(Current_User[USER_ALL_ROLE_ID]) , " , " )
  )) = 1,

  IN(
    "e5216f58",
    SPLIT(TEXT(Current_User[USER_ALL_ROLE_ID]) , " , " )
    )
  )
))

Which could maybe fix your issue? I'm not super clear on the requirements here.

[Linus_Grupp]

Thank you very much.

I did not implement the "Current_User" Slice yet, but the general approach of the expression you suggested is working.