I have a base of orders that have a customer_id a total cost and a paid column. I would like to know how I can do a formula that would find a customer_id and total all of their outstanding costs. So basically I need to sum total cost for a unique customer_id that has 0 in the paid column.
Anyone has an idea how to do this?
+21Another option is to summarize the data in another table. This would be especially useful if your customer base is particularly large, and scrolling through grouped transactions may prove tedious.
If you don’t already have a table for your customers, add one. The primary field would be the customer ID, with other fields storing names, addresses, phone numbers, etc.
In your main table of orders, make the {Customer ID} field a link to the [Customers] table. Assuming all the IDs match, it should link up to what’s there, adding a field to [Customers] with links to the [Orders] table.
Back in [Customers], add a couple rollup fields matching the names of your {Total Cost} and {Paid} fields in the [Orders] table. Each would use SUM(values) to rollup the info for the linked orders, with one summarizing the {Total Cost} field, the other summarizing the {Paid} field. To get the difference, add a formula field named something like {Balance Due} to [Customers], containing the formula: {Total Cost} - {Paid}
Now you can see the balance due for all customers at a glance, without the need to scroll through grouped transactions.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
