report won't run for range of dates in filter

johnsogj
johnsogj Member Posts: 103
edited 2008-09-09 in NAV/Navision Classic Client
hello, I've created a report that runs over the sales line table. I have the user filter request set up so that "shipment date" defaults. however, when I enter a range of dates: "11/12/07..11/30/07", it only prints the data for the first date in the range. has anyone seen this happen before?

thanks

Comments

  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    johnsogj wrote:
    hello, I've created a report that runs over the sales line table. I have the user filter request set up so that "shipment date" defaults. however, when I enter a range of dates: "11/12/07..11/30/07", it only prints the data for the first date in the range. has anyone seen this happen before?

    thanks

    Something is wrong with your code. Post your code on here so people can take a look.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • johnsogj
    johnsogj Member Posts: 103
    SalesInvLine.RESET;

    SalesInvLine.SETCURRENTKEY(Type,"Shipment Date");
    SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
    SalesInvLine.SETRANGE ("Shipment Date", "Shipment Date");
    TotalLinesDay := SalesInvLine.COUNT;
    SalesInvLine.CALCSUMS(Quantity);
    TotalPiecesDay := SalesInvLine.Quantity;

    SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No.");
    SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
    SalesInvLine.SETRANGE ("Shipment Date", "Shipment Date");
    SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No.");
    TotalLines := SalesInvLine.COUNT;
    SalesInvLine.CALCSUMS(Quantity);
    TotalPieces := SalesInvLine.Quantity;

    SalesInvHead.SETRANGE("Shipment Date", "Shipment Date");
    IF SalesInvHead.FINDSET THEN
    TotalInvoicesDay := SalesInvHead.COUNT;

    SalesInvHead.SETRANGE("Shipment Date", "Shipment Date");
    SalesInvHead.SETRANGE("Sell-to Customer No.", "Sell-to Customer No.");
    IF SalesInvHead.FINDSET THEN
    TotalInvoices := SalesInvHead.COUNT;

    AvgLinesDay := TotalLinesDay/TotalInvoicesDay;
    AvgPiecesDay := TotalPiecesDay/TotalInvoicesDay;

    AvgLines := TotalLines/TotalInvoices;
    AvgPieces := TotalPieces/TotalInvoices;
  • garak
    garak Member Posts: 3,263
    SalesInvLine.SETRANGE ("Shipment Date", "Shipment Date");
    TotalLinesDay := SalesInvLine.COUNT;

    Do you need to filter the SalesInvLine basicly of the insertet filter in field "Shipment Date" from Sales Line :?:

    But here you filter SalsInvLine."Shipment date" with the value of "Shipment Date" from a located Rec in SalesLine. So you need to filter.
    SalesInvLine.SETfilter("Shipment Date", getfilter("Shipment Date")); //Use the Filter from Request Form for the SalesInvLine
TotalLinesDay := SalesInvLine.COUNT;

    The same issue for the other "Shipment date" Filters .....

    Regards
    Do you make it right, it works too!
  • johnsogj
    johnsogj Member Posts: 103
    I've changed my code as you suggested but I am still seeing the same problem. the report only runs for the first shipment date in the range specified on the filter form:


    SalesInvLine.RESET;

    SalesInvLine.SETCURRENTKEY(Type,"Shipment Date");
    SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
    SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
    TotalLinesDay := SalesInvLine.COUNT;
    SalesInvLine.CALCSUMS(Quantity);
    TotalPiecesDay := SalesInvLine.Quantity;

    SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No.");
    SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
    SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
    SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No.");
    TotalLines := SalesInvLine.COUNT;
    SalesInvLine.CALCSUMS(Quantity);
    TotalPieces := SalesInvLine.Quantity;

    SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
    IF SalesInvHead.FINDSET THEN
    TotalInvoicesDay := SalesInvHead.COUNT;

    SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
    SalesInvHead.SETRANGE("Sell-to Customer No.", "Sell-to Customer No.");
    IF SalesInvHead.FINDSET THEN
    TotalInvoices := SalesInvHead.COUNT;

    AvgLinesDay := TotalLinesDay/TotalInvoicesDay;
    AvgPiecesDay := TotalPiecesDay/TotalInvoicesDay;

    AvgLines := TotalLines/TotalInvoices;
  • David_Singleton
    David_Singleton Member Posts: 5,479
    johnsogj wrote:
    I've changed my code as you suggested but I am still seeing the same problem. the report only runs for the first shipment date in the range specified on the filter form:

    John,

    the code you posted here is NOT the code that is causingthe problem. The filter you are applying is (I will have to assume since you seem to be holding out on us :evil: ) on "Sales Inv. Line"."Shipment date".

    that being the record in the Report section. We need to see how you are filtering that one, its irrelevant what you do withthe SalesInvLine table, since that is not the record in the loop.
    David Singleton
  • johnsogj
    johnsogj Member Posts: 103
    I posted the entire code that I have.... I'm confused.
  • DenSter
    DenSter Member Posts: 8,307
    I also have a feeling that we're not seeing all the code, although that doesn't necessarily have to be a deliberate omission. Maybe the dataitem has the name SalesInvLine? In that case it seems you set the range as a filter before the report starts, and at the first record, you change the filter to the one value.

    By the way, you could use a C/AL syntax primer. Some statements are repeated that don't need to be repeated. For instance, once you set a filter on a field, it keeps the filter, you don't have to set it twice when you add a filter on another field. Also, you don't have to say IF FINDSET THEN COUNT, you can leave out the IF FINDSET part. If there are no records the system will return 0. Now you're getting the COUNT as well as the entire record set for every record. Lots of overhead in that code :).
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • garak
    garak Member Posts: 3,263
    To end this confusion ;-) could you export the Report as Text (Object Designer -> file -> Expert -> TextFile) and Post it here.

    If you need to output the located SalesInvLines you need a seperate DataItem. I think you source is behind the OnAfterGetRecord Trigger of DataItem Sales Line or?

    @DenSter: In his first post he wrote, that he go over the Sales Line. With the CalcSums Function, my opinion was, that he only need the SalesInvLine to calculate some fields basicly on Shipment Filter, etc. That the code is not the best, is an other part ;-)

    Regards
    Do you make it right, it works too!
  • Franco_Festi
    Franco_Festi Member Posts: 19
    Hi,
    can you please post the dataitems you have in this report and their dataitemindent and DataItemLinkReference ?

    Cause maybe we're not getting it right!
  • DenSter
    DenSter Member Posts: 8,307
    garak wrote:
    @DenSter: In his first post he wrote, that he go over the Sales Line
    I know that's what he wrote, I am asking if his dataitem (for the Sales Line table) has the NAME SalesInvLine. You can give a dataitem any NAME you want. If he indeed has named his "Sales Line" dataitem "SalesInvLine", then that explains why his filter gets screwed up. I've seen crazier names than that, so it's not a strange question :mrgreen:
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • ara3n
    ara3n Member Posts: 9,258
    The problem is that he is setting filter on the SalesLine that he is in. If he wants to set a range,
    Create two new variable. StartDate, EndDate.
    OnPrereport

    Copy the getrangemin and getrangemax to the variable. and in your code add

    StartDate := "Sales Line".GetRANGEMIN("SHipment Date");
    EndDate := "Sales Line".GetRANGEMAX("SHipment Date");





    SalesInvLine.SETFILTER("Shipment Date", StartDate,EndDate);
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DenSter
    DenSter Member Posts: 8,307
    We're all thinking about exactly the same issue, we're just going about getting to the bottom of it in a different way :mrgreen:

    The only way to really see what is going on is to see the object.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • johnsogj
    johnsogj Member Posts: 103
    thanks for all of the feedback! I feel so badly that I had a typo in the beginning. The Sales Line table is not part of this at all. All of the data I need is in the Sales Invoice Line table. So that is the data item I'm using. I'm so sorry for the confusion.

    I'll try to export the code based on the instructions of one of you previously...
  • johnsogj
    johnsogj Member Posts: 103
    Unfortunately I do not have access to export via text... here is another copy of my code (its in the OnAfterGetRecord section)


    SalesInvLine.RESET;

    SalesInvLine.SETCURRENTKEY(Type,"Shipment Date");
    SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
    SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
    TotalLinesDay := SalesInvLine.COUNT;
    SalesInvLine.CALCSUMS(Quantity);
    TotalPiecesDay := SalesInvLine.Quantity;

    SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No.");
    SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
    SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
    SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No.");
    TotalLines := SalesInvLine.COUNT;
    SalesInvLine.CALCSUMS(Quantity);
    TotalPieces := SalesInvLine.Quantity;

    SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
    IF SalesInvHead.FINDSET THEN
    TotalInvoicesDay := SalesInvHead.COUNT;

    SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
    SalesInvHead.SETRANGE("Sell-to Customer No.", "Sell-to Customer No.");
    IF SalesInvHead.FINDSET THEN
    TotalInvoices := SalesInvHead.COUNT;

    AvgLinesDay := TotalLinesDay/TotalInvoicesDay;
    AvgPiecesDay := TotalPiecesDay/TotalInvoicesDay;

    AvgLines := TotalLines/TotalInvoices;
    AvgPieces := TotalPieces/TotalInvoices;
  • Savatage
    Savatage Member Posts: 7,142
    I don't understand why you have both of these sections??
    You define some fields twice? any reason?
    Did you try one than the other? Are these really key's? Or did you make new keys? 
    SalesInvLine.SETCURRENTKEY(Type,"Shipment Date"); 
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item); 
SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date")); 
TotalLinesDay := SalesInvLine.COUNT; 
SalesInvLine.CALCSUMS(Quantity); 
TotalPiecesDay := SalesInvLine.Quantity;

    SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No."); 
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item); 
SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date")); 
SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No."); 
TotalLines := SalesInvLine.COUNT; 
SalesInvLine.CALCSUMS(Quantity); 
TotalPieces := SalesInvLine.Quantity;
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • ara3n
    ara3n Member Posts: 9,258
    getfilter does not work onafter get section. Please follow my advise above and you should be set.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DenSter
    DenSter Member Posts: 8,307
    Yes I agree, follow Ara3n's advice (which is a very safe bet under any circumstances :mrgreen:), put the range into variables, set the date filter to that range in the OnPreDataitem trigger, and you should be set. This is without having a clue about what it is that you are trying to do though.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • garak
    garak Member Posts: 3,263
    why an "getfilter" should not work on the onaftergetrecord section :?:

    RecVariable.setfilter(Somefield,OtherRecVariableOrDataItemVariable.getfilter(SomeField));

    It works. It is the same like getrangemin, getrangemax <- but these have troube if you use a filter like 01.01.08.. or ..31.12.08. Its work only for 01.01.08..31.12.08

    Regards
    Do you make it right, it works too!
  • garak
    garak Member Posts: 3,263
    johnsogj
    All of the data I need is in the Sales Invoice Line table. So that is the data item I'm using

    The Name of you used DataItem is realy SalesInvLine :?:
    If yes, why you reset the filters :?:
    johnsogj
    SalesInvLine.RESET;

    if no, forgett this post ....
    Do you make it right, it works too!

Categories