dynamic, scalable solution where the app tracks income, expenses, and balance by department and month/year without needing to manually add columns for every new department or time period in Glide?

Ask for Help
1

I got stuck developing the Glide database. The client wanted an app to manage agency finance. The agency has a few departments. The client firstly wanted to see the agency’s monthly and yearly expense income flow and balance in the dashboard screen. Income enters the agency through departments. I have created tables like Transactions to record all income and expense, Departments table with only name, photo, and staff relation column, and Finance Overview table with columns of month names, relation to transactions, rollup columns to calculate monthly and yearly expense, income, and balance, which is an overview of agency transactions. But now, the client wants each department and separately an agency to have its income, expense, and balance records, meaning each has a separate overview. How do I do that?The problem is that if I add more columns in the Finance Overview table to have each department rollup columns, it is going to be lengthy, and if another department is added on the app, the new column in the Finance Overview table with rollup columns should be added manually; this cant happen automatically in Glide. If you use Relation Column for Transactions table and do rollups column to calculate monthly and yearly income, expense flow, and balance in Department table, it is still going to be lengthy and manual because columns for each year’s months should be added manually all the time. What is the solution?

2

Does a structure like this work?

image
image1384×1206 87.3 KB

For scalability:

  • Adding new departments: No manual column addition is necessary. When a new department is created, a new row is added to the Departments table. The dynamic relations will automatically scale without the need for new columns.

  • Adding new months/years: If you’re on a plan that supports Workflows, I would suggest using it to add a new row to the Finance Overview Table, and loop through the Departments table to add rows to the Department Financial Overview table every start of the month.

This is just to keep your current structure as much as possible. Theoretically you can build everything out in the Transactions table, but it’s not as clean.

1 Like