In the coming weeks, we will make these changes:
- SUM(): If given a List of Refs, returns a value with the referenced type, instead of a Ref
- AVERAGE(): If given a List of Refs with a numeric referenced type, returns a Decimal, instead of a Ref
- ABS(): If given a Ref with a numeric referenced type, returns a value with the referenced type, instead of a Ref
List arithmetic (e.g.,
LIST(1, 2, 3) + LIST(4, 5)) will now require that the element type of the second List is convertible to the element type of the first.
If your app depends on these functions returning a Ref, we suggest converting the output of the function into a Ref through setting the type of a virtual column. The guide on virtual columns is here: https://help.appsheet.com/en/articles/919891-virtual-columns
If your app uses List arithmetic on Lists of incompatible types (e.g., adding a List of Numbers and a List of Dates), this is not recommended, because elements of the same List should be the same type. However, for cases where this is unavoidable, we suggest converting the second List to a List of Text. You can do this by either:
- Wrapping each element of the second List in a TEXT() expression, as in
LIST(1, 2, 3) + LIST(TEXT("6/29/20"), TEXT("6/30/20")), or
- Using more List arithmetic, as in
LIST(1,2,3) + (LIST("") + LIST("6/29/20", "6/30/20") - LIST("")). This method has the side effect of removing any blank items or duplicates from the second List.
We will accompany this change with some smaller bug fixes:
- DISTANCE() now accepts Refs with referenced type LatLong
- INTERSECT() now accepts two Lists of different types, if the element type of the second List is convertible to the element type of the first
- INDEX() and TOP() now accept types convertible to Number in the second argument