How to work with record-variables (version 3)?

lyot

Hi,

I have a question about the following piece of code in your "How-to".

"You read a set of records and you need to change all records"

IF TheTable.FINDSET(TRUE,TRUE) THEN
  REPEAT
    TheTable2 := TheTable;
    TheTable2."Some Field" := 'Some Value';
    TheTable2.MODIFY(FALSE);
  UNTIL TheTable.NEXT = 0;

Ok, you state clearly that the second true is only need in certain circumstances.
And I guess one of those circumstances is if "Some Field" is part of the primary key of the table, correct?

A valid alternative, as read in "C/SIDE Solution Development in MS Dynamics NAV 5.0", according to MS is the following:

SalesLine.SETRANGE("Document Type","Document Type"::Order);
SalesLine.SETRANGE("Document No.",'S-ORD-06789');
SalesLine.SETFILTER("Location Code",'');
SalesLine.LOCKTABLE;
IF SalesLine.FINDSET THEN
  REPEAT
    SalesLine2 := SalesLine;
    IF SalesLine.Type = SalesLine.Type::Item THEN
      SalesLine2."Location Code" := 'GREEN';
    IF SalesLine.Type = SalesLine.Type::Resource THEN 
      SalesLine2."Location Code" := 'BLUE';
    SalesLine2.MODIFY
UNTIL SalesLine.NEXT = 0;

Ok, if the record count is > 500 then a FINDSET(TRUE), should be used.

Is this also correct in your opinion?

kriki

lyot wrote:

Ok, you state clearly that the second true is only need in certain circumstances.
And I guess one of those circumstances is if "Some Field" is part of the primary key of the table, correct?

Yes.

lyot wrote:

Ok, if the record count is > 500 then a FINDSET(TRUE), should be used.

Is this also correct in your opinion?

About a week ago I talked about this with Hynek Mulbacher and he cleared up a doubt I had about it:

If your record count is < 500 (<50 for NAV2009+ [if you leave the standard value]), it is best to use FINDSET.
If your record count is OVER that value, it is best to use the old FIND('-') because when NAV reaches the last record, it has to switch to the old way, loosing time anyway.

I'll update the How-to with this info when I have some more time.

lyot

kriki wrote:

If your record count is OVER that value, it is best to use the old FIND('-') because when NAV reaches the last record, it has to switch to the old way, loosing time anyway.

Forgot about that one...

kriki wrote:

I'll update the How-to with this info when I have some more time.

Great! :thumbsup:

lyot

I it necessary to use explicit LOCKTABLE statement when looping with FIND('-')?
Or does the cursor handle this automatically?
If we have more than 500 records and want to modify all records.

kriki

lyot wrote:

I it necessary to use explicit LOCKTABLE statement when looping with FIND('-')?
Or does the cursor handle this automatically?
If we have more than 500 records and want to modify all records.

The LOCKTABLE tells SQL to exclusively lock the records, because you want to update them.
If you don't do it, SQL will send a NO-LOCK to read the records for the loop and when you do an UPDATE, it will FIRST send another SELECT with exclusive lock to SQL, so NAV can check if no one else changed the record and THEN it will send an UPDATE. This means 1 SELECT per record more then needed.

superjet

if there would be a best tip of the year award, i would vote for this post :thumbsup:
bravo, kriki! learned some new useful things from your how-to =D>

lyot

superjet wrote:

if there would be a best tip of the year award, i would vote for this post :thumbsup:
bravo, kriki! learned some new useful things from your how-to =D>

I totally agree! =D>

vaprog

pdj wrote:

I found a few small mistakes in your list: (To the best of my knowledge - at least)
“FIND('>') / FIND('<'): This bases itself on the values in the primary key to get the next or previous record, it ALSO considering the filters. Better use NEXT or NEXT(-1).”
Should say “…values in the current key…”

This also applies to FIND('=') and NEXT. In fact you need to consider both, current key and primary key (which is part of the current key, as primary key fields which are not explicitly included in the key definition are appended to that definition as the key is created in tha database). To get success on a FIND('=') you need to fill in all field values of the current key and the primary key. If a record exists where all these field values are equal, the function succeeds.
When it comes to NEXT it works on the current values of the rec. Logically it does not take into account any row position or cursor position of any record retreived before. I assume NAV records whether you modified the record (or relevant fields of the record) so it actually is able to reuse a cursor or fetched dataset without repositioning, but I can't remember having read about this condition and havent investigated myself.

kriki wrote:

CLEAR(TheTable): this statement does a TheTable.RESET AND a TheTable.INIT AND clears the primary keyfields. I almost always use this statement to INSERT a new record. It is the only command that also resets the CHANGECOMPANY-statement.

That was news to me. I did not know CLEAR does an INIT (i.e. respects the InitValue property of the table) definition. There is but one little quirk to it. If the record variable was not jet used, i.e is still undefined, it remains undefined and INIT does not happen.

Belias

vaprog wrote:

There is but one little quirk to it. If the record variable was not jet used, i.e is still undefined, it remains undefined and INIT does not happen.

:shock: quite funny...just noticed it, too...i think that the bright side of this is that nav does not instantiate record variables unnecessarily (e.g. if someone does a only a clear on a variable... :-k )

kriki

Belias wrote:

vaprog wrote:

There is but one little quirk to it. If the record variable was not jet used, i.e is still undefined, it remains undefined and INIT does not happen.

:shock: quite funny...just noticed it, too...i think that the bright side of this is that nav does not instantiate record variables unnecessarily (e.g. if someone does a only a clear on a variable... :-k )

What do you mean with "undefined"? If the record has not been used yet, it is like a CLEAR has happened.

Belias

try to debug this:

clear(mytable);
mytable.init;
clear(mytable);
clear(mytable);  //this is to not jump off the debugger after the clear

after the first clear, the variable is still <Undefined>, after the second, the variable is not <Undefined> anymore...i don't know if there's a difference, but it's strange at a first look...

kriki

Well, that is the debugger that shows it.
In NAV it is a little different. I have create a table with an integer init with property InitValue=10 and ran the following code:

MESSAGE('Before anything:%1',reccleartest.int); // this results 0!
CLEAR(reccleartest);
MESSAGE('after clear:%1',reccleartest.int); // this results 10!
CLEAR(reccleartest);
MESSAGE('after clear 2:%1',reccleartest.int);  // this results 10!

This means the in the debugger, the variable is "undefined" until it is really used (with the first message, the variable is NOT "undefined" anymore in the debugger, but the value of the field is still 0!
BUT there is definitely a difference between a variable that has NEVER been used (not even in CLEAR or INIT!) and a variable that has been CLEAR-ed or INIT-ed! I have to admit I didn't know this! But I never ran into the problem, because before starting to use it I make sure to CLEAR it to make it clear for other programmers that see my code that I DO NOT expect that some fields have been filled in by some other part of the program. (I don't do a CLEAR if I do a GET/FIND* on it [Except if I use the record (blank or not) anyway:see the example]).

Example of my exception:

CLEAR(recSomeRecord);
IF NOT recSomeRecord.GET(..) THEN ; // or also FINDFIRST/FINDLAST
"Some Var" := recSomeRecord.Description; // if I don't find the record, I want a blank value in "Some Var"

Belias

so, i can guess that this: (with your sample record variable, but with one more field to evaluate)

clear(reccleartest);
reccleartest.anotherintfield := 1;
message(reccleartest.int);

will return 10
and this

reccleartest.anotherintfield := 1;
message(reccleartest.int);

will return 0
because ther was not an initialization in the second piece of code, am i correct?...well, i hope i'll remember this

kriki

Correct.

In general it is better to explicitely initialize your variables. It makes clear you don't expect something has been done to it before and makes it easier to modify/debug the code.

Andwian

If we use REPEAT UNTIL, then we must use FIND('-'), instead of FINDFIRST?
Why?
I have tried a scenario for both of them and work well.
Still did not understand.

einsTeIn.NET

It's just because of optimization for SQL. If you're on SQL then in most cases FINDSET will be the best to use in combination with REPEAT...UNTIL. Search the forum for the difference between FIND('-'), FINDFIRST and FINDSET.

Andwian

einsTeIn.NET wrote:

It's just because of optimization for SQL. If you're on SQL then in most cases FINDSET will be the best to use in combination with REPEAT...UNTIL. Search the forum for the difference between FIND('-'), FINDFIRST and FINDSET.

Oh so it only just about the SQL optimization?

Thanks then.

DenSter

Not really. FINDFIRST gets just one record. FIND('-') and FINDSET both get a set of records. If you intend to loop through a set of records, you use FINDSET (or FIND('-')).

Unfortunately, they made FINDFIRST work in conjunction with NEXT, so it does work. It just needs to create a new cursor every time NEXT happens. Personally, I would have preferred it if the compiler would not allow you to use NEXT with a FINDFIRST.

Andwian

DenSter wrote:

Not really. FINDFIRST gets just one record. FIND('-') and FINDSET both get a set of records. If you intend to loop through a set of records, you use FINDSET (or FIND('-')).

Unfortunately, they made FINDFIRST work in conjunction with NEXT, so it does work. It just needs to create a new cursor every time NEXT happens. Personally, I would have preferred it if the compiler would not allow you to use NEXT with a FINDFIRST.

Good idea!

That's why when I tried it in a code, it will works. But in online help would not suggest the FINDFIRST to use in conjuction with the loop.

Thanks for the comment.

DenSter

Andwian wrote:

in online help would not suggest the FINDFIRST to use in conjuction with the loop.

That's because you should NOT use FINDFIRST with a loop. DO NOT use FINDFIRST for loops.

When you need a loop: use FINDSET or FIND('-')
When you need ONE record only: use FINDFIRST

Andwian

DenSter wrote:

That's because you should NOT use FINDFIRST with a loop. DO NOT use FINDFIRST for loops.

When you need a loop: use FINDSET or FIND('-')
When you need ONE record only: use FINDFIRST

Thanks Denster!

I now understand that although we can use FINDFIRST with loop, we should not use it, because of the SQL Performance issue affected.

DenSter

You're not getting the point. Bad performance on SQL Server is a side effect of inproperly using FINDFIRST in a loop, but it is NOT the main reason why you should not use FINDFIRST in a loop. In fact, the keyword was invented to replace FIND('-') for those cases that you only need one record. FINDFIRST only gets one record, so it is not intended to work with a loop. If you want to loop, you are by definition getting more than one record, and therefore you should NOT use FINDFIRST when you program a loop.

It sounds to me like you think that it is alright to use FINDFIRST in a loop, for when you are not on SQL Server, but it's not. When looping, just don't ever use FINDFIRST. Not just because of SQL performance, but because it's quite simply the wrong thing to do.

Andwian

Apologise for my understanding.

I understand that we should NOT use the FINDFIRST using loop. But why? Could you please kindly define me the reasons (if any)? Because it still around in mind. I still can use FINDFIRST altogether with loop well, but why should we avoid this use? Maybe because there is an error message when we do it?

Simply give me the light

DenSter

I already told you: FINDFIRST gets just one record, and therefore is not intended to be used in a loop. If you KNOW that you shouldn't then why do you insist on using it? It's such an easy thing to do, just type FINDSET instead of FINDFIRST!

It's like using a kitchen knife as a screwdriver. Sure it works, but it's not what it's made for. At some point, the thing is going to break, and you are going to wish you had just used the screwdriver.

Andwian

Hi DenSter,

Apologise for insisting, but thank you anyway. I get the point clearer now.

kriki

Give also a look at kine's blog post : Overlooked new parameter in NAV 2009.

rec.COPY

The COPY function can only be used with the shareTable argument set to true if both records are temporary.

Quote from the on-line help:

ShareTable
Type: BooleanSpecifies whether the function creates a copy of the record or creates a reference to a temporary record.

If FromRecord and Record are both temporary and ShareTable is true, then the COPY function does not create a new copy of the record. Instead, the COPY function causes Record to reference the same table as FromRecord.

The default value is false. If you specify false, all records are copied to Record from FromRecord.

Administrator

How to work with record-variables (version 3)?

https://mibuso.com/howtos/how-to-work-with-record-variables-version-3

Discuss this How To here.