Filtered list of records but need to include others...

AppSheet AppSheet Q&A
1

In a water meter reading AppSheet, I’ve created a dashboard to have the user enter a month, and the slice will show the water meter readings for that month. this will then be exported via CSV and on to the billing software.

However, I just realized that a few of the meters, in remote locations, will not be able to be read each month, so if the meter reading is blank , I need to calculate the running average (over 2, 3 or 4 months), to enter an estimated value for the month, and include these in the CSV file export.

I’m not sure where in the app I’m suppose to do this running average.

My dashboard and slice look like this:

Screenshot 2024-06-16 205411.jpg
Screenshot 2024-06-16 205411.jpg434×165 8.91 KB

But, I’ll need to capture the meters whose values (Lectura) were calculated, and not entered manually. I’m not sure when and where to do this.

2

I would calculate it in the CSV template.

1 Like
3

I was thinking the same at first, but the CSV template will only have the current month’s data in it, not the previous months to calculate the average. Plus, the CSV template I have now will only include the meters that have data entered into the “Lectura” field. I would need to bring all of the meters into it.

4

A template can access any data from the entire app.

5

I need the estimated readings/values to be in the database. I just realized if I calculate the estimated readings/value in the CSV, it won’t be in the database.

So, I have to have a calculation on the last day of the month for a meter that doesn’t have a reading. This has to be done in the database.

6

Did you work out a solution to this yet? It seems like you could:

  1. Create a virtual column [Calculated Average] to calculate the running average from previous months for each meter read
  2. Add a yes/no column [Added by Bot] to indicate if the value was added manually or by a running average calculation
  3. Add an bot to run on the last day of the month that writes the value from [Calculated Average] to the [Lectura] column and marks the [Added by Bot] as yes for all [Lectura] that don’t already have an value
7

Those are great options.

What I ended up doing is create a yes/no field for “Not read”. It’s default is Yes. When the person reads the meter, he enters the meter reading and switches the Yes/No to “Read”. When he gets to a home/meter that he cannot read, he leaves the yes/no as “Not read”, and saves the form entry.

I’ve created an if statement to look at the Read/Not Read flag, and calculate an estimate based on that.

1 Like
8

Nice! Your project is cool so I am interested to see how it develops. Thanks for sharing!