Conditional Relations

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.

13 Likes

Yes. Yes. …and yes.

2 Likes