How can I efficiently find data in a table WITHOUT using the primary key fields? Example: I want to check whether the “Ext. Doc. No.” of a sales header already exists in the “Sales HEader” table when creating a new sales order. Can’t use GET (because of prim. keys), how could I use FIND? Thanks.
SETRANGE(“Ext. Doc. No.”, ‘xyz’); IF FIND(’-’) THEN… or IF COUNT > 0 THEN… To increase efficiency, you should set the current key of the record to a key containing the “Ext. Doc. No.”.
Do NOT use the count. It’s much to slow! rec.RESET; // Delete old filters on this record rec.SETCURRENTLEY(foo); // Activate current key if filtering on a non primary field. * rec.SETRANGE(foo, bar); // Filter all records with field foo = bar rec.SETFILTER(foo,'<>%1',bar); // Filter all records with field foo <> bar (not equal) IF rec.FIND('-') THEN // If the first records can be found, there is a record within the filter REPEAT foobar := barfoo; UNTIL rec.NEXT = 0; // Get the next record within the filter until no more records can be found * If you want to optimize the search (FIND(’-’)) create a secondary key with the field you are filtering on (foo). But be carefull, don’t go changing and adding key without knowing what you are doing.
Emiel, If you set a filter on field foo for a value of ‘bar’ why do you set another filter for value <> bar? Michael
Thank you very much for your quick help. It works! Markus
It was an example to show Markus he could use both setrange as setfilter. But just in case… Markus, don’t user both statemants, just pick one. rec.SETRANGE(field, value); rec.SETRANGE(field, fromvalue, tovalue); rec.SETFILTER(field,'%1 & %2',value1, value2); // %1 = value1, %2 = value2
Emiel, NP, I thought I was missing something. Thanks…