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

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!

You need Split

1 Like

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?

2 Likes

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

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:

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

App editor test shows that it works:

In App it comes out blank:

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

2 Likes

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

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

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

3 Likes

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

1 Like

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

1 Like
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. :hugs:

Now it works perfecto on editor test and app:

3 Likes

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

For reference:

3 Likes

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

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

1 Like

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]

1 Like

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
Screen Shot 2021-11-16 at 3.02.02 PM

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

2 Likes

Whoa…crazy.

3 Likes