Help with a google sheet query for a billing app

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:
Screen Shot 2020-05-13 at 3.47.09 PM

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:
Screen Shot 2020-05-13 at 3.49.13 PM

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:
Screen Shot 2020-05-13 at 3.50.27 PM

I would really appreciate any ideas folks in this forum might have. Any help or thoughts would be appreciated!

Hi, here’s my 2 cents on your problem.

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.

image

The formula: ={"Helper";ARRAYFORMULA(IF(A2:A<>"",CONCAT(B2:B,C2:C),""))}

Your desired result:

image

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)

Tell me if I can help more.

2 Likes

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)Screen Shot 2020-05-14 at 1.28.56 PM
Really appreciate your interest and offer for help.

Hi Fassil,

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)

Tell me if it works!

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.

3 Likes

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!Screen Shot 2020-05-14 at 5.30.52 PM

My mistake. If you have columns from A to I, then setup a helper column in J. The query to achieve your desired result would be:

=QUERY(A2:J,"SELECT MAX(A), B, C, D, E, F, G, H, I GROUP BY B, C, D, E, F, G, H, I, J ORDER BY C, D, E, F, G, H, I LABEL MAX(A)''",-1)

If you have time, try Jeff’s method as well.

Here’s a test sheet: https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1244323104

1 Like

I tried your method today. Works fine. Learned something new.

1 Like

Nice to hear. Hit me up if any problem occurs.

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:

  1. 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?
  2. Is everything you are suggesting above happening on the same single sheet (Billing Sheet)?
  3. 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?
  4. What is the best way to create the Rollup Column with the latest dates (eg. query? IF statement?)
  5. When referring to the ‘date’ column - are you referring to the last billing date (as entered by the biller)?
  6. 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?

Thanks in advance for your insight!

Just a note, non of the following involves an additional changes within the google sheet. This is all done within the glide data editor.

  1. I was referring to the Payer, so everything I said before was assuming it was the payer signing in and seeing their own data.
  2. Yes, everything would be in the same sheet as far as the additional columns, but in the Glide data editor. Not the actual spreadsheet.
  3. Just like @ThinhDinh mentioned with a helper column that joins to column values together into one, you would create a template column in the Glide data editor to join the payer and bill title together in one column. 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. So essentially you are creating a relation for that payer and bill, but it will relate to all similar payer/bill combinations in the same sheet.
    https://docs.glideapps.com/all/reference/data-editor/computed-columns/template-column
    https://docs.glideapps.com/all/reference/data-editor/computed-columns/relation-column
  4. The rollup column would be created in the data editor and use the relation you created to find the latest bill date of the related payer/bill combination.
    https://docs.glideapps.com/all/reference/data-editor/computed-columns/rollup
    image
  5. Yes, I’m referring to the date entered by the biller.
  6. Sure, you could create a choice component that uses in the form that is built from the list of users as long as you have a sheet that contains those users.
    https://docs.glideapps.com/all/reference/components/pickers/choice
3 Likes

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?

Here are what the columns look like. Does not look like the Rollup is picking up the latest dates.

Not sure if it helps but when I try to do the Rollup, the “Done” button is grayed out.

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.