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:
IN(
  [A_COLUMN_THAT_HAS_THE_KEY_OF_COMPANY],
  SPLIT(
    CURRENT_USER[ENUMLIST_BASETYPE_REF],
    " , "
  )
)

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

2 Likes

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

2 Likes

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…
4 Likes

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.

4 Likes

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!

5 Likes

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

or:

  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.

3 Likes

Same.

1 Like
1 Like

image

Nice.

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

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

2 Likes