Hey all, just a small question: when i use SETCURRENTKEY, filter the table and use SETCURRENTKEY with another key, does the second SETCURRENTKEY revoke the first SETCURRENTKEY ? Example: table.SETCURRENTKEY(birthdate); table.SETRANGE(birthdate, 01011963D, 31121963D); table.SETCURRENTKEY(name); table.SETRANGE(name, ‘Weinreich’); does this example show me all customers named “Weinreich”, who are born in 1963 ? Or does it simply show me every customer named “Weinreich”, disregarded the birthdate ? Of course, i could create a key containing both fields. But if i don’t have such a key and don’t want to create it ?!..
This code filters the table by birthdate and name.The table is ordered by name. Regards, Anu
Hi Stefan
quote:
table.SETCURRENTKEY(birthdate); table.SETRANGE(birthdate, 01011963D, 31121963D); table.SETCURRENTKEY(name); table.SETRANGE(name, ‘Weinreich’);
is the same as table.SETCURRENTKEY(name); table.SETRANGE(birthdate, 01011963D, 31121963D); table.SETRANGE(name, ‘Weinreich’); The table filters are not used until you retreive data from the database.
thanks Anu and Janus. I was asking for because we have a table with more than 10 million records in. Now i need a special analysis for this table, but i don’t have a required key. I don’t want to create a key for just this analysis. You can imagine how long it would take if i can’t use a key with the needed fields in it. @Janus: wouldn’t your example take a lot of time because the database has to run through every record because field “birthdate” is not part of the key ?
Setting key discards the previous key, but doesn’t discard the filter. That means, setting key to name doesn’t help while you use record that is filtered by name AND by date. To achieve record quickly you shoud set key that has fields name and date. It takes lot of time to create such a key, therefore your analyze is quick. Anu
Hi As a general rule when using filters on a large table and which key to select, you should choose a key which eliminates then maximum number of records in the filter. So if you have several filters to set and can not select a key with the same fields then choose the key which will match the fewest records to the filters. In Janus’s example Navision would select all the records where name = “Weinreich”, which is quick because we have a key and only then select birthdate. As we are only looking at the birthdate of those named “Weinreich” this to should be quick. The order the filters are applied has nothing to do with how navision processes the table. If you do not set a key navision seems to “guess” at a key and not use the primary key as you may expect; it also tend to guess wrong if you have more than one filter. Paul Baxter