Next Record

sabzam

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

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

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

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

ara3n

The Temp record variable is very clean.

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

pdj

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

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

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

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

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

We actually use Native DB.

pdj

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

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

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

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

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

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

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

check this please :roll:

pdj

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

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

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

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

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

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

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

Kine solutions seams to work?