I’m sorry, I’ve been here recently and I didn’t realize you used the same methodology. Thanks because at least for me it is the confirmation that at the moment this is the best solution, while I am waiting like you for a built-in conditions
1 Like
Thanks for the input. Yes, I have also used your method with a template, but my situation is similar but different. For me, I need the email to match, but then I need to find a range of dates that are less than the date of the current row. This would allow me to perform a Rollup Sum of all of the previous values from any matching row prior to the current date of the row.
I have a billing system for lessons that are taught to students and for a current bill, I want to be able to look at prior bills, get a total amount they were billed previously and also get the total amount that was paid on those previous bills. Then subtract the difference and display on the current bill if they have a previous unpaid balance, or if they overpaid their previous bills. This would be used to determine, on the current bill, how much is due, by combining the current billing amount and the previous unpaid/overpaid amount.
Here’s a simple mock up of what I’m talking about. So, say I have 2 emails with multiple rows. With just the email column for the relation, I can find all the matching rows. A rollup of the the Rate column would give me a total of 25, but that’s not what I want. I could create a template of email and date, but that would only give me one match with a rollup total of 5, which is not what I want. The ‘Total Previous’ column shows what I would like to do within Glide with a conditional relation. I need a total rollup sum value of all rates for a particular email, but only the ones prior to the current row date. Because this happens dynamically for each row, and relates to the same set of data, using the email-TRUE method wouldn’t work because I can’t dynamically set TRUE for a row based on the row I’m currently viewing.
Currently I can filter a list to only show certain records prior to the current row date, but I have no way to perform a Rollup conditionally. At this time I can only accomplish what I want with a query in the sheet. Unfortunately a query doesn’t work with an arrayformula, so I have populated a couple thousand rows with the same query formula to allow for growth of new students and billing periods. This takes a lot of time for the google sheet to process and causes sync issues with my app, so that’s why I have offloaded this query processing into a completely separate google spreadsheet and use ImportRange to move the certain sheets and values back and forth between google spreadsheets. It used to be much worse because I had several columns that would calculate this way and all of it in one google sheet and it would take several minutes for the sheet to recalculate every time data was changed in the sheet. This led to the sync issues and lost data due to glide trying to push data to the google sheet while it was in the middle of these massive calculations. Offloading to a separate google spreadsheet eliminated the sync issues and I was able to update all but one column to use glide functions instead. If I could get conditional relations, I could move this last column to entirely into glide, eliminate the separate google spreadsheet, and possibly turn off Background Refresh, because it wouldn’t be necessary anymore. This one piece is holding me up from majorly streamlining many parts of my app. I imagine a relation like a SQL statement, but all I need is the be able to add a WHERE clause like you can with a Query.
1 Like
Thanks @Jeff_Hager for the detailed explanation of this very interesting scenario. Certainly the method I have expounded, and which was clear to most of you here, does not cover your case and I am now well aware of that.
1 Like
Here’s a similar feature request.
2 Likes
Need this Need this Need this.
Here’s another scenario where this feature is necessary!
3 Likes
Yep, that’s exactly my problem too. I need to do a rollup sum from all rows prior to the date of the current row, so each row is going to have a different number of related rows. Since we have a range of dates to check against, and that range can vary indefinitely, you can’t do an exact match relation.
Similar to my other situation where I want the max date from a related sheet, for a particular user, so I can use that date to build another relation to get only the rows that match that latest date. But, I first have to create a full relation to all 3000 rows, which appears to have to first process all computed columns on all 3000 of those related rows, just I can get that latest date, so I can turn around and create another relation of the 10bor so rows I need. It’s a lot of unnecessary processing overhead. This is all in an attempt to help speed up some slow areas of the app, but these attempts ultimately slow things down more. I still have one relation I can’t build. Whenever I try to create the relation column, it locks up and crashes my browser tab just trying to process the data. I get why it happens (3000 rows each relating to the same 3000 rows), and there are better ways for me to set up what I need, but I’m just trying different things to make the app more efficient and striking out so far.
I feel like conditional relations/rollups would make some of these issues more manageable.
5 Likes
Giving this a bump, so hopefully it will attract a few votes 
4 Likes
Would this enable rollups based on the current user email? If so, +1 from me
You can rollup against a relation for a specific user. If all you are comparing is a static known value, like emails, then a relation and rollup should work for you.
My main problem is that I need to filter to a range of dates prior to a date on each individual row before I can perform a rollup, so I have no way to create a relation to an infinite number of dates. Thus, no way to create a rollup.
My other situation is that I want to create a relation that finds only the latest date for a specific user. I can do the relation based on user, as it’s a known value, but I don’t have a way to have the relation only return the rows for that user with the latest date. I have to first return all rows for that user, perform a rollup to get the latest date, create a template of user and latest date, then use a second relation to just get those latest records. I think that first relation is creating a lot of overhead and slowness due to the number of rows I have to pull back just to get a date.
2 Likes
This would also allow for inverse relations, correct? eg. Rel _ user where user is not signed in user?
3 Likes
Yep - would dearly love to see this one!
It’s such a pain to do the workarounds…
2 Likes
Yeah, the way I picture it would allow for inverse as well, just like how we can filter lists based on screen values.
2 Likes
Bumped
1 Like
Stumbled across this whilst searching for an answer to my problem. Would be extremely useful.
2 Likes
Me too!
1 Like
I used a combination of 8 columns on 2 sheets to get this functionality working.
Having a simple conditional rollup function like SUMIF, or just plain conditions on relations would simplify this process significantly.
1 Like
Quick thought - if you have voted on a feature and it comes true pin it at the top of their community feed (only for the users who voted for it) until it has been seen/unpinned. I haven’t been as active perusing the community and only got lucky to see this ‘wish come true’.
Congrats - awesome new feature - and looking forward to testing.
2 Likes
Now available as the Query column (you can enable this as a Preview feature on the upper-right side of your dashboard).
8 Likes
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.