Adding 2 column values

David_B · 09-23-2019 09:33 AM

I am developing an app to keep track of my mushroom hobby. Often times one can get more than one harvest out of the product. I am keeping the records in Google Docs and would like to have a total of 2 Columns/harvests displayed in a separate Column/Total Yield. I know I can do this within Google Docs with a simple formula but the formula would have to be carried down eventually hundreds of rows. I’ve tried different equations within App Sheet to accomplish this and none seem to work. I also added a Virtual Column thinking it could be done there. What equation would work and where Please?

Bahbus

You simply want the sums of these two different columns?

Create a second table with two columns: Description and Total.
Under Description, type "Sum of " in the first row and "Sum of " in the second row. Description should be of the text type, and Total should be a number type. In AppSheet, make the app formula for Total = SUM(TableName[columnA]) and SUM(TableName[coulmnB]) swapping out TableName and columnA/B for your table and column names.

If you want to sum across the rows. Then simply create a Virtual Column and make the app formula SUM([_THISROW].[columnA], [_THISROW].[columnB]).

Should cover both bases since I couldn’t be sure.

David_B

I likely didn’t explain myself quite right. I want to add 2 values in a row to a third total value.
[Harvest 1] + [Harvest 2] displayed in [Total Yield]
And have this carry down through all rows in the app.

Bahbus

Ok, so solution #2.
Total Yield app formula SUM([_THISROW].[Harvest 1], [_THISROW].[Harvest 2])

David_B

SUM([_THISROW].[Harvest # lbs 1], [_THISROW].[Harvest # lbs 2])
When I paste that into my app it errors displaying

Error in expression ‘[Date & Time].[Harvest # lbs 1]’ : Unable to find column ‘Harvest # lbs 1’

“Date & Time” are not referenced in this column.

Bahbus

What? Where is [Date & Time] coming from?

Steve

_THISROW is an alias for the key column, so Date & Time must be the key column. The Expression Assistant sometimes replaces _THISROW with the actual key column name in its messages.

David_B

Yes, Date & Time are my key Key columns

David_B

No clue… It is listed as a Number column.

David_B

[Harvested # lbs 1] and [Harvested # lbs 2] are both set as Type Number
Yield is also set as Type Number.

Bahbus

I wonder if AppSheet is having an issue with your column names. Other than that, I can’t think of what that error would be happening. I suggest having simple column names with no spaces or symbols when possible.

David_B

Well, one problem was I had teh column names incorrect. (It is Harvested not Harvest)
But when I put in
SUM([_THISROW].[Harvested # lbs 1], [_THISROW].[Harvested # Lbs 2])
It tells me
“SUM function is used incorrectly”

Bahbus

Ah, that’s right. I forgot this isn’t Excel. Sum here needs a list. So this should work now:

SUM(LIST([_THISROW].[Harvested # lbs 1], [_THISROW].[Harvested # Lbs 2]))

Steve

[_THISROW]. is not needed here. The following will work:

SUM(LIST([Harvested # lbs 1], [Harvested # Lbs 2]))

This would also work:

([Harvested # lbs 1] + [Harvested # Lbs 2])

@David_B, I would suggest using a normal (not virtual) column for the total. Be sure to enable the Reset on edit option for that column.

David_B

I did disable the Virtual Column and using the Column Name Yield in the Google Doc.
When I enter either of these into the Auto Compute App Formula it brings up zero’s in my Yield.
SUM(LIST([Harvested # lbs 1], [Harvested # Lbs 2])) or
([Harvested # lbs 1] + [Harvested # Lbs 2])

Steve

Are Harvested # lbs 1, Harvested # Lbs 2, and Yield all of the same column type?

Bahbus

I figured the [_THISROW]s were unneeded. I just use them a lot to help me keep track of things, even if they’re superfluous, when I know they don’t hurt. Usually…

David_B

Yes, all are Type Number

Steve

You’ll need to edit and re-save the row from the app to recompute the Yield value.

David_B

~Sigh~ I edited, saved, Regenerated Structure and re-synced the app and it still shows zeros in the Yield field and nothing in the Yield column.

Steve

In the app itself, tap on a row to get to the detail view, tap on the edit icon to get to the form view, tap Save.

David_B

David_B

Yes, that did what I needed for this function. But, I also have the app set to auto increment new entries to a new ID Number. When I edit, it assigns the edited item to a new ID Number. I’ll have to work on that aspect of the issue tomorrow.
Thanks for the help in this issue.

Steve

For this, use Initial value rather than App formula, and make sure Reset on edit is OFF.

David_B

Thanks Steve,
At first I thought you meant to put the Column adding formula in Initial Value which didn’t work.
Then I realized you meant to add the Auto Increment formula in the ID Number which now works like a charm.
I can’t tell you what it means to me being new to coding and stuff to have this kind of support.

David_B

I’m afraid I am stuck again with one aspect of this section.
I can show the total yield as I wanted but it also shows all of the zero yield containers as well.

I would much rather it only show containers that have a yield number.
I have tried several things but none change the output.

Is it possible to not show zero yield containers?

Steve

To do what you want, you’ll need to use a slice with a row filter condition that excludes rows with a zero yield.

David_B

That did the trick. Thank you AGAIN…