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. [image] Diffferent formula from Row 2 onwards Ask for Help Make sure you record the user’s ID/email. Use a query column, filter by user’s ID/email equals to this row > user’s ID/email. Add a rowID column to your table. Add a lookup column on top of the query resulr, pointing to that rowID column, so you get an array of IDs for the same user. Add a find element index column, use the lookup result as the base, and look for the current row’s rowID. You will get a 0-based list of index for each user. Add a math column, with the formula being: I-1, with I b…

@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.

@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.

@Jeff_Hager awesome! that worked, thanks so much for your help!

@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

[image] bmagnotta: The issue is that the “Index Relation” keeps referring to the first facility. 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: https://www.loom.com/share/042be3be13004355b0dedec1be67b400

Fetching data from a previous row using date

Ask for Help

Jeff_Hager January 9, 2025, 3:06am 3

You would use a variation of this.

Topic		Replies	Views
Calculating between two rows // Water Metering App Ask for Help	11	688	August 10, 2022
User based previous date Ask for Help	6	363	November 11, 2021
Calcul entre deux rows Ask for Help	5	241	June 21, 2024
Conditional Relations Feature Requests	22	3679	May 4, 2023
Pulling Data into one row Ask for Help	2	398	January 14, 2022

Fetching data from a previous row using date

Related topics