Inactivity Report

Business Central/NAV Developers Forum
1

Dear All,

Is it possible to run a Navision report which lists

Customer code Customer name Balance Last contact date (comment line table)

Selection on balance >0, and latest diary date > 30 days old

We’re looking to identify active customers that may have been missed or dropped.

Thanks,
Vinod

2

Hi Vinod,

There isn’t a standard report but this is an easy report to write. Create a dataitem for customer and filter to balance > 0. Then create a variable for the comment line and get the last line with a date. compare the date and if less than 30days then skip the customer.

3

Hi Dave,

Thanks for this. Kindly note that I’m not a Navision expert.

I managed to do the first part. Now my report shows company names that has a balance >0. Can you please explain on how to find the maximum date value (or the last record) from Comment Line for each Customer?

Thanks

4

Hi Vinod,

Declare a variable, type record for the comment line table then use:

CommentLine.Reset;

CommentLine.setrange( “Table Name”, CommentLine.“Table Name”::Customer );

CommentLine.setrange( “No.”, Customer.“No.” );

CommentLine.setfilter( Date, ‘<>0D’ );

if CommentLine.Findlast then // CommentLine.find( ‘+’) on pre version 5.0

do the check.

5

This compiles fine but while running the report I’m getting a message box saying - ‘0D’ is not a valid date. Any help on this?

6

Hi Vinod,

My mistake it should be

CommentLine.setfilter( Date, ‘<>’’’’’ ); // ie. <> and 5 's

7

Hi,

I would suggest to do another kind of filtering on the date.

CommentLine.SETFILTER(Date,’%1…’,CALCDATE(’<-30D>,TODAY));
You could also prompt the user for how long back he/she wants to check for commentlines, and then use that propmted value as parameter to the CALCDATE.

If you filter like this, you can simply do:

IF CommentLine.FINDSET(FALSE,FALSE) THEN
CurrReport.SKIP;

8

This works for 1 customer at a time. While giving a range of customers or the complete list of customers, I’m getting another message (given below) and report exits.

"There is no Comment Line within the filter."

"Filters: Table Name: Customer, No.: #AAA01, Date: <>’'

I’m pasting the code here. Please correct if any (there should be) mistakes.

##Comm is the record variable to Comment Line table, ChkNo is an integer variable to calculate no. of days since last comment.##

Comm.RESET;
Comm.SETRANGE(“Table Name”, Comm.“Table Name”::Customer);
Comm.SETRANGE(“No.”,Customer.“No.”);
Comm.SETFILTER (Date, ‘<>’’’’’);
Comm.FIND(’+’);
ComDt := Comm.Date;

ChkNo := ROUND(Comm.Date - TODAY);

IF ChkNo < 30 THEN
CurrReport.SKIP
ELSE
ComDt := Comm.Date;

Thanks

9

Hi Vinod,

The line

Comm.FIND(’+’);

is causing the problem - this will generate an error message if no comment exist.

Try

IF Comm.find(’+’) then

comDt := comm.date

else

comdt := choose a date - either today or 0D depending on whether you want to include/exclude the record.

10

Thanks. It works fine. :slight_smile:

Also, can I sort this list by Balance descending?

11

Hi Vinod,

This is not a simple task as balance is a flowfield and cannot be part of the key.

You would need to create a temporary table, calculate the balance and then sort on the temporary table

12

How do I sort this by date ascending? Any ideas??

Please let me know.

Thanks