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?
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.
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.
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]
Ok, so solution #2.
Total Yield app formula SUM([_THISROW].[Harvest 1], [_THISROW].[Harvest 2])
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.
What? Where is [Date & Time] coming from?
No clue… It is listed as a Number column.
[Harvested # lbs 1] and [Harvested # lbs 2] are both set as Type Number
Yield is also set as Type Number.
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.
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”
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]))
Bahbus:
What? Where is [Date & Time] coming from?
_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.
Yes, Date & Time are my key Key columns
1 Like
Bahbus:
SUM(LIST([_THISROW].[Harvested # lbs 1], [_THISROW].[Harvested # Lbs 2]))
[_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.
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])
Are Harvested # lbs 1, Harvested # Lbs 2, and Yield all of the same column type?
Yes, all are Type Number
You’ll need to edit and re-save the row from the app to recompute the Yield value.
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…
1 Like