Modify report to only show date of last invoice..

yuppicide

Navision 3.10

I'd like to make a condensed report out of "Report 10042 - Customer Account Detail". I can filter it by Salesperson and only to show Invoices. The problem is that report can become a lot of pages.

I don't need a lot of stuff that the report had, so I got rid of them. Anyway, it shows Date something was invoiced and Amount. I only need to show the last invoice, not all of them. Anyone have any idea how?

My idea is that maybe a customer is still in business, but has been neglected by a Sales Rep. This would be an easy way for me to give the Sales Rep a list and say "hey this person hasn't bought since 2011, do some research to see if they are still in business and reach out to them".

Savatage

easier way to to create a new field on the customer card called "Last Invoice Date" type date (50000+ range)

Data Type=DATE
FieldClass=Flowfield
CalcFormula=Max("Cust. Ledger Entry"."Posting Date" WHERE (Document Type=FILTER(Invoice),Customer No.=FIELD(No.)))

then you can just create a customer list and add that field.
then each salesperson will know when the customer made their last order.

you can even use it in a filter for the report..say "Last sale Date" < 01/01/2014

to see who has not placed an order this year.

yuppicide

I don't have access to add a new field. I pretty much have the report as I want it, except for only showing the date/amount of last sale.

I am wondering though, if I only show date/amount of last sale, will it still give me a total of every invoice for that customer.. or will my total then reflect only that one invoice I showed.

Savatage

Fine, I do think this is a field that you could find dozens of useful ways to use.

Ok so in this report...
Create a Global Variable called "ShowLastInvoice" Type Boolean.
View the request form - enlarge it so you can add another option.
(you can copy one of the current options & paste it at the bottom)
Change Caption to "Show Last Invoice Only" & the SourceExp of the box is "ShowLastInvoice"

Then view the sections of the report.
Find the one (Cust. ledger Entry, Body (1)) Click on it and hit F9

Add:
OnPreSection()
IF ShowLastInvoice THEN BEGIN
"Cust. Ledger Entry".SETRANGE("Document Type","Document Type"::Invoice);
IF NOT "Cust. Ledger Entry".FIND('+')
THEN CurrReport.SKIP;
END;

Save->Compile->Enjoy

Savatage

After some testing looks like setting
Cust. Ledger Entry".SETRANGE("Document Type","Document Type"::Invoice);
can cause an error message on random accounts.

Two ways to fix it.
One is when you run the report on the (cust legder entry) tab for Document type - Manually Enter : Invoice

Second Way is on the <Cust. Ledger Entry> dataitem itself add

OnPreDataItem()
IF ShowLastInvoice THEN "Cust. Ledger Entry".SETRANGE("Document Type","Document Type"::Invoice);

at that point when you run the report you will probably not want to see the "Beginning Balance" & "Ending Balance" info.
So surpress them. view the sections again & find:
Customer, Body (10)
F9 & add:
IF ShowLastInvoice THEN CurrReport.SHOWOUTPUT(FALSE);
& add the same on Cust. Ledger Entry, footer (2)

yuppicide

Perfect! Grabs the last date, but the dollar amounts are wrong now.

For Last sale it shows $0.00. My Cust. Ledger Entry, Body (1) has =<Amount> in it.

For Total it shows the amount of their first sale, not all sales.

Savatage

As I said I would have created a new report utilizing the new field of "Last Invoice Date".

& not munch up an existing report. Because you are dealing with what ever code already exists in that report.

Anyway (Again). try this.

Create A new field in the globa's called "OrigAmt" type decimal.

Change the code on "Cust. Ledger Entry", Body (1) Section
OnPreSection()
IF ShowLastInvoice THEN BEGIN
IF NOT "Cust. Ledger Entry".FIND('+') THEN BEGIN
CurrReport.SKIP
END ELSE BEGIN
"Cust. Ledger Entry".CALCFIELDS("Amount ($)");
OrigAmt := "Cust. Ledger Entry"."Amount ($)";
END;
END;

Add the new field with sourceexp = OrigAmt; to the "Cust. Ledger Entry", Body (1) Section

Now this is your report and I'm just helping you get the info you need. the designing of how it looks is up to you. Now using the examples from above about on how to show/hide sections based on criteria, perhaps you should create a NEW body section "Cust. Ledger Entry", Body (2). That just shows the info you need if the option of only showing the last invoice was checked. This way you don't have to mess with sections that already do what you wan them to do. and hide & show the sections you need when you need them.

And copying this report to a new report (50000+) range so you can play around with it and not effect the live report would be a good learning tool.

yuppicide

Yeah I have a bunch of my own reports in the 50000+ range, but one thing I've never did was totals before. Thanks.. everything is perfect, but how do I now get total to show again?

We have total of last invoice with OrigAmt, but then Total Debits field in the footer only shows the first invoice.. doesn't add them all up.

Savatage

This is not how I would have done this report. You are taking a functional report and trying to morph it into something that it's not. But if you are looking for the customer balance then did you try adding a field with sourceexp = Customer."Balance ($)" ?

I don't need a lot of stuff that the report had, so I got rid of them. Anyway, it shows Date something was invoiced and Amount. I only need to show the last invoice, not all of them. Anyone have any idea how?

My idea is that maybe a customer is still in business, but has been neglected by a Sales Rep. This would be an easy way for me to give the Sales Rep a list and say "hey this person hasn't bought since 2011, do some research to see if they are still in business and reach out to them".

yuppicide

No, this report is everything I need.. except instead of showing every single invoice, just show the last one, which you've kindly helped me do.

Problem is Balance ($) won't show a total of all sales over the years. Balance ($) is 0, since all (well most) orders have been paid for already.

This was the best report I could see to modify for my needs.

Savatage

If you are looking for their total sales then balance($) is not the field you need.

try "Sales ($)" instead. Might have to use calcfields for this field if it comes up "0"