Re: [Emerald] Query for last payment and missing InvoiceID in Payments

Dale E. Reed Jr. ( (no email) )
Mon, 15 May 2000 09:33:56 -0700

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