Try wrapping your list-of-a-list inside a `concatenate()` and see if that helps.

Hamlet · 11-16-2021 04:59 AM

I’m trying to filter out some records by checking their ID column values against another column’s Enumlist (Ref Type) values.

When I use this expression and test it with the editor it works:

IN([linkLocalidad], INDEX(currentUser[localCSV], 1)
)

But when running it in the App it only brings records that match the first value of the Enumlist.

If I manually make a list of the Enumlist values like so:

IN([linkLocalidad], LIST(E01 , E02 , E03)
)

It works fine in the App.

I have tried a bunch of different ways of turning this “INDEX(currentUser[localCSV], 1)” into a list, but so far nothing seems to work.

Let me know what other Info you guys need to help me out.

Thanks in advance!

MultiTech

Try:

in([linkLocalidad], Split(Concatenate(currentUser[localCSV]), " , "))

@Hamlet your original formula would only pull the first value from the Current_User’s [LocalCSV] column.

If you had, for example: {1, 2, 3, 4} in that list, your original formula (using index) might be pulling the first number, not the list-of-a-list

Sometimes things process differently on the device than they do in the editor
- Maybe on the device, the list-of-a-list is being converted automatically - and that ‘automatic’ action is getting in the way… maybe. I dunno.

Try wrapping your list-of-a-list inside a `concatenate()` and see if that helps.

View solution in original post

SkrOYC

You need Split

Hamlet

Tried SPLIT and the editor shows it working but not in the App

WillowMobileSys

I am not sure I follow. This comment sounds as if you are trying to compare a list to a list. What value is stored in [linklocalidad]?

The IN function can only check that a single value is part of a List of Values:

IN(<single value>, <List of Values>)

You won’t be able to compare an EnumList to a List or a List to an EnumList - unless the left-hand side picks out a single value first:

IN(Index(<EnumList>, 1), <List of Values>)

OR

IN(Index(<List of Values>, 1), <EnumList>)

Can you describe the intended goal for your app?

Hamlet

I’m trying to make a Slice of a table by having the user select a few RefType Values from an Enumlist.

This is what it should look like when it works:

You can see different names on the first column.

But I keep getting this:

Even though the editor test shows it working:

SkrOYC

You have to replace INDEX() with SPLIT()
INDEX is for just a value

Hamlet

App editor test shows that it works:

In App it comes out blank:

SkrOYC

Check how the data is separated on the EnumList [localCSV]
You can post here the value inside of one of the rows that has more than one value.
Example:
If it’s something like Data1,Data2,Data3 the item separator inside SPLIT() should be ","
If it’s Data1, Data2, Data3 → ", "
Data1 , Data2 , Data3 → " , "
It depends on your “Item separator” config for the EnumList [localCSV] column

Hamlet

Yep, I also checked this, and I have it set up as it should be and still problem remains…

MultiTech

Actually there is a way to do this, it’s just not very straightforward

It involves the use of INTERSECT(), to create a list of all common values between the lists, and IsNotBlank() to see if there’s anything in that resultant list.
- If there is, then there are common elements between the lists.

IsNotBlank(INSTERSECT(
  list(1, 2, 3), 
  list(2, 4, 5)
))

Result: true
- Meaning: there are common elements between the two lists
  - They both share a 2; so the INTERSECT() results in that number (becoming a list of just the number 2), and IsNotBlank() results in true

Here’s an example of a permission formula I’m using in an app (it controls when an action should be seen):

IsNotBlank(INTERSECT(
  list("Dev", "Admin"), 
Split(Concatenate(Current_User[User_Permissions]), " , ")
))

The action is visible to “Dev” and “Admin” permissions types
If the Current_User has either of those inside their [User_Permissions], then the action is shown

WillowMobileSys

Yes, I agree (and I still forget about INTERSECT())

…we still do not KNOW what is actually stored in [linkLocalidad].

@Hamlet Is [linkLocalidad] an EnumList with Base Type = REF??

Hamlet

[linkLocalidad] is just a Ref type field. Not Enumlist.

WillowMobileSys

Yep, got it from the previous post. I missed a couple things from original post. The column [localCSV] is the EnumList of Ref’s and currentUser, I now assume, is a slice with only a single row of details for the currently logged in user. I didn’t infer these details.

So, the basic issue here is that currentUser([localCSV]) is actually a List of a single item which is also a list. Your goal was to check that list ITEM (a list) and see if [linkLocalidad] was an included value.

Your final expression works because the CONCATENATE “flattens” the list into a single comma separated TEXT value. The SPLIT then transform that into a List again but this time just one list instead of a List of Lists.

What I don’t understand is why didn’t this expression below work? Shouldn’t INDEX have returned the first List item - the column [localCSV] which is also a list?

IN([linkLocalidad], INDEX(currentUser[localCSV], 1))

By the way, when I use the currentUser slice technique, I access the values using the ANY function. So far I have not seen an issue. An example would be:

IN([linkLocalidad], ANY(currentUser[localCSV]))

Hamlet

Both your examples work on the Editor Test, but not on the App itself. That’s what was throwing me off

SkrOYC

WillowMobileSystems:

What I don’t understand is why didn’t this expression below work? Shouldn’t INDEX have returned the first List item - the column [localCSV] which is also a list?
IN([linkLocalidad], INDEX(currentUser[localCSV], 1))

I know, it’s counterintuitive.
Since what’s inside the [localCSV] is a list also, Index just takes the first value from that list.
Is like a list of lists (eventhough there is just one row) and then index takes the first value.
In other words, INDEX() is just for values, not for lists
That’s why I suggested the SPLIT() expression since it creates a list from [localCSV]

WillowMobileSys

This didn’t mesh with my understanding , so I have been playing with various ways to access an EnumList of Ref’s. The behavior seems completely as I would expect. I am not seeing the issues highlighted here.

In the Image below, I am showing a Form where I have chosen a REF value into an EnumList and then used that in 3 different versions of an IN function (labeled 1, 2, 3). To help show the complete function, I am also showing the resulting Lists compared to (red arrows). I am aware you can’t see all details.

You will note that when I compare against CurrentUser[Choices], the test fails as indicated with a result of N. The other two tests, which pick off the first item from the CurrentUser[Choices] list Pass the test as indicated with a result = Y.

I’d also like to call your attention to the list results for Test List 1 and Test List 2. Notice how Test List 1 has NO spaces while the other Test Lists DO have spaces included? This is how AppSheet is distinguishing the list items. Test List 1 is a List of 1 item also a list. Test List 2 and Test List 3 are both a list of 4 items of distinct values.

Point is that the use of ANY and INDEX seem to work as expected without the need for CONCATENATE and SPLIT.

Example of List of Lists - Multiple list items. Note the placement of spaces to distinguish List items as lists

Here is a Mind Blower!!

It appears that the IN function WILL compare Lists against other Lists!!! And more over, the order of the chosen list doesn’t matter!!

In my Choice List, I have chosen multiple REF items and in reverse order of the lists I am comparing against. In all 3 variations of the test, the returned result was Y. Test 1 Result returned Y this time because its result matched the single list item of {1,2,5,7] though it is in reverse order.

The other 2 tests matched a list against a list.

If I then only choose 7 and 1, in that order. Test Result 1 is N as expected but the other 2 are still Y - compared list against list.

Choose a mix in lists and not in lists - all three fail - as expected

Choose none in common among lists - all 3 fail - as expected

Marc_Dillon

Whoa…crazy.

MultiTech

Split and concatenate… expensive???

Starts shaking head in disagreement
- Even with a big string… I wouldn’t think the time needed for the operation to process wouldn’t be close to a brute force SELECT()???
- They’re only text-manipulation formulas…

WillowMobileSys

This is exactly why they are expensive. Text operations are one the most expensive operations when compared to other data types. In this context there will need to be N-1 concatenations and then N-1 splits. That’s 2(N-1) operations just to get to the point to perform the IN function. The IN could be another N-1 function depending on where the value sits in the list. That’s a total of 3(N-1)

A similar SELECT would be simply N. But something that Big O doesn’t really account for is the expense of the operations. Concatenating 2 text values is a lot more expensive than simply comparing those same text values.

My main point is that if a CONCATENATE/SPLIT are performed on large lists or even worse, lists that grow as the table grows, the impact of CONCATENATE/SPLIT will become huge. I believe its a performance hit that is not needed - or at least no longer needed in AppSheet.

SkrOYC

I think that at this point we should be making a Feature Request so that AppSheet teams adds exactly how expensive each function on AppSheet is in the docs

SkrOYC

This is interesting.
Have you tried with strigns?
A common scenario for me is:

There is info from more than one company inside the App. Lets call this table Company
There is a User table that has an EnumList base type Ref where I select wich Company's info the user can access to
There is a Slice called Current_User where the row filter condition is [EMAIL_COLUMN]=USEREMAIL()
There is a Security Filter where I need to use this expression in order to work:

IN(
  [A_COLUMN_THAT_HAS_THE_KEY_OF_COMPANY],
  SPLIT(
    CURRENT_USER[ENUMLIST_BASETYPE_REF],
    " , "
  )
)

Another example that could be easier to replicate

There is an app that works as a launcher
There is a table with AppSheet Apps
There is a user table
There is the same Current_User slice
I allow the users just certain apps on the launcher view depending on a EnumList basetype ref
The users just see the Apps that are allowed base on security filter

Now, maybe there was a bug related to how `IN()` was dealing with this list-of-lists scenario and now it takes into account all the values as if there was a List addition. But, eventhough that might be true, I prefer what @MultiTech_Visions points out about reliability. If it works now, It may not in the future, so adding `SPLIT()` and `CONCATENATE()` might be redundant, it works

WillowMobileSys

Sure! But I recommend using sparingly! These are expensive operations and can cause sluggish performance if the lists become long and used frequently.

Steve

This is new(-ish?). It certainly didn’t used to work this way!

MultiTech

Try:

in([linkLocalidad], Split(Concatenate(currentUser[localCSV]), " , "))

@Hamlet your original formula would only pull the first value from the Current_User’s [LocalCSV] column.

If you had, for example: {1, 2, 3, 4} in that list, your original formula (using index) might be pulling the first number, not the list-of-a-list

Sometimes things process differently on the device than they do in the editor
- Maybe on the device, the list-of-a-list is being converted automatically - and that ‘automatic’ action is getting in the way… maybe. I dunno.

Try wrapping your list-of-a-list inside a `concatenate()` and see if that helps.

Hamlet

in([linkLocalidad], Split(Concatenate(currentUser[localCSV]), " , "))

This did it!! I guess there’s something going on with how the App works out the expression on the device.

Thank you to all that stopped by.

Now it works perfecto on editor test and app:

Steve

For reference:

Marc_Dillon

So, I discovered a weird oddity/bug today that has to do with some things discussed here.

Assuming the question is “how do I compare a value against a List of Lists”, and we have a Table “table” with a List type column “list_col”, do we use:

IN( [value] , table[list_col] )

or:

IN( [value] , SPLIT(TEXT( table[list_col] ) , " , " ) )

I’m quite sure that #1 did NOT work in the past, and you had to use #2. However it does seem that #1 works today, at least in most cases.

What I found today was a complex edge-case where #1 was still not working. I’m far too tired right now to explain in more detail, but for now just take this as my recommendation to:

Always use #2.

SkrOYC

Same.

Steve

Marc_Dillon

Nice.

But, why not retain the standard space-comma-space delimiter, " , " ?

Steve

I have no idea, actually. I just documented the observed behavior.

Having trouble with expression for turning a field into a list for using in a IN() expression

Try wrapping your list-of-a-list inside a concatenate() and see if that helps.

Actually there is a way to do this, it’s just not very straightforward

Split and concatenate… expensive???

Another example that could be easier to replicate

Try wrapping your list-of-a-list inside a concatenate() and see if that helps.

or:

Always use #2.

Try wrapping your list-of-a-list inside a `concatenate()` and see if that helps.

Try wrapping your list-of-a-list inside a `concatenate()` and see if that helps.