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

[Hamlet]

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!

[SkrOYC]

Current_User (Slice) - How to conform your app around WHO is using the app

To pull a list> SPLIT(Current_User[Related Timesheets], " , ")> SPLIT(Current_User[User_Assigned_Clients], " , ")

You need Split

[WillowMobileSys]

Hamlet:

checking their ID column values against another column’s Enumlist (Ref Type) values.

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]

Tried SPLIT and the editor shows it working but not in the 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

[MultiTech]

WillowMobileSystems:

You won’t be able to compare an EnumList to a List or a List to an EnumList

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

[MultiTech]

Hamlet:

When I use this expression and test it with the editor it works:> > auto> IN([linkLocalidad], INDEX(currentUser[localCSV], 1)> )> >

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]

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

[WillowMobileSys]

MultiTech_Visions:

Actually there is a way to do this, it’s just not very straightforward> > - It involves the use of INTERSECT(),

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]

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:

[Hamlet]

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

[Steve]

For reference:

[WillowMobileSys]

Hamlet:

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

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

SkrOYC:

I know, it’s counterintuitive.> Since what’s inside the [localCSV] is a list also, Index just takes the first value from that list.

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

[Marc_Dillon]

WillowMobileSystems:

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

Whoa…crazy.