Thanks Geoff for the idea. I got it working by adding an extra SUM() and
retrieving invoices instead of customers. Now my virtual field in the
Invoice model looks like:
'SUM(IF(Invoice.due_date > \''.date('Y-m-d', strtotime('31 days ago')).'\',
(SELECT IF(SUM(amount) IS NOT NULL, Invoice.total_
fly2279,
In order to get the aging for the customers, you need to use the data from
the invoices model. You need to select the invoices, Cake will join in the
customer data because of the belongsTo relationship, then you group on
customer, summing up the various fields that you want.
If I wher
I don't save a column in the invoices table because when an invoice gets
saved there are sometimes discounts that get saved with them, if you try to
calculate the balance based on payments and discounts in the afterSave
method, you can't determine the balance due because the related data hasn't
I was going to suggest replacing the balance_due in the 2nd query with
the 1st complex query, but it looks like that would become pretty
messy.
So, why not just add a balance column to the invoices table and update
it from the model?
On Fri, Oct 26, 2012 at 6:05 PM, fly2279 wrote:
> I am trying
