Issue selecting records

[Newbi1]

I am using windows XP CR 9.0 against a SQL Server 2000 database.

I am pulling invoices and payments from a table which has the following fields: DocType Invno type DueDate amount balance docnumber

On invoices I get valid fields:
DocType Invno type DueDate amount balance docnumber
SO1 76767 11/7/2004 78000 24000 554665

On Payments I get:
DocType Invno type DueDate amount balance docnumber
SO1 76767 UAR -2400 0 54545

I need to show both of these records but cannot find a way to get the records that have a due date and have been paid off along with the associating records removed without losing neccessary information.

I need to show an aging for all invoices that are over 10 days past the aging date. I have tried to limit it by balance, but then I lose all the payments. Same with due date. Is there a way to accomplish this in crystal?

 

[Madawc]

You can create an alias - adding a table for the second time, which means it gets treated as a different table and can have different selection criteria. Does this help?

Another possibility is a subreport, attached to the detailed line, which is a very inefficient way to do it. Subreports make a lot of work for computers, but save time for humans. You decide which is more important for your particular circumstances.


Madawc Williams (East Anglia, UK)

 

[dgillz]

Both transactions are inthe same table correct? If so group by docnumber, and you should be fine. If not please explain more.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Newbi1]

well, I had to group by invoice number. I need to list the invoice info, then the payments. It then drills down to the invoice details so I cannot place the payments in the details section. I tried using the alias, but it is seeming more efficient to use the subreport.

 

[dgillz]

The invoice numbers are the same in the 2 examples you gave us. So why is it you are saying:

On invoices I get valid fields:
DocType Invno type DueDate amount balance docnumber
SO1 76767 11/7/2004 78000 24000 554665

On Payments I get:
DocType Invno type DueDate amount balance docnumber
SO1 76767 UAR -2400 0 54545

This makes me believe that the invoice and payment are not getting grouped together. Are they?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Newbi1]

it was not displaying all of the information and could not limit by balance with a due date greater than 10 days from today. On the records with payments, the balance was always 0. I needed to exclued the records with a 0 balance.

It worked with the subreport cept not lining up with the field headers in main report.

 

[dgillz]

Have you tried a record selection formula:

{Balance}<>0

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Newbi1]

yes, then all of the payment disapear because on the lines with payments balance is always = 0

 

[dgillz]

You just said you need to exclude records with a zero balance right? Or did I miss something?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Newbi1]

Only the invoice records with a 0 balance. Not the payment records which also have a 0 balance in the same table with the same balance field

 

[dgillz]

So you want to net the invoice against the payment and then remove items with a zero balance? Have you grouped by invoice number?

If so, you need a GROUP selection, not a record selection. Click on report, edit selection formula, GROUP and enter a formula:

Sum({balance},{InvoiceNo})<>0

This will eliminate fully paid invoices from the report.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz