I’m working with two large databases (1,000+ rows each), and I need to check if a client has unpaid invoices. The only reliable way to do this seems to be using a Query column (filtered by the client name) and then a Rollup column to sum unpaid values.
However, when I implement this inside the client rows (so the Query can reference that specific row’s client name), my app starts lagging heavily or even freezes — presumably due to the number of queries running simultaneously.
I understand the suggestion of using a single-row helper table to run one query and avoid the performance issues, but then I lose the ability to see the unpaid amount per client. I would end up with something like this (see image), which is not helpful for individualized tracking:
Is there any workaround that allows for client-specific queries without tanking performance?
Or is there a more scalable way to structure this that still gives me visibility into each client’s balance?
So it sounds like you have your query in the Clients table and not the Invoice table, correct?
What does your query filter look like? A relation is usually quicker, but obviously you are restricted to matching one column in each table. However, you could try using a template column in each table to merge multiple values together and then use those template columns to form your relation. It adds additional computed columns, which can be a negative, but still might be better than running a query.
I have a table called Services, where all my service records live, and a Clients table with one row per client.
When I send an invoice, I store the date in a column called invoice sent date (in the Services table). Clients have 3 days to pay — after that, the invoice is considered due.
To determine this, I use an if-else column that checks if today’s date is after the 3-day limit. If it is, the invoice is marked as due.
What I need: For each row in the Clients table, I want to sum all due invoices related to that client.
Using a Query column to filter services by client + due status works perfectly — but as soon as I apply this for every client row, the performance drops massively.
That’s it — just a per-client total of overdue invoices.
Still not sure how to do it without killing performance…
Ok. Like I mentioned in my previous post, create a template column in the Clients table that concatenates the client ID and whatever value you use for a due status. Make the same template column in the Services table by concatenating the client ID and the result of the IF column. Then create a relation in your Clients table in place of the query. Relations are faster than Queries. Like I said, the downside is the extra template columns but hopefully the overall performance is better than using a Query.
In cases like this, I’m pretty sure relations are always better. Even more so when there’s a date in the process, as dates (even basic columns) take more time to compute.
@Jeff_Hager You’re right — I initially thought relations couldn’t be used for filtering, but your method using a tag template totally works.
What I did:
Created a template column in both the Clients and Services tables that combines the client ID with the due status (from the if-else column).
This means only services that are marked as due get the correct tag.
Then I created a relation from the Clients table to Services using that template — and it effectively works as a filtered query, since the tag only applies to due services.
So now the relation gives me only the due invoices per client, and I can sum them with a Rollup.
It works perfectly for now! No noticeable performance issues yet — fingers crossed it stays that way.
What I need:
For each row in the Clients table, I want to sum all due invoices related to that client.