Filter out a table depending on useremail and enumlist

Aurelien
Participant V

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
3X_a_0_a0c9eab6921de48c1b8f42882fb1188452c581a2.png

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

  • Type: EnumList,
  • Base Type: Ref,
  • Referenced table name: MenuView
    3X_b_d_bdd8895a705640d37830366fcff4c9a7bc44c078.png

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
3X_f_1_f1f4da55af625c0f8efd268e6fb8040d0209d057.png

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.
3X_8_6_86c4efc911fd75df19f897291c208746283038a1.png

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

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

Does someone have a clue ?
Many thanks in advance !

Solved Solved
0 16 380
1 ACCEPTED SOLUTION

Try:

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

View solution in original post

16 REPLIES 16

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.

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

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

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

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

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

so bad

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

Steve
Participant V

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
Participant V

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

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.

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.

Hi @Steve and @Suvrutt_Gurjar

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

"Users_currentUser_AccessLevel_ReadOnly_Slice"
Very basic slice…
3X_b_c_bc5959939856e1061e5f2a63d9c2eaee5869860d.png

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

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.

Try:

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

That WORKS !!!
3X_2_1_21503569584442b00cfb16e1f42bf428656c84ff.gif

I feel like Dr Frankenstein after sewing some human pieces indefatigably

Thank you @Steve !

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

@Steve special

Top Labels in this Space