I want build a crystal report would show me all the sale hisotry in last 12 month -- including " customer code, customer name, invoice#, order #, invoice date, item code, item desc, qty ordered, qty-shipped"

I notice that there are two set history tables in Adagio
OEW65 order History detail - OEW65 order History header
OEW65 order and invoice details - OEW65 Order and invoice header

Please let me know which one I should us

Thanks

Henry

I linked

OEW65 Order History Header and OEW65 Order History detail through 'prefix. doc.number'

set the record formula = {OEW65 Order History Header.Invoice date} > (CurrentDate -365)

Sent the exported data to a user, and was told that lost of invoice are missing

Please advice

Hello Henry,

You have chosen the correct tables and linking (although this report would be more easily created from SalesAnalysis if you have that module).

If you look at the history file in Adagio OrderEntry, do you see Invoices back more than a year? Can your user give you an example of invoice numbers that should be there? Can you see those invoices in the history?

Users control how long invoies remain in history. Perhaps they have been purged. Check the OrderEntry company profile for the option setting.

Here is the setting in Profile

Purge from Current after days

completed order 35
invoices 93
credit notes 93

Purge from history after days

Orders 1825
invoice 1825
credit note 1825

from the setting, the data should be in the file

By the way, we do have SaleAnalysis , would you please let me know how could we generate the report from SA?

Another question I have is that whether the following formula is correct? ( for filtering the data order than an year )

{OEW65 Order History Header.Invoice date} > (CurrentDate -365)

Hi Henry,

SalesAnalysis could be useful because the 'header' information (such as customer number and salesperson) and 'detail' information (such as item number and qty sold and unit price) is all stored in a single file - the Sales Analysis Transaction file. Also, you don't have to worry about whether anything has been purged in OE. The main purpose of SalesAnalysis is for collecting the sales data for reporting, just like what you are wanting to accomplish.

As long as your filter passes the verification, it appears to be just fine for selecting all invoices with a date later than 365 days before today. Are there any other filters? Perhaps re-adjust the filter to: CurrentDate - Invoice Date < 365

As a temporarly measure (to test out the filter), you may want to add a formula field to your report calculating Current Date minus Invoice Date. You should see the resulting values as all positive (except for future-dated invoices) and equal to the number of days old. Any numbers less that 365 means the invoice is less than a year old and should be selected.

And even though defaults are set for purging historical documents to 1825 days old, these dates can be over-ridden within the Purge History function. You may want to verify in the OE History data (and also SalesAnalysis) that nothing has been purged that is expected to be there.