RecordRef: How to select best key programmatically

PrebenRasmussen

The following code shows how to programmatically get the best key given the filters set on a recordref variable.

RecRefGetBestKeyNo(rr : RecordRef) : Integer
//The function returns the index of the best key to use given the applied filters.
intBestKey := 1;

//Enumerate all fields with filter. Temporary record used for speed.
FOR i := 1 TO rr.FIELDCOUNT DO BEGIN
  fr := rr.FIELDINDEX(i);
  WHILE (NOT loctmprecInteger.GET(fr.NUMBER)) AND (j <= 255) DO BEGIN
    rr.FILTERGROUP(j);
    IF fr.GETFILTER <> '' THEN BEGIN
      loctmprecInteger.INIT;
      loctmprecInteger.Number := fr.NUMBER;
      loctmprecInteger.INSERT(FALSE);
    END;
    j += 1;
  END;
  CLEAR(fr);
END;

//Loop through all keys to find best match.
FOR i := 1 TO rr.KEYCOUNT DO BEGIN
  CLEAR(intScore);
  CLEAR(intQty);
  kr := rr.KEYINDEX(i);
  FOR j := 1 TO kr.FIELDCOUNT DO BEGIN
    fr := kr.FIELDINDEX(j);
    IF loctmprecInteger.GET(fr.NUMBER) THEN BEGIN
      //Score for Placement:
      intScore += POWER(2, 20 - j);
      //Score for Quantity:
      intQty += 1;
      intScore += POWER(2, 20 - j) * (intQty - 1)
    END;
    CLEAR(fr);
  END;
  IF intScore > intBestKeyScore THEN BEGIN
    intBestKey := i;
    intBestKeyScore := intScore;
  END;
END;

EXIT(intBestKey);

You just MIGHT think: Hey isn't there a difference between Native and SQL so score should vary by looking at rr.RECORDLEVELLOCKING. Your thoughts on this would be appreciated in the forum, even though the above code covers both key field placement and key field quantity.

The code will also be uploaded to Downloads...

pdj

That looks very interesting! I need such a function as well :-)
I have looked and tried your code and think I found a few problems.

First of all it seems you need to clear j as well when you clear fr. (You could actully clear j instead of fr - it shouldn't be nessesary)

Secondly there there is a major bug in the KeyRef datatype. The KEYCOUNT returns the number of enabled keys, but the KEYINDEX refers to the key number, no matter if it is enabled or not. That means that you in many cases are unable to use the last keys... Very frustrating and I can see a friend of mine reported it to MBS in Feb. 2004 but the problem is still in 4.0. (He was even told it was already reported in LT-596-644-YDGV) This bug almost makes the datatype useless...

My test code was like this:

Cust.SETFILTER("VAT Registration No.",'123');
Cust.SETRANGE("Name 2",'Preben');
RecRef.GETTABLE(Cust);
MESSAGE(FORMAT(RecRef.KEYINDEX(RecRefGetBestKeyNo(RecRef))));

I tried it in W1 4.0 Native and it worked fine (after having inserted the CLEAR(j) after your CLEAR(fr). It correctly picks the last key.

But if I go to the customer table and disable a key, i.e. on SearchName, then it suddenly doesn't work. Can't someone report it again to MBS???

I can't quite understand your point system, but I'll give it a closer look soon. I guess such a function can never be perfect unless it also knows the data spread but it could help a LOT. Things that should be possible to add:
* The datatype of fields NOT with filter but has filterfields after. (i.e. a Boolean doesn't hardly matter (for Native db), but a Decmal is awful)
* The type of filter. (Does it start with "*/.." or includes "|*/..") It would be nice to be able to use GETRANGEMIN and GETRANGEMAX, but they always fail with an error directly to the user :-( - Well, I guess we could use the IF CODEUNIT.RUN in some way, but it would be a mess :-(
* It should know that the primary key is in-directly after all secondary keys.

It's not going to be easy :-(

PS: I'm actually surprised that the rr doesn't have to be a VAR parameter. A normal Rec parameter doesn't transfer filters unless it is VAR...
PPS: The function could actually be called SetBestKey and simply change the key without having a return value.

pduck

pdj wrote:

PS: I'm actually surprised that the rr doesn't have to be a VAR parameter. A normal Rec parameter doesn't transfer filters unless it is VAR...

yes this is a bad feature which tooks me some time for error search

i always use RecordRef.DUPLICATE() on a local RecordRef in the first line when i create a function with a "copied" RecordRef parameter.