Modify report to only show date of last invoice..

yuppicideyuppicide Member Posts: 410
edited 2014-10-07 in Navision Attain
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".

Comments

  • SavatageSavatage Member Posts: 7,142
    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.
  • yuppicideyuppicide Member Posts: 410
    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.
  • SavatageSavatage Member Posts: 7,142
    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
  • SavatageSavatage Member Posts: 7,142
    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)
  • yuppicideyuppicide Member Posts: 410
    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.
  • SavatageSavatage Member Posts: 7,142
    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.
  • yuppicideyuppicide Member Posts: 410
    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.
  • SavatageSavatage Member Posts: 7,142
    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".
  • yuppicideyuppicide Member Posts: 410
    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.
  • SavatageSavatage Member Posts: 7,142
    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"
Sign In or Register to comment.