Next Record

sabzam · 2008-01-02

Dear All,

Let say we have the Gen. Journal line table with Document No. as current key and I would like to read the first record of every document No. What should I do?

For ex. let say we have three records 111 and two records 222. The current key is document No. I would like to read the first document 111 and after that the first document of 222. Can this be done?

ara3n · 2008-01-02

yes, you crate a variable called perviousDocNo

while you are looping you check

if (perviousDocNo <> '') and (perviousDocNo <> "Document No.") then begin

perviousDocNo := "Document No.";
end;

ara3n · 2008-01-02

You can also use a tempJournal variable

repeat
  tempJournal.setrange("Document No.","Document No.");
  if tempJournal.isempty then begin
     tempJournal := JournalLine;
     tempJournal.insert;



  end;



until

sabzam · 2008-01-02

Isn't there anything cleaner; sort of next primary key?

ara3n · 2008-01-02

The Temp record variable is very clean.

I don't think there is something cleaner (I'm guessing you mean something simpler)

pdj · 2008-01-02

For SQL you could make a new View, and a new linked table in NAV. Then the C/AL code will be very simple, but maintaining different databases could be quite troublesome and you quickly end up having hundreds of different views. Unless you really need to optimize it for speed I wont use this method.

For Native you could optimize it like this:

xx.SETCURRENTKEY("Document No.");
IF xx.FIND('-') THEN
  REPEAT
    <insert your code here>
    xx.setrange("Document No.",xx."Document No.");
    xx.find('+');
    xx.setrange("Document No.");
  UNTIL xx.NEXT = 0;

But never do this on SQL :-)

gumboots · 2008-01-05

For Native you could optimize it like this:

xx.SETCURRENTKEY("Document No.");
IF xx.FIND('-') THEN
  REPEAT
    <insert your code here>
    xx.setrange("Document No.",xx."Document No.");
    xx.find('+');
    xx.setrange("Document No.");
  UNTIL xx.NEXT = 0;

But never do this on SQL :-)

Hi,
1. Would there be any side-effect if you use just one xx instead of xx & xx2

xx2.COPY(xx)

then apply

xx2.SETRANGE("Document No.")

2. Would applying SETRANGE multiple times slow the iteration down a little? At least slower than looping through the filtered records once like what ara2n suggested?

Scott

gumboots · 2008-01-05

sabzam wrote:

Isn't there anything cleaner; sort of next primary key?

I'd say what ara3n suggested is the way to go. C/AL is not T-SQL. I see what you mean by a cleaner way of the same thing. It would really be nice to have something like SELECT DISTINCT to get the UNIQUE keys. I have alway wanted C/AL to introduce a SETUNIQUE keyword in future.

Scott

pdj · 2008-01-05

gumboots wrote:

1. Would there be any side-effect if you use just one xx instead of xx & xx2

It wouldn't make any change. Well, a tiny bit slower because of more filter copying. Why would you do this?

gumboots wrote:

2. Would applying SETRANGE multiple times slow the iteration down a little? At least slower than looping through the filtered records once like what ara2n suggested?

You still haven't mentioned if you were coding for Native, SQL or Both.
But I guess you are using SQL, but please confirm.
For SQL you should go for the suggestion by ara2n. Otherwise the SQL server has to make a new execution plan for each Next, which would kill the performance.

gumboots · 2008-01-07

But I guess you are using SQL, but please confirm.

We actually use Native DB.

pdj · 2008-01-07

gumboots wrote:

We actually use Native DB.

Well, then you could use my solution by jumping the data if you have a company acceptance of making code optimized only for Native.
I don't quite understand your suggestion about using two variables. Please try modifying my code and show it.

valkatamake · 2008-07-03

pdj wrote:

xx.SETCURRENTKEY("Document No.");
IF xx.FIND('-') THEN
  REPEAT
    <insert your code here>
    xx.setrange("Document No.",xx."Document No.");
    xx.find('+');
    xx.setrange("Document No.");
  UNTIL xx.NEXT = 0;

<insert your code here> :?: :?:
what is exactly my code ?

example i have 4 records EEEE 5 records FFFF and 3 records GGGG the result in the form must be 1 eeee 1 ffff and 1 GGGG ?

pdj · 2008-07-03

valkatamake wrote:

what is exactly my code ?

No idea 8)
But since you need to read a table and "group" it by one or more fields I would guess you would like to do something with each of these "groups". So whatever you would like to do with the "group" should be put in this section.
But I have to repeat: Do never do like this on a SQL server...

valkatamake · 2008-07-03

Sorttemp.SETCURRENTKEY(Code);
IF FIND('-') THEN REPEAT //i'm using temp table
Sorttemp.INIT;
Sorttemp.COPY(Rec);
Sorttemp.INSERT;
UNTIL NEXT=0;
Sorttemp.SETFILTER(Code, :?: :?: :?: :?: ); //what i need to put there to filter one of a kind

DenSter · 2008-07-03

valkatamake wrote:

Sorttemp.SETFILTER(Code, :?: :?: :?: :?: ); //what i need to put there to filter one of a kind

You can't, there is no way in NAV to do what SQL Server calls 'SELECT DISTINCT'. The only way to do something lilke it is to loop through the table, keep track of unique ones and copy those into a temporary variable. Read back to Ara3n's first reply, and try it.

pdj · 2008-07-03

DenSter wrote:

You can't, there is no way in NAV to do what SQL Server calls 'SELECT DISTINCT'. The only way to do something lilke it is to loop through the table, keep track of unique ones and copy those into a temporary variable. Read back to Ara3n's first reply, and try it.

Any problems with my suggestion? It runs perfectly and is a lot faster than ara3n's suggestion...

valkatamake · 2008-07-03

http://www.mibuso.com/forum/viewtopic.php?t=26846

check this please :roll:

pdj · 2008-07-03

valkatamake wrote:

check this please :roll:

Ahh, you wish to show them in a form :-k

Then you will have to store the records in a Temp table, and then show the temp table on the form. If you are using NAV5 or later you can use the SourceTableTemporary property. For older versions you could override the OnFind and OnNext triggers like form 344.

My suggestion would be something like this:

OnOpenForm()
xx.SETCURRENTKEY(Code);
IF xx.FIND('-') THEN
  REPEAT
    TempXx := xx;
    TempXx.insert;
    xx.setrange(Code,xx.Code);
    xx.find('+');
    xx.setrange(Code);
  UNTIL xx.NEXT = 0;

But never do this on SQL :-)

DenSter · 2008-07-03

pdj wrote:

DenSter wrote:

You can't, there is no way in NAV to do what SQL Server calls 'SELECT DISTINCT'. The only way to do something lilke it is to loop through the table, keep track of unique ones and copy those into a temporary variable. Read back to Ara3n's first reply, and try it.

Any problems with my suggestion? It runs perfectly and is a lot faster than ara3n's suggestion...

What makes you think I was commenting on anything you said, or that I think there is anything wrong with your suggestion? I was replying to something valkatamake said, and referred him to something that Ara3n suggested, because I think that is a very clean way to do this.

David_Singleton · 2008-07-04

I get a little bit concerned by threads like this one. Actually I started writing a rather long blog about it, but it all got too complex.

Writing code is not always the solution to a problem. Many times I fix problems simply by removing code.

In most cases where you need to find a complex code solution to a problem, maybe you need to take a step back and redesign the logic of what you are doing.

most likely you are just doing this all wrong. Maybe you need a Header table and consider your existing table as a lines table.

Either way, I don't think that this is a programming problem.

pdj · 2008-07-04

DenSter wrote:

What makes you think <cut> I think there is anything wrong with your suggestion?

Great, I was just worried that you knew of a problem in my suggestion, since I have used it quite a few places [-o<

valkatamake · 2008-07-04

Check this out

xxtemp.SETCURRENTKEY(Code);
IF FIND('-') THEN REPEAT
xxtemp.RESET;
xxtemp.SETRANGE(Code,Code);
IF NOT Sorttemp.FIND('-') THEN BEGIN
xxtemp.INIT;
xxtemp.COPY(Rec);
xxtemp.INSERT;
END;
UNTIL NEXT=0;

can you help me do this without temp table

David_Singleton · 2008-07-04

valkatamake wrote:

Check this out ...

can you help me do this without temp table

Did you read my reply, or just chose to ignore it?

ajhvdb · 2008-07-04

In this one http://www.mibuso.com/forum/viewtopic.p ... =skip+next

Kine solutions seams to work?

Next Record

Comments

Categories