Problem with SETRANGE statement.

JamieBrown
JamieBrown Member Posts: 107
edited 2006-11-24 in NAV/Navision Classic Client
Hi,

This is a follow-on from my last post, but I'm having trouble filtering the table on the date ranges.
intyear := DATE2DMY(TODAY,3); 
FOR Counter := 1 TO 12 DO BEGIN
  datstartdate[Counter] := DMY2DATE(1,Counter,intyear);
  datenddate[Counter] := CALCDATE('<+1M-1D>',datstartdate[Counter]);

"Trans. Sales Entry".SETRANGE("date",datstartdate[Counter],datenddate[Counter]);

netamount[Counter] := "Trans. Sales Entry"."Net Amount";
grossamount[Counter] := "Trans. Sales Entry"."Gross Amount";
qty[Counter] := "Trans. Sales Entry".Quantity;

END;

The SETRANGE statement doesn't work at all. I'm sure I'm probably doing something stupid...

thanks as always.

Comments

  • MTC
    MTC Member Posts: 159
    What is in "Trans. Sales Entry" ? What is it referencing?

    You need to do a FIND after the SETRANGE statement.
  • JamieBrown
    JamieBrown Member Posts: 107
    MTC,

    Thanks for the quick reply.

    I'm trying ro reference:

    "Trans. Sales Entry"."Net Amount";
    "Trans. Sales Entry"."Gross Amount";
    "Trans. Sales Entry".Quantity;

    With the monthly date filter applied.

    The following manual code does filter correctly for Jan.
    "Trans. Sales Entry".SETFILTER(Date,'010106..310106');

    thanks
  • MTC
    MTC Member Posts: 159
    edited 2006-11-24
    Your date calculation code is fine, but you need to do a IF "Trans. Sales Entry".FIND('-') THEN after the "Trans. Sales Entry".SETRANGE statement, otherwise it is always stuck in the same place and will not move.

    I am supposing that there is only one record in "Trans. Sales Entry" per date range?
  • cletenaff
    cletenaff Member Posts: 6
    Hello,

    Have u tried to put a
    "Trans. Sales Entry".SETRANGE(date);

    before applying any other date filters?

    bye.
  • JamieBrown
    JamieBrown Member Posts: 107
    MTC,

    No there are tens of thousands of entries per date range.

    I'm a little confused, wouldn't the 
    FOR Counter := 1 TO 12 DO BEGIN
    run through each record in "Trans. Sales entry" per date range?

    Cletenaff, I tried your suggestion but it made no difference.

    thanks
  • MTC
    MTC Member Posts: 159
    edited 2006-11-24
    JamieBrown wrote:
    MTC,

    No there are tens of thousands of entries per date range.

    I'm a little confused, wouldn't the 
    FOR Counter := 1 TO 12 DO BEGIN
    run through each record in "Trans. Sales entry" per date range?

    Not unless you have got some sumindexfields setup, no, it's simply giving you 12 iterations.
    intyear := DATE2DMY(TODAY,3);
FOR Counter := 1 TO 12 DO BEGIN
  datstartdate[Counter] := DMY2DATE(1,Counter,intyear);
  datenddate[Counter] := CALCDATE('<+1M-1D>',datstartdate[Counter]);

  "Trans. Sales Entry".SETRANGE("date",datstartdate[Counter],datenddate[Counter]);
  IF "Trans. Sales Entry".FIND('-') THEN REPEAT
    netamount[Counter] += "Trans. Sales Entry"."Net Amount";
    grossamount[Counter] += "Trans. Sales Entry"."Gross Amount";
    qty[Counter] += "Trans. Sales Entry".Quantity;
  UNTIL "Trans. Sales Entry".NEXT = 0;
END;

    That would use each record within the date range.

    I think. 8-[
  • lubost
    lubost Member Posts: 633
    I think that better and faster way is:

    1. Create key Date in "Trans. Sales Entry" with SumIndexFields "Net Amount", "Gross Amount" and Quantity
    2. Insert "Trans. Sales Entry".CALCSUMS("Net Amount", "Gross Amount", Quantity); after your setrange command
  • MTC
    MTC Member Posts: 159
    lubost wrote:
    I think that better and faster way is:

    1. Create key Date in "Trans. Sales Entry" with SumIndexFields "Net Amount", "Gross Amount" and Quantity
    2. Insert "Trans. Sales Entry".CALCSUMS("Net Amount", "Gross Amount", Quantity); after your setrange command

    Yes, that would be faster, but I think that it's important for him to know why his loop would not work on its own.
  • JamieBrown
    JamieBrown Member Posts: 107
    MTC, I have tried using FIND and the Navision client actually crashes. But I think I can see the problem with my original logic.

    Lubost, I've never used sumindexfields. (Never created flow fields etc)

    How would I go about 'coding' it.

    many thanks
  • MTC
    MTC Member Posts: 159
    To use the sumindexfield, you need to create a key in the table as described by lubost. Then, place the statement
    SETCURRENTKEY(Primary Key,"Date");
"Trans. Sales Entry".SETRANGE("date",datstartdate[Counter],datenddate[Counter]); 
"Trans. Sales Entry".CALCSUMS("Net Amount", "Gross Amount", Quantity);



    instead of where you currently have the SETRANGE command. So, something like this:
    SETCURRENTKEY(Primary Key,"Date");
"Trans. Sales Entry".SETRANGE("date",datstartdate[Counter],datenddate[Counter]); 
"Trans. Sales Entry".CALCSUMS("Net Amount", "Gross Amount", Quantity);
  • MTC
    MTC Member Posts: 159
    To use the sumindexfield, you need to create a key in the table as described by lubost. Then, place the statement
    SETCURRENTKEY(Primary Key,"Date");
"Trans. Sales Entry".SETRANGE("date",datstartdate[Counter],datenddate[Counter]); 
"Trans. Sales Entry".CALCSUMS("Net Amount", "Gross Amount", Quantity);



    instead of where you currently have the SETRANGE command. So, something like this:
    intyear := DATE2DMY&#40;TODAY,3&#41;;
FOR Counter := 1 TO 12 DO BEGIN
  datstartdate&#91;Counter&#93; := DMY2DATE&#40;1,Counter,intyear&#41;;
  datenddate&#91;Counter&#93; := CALCDATE&#40;'&lt;+1M-1D&gt;',datstartdate&#91;Counter&#93;&#41;;

SETCURRENTKEY(Primary Key,"Date");
"Trans. Sales Entry".SETRANGE("date",datstartdate[Counter],datenddate[Counter]); 
"Trans. Sales Entry".CALCSUMS("Net Amount", "Gross Amount", Quantity);

netamount&#91;Counter&#93; := "Trans. Sales Entry"."Net Amount";
grossamount&#91;Counter&#93; := "Trans. Sales Entry"."Gross Amount";
qty&#91;Counter&#93; := "Trans. Sales Entry".Quantity;

END;

    Whoops, sorry, I hit quote instead of edit.

Categories