I am looking for help in coming up with the best approach (in terms of Google Sheet design) to enable me to utilize Glide to create a bill payment and support app.
My customers (ie. app users) are the Billers. They are targeted to use my app to send bills to their Payers. Payers (ie. those who owe money to my customers) do not have access to my app.
Billers are able to issue bills to their Payers (separately via email).
- Each Customer can create and issue multiple Bills (ie. Bill types)
- Each Bill can have multiple Payers
- Each Payer can be on multiple Bills
- Bills are issued monthly
I would like a design that makes it easy for Billers to:
- add (current or new) Payers to Bills
- add or remove Bills from their repertoire of bills
How should I design my Google sheet (e.g. should I have a tab for each Bill? should I have a tab where all Bills are listed? Should I list all the Payers under a single tab?)?
Any thoughts or examples of similarly designed apps would be appreciated.
What tabs do you want to show to the end user?
1 Like
I would like the Customer (end user) to be able to view their Payers and their Bills. I would also like them to be able to add Payers to any Bill or assign Bills to any Payer. I hope that answered your question @ThinhDinh.
My biggest challenge is in how to design for the fact that payers can be on multiple bills. In Glides Multiple Relations example (of employees and locations), they talk about employees and locations and of how multiple employees can be assigned to the same location and of how Multiple Relations can handle that. In my case, I have the equivalent of multiple employees having the ability to be assigned to multiple locations. How do you design for that?
https://docs.glideapps.com/all/guides/quick-starts/intermediate-techniques/match-multiple-relations
I would personally design this with 3 sheets. One for Bills and one for Payers, and one that lists the bills and each associated payer. The Bills sheet would contain information about the bill. The Payers sheet would contain information about the payer. The Bill/Payer sheet would contain the link between each bill (ID or Name) in one column and payer (ID or name in another column. You could display 2 tabs, One for Bills and one for Payers. On the bills sheet, you can create a relation to link the bill id to the bill id in the Bill/Payer sheet. Then you would create a lookup of Payer ID’s. Then you can use that lookup for a relation to the payers sheet. That way you can display each payer in an inline list under each bill. The same process would happen to show bills under each payer. On either tab, you can add a form button to add payers to a bill, or bills to a payer. If you ever need to delete that link, then you would probably need to use the first relation on either sheet to show a list of all the associated Bill/Payer items and link into the details so you can delete the link.
Another option is to scrap the lookup and second relation and just use the relation to show a list of the Bill/Payer links. Then you can drill down into the details of each bill/payer and in that third sheet, create the necessary relations columns to the associated bill and the associated payer to use for inline lists of each bill and each payer, or add lookups to display certain details from each bill or payer. From there you can delete each link if necessary.
Thank you, @Jeff_Hager. I have implemented using 3 sheets as you suggested - Bills, Payers and Bill/Payers. This seems to be working fine - so thank you!
Where I am having trouble (and where I have been wracking my brain for a week) is when I try to incorporate an additional critical feature related to bill updating and reminder scheduling:
- I would also like to implement a mechanism whereby each Biller automatically gets a notification at the beginning of each month asking them to view and confirm the accuracy of this months $X billing amounts and DateY payment due dates for each bill and each Payer. This is important to do because I would like to send payment reminders to all the Payers associated with the bill as to what their amount due is and when it is due.
Note: $X billing amounts are not necessarily fixed and can vary from Payer to Payer. Due dates are monthly.
- In which sheet do you think I should include the info related to bill due dates and $ Amounts due?
- What additional relationships do you think I need to create?
-
With respect to the DateY (payment due date), I would like to give the Billers the option to choose from a list of dates (eg. they might want to enroll Payers for a day this month or next month etc) at the time they are enrolling a Payer to a Bill. The choice of dates options I come up with is, however, is dependent on the Bill Title they wish to add the Payer to (eg. a bill might require payment on the 15th so I wish to give them a choice of the 15th of this month or the 15th or next month etc.) and needs to be calculated in real-time. In other words, I would like to present them with their choice of dates right after they select the Bill Title on the Form. In the current design, the Bill Title they choose won’t be known until the Form is submitted. I would ideally like the Form submission to include their choice of date. Any suggestions on what kind of design could accommodate this?
-
When you say “If you ever need to delete the link…” in your response from 9 days ago what link are you referring to? Why do you think I might need to delete it?
-
When you said “The Bill/Payer sheet would contain the link between each bill (ID or Name) in one column and payer (ID or name in another column.” do you mean I should create a separate column that concatenates the Bill ID and the Payer ID (in order to identify each bill uniquely by the bill/payer combination)? If so, it is not clear what I should be doing with this information. Can you please elaborate?
Thanks in advance for any insights you or any others might have!
-
I don’t have any good advice regarding notifications. There are several posts in for forum regarding different types of notifications, whether it’s emails, text, whatsapp, etc. There are also several methods for implementation, like scripting, third party integration like Zapier, etc. Do a search to find out some more info on that.
As far as structure, maybe you could use the Bill/Payer sheet or create another sheet that is similar and contains the biller, payer, and specific bill details.
-
You could maybe create a sheet that will hold all of your date choices and use formulas in the sheet to calculate the date that pertains to that value. So one column could have of value of ‘15th of this Month’ and the next column would calculate the June 15th. In the choice component you can selet 15th of this Month, but write the actual date to the sheet. You might need to establish several choice components with different billing day values and only show the respective choice component via visibility depending on which bill title was selected. That or you would be better on making the Bill title selection on a detail view screen, let it write to a user specific column in the sheet, then build a relation to the choice sheet where you would need to also add a bill title column. Then you can use the relation for a second choice component to show the date choices that relate to that bill title. Once both choices are selected, then display a form button to continue issuing the rest of the bill with any additional details you need. Look at Dynamic List Filter and Booking in https://concepts.glideapp.io/ to get a better idea of what I’m talking about.
-
The link I was referring to was if you ever need to remove a payer from a bill title. A form can create the link, but unless you are viewing it somewhere in your app, there is no way to delete that association unless you do it manually within the sheet.
-
I was only saying to have the bill id and the payer id in separate columns so you can create relations from the bill sheet to the bill/payer sheet and the payer sheet to the bill/payer sheet. What you do beyond that is up to you.
These are all pretty broad questions, so without knowing specific details about your app, how it looks, or how it’s supposed to function, it’s hard to give specific answers. Hopefully you can make sense of what I said above.