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

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

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

Another example that could be easier to replicate

  1. There is an app that works as a launcher
  2. There is a table with AppSheet Apps
  3. There is a user table
  4. There is the same Current_User slice
  5. I allow the users just certain apps on the launcher view depending on a EnumList basetype ref
  6. 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


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


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()??? :thinking:
    • They’re only text-manipulation formulas…

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.


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 :sweat_smile:

1 Like

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


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:

  1. IN( [value] , table[list_col] )


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



1 Like
1 Like



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

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