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 11 1,389
11 REPLIES 11

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?

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.

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.

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.

Thanks Natalie

Hi! Iโ€™m coming to this discussion late. I havenโ€™t been very active on the community recently and so I missed this thread.

@natalie helped me understand a problem that a student experienced with some list expressions that is related to the change to the SELECT() expresion. In response, I posted the following question:

One more point that I think may be related to @Daisy_Ramirezโ€™s question about finding the spots that need to be fixed: Since AppSheet is a sophisticated technology company and part of Google, it would seem to me that it ought to be possible for AppSheet to search through the code of apps that will be affected and send out warnings about which parts or which apps with produce errors before the rollout occurs. Perhaps a period of two-weeks from such a warning until the actual rollout occurs would be appropriate.

Also, if AppSheet were to search apps for spots that will produce errors, it could then make a special documentation page with examples of the kinds of configurations that will cause errors using each of the expressions and how to fix them. That would be very nice to have.

Ha! Good one, @Kirk_Masden!

@MultiTech_Visions @Stefan_Quartemont
@Fabian @Jonathon
Making sure youโ€™re posted

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

@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(โ€

This is the only way.