3 Invoices in 30 Days - How ?

[U079310]

This should be be easy but cannot decide how to build. We have a Customer table and an Invoice table. Want to display Customers who have 3 (or more invoices) in the last 30 days.

Our display should be the Customer record plus all Invoice records which are within the last month. How to do this ? We have to process all the Invoice records for a Customer first to determine if they have 3 or more that fall within the most recent 30 days.

Any help certainly appreciated ! (MSSQL database)

 

[bdreed35]

Create a new report.
In the database expert, choose your datasource, and then your Customer adn Invoice tables, and link them appropriatley.
Next, insert a new group on CustomerID or Customer Name, depending on which one you need.
Add your invoice fields to the details section.
Next, right click your InvoiceID and choose Insert, Summary.
In the summary dialog box, your field should be InvoiceID, summary type is Count, and the Location should be group footer 1.
Now, go to Report, Selection Formulas, Record so we can limit records to ones that qualify.
In the editor, add the following logic, keeping in mind that you need to modify it with your exact field:

{Invoice.DateField} in Aged0to30Days //crystal builtin function

Save and Close the editor.

Next, lets limit the records down to customers who have atleast 3 orders.
Go to Report, Selection Formulas, Group.
In the editor, add the logic below:

Count({invoice.invoiceid},{customer.customerid}) >= 3

Save and Close and Test.

~Brian

 

[U079310]

Excellent, that worked perfect. Thanks very much Brian.