Filters and SETCURRENTKEY

fve

Hi,

I have the following situation:

Sales Invoice Header, with multiple Sales Invoice Lines.
I would like to do a SETRANGE on the Sales Invoice Lines, to get all lines that are connected to the Sales Invoice Header.

But, for the result subset of Sales Invoice Lines, I would like to work with another key, for functional reasons.

Is it possible to use the SETCURRENTKEY-command, after the SETRANGE-command, without loosing my subset of Sales Invoice Lines?

In coding:

"Sales Invoice Line".SETRANGE("Document No.", "Sales Inv. Header"."No.");
"Sales Invoice Line".SETCURRENTKEY(<other than primary key>);
IF "Sales Invoice Line".FIND('-') THEN BEGIN
...
END

Reason I would like to do this, is because of performance. I figured that it might be useful to do the SETRANGE with the primary key active.
Is this the case, or am I wrong?

lessi

Hi,

It is possible to do that but not a good idea, because filters are apply when executing Find, so it is the last setcurrentkey which is used.

I thnik the best is to filter on the primary key, then copy the lines in a temporary table and use this temp table to sort on the key you need.

Regards.

SteveO

You can just use the secondary key the first time, from the Navision side of things (as opposed to SQL) the primary key is automatically added to every secondary key.

Also, Navision doesn't really have a concept of recordsets. For all intents and purposes, each read you do only returns 1 record. You can do SETCURRENTKEY and SETFILTER (same goes for LOCKTABLE) and nothing happens until you actually do a read from the database.

lessi

Just note that even with SQL, the primary key is added to every secondary index so each of them is unique.

SteveO

I was under the impression that whichever key you marked as the clustered index was added to the end of the secondary keys? This may or may not be the primary key in Navision (most usually it is but perhaps not always...).

Am I perhaps mistaken :?:

lessi

Read in a doc :

It is a standard feature of database design to add all the fields in the primary key to the secondary keys as this facilitates sorting. This extended key is not visible in Navision Attain but can be seen in SQL Server.

So i Think it is true. An other reason to think this is that the goal is not really the same between primary key (unique key) and a clustered key (the way data is store on the disk).

The fact that a clustered key must be a unique key will be confusing, becaus if it is not, SQL will make it unique by adding an increment field.

Just note it is really hazardous to play with clustered key in Navision. A rule is to choose the primary key to be a clustered key (Done in 4.01 i beleive). Changing the clustered key to another key is not a good idea.

DenSter

If you know what you are doing it's not a bad idea to define another key as the clustered index, most of the time it's just not necessary.

To answer the original question... The SETCURRENTKEY results in an 'ORDER BY' statement in the SQL query, and has nothing to do with which index SQL Server uses to retrieve the data. If you set a filter on the document number, SQL Server will decide for itself (and there is nothing you can do about it) which index it will use. So yes you can set a filter on one field, and do SETCURRENTKEY on another index. This will result in a recordset that is filtered on the field value you specify, and it will be sorted in the order of the key that you specify.