Bug: Using ANY() as a slice filter on a table with only one row

Suppose you have a table. Let’s call it Table1. Suppose Table1 only has one row.

In that table there is a column of type List. Let’s call it Column1.

Now you want to create a slice (let’s call it Slice2) of a different table with many rows (let’s call it Table2) based on whether a value (e.g a column, Value2) in a given row is in the list in Column1 of the only row of Table1.

So the filter expression defining Slice2 would be

IN( [Value2], ANY(Table1[Column1]) )

Table1[Column1] is a list of the lists from each row. ANY(Table1[Column1] ) normally pulls a single value from a list. In this case you would expect one list in return, i.e. the [Column 1] value from a single row.

However, if there is only one row in Table1[Column1], what you will actually get is only one item from the list, i.e. one single value back from ANY(Table1[Column1]) ) to compare against. For example, if the value of Table1, Column1 for row 1 is {A, B, C, D}, ANY() will return only A instead of {A, B, C, D}.

This happens only when filtering a slice (as far as I can tell) and is inconsistent with how ANY() behaves in other places. If you apply the exact same formula in a virtual column, the expression works as expected against the whole list. It will also correctly return the full list in a case like this:

ANY(Table1[Column1]) )

There’s something else odd as well: the full list of values is correctly returned if you put something like this in the filter expression for the slice.

IN( [Value2], Table1[Column1] )

But if you put this in a virtual column, it will (expectedly) cause problems, as you are essentially asking to compare a single value to a list of lists.

1 Like

I have demoed this bug in a sample app called ANY Bug: portfolio of mobile apps created with AppSheet

A list of one item is still a list, so you have to treat it as a list. Historically, ANY() doesn’t return lists properly. Instead:

IN([Value2], SPLIT(("" & Table1[Column1]), " , "))
2 Likes