I have two sheets one for EQUIPMENT and one for METER READINGS. I’m trying to create a column in EQUIPMENT that looks for the LAST meter reading in the METER READINGS sheet. My head hurts!
How do I achieve this? The meter readings is a number field and on the METER READINGS sheet I have timestamps for every row. I just can’t wrap my head around how to retrieve the most recent row for every piece of equipment in the EQUIPMENT sheet.
Thank you!
Maybe add a relation from equipment to meter readings (multi) based on serial number or whatever your “key” is, then a rollup on that relation summarising the timestamp by “latest”?
Exactly. You’ll need to do a mult relation to get all time stamps. Then a Rollup to get the max timestamp of that relation. Then join the meter number with the max timestamp using a template column. Do the same template in the other sheet joining the meter number and the timestamp. Then do ANOTHER single relation between the two templates.
In line with what @V88 suggested…
In Readings sheet:
In Equipment sheet:
The above methods are best to be assured that you get the latest meter reading based on the timestamp, but if you can guaranteed that the order of the records in the sheet are always in earliest to latest, then a relation like what’s mentioned above, in combination with a single value column would get you the Last matching meter reading from the relation. A Rollup with Max would also work against the relation. But checking the latest timestamp first would guarantee that you would be get the latest meter reading regardless of the order of the meter reading sheet.
A relation and a rollup for max(reading) should do the trick?
Hi,
If I were you, I’d create a table (new tab) in GS using Query() function grouped by Equipment and sorted by Timestamp (Des).
In this way, you will load few data on APP and the GS will do the hard work counting and sorting your data automatically.
Saludos!
@gvalero I get the impression from the community, and this has been reinforced by the Glide team on occasion, that bringing the heavy-lifting/computations into Glide is preferable to keeping it in GS (tons of formulas, scripts, etc.). Has it been your experience that the opposite is true? Making GS do as much work as possible is better from a performance perspective? Or, is this simply an “it depends” sort of thing?
Good question @Rasha, I have seen this kind of discussion before and I put my opinion here:
1- if you need results in real-time such as a quotation, a math calculation, etc I could load the whole data because it will be must within Glide
2- If you need only a static value which does not change very fast, it is a stats and/or is part of a huge data, I’d rather to have it in GS, create my key values there and keep my app light by avoiding unnecessary data.
E.g: the data associated to a small town with 20.000 persons to know its average age, male/female stats, oldest person name, etc.
Why load 20k rows to APP when all these values can be done and save in GS using simple formulas (or maybe, some Query() function)?
Doing this, you could load few data (1-2 rows with 3-5 columns) and everything will be easier to maintain and faster to load.
It’s not necessary to use always brute-force and let Glide or any software makes the heavy job. GS is a powerful tool and you need to know when and how to use it, Glide’s Data Editor can’t defeat it most cases.
Feliz noche Rasha.
Thanks for sharing your reasoning! I typically mind the “volume” of data that I have running through Glide, keeping that to a minimum. Right or wrong, what I’ve done differently over the past 2-3 months is move more of the calculations to GE when possible. But nothing I can’t afford to lose, i.e., hard data.
Overall, it’s tricky to know what impacts performance the most. At times it seems arbitrary. In some of my apps, the number of computed columns is worrisome despite app data itself being on the low side.
I’m not a fan of scripts. It’s enough that I have to contend with being at the mercy of single formula breaking an entire app or two. So I try to optimize where I can.
I’ve done the same as you @Rasha. I’ve been slowly migrating more and more calculated columns into Glide, while keeping critical data in the Google sheet. I’ve done what I feel are pretty crazy things as far as relations, calculations, and relations based on those calculations between multiple sheets. I’ve been pretty impressed with how glide handles it. I used to have a dynamically built sheet that would take several minutes to run queries and calculations in google sheets whenever a single row was added to another sheet by the app. This caused a whole host of issues with sync issues and lost data when there was rapid data entry coming in from the app. The sheet is still dynamically built with a query, but I’ve since managed to narrow it down to one calculated column left in that glide can’t quite do what I want yet. This sheet acts mostly as a container that holds rowid’s and emails. The rest of the content is within glide itself. It’s made a huge improvement in speed, since Glide handles all the heavy work very efficiently and quickly. I still have more I want to do, but it’s coming along nicely and I have very few app issue now.
Agreed. I have also migrated pretty much all of my calcs into Glide. Significantly quicker. It’s now my default plan, only falling back to Google Sheet calcs when there is absolutely no other way (often date related).
Thanks for sharing thoughts on this one. I hear you on “crazy things” and I’m finding it difficult to trace my logic as more time goes by. I think of reworking apps as moving back and forth between #150 - #400 grit sandpaper. I feel I’m always on the brink of falling back to #80 because of the daisy chaining and the prospect of accidentally deleting something that seems unnecessary in the moment. I digress.
