3 replies to this topic

[ypf1258]

I am trying to create my first report

what i am trying to get is a report of customers that purchased in 2004 and did not return in 2005
i have created a query as following

Cust (table) >odr_date is greater then{07/01/2002}
and Items(table) IT_DATE is less then {07/01/2004}

what should of happened is that it should pull up all the orders that where placed after Jul. 01 2002 till jul.2004
what happens is that it pulls up all the orders since 2002 and does not filter for orders placed past Jul 2004
according to my knowledge the field odr_date from the cust table is what shows you it the main screen when you view a customer is the last time the customer placed an order
am i doing anything wrong does anybody have any alternative way to get this report done it would be greatly appreciated

[dseibold]

Why don't you search the customer table and compare the entry date (the date the customer was first entered in your system) with the odr_date which is the date of the last activity?

CUST>entrydate is greater than {07/01/2002}
and CUST>odr_date is less than {07/01/2004}

This should get the results you want.

[ypf1258]

Why don't you search the customer table and compare the entry date (the date the customer was first entered in your system) with the odr_date which is the date of the last activity?

CUST>entrydate is greater than {07/01/2002}
and CUST>odr_date is less than {07/01/2004}

This should get the results you want.

David thanks for your help but in my case it will not help me

1 our average prospect before purchase could be in our system for 3 years before a purchase

2 what i want is to be able to see all the non return customers

3 the main reason y i want cms.dbf is because after i get my results i want to modify the report to get more inf like what items did the non return customer order

[dseibold]

David  thanks for your help but in my case it will not help me

1 our average prospect before purchase could be in our system for 3 years before a purchase

2  what i want is to be able to see all the non return customers

3 the main reason y  i want cms.dbf is because  after i get my results i want to modify the report to get more inf like what items did the non return customer order

OK. Then this should help you:

CUST must be the master table
CMS is linked to the CUST table by custnum
ITEM is linked to the CMS table by order

Your query should be:
CUST>odr_date is greater than or equal to {07/01/2002}
and CUST>odr_date is less than {07/01/2004}

This will select your customers who (1) ordered between these dates and (2) have not ordered since that date.

The CMS link will identify the orders they made and the ITEM link will list the items made in those orders.

You may want to group your report by customer and by order. Then you will be able to print the detail of each order.

Hope this is helpful.