Autopopulating tables with statistics (monthly sums, monthly averages, yearly sums)

[leonsteber]

Hello good people - I need some guidance please.

I'm making a free app, firstly for myself but hopefully will help other farmers here in the Philippines.  I've been using Appsheet for a few months but am at that stage where I've successfully made some working apps, they're slow or not done to best practices, so I'm redoing them with new stuff I've learned. 

I'm a farmer and we have a rainwater harvesting system so that I'm independent of all the problems and power-outs of the local power and water supplies.  I've always recorded rainfall and tank levels but now that we have a seriously dry El Nino I need to make decisions about my water usage based on previous years usage etc.

Graphs like this help me (from a previous version of the app that I'm trying to improve): Quickchart graph of monthly rainfall versus average, min and max. 

My main table "DIARY" includes records for either of two events; "RAIN" (where I record the rainfall in "mm" and also the final tank level) and "PUMP" (where I have pump start and stop times, tank start and finish levels etc). All use the same DATE TIMESTAMP. All in Google Sheets.

Originally I used Google Sheets to make a table/sheet "STATISTICS" which I filled with formulas to do the work. Then I discovered Google Query but my head struggled with the syntax and then I learned it's incompatible with Appsheet?

Then I wanted Appsheet to do all the work so have been using ref_rows().  I'm creating two or three statistics tables. First is "STATISTICS MONTHLY TOTALS"  which shares a common column "YYYYMM" (date shown as 202404) with DIARY. In DIARY, the YYYYMM column is type ref.  Then to get the monthly rainfall it's simply:

sum([Related Diarys By yyyymm][RAIN Gauge mm])

Originally I used this table to also calculate average monthly rainfall, minimum and maximum monthly rainfalls:

average(select(Statistics[Monthly Rain VC],and([Monthly Rain VC]>0,[Month Number]=[_THISROW].[Month Number])))

(Wondering if a new table of STATISTICS MONTHLY AVERAGES using a common column of "MONTH NUMBER" with a ref_row to the first STATISTICS MONTHLY TOTALS would be better?) 

Then for STATISTICS YEARLY the plan was to have a common column of YYYY to pull the yearly rainfall.

Now all this worked fine when I was using my google sheets that already contained STATISTICS formulas, I got all my VCs to replicate the google sheet formulas and give me the right values.  HOWEVER, as I'm going to share the app, I need the STATISTICS sheets to be automatically populated. I don't know if a user is going to add data from ten years ago (so I start my STATISTICS at that date) or is going to add rainfall data not in chronological order. I have used ref_rows to pull the first date from the DIARY and incremented it using _ROWNUMBER but I'm very lost how to get APPSHEET to populate the table.  Is this an ACTION? Now that I've cleared my Google sheets STATISTICS sheets of all data I no longer see any results when i test my expressions. 

I need some guidance (but hopefully not hand holding, haha).  Please be gentle, I'm not a database person and a lot of this is a struggle for me.

Am I going about this in a really convuluted way? Is there a much simpler way to do it?

I'm looking for efficiency and things to be calculated by the App as internet here is bad. I want rainfall graphs and predicted pumping times calculated immediately.

Cheers

Leon

[Derick_Paula · Accepted solution]

Hi Leon, as a fellow farmer and agronomist, your idea is brilliant. However, you should be aware of the limitations of the free AppSheet version if you plan to share this app with other farmers. Regarding your question, you could create a table with columns for every statistic you want to track (e.g., average rainfall this month, sunlight, fertilizers used), a column for the month, and a column for the year. Then, you could set up an automation to run on the last day of each month to populate the relevant data.

You could then create a process using the necessary formulas to collect all the data you want and register it in the table by utilizing the "Add Row to This Table" function.

This approach could potentially solve your question. Additionally, you can further enhance this process by implementing Google Apps Scripts in your sheet.

[leonsteber]

Thank you @Derick_Paula and sorry for my very tardy reply. I got distracted with a bunch of other shiny app things and only got around to making my first automation / action today.  It was for a different task but now that I've done it I can see your idea of monthly updates will be good.

Thanks so much!

Cheers

Leon