----- Original Message -----
From: "Dale E. Reed Jr." <daler@iea-software.com>
To: <emerald@iea-software.com>
Sent: Monday, May 15, 2000 12:33 PM
Subject: Re: [Emerald] Query for last payment and missing InvoiceID in
Payments table..
> Billy Huddleston wrote:
> >
> > Trying to write a query to bring up the very last payment for a customer
> > (for use in invoice.rpt in Crystal Reports) because the existing method
> > found in the supplied report doesn't work. It searches through the
Payments
> > table looking for a matching InvoiceID and none of the Payments have a
> > InvoiceID, and I really don't understand how that would bring up the
last
> > payment anyways...
>
> Emerald applies the InvoiceID as appropriate. This was addresesed/fixed
> and noted in the changes.txt:
>
> >
> >
> > v2.5.303 - 10/2/99
> >
> >
> > * Emerald was NOT updating the InvoiceID in the payments table.
> > This was causing the payments section of the Invoice to always
> > be blank. You *MUST* run this scrip to update your database,
> > or afer your first billing all payments for each customer will
> > appear on their next bill:
> >
> > <incorrect Query removed>
>
> However, the query was wrong, and fixed in:
>
> > v2.5.327 - 1/27/00
> >
> > * The query for Payment updates for version 2.5.303 should have
> > set to the minimum invoice, not the maximum:
> >
> > Update Payments
> > Set InvoiceID = (Select Min(InvoiceID)
> > From Invoices i
> > Where i.CustomerID = Payments.CustomerID
> > and Payments.Date <= i.Date)
> > Where InvoiceID is NULL
>
> If you run the above query, then your payments will be right. You
> only need to run this once, and Emerald will keep them Updated after
> that (assuming you are running .303 or higher).
>
>
>
> Dale E. Reed Jr. Emerald and RadiusNT/X
> __________________________________________
> IEA Software, Inc. www.iea-software.com
>
> For more information about this list (including removal) go to:
> http://www.iea-software.com/support/maillists/liststart
>
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart