Filter out a table depending on useremail and enumlist

[Aurelien]

Hi AppSheet Team,

I’m currently building a slice depending on user allowances.
The purpose is to display a list of buttons to the users, so that they can jump to view according to what they are supposed to use.
Despite testing my expression outputs that looks OK, the result is not what I expect.
I describe the structure below.

Table : Menu View
List of possible views and deeplink expressions

Table : Users
List of users, with column [AccessLevel] set this way:

• Type: EnumList,
• Base Type: Ref,
• Referenced table name: MenuView
  

Slice: MenuView_ReadOnly_PerUser_Slice
This slice filters the views allowed to the current user, depending on its email.

• Source Table: Menu View
• Row filter condition:

ISNOTBLANK(
  INTERSECT(    
    SPLIT(
      LOOKUP(
        USEREMAIL(),
        "Users",
        "UserEmail",
        "AccessLevel"
      )," , "
    ),
    LIST([key])
  )
)

What I expect:
a slice keeping the items that are allowed to the user.

What I see at evaluating data from my Table Users:
This is a result of the enumList I ticked

What I see at evaluating data from my slice:
This is the expected result (see below)

What I see at emulating in the editor:
Only one item, this is not what I expect to be displayed.

I checked multiple times, the displayed view is based on the correct slice.

I just can’t understand what’s going on.

Does someone have a clue ?
Many thanks in advance !

[Suvrutt_Gurjar]

Hi @Aurelien
I know the following suggestion is too basic and you must have already tried. Since you mention editor not displaying properly, hope you have cleared the browser cache, just in case.

[Aurelien]

Hi @Suvrutt_Gurjar

Many thanks for your help.
I didn’t think about that, I feel dumb
I just tried with incognito mode, but unfortunately that did not solve my issue.
While I was on it, I opened the app with browser (purpose: get out of the editor), the result is the same.

Feel free to offer any other suggestions

[Suvrutt_Gurjar]

Hi @Aurelien ,

Thank you.

If the slice expression test results are not selecting all the necessary components of the list, I will request one more try with the following slice expression-

ISNOTBLANK(
  INTERSECT(    
    SPLIT(
      LOOKUP(
        USEREMAIL(),
        "Users",
        "UserEmail",
        "AccessLevel"
      ),","
    ),
    LIST([key])
  )
)

[Aurelien]

Thank you @Suvrutt_Gurjar

Suvrutt_Gurjar:

If the slice expression test results are not selecting all the necessary components of the list

Actually that part is OK.
Just in case, I made the change in order to give it a try (we agree this is about the blank spaces around the comma ?): no evolution on the result : the slice still filter out the correct rows, and the view still displays an unique item

[Suvrutt_Gurjar]

Yes @Aurelien , the suggestion was about blank spaces around comma because the SPLIT() with the first argument as list, needs a comma without spaces as mentioned in the article below.

help.appsheet.com

SPLIT()

List from text

I have currently no more suggestions . Is it correct to understand that slice shows all the correct rows and the view based on that slice shows just one record? I will again post if I come across any suggestion. I am sure other community colleagues too will have their guidance/ sugegstions.

[Aurelien]

Suvrutt_Gurjar:

with the first argument as list, needs a comma without spaces as mentioned in the article below

I was not aware of it, thank you for getting that point to my attention

Suvrutt_Gurjar:

I have currently no more suggestions

so bad

Suvrutt_Gurjar:

Is it correct to understand that slice shows all the correct rows and the view based on that slice shows just one record?

That is exactly correct ! Thank you for expressing on a much clearer way what I said

[Steve]

I don’t see why your slice row filter expression isn’t working, but this is how I’d write it instead:

ISNOTBLANK(
  FILTER(
    "Users",
    AND(
      ISNOTBLANK([UserEmail]),
      ([UserEmail] = USEREMAIL()),
      IN([[_THISROW].[key], [AccessLevel])
    )
  )
)

You’d benefit from having a CurrentUser slice:

ISNOTBLANK(
  FILTER(
    "CurrentUser",
    IN([[_THISROW].[key], [AccessLevel])
  )
)

[Steve]

This:

ISNOTBLANK(
  INTERSECT(    
    SPLIT(
      LOOKUP(
        USEREMAIL(),
        "Users",
        "UserEmail",
        "AccessLevel"
      )," , "
    ),
    LIST([key])
  )
)

Can be simplified to:

IN(
  [key],
  SPLIT(
    LOOKUP(
      USEREMAIL(),
      "Users",
      "UserEmail",
      "AccessLevel"
    ),
    " , "
  )
)

Or, with a CurrentUser slice:

IN([key], SPLIT(("" & CurrentUser[AccessLevel]), " , "))

If the simplified expression works, there’s a bug in INTERSECT() (which wouldn’t surprise me).

[Aurelien]

Hi @Steve

Thank you for your help.
I will simplify my expression as you suggest it.
However, my issue is not on the slice filter.
It’s on the view, not displaying as expected despite the slice giving the correct set of keys.

[Steve]

The results using the Test button in Expression Assistant are generated by an AppSheet server, where the slice is generated entirely on your device. The expression engines in the server and within the app itself are different implementations with slight differences. It’s possible for an expression to behave as desired when tested from Expression Assistant but not in the app itself. When it happens, it’s a bug.

[Aurelien]

Hi @Steve and @Suvrutt_Gurjar

I tried another strategy, following @Steve 's suggestion, same result.

"Users_currentUser_AccessLevel_ReadOnly_Slice"
Very basic slice…

MenuView_ReadOnly_PerUser_Slice
Another slice, less basic but not so complex:

with expression:

IN(
  [key],
	ANY(Users_currentUser_AccessLevel_ReadOnly_Slice[AccessLevel])
)

And the result is the same.

I just contacted support, I’ll keep you updated on a way to bypass this engine difference !

[Suvrutt_Gurjar]

Hi @Aurelien ,

Thank you for the update. It will be interesting to know the resolution/solution you will ultimately get. Please do post the response from the support team.

[Steve · Accepted solution]

Try:

IN(
  [key],
  SPLIT(("" & Users_currentUser_AccessLevel_ReadOnly_Slice[AccessLevel]), " , ")
)

[Aurelien]

That WORKS !!!

I feel like Dr Frankenstein after sewing some human pieces indefatigably

Thank you @Steve !

[Aurelien]

I saw that kind of workaround a few weeks ago, it still looks very weird and un-explainable to me.
Some piece of magic !
Thank you again

[Suvrutt_Gurjar]

Steve:

IN(
  [key],
  SPLIT(("" & Users_currentUser_AccessLevel_ReadOnly_Slice[AccessLevel]), " , ")
)

@Steve special