I know myself and others have made this case before, but I’d like to mention it again. I think it would be hugely benificial to be able to apply IF conditions within Relation settings. My use case for this is to get the unpaid dollar amount from unpaid invoices.
I have invoices for students that, let’s say, are billed monthly. The user creates the billing period for a particular month, and any lessons that have occured within that billing period are automatically included in that invoice. In the back end of my sheet, I currently obtain the beggining date of the billing period and through a QUERY, I get the amount of all payments made for the student prior to the current billing period beginning date. The problem is that I have offloaded these query statement (2000 total), to a completely separate spreadsheet to offload processing time from the main Glide sheet, and prevent any sync issues when processing the queries can take up to a couple of minutes. This process works fine, but I would love to make it all inclusive within Glide.
If I could create a relation column in combination with IF statements that use the new date compare, then I could create the relation based on Student/Coach ID’s for each invoice, but only include related payment data that is prior to the beginning date of the invoice billing period. This would allow me to create a Rollup column with the total amount of payments prior to the billing period invoice I am viewing. Sure, I can filter the relation data, but there is no way to create rollups, lookups, etc. that only relate to a subset of data in the relation. And in my case, since it’s a date compare of ‘is Before’, there is no way for me to structure a relation that includes all prior dates, unless I create some crazy formulas in the sheet with massive array columns.
Basically in SQL terms, this is what I’m picturing:
SELECT *
FROM Invoices AS I
INNER JOIN Payments AS P ON I.StudentCoach = P.StudentCoach
WHERE P.PaymentDate < I.BillingStartDate
Where the WHERE clause would be the IF or FILTER part of the relation. Potentially allowing for multiple AND/OR conditions.
I think this would cover a lot of scenarios, but I would also possibly accept applying the IF condtions or filtering to Rollup or Lookup columns instead of the relation.
Hi @Jeff_Hager
I solved this problem without Query in the sheet in this way:
I create a unique string-key in Table1 using a template column; for example the key could be ‘email@email.comTRUE’
in Table2 I create a column that returns TRUE when the condition satisfied
in Table2 I create a template column by joining email & condition (for example now here I can have ‘email@email.comTRUE’ or ‘email@email.comFALSE’)
By setting the relations between Table1 and Table2 based on the respectve template columns where the keys have been built, I get the subset of the rows that satisfy the relation.
Of course, rollups work too.
I don’t know if there can be any limitations or problems using template columns in relation column, anyway for now it seems that everything works fine in my APP.
Yes, the solution you posted is exactly what we do in the majority of our apps. As you mentions, it requires at least two additional columns to make the relation happen. Jeff, was referring to a relation column with built-in conditions to avoid creating these extra columns.
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
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.
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.
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.
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.
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.
