Hey all, I am trying to do the following in a Glide Big Table.
In my Glide Big Table, I want to be able to query rows within each row of that table, by using a query column to filter those related rows by the time they were created on.
Let me elaborate.
My glide big table is hosting each and every customer’s transaction data like invoices and payments. In the end, it allows me to view the account’s transactional activity and also give me perfect outstanding balances by using a roll up column.
I recently got an idea to display the transactional activity like a simple bank statement, within which I want to show two things:
The effective amount of that transaction
The outstanding balance until that transaction
What I did to achieve this si the following:
For each row in my glide big table (1 row = 1 transaction) I created a multiple relation column. There, for each transaction of client ‘A’, I will have a relation column holding all their other transactions.
Next, I created a query column which targeted the previously made multiple relations column and asked to to query the data with this specific filter: Filter the data such that you query all the rows where the timestamp is ‘on or before’ the specific row timestamp. This will essentially allow me to get a dynamic outstanding balance for every transaction.
Problem: When I use the query column, it is not able to return any results, even though my data has the possibility to be filtered within those parameters.
In the above, I’ve queried by the creation date is before this row->creation date. As the rows were created in chronological order, the count of returned rows starts at zero, and increases by one for each row - which is exactly what I would expect to see.
The only difference is that I used before instead of on or before. If I use on or before then each row returns all related rows - which again is as expected because they were all created on the same day.
So I’m not sure what to suggest. You could perhaps try using before, although honestly I wouldn’t expect that to change anything
Hey, so I tried using only the ‘before’ validator, like you suggested and it worked.
What I understand from this is that the ‘before’ validator can work with date and time. Therefore, it ca detect if something is even 1 second different from the other.
The ‘on or before’ validator only works with dates and not time.
Just to ensure that this is how it works, I am attaching 2 screenshots below. One screenshot shows that Glide is allowing you to compare today’s date or today’s time when using the ‘before’ validator.
Therefore, to compare date and time, you must use the ‘before’ validator.
I just wish Glide was a little more clear about this by mentioning which validator can work with which date type.
My work around:
I used the before validator to query my rows and I was able to query my rows until a certain date and time Then, I created extra helper columns that would allow the adding of that row’s data with the rolled up data from the query column.
The screenshot above shows the helper columns adding their own rows and to the queried amount (sum of the transaction amount from all the rows before that specific row) to finally show the outstanding balance accumulated by the customer after a specific transaction.
This way, I was able to show the outstanding balance for the customer, after the effect of the current transaction.
It’s kind of implicit from the naming. That is, you would normally never use “on” when referring to a time value, and if you did it would be considered poor English. eg. you don’t say “I’ll be there on 3pm”. Instead you say “I’ll be there at 3pm”.
But yes, I agree that it could probably be made clearer. Will see if we can get something added to the docs regarding this.
