A group sales coordinator asks for a simple list: every order that still owes money, with the amount due. It sounds like a five-minute query. Then you open the sales order fields in Altru’s query tool and can’t find a clean “balance due” column to drag in.
You’re not missing something obvious. Altru’s query sources show plenty of order detail, but there may not be a ready-made field for the outstanding balance on an order. What’s available varies with how your database is set up and what Blackbaud has added over time. This article covers how to check what you have, how to work out the balance when the field isn’t there, and how to keep the list useful once you’ve built it.
Step one: confirm what your query source actually offers
Before building a workaround, spend ten minutes checking. Open a new ad-hoc query on the sales order source, then use the field search to look for terms like balance, amount due, paid, and payment. Also look inside any related nodes for payments or revenue that hang off the order.
Check three things:
- Is there a direct balance or amount-due field? If yes, you’re done. Add it, filter for values greater than zero, and save the query.
- Is there an order total and a separate amount paid? If both exist, you can work out the balance yourself (more on that below).
- Do payment amounts show up as a related, one-to-many node? That’s common, and it’s the trickiest case, because one order can have several payments.
If you can’t tell what a field represents, or you’re not sure a balance field exists in your version, ask Blackbaud support directly. They can tell you whether the field is available and whether a smart field could cover it in your setup.
When there’s no balance field: calculate it
The balance on an order is the order total minus what has been paid against it. Altru’s ad-hoc query tool doesn’t let you write a formula that subtracts one column from another, so the math has to happen somewhere else. You have a few options.
Option A: Total and paid in one row, subtract in a spreadsheet
If your source gives you an order-level total and an order-level amount paid, build a query with:
- Order or lookup ID, so staff can find the order quickly
- Constituent name and email or phone for follow-up
- Order date and event or program date, so you can see what’s coming up
- Order total
- Amount paid (or total payments)
Export to Excel, add a column that subtracts paid from total, and filter to anything above zero. It works, but someone has to redo the export every time they want a current list.
Option B: Payments as separate rows, summarize before subtracting
If payments only appear as related rows, a direct export will repeat the order once per payment. Adding the order total across those rows will overstate it. Use the query’s summary options to sum payment amounts per order if your source supports it. If not, export and build a pivot table by order ID before you do the subtraction. Test your result against two or three orders you’ve checked by hand, including one with a deposit plus a later payment.
Option C: Check for a smart field
Smart fields can sometimes calculate a value per record that the base query can’t. If your organization already uses smart fields for revenue, check whether one covers payments or amounts due on orders. Smart field options vary, so test one in a sandbox before relying on it, and remember smart fields need to be processed on a schedule to stay current.
Things that throw the number off
Whatever method you use, watch for these before handing the list to anyone:
- Refunds and partial refunds. Decide whether a refunded payment should reduce “amount paid.” Your query may show the original payment and the refund in different places.
- Canceled orders. Filter them out, or they’ll show up as owing money forever.
- Discounts and adjustments. Make sure the total you’re using is the amount actually owed after any adjustments, not the list price.
- Payments posted elsewhere. A check logged as general revenue instead of applied to the order won’t show as paid. That’s a process problem, not a query problem, but the list will expose it.
Turning it into something people use
Once the numbers check out, save the query with a clear name and share it with the people who chase payments. Add a filter on event date so the list shows orders coming up in the next 30 or 60 days, since those are the ones worth a call this week. If the balance math lives in a spreadsheet, keep one master copy rather than letting each coordinator build their own.
The weak spot is freshness. An Excel export is accurate the moment it’s pulled and stale the next morning, after a deposit comes in and someone gets a reminder they didn’t need.
Keeping the balance list current automatically
This is where our Data Bridge can help. It syncs an Altru query to Google Sheets, so the order total and payment columns land in a sheet on their own instead of through manual exports. Add the subtraction formula and a filter once in the sheet, and the balance-due view updates as the query data syncs. Altru remains where orders and payments are recorded; the sheet is just a working view for whoever follows up.
Quick answers
Can I subtract two columns inside an Altru ad-hoc query?
Not as a formula in the standard query designer. Do the math in a spreadsheet, or check whether a smart field gives you the calculated value.
Why does my exported total look doubled?
Probably because payments come through as multiple rows per order. Summarize payments by order ID before comparing them to the order total.
