Filter out a table depending on useremail and enumlist

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
image

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

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

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
image

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

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

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

Does someone have a clue ?
Many thanks in advance !

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.

2 Likes

Hi @Suvrutt_Gurjar

Many thanks for your help.
I didn’t think about that, I feel dumb :sweat_smile:
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 :slight_smile:

1 Like

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])
  )
)
1 Like

Thank you @Suvrutt_Gurjar

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

2 Likes

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.

I have currently no more suggestions :slight_smile: . 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.

2 Likes

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

so bad :disappointed_relieved:

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

2 Likes

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])
  )
)
2 Likes

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

3 Likes

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.

2 Likes

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.

1 Like

Hi @Steve and @Suvrutt_Gurjar

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

"Users_currentUser_AccessLevel_ReadOnly_Slice"
Very basic slice…
image

MenuView_ReadOnly_PerUser_Slice
Another slice, less basic but not so complex:
image
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 !

2 Likes

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.

2 Likes

Try:

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

That WORKS !!!
italive

I feel like Dr Frankenstein after sewing some human pieces indefatigably :rofl:

Thank you @Steve !

3 Likes

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 :+1:

2 Likes

@Steve special :+1:

4 Likes