Changes to SUM(), AVERAGE(), ABS(), and List arithmetic

Hello everyone!

In the coming weeks, we will make these changes:

  1. SUM(): If given a List of Refs, returns a value with the referenced type, instead of a Ref
  2. AVERAGE(): If given a List of Refs with a numeric referenced type, returns a Decimal, instead of a Ref
  3. ABS(): If given a Ref with a numeric referenced type, returns a value with the referenced type, instead of a Ref
  4. 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:

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

  1. DISTANCE() now accepts Refs with referenced type LatLong
  2. 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
  3. INDEX() and TOP() now accept types convertible to Number in the second argument
7 Likes

Hi Natalie, just want to confirm. Are you saying we’ll need to update all of our apps that currently use these formulas since the formula now functions differently?

5 Likes

@MultiTech_Visions @Stefan_Quartemont
@Fabian @Jonathon
Making sure you’re posted :blush:

3 Likes

Thanks @Grant_Stead. I appreciate the more strongly typed expressions. It’s common in list expressions to get types confused.

2 Likes

Hi @Daisy_Ramirez, thanks for asking. Whether you need to change your apps depends on how your apps use these functions. If your app needs SUM(), AVERAGE(), or ABS() to return a Ref, then yes. If your app uses List arithmetic on Lists of incompatible types, then yes. If your app doesn’t do either of these things, then you don’t need to do anything.

1 Like

Hi Natalie, is there a fast way to find all locations where SUM() is used across all apps and tables? Maybe I’m missing something but this will take me quite a bit of time as I use SUM frequently. It seems odd to change the functionality of an existing expression vs. creating a new one due to the impact on existing solutions but again - not sure if I’m missing something.

Will standby, thanks.

1 Like

Good questions! As far as I know, there isn’t a shortcut for searching app formulas across all apps and tables. If others know of one, feel free to share it.

I will explain that I believe the case where an existing app needs SUM() (or AVERAGE() or ABS()) to return a Ref is rare, because the value of a Ref is a key of a table. If an expression was finding the sum of table keys (Refs), the output of the expression could be a value that is not a valid Ref. Instead of showing this invalid Ref, the sum should just be the numeric value by default.

The reasoning that we preferred to change existing behavior as opposed to creating new functions in this case was because (1) we anticipated the impact on existing apps to be low and (2) we don’t want to continue to allow as many expressions that will create invalid/unexpected results (e.g., an invalid Ref).

If I’m wrong in your case and you know that you are doing arithmetic with table keys, then yes, I can understand that you will need to spend some time using ctrl+f or some other method to find and update your expressions. I accept this feedback and will investigate ways to prevent app creators to have to spend this time in the future.

1 Like

@natalie thank you for this update.
I hope you will also send also an E-Mail to all AppSheet customers. Not all are reading those posts in the community. Otherwise we will again get a bunch of questions like we had when GetTableFileUrl changed and some Apps stopped working.

@Daisy_Ramirez one way is to download the App documentation and search for “SUM(”

6 Likes

Thanks Natalie

This is the only way.

2 Likes