Is it new behavior of ANY() that always return the first item in the list?

AppSheet AppSheet Q&A
1

image
image1745Ă—940 117 KB

I am testing this case of ANY as shown on the attachment.

  • The Refresh action (button) only updates a text column with NOW() value.
  • I have repeatly hit the button like 30-40 times already.
  • I have even changed the values directly in the google sheet (as the data source), re-sync the app, then hit the button like 10 more times.

According to AppSheet official document: ANY() - AppSheet Help

Returns an item from a list, as follows:

  • The first item from a list if it is constructed (for example, using LIST()).

  • An arbitrary item if a list is a generated list (for example, using FILTER() or SELECT()).

  • Blank if the list is empty.

Note: The order of the list cannot be guaranteed unless wrapped in SORT().

Sample usage

ANY(Students[Name]) returns an arbitrary value from the Name column of the Students table. Arbitrary because the order of values in the Students[Name] column list isn’t guaranteed unless wrapped in SORT(). Equivalent to ANY(SELECT(Students[Name], TRUE))

WHAT I EXPECTED:

  • An arbitrary value of ID of the child table would be returned.

WHAT I GOT AS RESULT:

  • Only the ID of the first row, which is “99” is returned.

So, im just wondering if it is a new change that:

  • The order of a list generated by a REF_ROWS column, table[column], or SELECT() is now preserved, sorted by _ROWNUMBER?
  • ANY() always returns the first item in a list?

Thank you in advanced!

2

Not a change. It will always be the first item in the list. The reason I (yes, I used to work on the AppSheet team) wrote the doc with that verbiage for “a generated list” is because query results don’t guarantee an order: they typically produce results in storage order, but that isn’t guaranteed. It’s a matter of semantics.

3

Thank you for your reply, Steve. Would you please help me clarify this:

  • Is it always the first item in the list, both constructed and generated lists, in a preserved order (let’s say, based on _ROWNUMBER)? Because it seems that way in the tests I have done.

PS: I think it is important for me to ask so I can expect the outcome of the function if I ever use it from now on.

4

Your question has already been answered.