I am new to Glide building my first app. The app is intended to collect water meter readings at water treatment facilities on a daily basis. The app needs to calculate the water flow on a daily basis, which is fetching the previous day meter reading and subtracting it from the current days reading. I built a table and screen to capture the meter reading for these facilities. The table includes the facility ID number (which is unique for each facility), the date and time, the meter reading and a field to calculate the flow. What formula in Glide can I use to fetch the previous day reading in order to calculate the flow?
Is someone gonna add data by hand somewhere?
Do you want the calculation on new added row or on a daily schedule automatically?
You would use a variation of this.
1 Like
@MaximeBaker Yes, there are operators that visit the facilities on a daily basis and they will key the meter readings into the app. The calculation can go on the new row but I will be referencing it at some point to add it to a monthly report.
Thanks
Thanks @Jeff_Hager I will check that out.
1 Like
@Jeff_Hager I followed your steps and I was able to create the index for the previous row. I am at this point:
- Add a query column, use the query from the 2nd step as a base, filter by index equals to this row > previous rowâs index.
- Add a single column to fetch the first âEndBalâ from the query above.
I donât believe I am following the first bullet correctly, can you clarify? I understand using the query from the 2nd step as the base, but donât understand the rest. Also, in the second bullet, how do I fetch from the query above?
Thanks!
So if you create a query column, you can point it at the same table and set the filter in the query to return rows where the Index matches the âthis rowâ prior index (index-1).
Personally for this use case, I would just create a relation instead which will be a simpler and a run a little faster. Just set the relation to match the row index to the calculated prior row index.
Then you can use a lookup column (or single value column) to retrieve the prior row column you want from the relation.
1 Like
@Jeff_Hager awesome! that worked, thanks so much for your help!
1 Like
@Jeff_Hager I thought I had it solved, but as I started adding meter readings to the table for different facilities, the relation I set up failed to work properly.
I have created a simple version of my table below. The issue is that the âIndex Relationâ keeps referring to the first facility. In other words, the âPrior Row Indexâ only matches on the first facility, so it always pulls in the âPrevious Meter Readingsâ from the first Facility.
I have the âIndex Relationâ set to âPrior Row Indexâ Matches the value in: âIndexâ
Do you have any suggestion on how to solve this?
Thanks very much
| Facility ID | Date | Meter Reading | Index | Prior Row Index | Index Relation | Previous Meter Reading |
|---|---|---|---|---|---|---|
| NY3920886 | January 6, 2025 at 2:06 PM | 575,799 | 0 | -1 | ||
| NY3920886 | January 7, 2025 at 2:06 PM | 575,850 | 1 | 0 | NY3920886 | 575,799 |
| NY3920886 | January 8, 2025 at 1:05 PM | 575,900 | 2 | 1 | NY3920886 | 575,850 |
| NY3920886 | January 9, 2025 at 5:24 PM | 600,000 | 3 | 2 | NY3920886 | 575,900 |
| NY3920886 | January 13, 2025 at 1:10 PM | 605,000 | 4 | 3 | NY3920886 | 600,000 |
| NY3920886 | January 13, 2025 at 1:11 PM | 606,000 | 5 | 4 | NY3920886 | 605,000 |
| NY3905695 | January 13, 2025 at 1:12 PM | 800 | 0 | -1 | ||
| NY3905695 | January 13, 2025 at 1:12 PM | 850 | 1 | 0 | NY3920886 | 575,799 |
| NY3905695 | January 13, 2025 at 1:15 PM | 860 | 2 | 1 | NY3920886 | 575,850 |
| NY3920886 | January 13, 2025 at 2:44 PM | 607,000 | 6 | 5 | NY3920886 | 606,000 |
| NY3920886 | January 13, 2025 at 2:45 PM | 610,000 | 7 | 6 | NY3920886 | 607,000 |
| NY3905695 | January 13, 2025 at 2:46 PM | 900 | 3 | 2 | NY3920886 | 575,900 |
| NY3902964 | January 13, 2025 at 8:17 PM | 450 | 0 | -1 | ||
| NY3902964 | January 13, 2025 at 8:17 PM | 500 | 1 | 0 | NY3920886 | 575,799 |
Since you have multiple facilities, you will need to use the Query and Single Value method so you can filter the query by multiple values such as facility AND prior index.
Havenât been following this thread too closely, but I think the below video might help:
2 Likes
@Jeff_Hager finally got it to work, thanks again. Now I am trying to figure out how to prevent multiple meter readings from being entered on a single day for each facility. Only want one reading per day per facility. There doesnât appear to be an easy way to do this. I am using a form screen with text, date and number fields added to it. See screenshot. Any suggestions?
Create a query targetting the readings table, in the facilities, filter by:
- Date is within today.
- Facility ID is this row > facility.
If the query is not empty, meaning a reading has been recorded for today, donât allow them to add another one.
1 Like
@ThinhDinh thanks for the suggestion. I am not sure how to implement this. I am assuming it must be done âon submitâ of the form so I can message the user but there does not seem to be an option to generate a query like you are suggesting. I only see a âQuery JSONâ option.
Am I looking in the right place?
You are better off not allowing the user to open the form if there is already an entry for toda instead of trying to do cleanup afterwards. Only show the form button if the query is empty.
@ThinhDinh is referring to a Query column in the table of the screen that would have the form button.
2 Likes