Using a virtual column to get the sum of a change column

As I’ve indicated in the title, I’d like to use a virtual column to get the sum of a change column in another table.

sum(table_name[change_column_name])

Was deemed valid at first but when I synced I got the following kind of error:


That made me think that what I want to do might be impossible. After some searching, however, I found the following:

If I’m reading this correctly, this indicates that I should be able to use a virtual column to get the sum of a change column. Unfortunately, however, I haven’t been able to get it to work. I still get the same error, even when I use a valid SUM(SELECT expression.

I wonder if anyone has any idea about what might be going wrong.

By the way, the fact that sum(table_name[change_column_name]) was deemed to be a valid expression but then caused an error that broke my app made me think that counter columns may have some special limitations. However, the documentation on this column type doesn’t seem to mention any such limitations.

Thanks!

0 1 1,425
1 REPLY 1

I couldn’t stop thinking about this and, since posting, I’ve come up with a work around. First, I made a virtual column that converts the change column into a numeric value.

number([Name of change counter column])

Then, in another virtual column in another table I used a simple

sum(table_name[Name of new virtual column])

to calculate the total. This works exactly as I had hoped. I still wonder, though, if there isn’t a way to do this without making a second virtual table. Also, I think it would be nice if AppSheet could find a way to allow change columns to be summed more easily. I can understand that they are not, technically speaking, numeric columns but I would think that AppSheet should be able to infer that they can be summed like numeric columns without the user having to find a way to trick the platform into summing them.

P.S. Referring to @Steve Coile’s explanation about Sum() led me to the idea of converting the change column to a number column via a virtual column.

Top Labels in this Space