Most people still don't understand this, and assume it doesn't work.
The InvoiceID on thre Payments table does NOT note which Invoice the
payment is against. It notes what invoice to DISPLAY the payment as
been "received" on. For example, when you make a payment, the InvoiceID
will be NULL. During the next invoicing for that customer, Emerald will
mark that Payment with the InvoiceID of the newly created invoice.
Then when you print/mail the invoice, the payment will show up as
received
on that invoice.
> ----- 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
--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