Date Range

Navi_Learner

Hi, I created a report based on customer and ledger to retrieve the customers that are not active for a period of time such as 30 days, 60 days, over one year. Here is the coding, but it didn't work, any suggestions? Thanks!
SETRANGE("Posting Date",0D, InactiveDate[1]); (in the customer ledger dataitem)
Then create differen section to retrieve records in different time frame:

InactiveDate[1] := InactiveDate[1] - 30;
CurrReport.SHOWOUTPUT (format("Posting Date" =InactiveDate[1] ))

InactiveDate[2] := InactiveDate[1] - 180;
CurrReport.SHOWOUTPUT (format("Posting Date" =InactiveDate[2] ))

David_Cox

Navi Learner wrote:

Hi, I created a report based on customer and ledger to retrieve the customers that are not active for a period of time such as 30 days, 60 days, over one year. Here is the coding, but it didn't work, any suggestions? Thanks!
SETRANGE("Posting Date",0D, InactiveDate[1]); (in the customer ledger dataitem)
Then create differen section to retrieve records in different time frame:

InactiveDate[1] := InactiveDate[1] - 30;
CurrReport.SHOWOUTPUT (format("Posting Date" =InactiveDate[1] ))

InactiveDate[2] := InactiveDate[1] - 180;
CurrReport.SHOWOUTPUT (format("Posting Date" =InactiveDate[2] ))

Just Use 2 Customer data items
Customer1
>>Customer2 (Indented and linked by "No.")

Then Use a Global for the Customer ledger
On after get record for the first data item Customer1
To find No Activity in the last 180 Days
CustLedger.RESET;
CustLedger.SETCURRENTKEY("Customer No.","Posting Date");
CustLedger.SETRANGE("Customer No.","No.");
CustLedger.SETRANGE("Posting Date",TODAY-180,TODAY);
IF CustLedger.COUNT <> 0 THEN
CurrReport.SKIP;

On after get record for the Second data item Customer2
To find No Activity in the last 30 Days for Customers that have activity in the last 180 days.
We have already filtered the customer ledgers
IF CustLedger.COUNT <> 0 THEN BEGIN
CustLedger.SETRANGE("Posting Date",TODAY-30,TODAY);
IF CustLedger.COUNT <> 0 THEN
CurrReport.SKIP;
END ELSE
CurrReport.SKIP;

Then just add your sections, only one will show per customer with no activity 1 - 30 Days or 1 - 180 Days .

Have fun

Navi_Learner

Thanks! I use two dataitems: customer and customer legger to link them together, the output is no difference from the output you provide here. My question is if I create several checkbox in the option form, for example, 60days checkbox to retrieve the customer without any activity for 60 days, 180days check box to retrive those data. How to write a code like this: CurrReport.SHOWOUTPUT("Cust. Ledger Entry"."Posting Date" > 06/01/06 & <10/31/06 ); Thanks a lot!!!

David_Cox

This is confusing you would not use check box, if you check 30 and 60 all the 30 will be in the 60 anyway.

You are looking for Customer without ledger entries within the last XXX days, so the second dataitem is no good.

So you need only the Customer and a C/AL Global variable, CustLedgEntry = Record = "Cust. Ledger Entry"

Dont use Check Box use a type Option C/AL Global Variable
HowLong = Option = 30 Days,60 Days,180 Days,360 Days
Put this on your Request form so the user can select one value!

Customer - OnAfterGetRecord()
CustLedgEntry.RESET;
CustLedgEntry.SETCURRENTKEY("Customer No.","Posting Date");
CustLedgEntry.SETRANGE("Customer No.","No.");
Case Howlong of
HowLong::"30 Days":
CustLedgEntry.SETRANGE("Posting Date",TODAY-30,TODAY);
HowLong::"60 Days":
CustLedgEntry.SETRANGE("Posting Date",TODAY-60,TODAY);
HowLong::"180 Days":
CustLedgEntry.SETRANGE("Posting Date",TODAY-180,TODAY);
HowLong::"360 Days":
CustLedgEntry.SETRANGE("Posting Date",TODAY-360,TODAY);
END;
// If the customer has entries in the last XXX days then skip
IF CustLedgEntry.FIND('-')THEN
CURRREPORT.SKIP;

:-k

Savatage

Just to add a bit - are you sure you want "Any" activity or do you want to know who hasn't had an Invoice in the last XXX days (for example).

we use form 302 Customer Entry Statistics to get the last dates of
-Payment
-Invoice
-Credit Memo
-Reminder
-Finance Charge Memo
-Refund

David_Cox

Savatage wrote:

Just to add a bit - are you sure you want "Any" activity or do you want to know who hasn't had an Invoice in the last XXX days (for example).

we use form 302 Customer Entry Statistics to get the last dates of
-Payment
-Invoice
-Credit Memo
-Reminder
-Finance Charge Memo
-Refund

Good Point ! :P

If it is just sales add before the case statement
CustLedgEntry.SETRANGE("Document Type",CustLedgEntry."Document Type"::Invoice);
Or
CustLedgEntry.SETRANGE("Document Type",CustLedgEntry."Document Type"::Invoice,CustLedgEntry."Document Type"::"Credit Memo");

Navi_Learner

Thank you all! David, I have the last question for you. In your coding, 30dys including 180 days and 360 days, and when running 180 days, it includes 360 dyas. How to ristrict those just within 30 days and those within 180 days. Thanks again!!

David_Cox

Navi Learner wrote:

Thank you all! David, I have the last question for you. In your coding, 30dys including 180 days and 360 days, and when running 180 days, it includes 360 dyas. How to ristrict those just within 30 days and those within 180 days. Thanks again!!

Other way around
"30 Days" = 30 Days to TODAY (No Activity for 30 Days)
"60 Days" = 60 Days to TODAY Includes 30 days (No Activity for 60 Days)
"90 Days" = 90 Days to TODAY Includes (60, 30) (No Activity for 90 Days)
"180 Days" = 180 Days to TODAY Includes (90, 60, 30) (No Activity for 180 Days)

IF You wanted to add Date Range options of Say "30 - 60 Days","60 - 90 Days"

HowLong::"30-60 Days":
CustLedgEntry.SETRANGE("Posting Date",TODAY-60,TODAY-30);
HowLong::"60-90 Days":
CustLedgEntry.SETRANGE("Posting Date",TODAY-90,TODAY-60);

David_Singleton

This is quite a common request. And normally the routine is only run once a month or so, so performance is not a huge issue.

So:

Add a new flow field to the Customer card.

No Of Transactions:
Source expression is
Document type = (which ever one you want, leave this out if you don't care).
Customer No = No
Posting Date = Date Filter.

Now just have a section

Customer, and set in properties
No Of Tranascitons <> 0

in the request window, just ask for posting date. And se that to the date you want (or dont want).

Now in the report, make sure the field No Of Transacitons is on a section, since it wont Calc properly otherwise. (CALCFIELDS property does not seem to work)

And there you are. It actuallly took me longer to write this than it will to write the code.

Also an advantage of this method is you can easily just do it on the customer list form, and copy paste the result to excel, then its even less work.

David_Cox

Hi David

I never thought of that one, but we dont need any new fields we have 3 as standard:
Inv. Amounts (LCY)
Cr. Memo Amounts (LCY)
Outstanding Orders

So we can add then to the form or if we still want a report change the Code to

Customer - OnAfterGetRecord()
Case Howlong of
HowLong::"30 Days":
SETRANGE("Date Filter",TODAY-30,TODAY);
HowLong::"60 Days":
SETRANGE("Date Filter",TODAY-60,TODAY);
HowLong::"180 Days":
SETRANGE("Date Filter",TODAY-180,TODAY);
HowLong::"360 Days":
SETRANGE("Date Filter",TODAY-360,TODAY);
END;

CALCFIELDS("Inv. Amounts (LCY)","Cr. Memo Amounts (LCY)","Outstanding Orders");

// If the customer has entries in the last XXX days then skip
IF (("Inv. Amounts (LCY)"<>0)OR("Cr. Memo Amounts (LCY)"<>0)OR("Outstanding Orders"<>0))THEN
CURRREPORT.SKIP;

Nice and Clean

Navi_Learner

Thanks Expert!