Re: Strange behavior with slice

Stephane_Liema · 04-27-2021 01:34 AM

I’ve got a slice

In my slice i’ve got a row filter condition :

IN([Mod_ID],ANY(SELECT(Variable[Var_V_ACCESS_L],[Var_Mail_ID]=USEREMAIL(),true)))

Variable : is a table that for each user contains his own data, the key is USEREMAIL
Var_V_ACCESS_L : is a virtual column that contains the list of module the user can access

This slice display for each user the modules he can access

When i test this slice, it works well. i’ve got this

This is good , i can access to the 2 first module

But on the view using the same slice

Only 1 line is displayed !!!

I should see the first 2 lines, exactly the same has the test done before directly on the slice

If i use the table in this view and not the slice

All the applications are displayed

I’ve checked in the documentation and slice can use virtual column in their condition
Do you know where i need to check ?

hru

What if you remove the ANY?

Stephane_Liema

Taking off the ANY
The result is bad in the test of the slide expression

But it is good in the view using the same slice !!

Congratulation
thank you
but for me this behevior is very strange
i would like to understand why?

Aleksi

Is the [Var_V_ACCESS_L] an EnumList?

Stephane_Liema

yes it is an enum list

Aleksi

@Stephane_Liema This is a known challenge with the combination of Slice & IN() and EnumList() which is causing this weird behavior… and that’s why it’s not working correctly. We have a fix for this, but we haven’t released it yet… and I’m afraid I don’t have exact timeline for it.

Koichi_Tsuji

never see the problem (not notifice before I mean) in terms of In and Enumlis combination.

I m not exactly sure what the nature of the problem is, but we do a trick to convert IN expression into CONTAINS expression by flating down the list type of data. We applied IN expressoin to filter out the records on the security filter and other place, but after flatting down (convert to contains expression) it massively improved performance as well.

Just as a quick thought to wonder.

@Takuya_Miyai

Stephane_Liema

top
thanks

Steve

Instead of:

IN([Mod_ID],ANY(SELECT(Variable[Var_V_ACCESS_L],[Var_Mail_ID]=USEREMAIL(),true)))

Try:

ISNOTBLANK(
  FILTER(
    "Variable",
    AND(
      ([Var_Mail_ID] = USEREMAIL()),
      IN([_THISROW].[Mod_ID], [Var_V_ACCESS_L])
    )
  )
)

Or:

IN(
  [Mod_ID],
  SPLIT(
    CONCATENATE(
      SELECT(
        Variable[Var_V_ACCESS_L],
        ([Var_Mail_ID] = USEREMAIL())
      )
    ),
    " , "
  )
)

Stephane_Liema

Theses 2 scripts are working good, in the slice preview and in the view using the slice the results are ok.

i’m going to use :

IN( [Mod_ID], SPLIT( CONCATENATE( SELECT( Variable[Var_V_ACCESS_L], ([Var_Mail_ID] = USEREMAIL()) ) ), " , " ) )

it is more easy to understand,
i suppose it is faster for the AppsheetEngine because

the where clause for the select is only ([Var_Mail_ID] = USEREMAIL()), and Var_Mail_ID is a unique key

and CONCATENATE, SPLIT and IN are i suppose very few time consuming.

I suppose also this :

SELECT(

Variable[Var_V_ACCESS_L],

([Var_Mail_ID] = USEREMAIL())

Is executed only once for all the table that i filter

For

FILTER(

“Variable”,

AND(

([Var_Mail_ID] = USEREMAIL()),

IN([_THISROW].[Mod_ID], [Var_V_ACCESS_L])

)

The where condition of the filter as to be executed for each row of my table to slice, because it use [_THISROW].[Mod_ID] in the where condition, and this value change for each row

Do you know if there is a debug tool on appsheet to check the time consuming, for me to compare and to choose the best script ?

Steve