FIND('-') and FINFIRST

Vineeth.R

Dear Friends,

I am using Nav4.0 SP3 and i found something funny.
USE FINDFIRST Result is wrong
vInt := 0;
Table1.reset;
Table1.setrange(field1,value);
Table1.setrange(field2,value);
if Table1.FINDFIRST then begin
message('%1',Table1.count); //Count shows 8
repeat
vInt += 1;
until Table1.next = 0;
message('%1', vint); // vint is 5
end;
USE FIND('-') working fine
vInt := 0;
Table1.reset;
Table1.setrange(field1,value);
Table1.setrange(field2,value);
if Table1.FINDFIRST then begin
message('%1',Table1.count); //Count shows 8
repeat
vInt += 1;
until Table1.next = 0;
message('%1', vint); // vint is 8
end;


Please advise why this funny scenario..

thanks and Regards
Vineeth.R

mohana_cse06

in both cases u have used FINFIRST..wre is find(-) :?:

kine

It is because you missused the FINDFIRST. It is used ONLY IF YOU NEED THE ONE Record, when you will not go through the set in loop like you did. In this case you need to use FINDSET. I recommend to read some basics about using FINDFIRST, FINDLAST, FINDSET and ISEMPTY... 8)

garak

The FINDFIRST / FINDLAST command is only for getting the first rec on the table and is not designed for looping.
Nav set a sql query like this:

Select Top 1 * from ......

To get a resultset use findset([ForUpdate][, UpdateKey]) or find('-') (find('-') with locktable if many datachanges in the loop self)

If you only need to know, exist there some datas in the table based on my filters use MyRecTableAsVariable.ISEMPTY.
Here NAV create a sql query like this

select Top 1 NULL from ....

Regards

Vineeth.R

Dear Friends,

Sorry i forgot to highlight FIND('-') please see again:



USE FINDFIRST Result is wrong
vInt := 0;
Table1.reset;
Table1.setrange(field1,value);
Table1.setrange(field2,value);
if Table1.FINDFIRST then begin
message('%1',Table1.count); //Count shows 8
repeat
vInt += 1;
until Table1.next = 0;
message('%1', vint); // vint is 5
end;

USE FIND('-') working fine
vInt := 0;
Table1.reset;
Table1.setrange(field1,value);
Table1.setrange(field2,value);
if Table1.FIND('-')then begin
message('%1',Table1.count); //Count shows 8
repeat
vInt += 1;
until Table1.next = 0;
message('%1', vint); // vint is 8
end;


Please advise why this funny scenario..



Dear Garak,

So you mean when ever i need to loop i have to go for FINDSET or FIND('-') in lower versions right? can u guess why i got vInt as 5 in the second scenario here.?

thanks and Regards
Vineeth.R

Waldo

I wrote a blog ones: What impact does my C/AL have on SQL? (you can find the entire series here).

I might suggest that you read it ... 8)

kine

Yes, read the documentation and blogs about this, you need to understand correctly how it works, to be able to use it correctly. E.g. replacing all FIND('-') before loop with FINDSET is not correct too... all depends on averae count of records you will loop etc.

Waldo

kine wrote:

...
E.g. replacing all FIND('-') before loop with FINDSET is not correct too... all depends on averae count of records you will loop etc.

Hm, I'm do not entirely agree with that... .
I investigated that in the article above ... off course I would like your opinion on this .

garak

search the forum for perfomance and findset, read the "waldo Blog" and read the MS Blogs (search google for Navision findset / isempty). There are a lot of examples.

kine

Waldo wrote:

kine wrote:

...
E.g. replacing all FIND('-') before loop with FINDSET is not correct too... all depends on averae count of records you will loop etc.

Hm, I'm do not entirely agree with that... .
I investigated that in the article above ... off course I would like your opinion on this .

I mean it in the intentions you have wrote. There can be dependency of "optimal" usage on record count and man need to know that. If it will have really measurable impact or not is another question.

DenSter

You can't just replace all FIND('-') occurrances with FINDFIRST, or FINDSET. It depends on the purpose. FIND('-') was used for a number of different purposes:

• To get a set of records to loop through. For this purpose we now use FINDSET. Check out the C/SIDE reference guide for syntax
• To get the first record within the filter. The bad thing about it is that it actually gets all records, so if you only need the first one, you get too much. For this purpose we now use FINDFIRST, which gets just one record. If you want to loop through a set of records, this is not the correct keyword to use. FINDLAST gets only the last one.
• To determine whether records exist within the filter. A better keyword for this purpose is ISEMPTY. So instead of "IF MyRecord.FIND('-')", you would do "IF NOT MyRecord.ISEMPTY". This returns a boolean instead of a set of records. Much easier on the system resources

So in your case, you are going to loop through the records, so you need to use FINDSET. Depending on whether you are going to modify these records, and even modify fields that are part of the filter, you need to set the right parameter values. See the C/SIDE reference guide for more details.

You should really not use FIND('-') anymore.