I have a billing app that has 3 unique data - bill title, due date, and payer. Every time a biller uses the app to bill a payer, these three pieces of information are recorded. The original data, looks a bit like this:
I’m looking for a query that takes this data and only extracts information pertinent to the latest date for each bill along with all the associated Payers affected. My results would ideally look like this:
Keep in mind each bill has monthly billing dates and that payers can be added or removed each billing cycle. The closest I have come is this query: ARRAYFORMULA(VLOOKUP(QUERY({ROW(A2:A),SORT(A2:C)}, “SELECT MAX(Col1) WHERE Col2 IS NOT NULL GROUP BY Col3 LABEL MAX(Col1)‘’”,0),{ROW(A2:A), SORT(A2:C)},{2,3,4},0))
Unfortunately, this query does not enable me to assign more than one bill to a Payer and I have been unable to come up with a query that does. Here are what the results look like for this query:
I would really appreciate any ideas folks in this forum might have. Any help or thoughts would be appreciated!
You’re having trouble with it because you want to return multiple matches from both the payer & bill title columns, but there’s no unique column to do so.
My idea is to concatenate them into one helper column, and do a simple query using that.
The formula: ={"Helper";ARRAYFORMULA(IF(A2:A<>"",CONCAT(B2:B,C2:C),""))}
Your desired result:
The query to achieve it: =QUERY(A2:D,"SELECT MAX(A), B, C GROUP BY B, C, D ORDER BY C LABEL MAX(A)''",-1)
Thank you so much for your suggestion, ThinhDinh. It seems to work. I did have a followup question. I actually have, in my original data set, columns of data all the way to column I that I would like to have included in the query results. Can you please tell me how to adjust your suggested query to include these addition columns in the result? I simply want the query results to include the relevant data from columns E thru I.
I tried changing the query as follows but keep getting the attached error:
=QUERY(A2:I,“SELECT MAX(A), B, C, D, E, F, G, H, I GROUP BY B, C, D ORDER BY C, D, E, F, G, H, I LABEL MAX(A)’’”,-1)
Really appreciate your interest and offer for help.
If you want more columns to be in the query results, just put them all in the GROUP BY clause.
It looks like this: =QUERY(A2:I,“SELECT MAX(A), B, C, D, E, F, G, H, I GROUP BY B, C, D, E, F, G, H, I ORDER BY C, D, E, F, G, H, I LABEL MAX(A)’’”,-1)
I know I’m a little late here and if you have a solution that works, then that’s fine, but I think this maybe can be achieved within Glide.
If the intention is for the user to sign in and simply see’s a list of latest bills, then here’s a simple solution all within Glide. In Billing sheet, create template (helper) column that joins the payer and bill title (much like @ThinhDinh mentioned) Use that template to create a relation column back to the same sheet and same template column. This will give you a relation of all matching payer/title combinations. Next create a Rollup column that gets the latest date for the payer/title combination. Next create an if/then column that compares the rollup column to the date column. If it matches, return ‘true’, else return ‘false’. Now you can filter the list by signed in user and when the if/then column is ‘true’. This will show only the latest bills for each payer/bill title.
Hi ThinhDinh. Unfortunately, I get a parse Error message with that. I am not familiar with Group By queries…do you have to list every column you want included in the results after GROUP BY Aand ORDER BY for this kind of query to work? Also, there is “B” missing after ORDER BY in your query - is that on purpose on your part? For the record, I still get an error whether I put the “B” in there or not. I really feel bad about taking up your time on this but no changes I tried with seem to work. Let me know if you see anything that is obvious. And thank you!
Thank you very much for stepping in and for the suggestion, Jeff. I wanted to be on the same page with you with respect to the fact that my primary customer would be the Biller and not the Billers customer before implementing. I am not intending for any of the Billers customers to use my app - just the Billers. As such, I have a couple of questions:
When you say “If the intention is for the user to sign in and simply see’s a list of latest bills” I assume the “user” you are referring to is the Biller. Is that correct?
Is everything you are suggesting above happening on the same single sheet (Billing Sheet)?
What do you mean by “Use that template to create a relation column back to the same sheet and same template column.” What is the “template” in relation to the Billing Sheet?
What is the best way to create the Rollup Column with the latest dates (eg. query? IF statement?)
When referring to the ‘date’ column - are you referring to the last billing date (as entered by the biller)?
Is there anyway for me to get the user email independent of the user submitting a form? In other words, is there a place I could automatically go to within my sheet where the email of any user that is currently using the app can be found in real time?
Thank you so much for the detailed response, Jeff. If my intention was for this just to be for the Biller - with no Payers having access - what would change?
Nothing really. I don’t know how you intend to display everything, but I’m picturing a simple filter on a list that only looks for ‘true’ in the if/then column. This will display the latest bill for each payer.
Thank you so much for the detailed explanation, Jeff. I am trying to implement but have a question. What do you mean by:
“Then create a relation column. In the relation you would set the template column as the initial value and then select the same sheet and the same template column in that sheet.”
Does this mean create a relation column of the Template column with itself? I don’t understand how “select the same sheet and the same template column in that sheet” can mean anything else. Assuming my interpretation is correct, it does not seem to work.
Yes, the relation would like the template column in that row to the same template column in the same sheet. Yes, it’s relating to itself, but will also pick up any other rows that have the same template value. What does not work? What is the relation showing?
This appears to currently be a bug. I’m guessing you renamed your relation column after you created it. Delete it and recreate it with the proper name, then create the rollup column again.
Glad you were able to verify. Thank you for putting the time you put in duplicating and for all your help.
I am trying to implement your recommendations but am getting stuck on the IF/Then part. What do you mean by:
“Next create an if/then column that compares the rollup column to the date column. If it matches, return ‘true’, else return ‘false’. Now you can filter the list by signed in user and when the if/then column is ‘true’. This will show only the latest bills for each payer/bill title.”
Is the ‘date column’ you are referring to - todays date? And when you say ‘if it matches’ - do you mean ‘if the rollup value equals today’s date’? What were the exact conditions you used for the attached IF/Then box?
Ugh, I keep forgetting that you can’t compare two column values to each other, so I’m going to further complicate this with additional steps. Now that your have your first relation and rollup combo, now we need to create another template to that will join the original date/payer/title columns together. Then create yet another template that will join the rollup/payer/title together. Create a relation column that links the rollup/payer/title template to the date/payer/title template. Then you can create an if/then column checking if the relation is empty or not. Or you can skip the if/then column and add a filter on your list that will check if the relation is empty or not.
