Select highest integer value

tompynation

Imagine that you have a table with following fields:

Enabled Field No. Field Name Data Type Length Description
Yes 1 ID Integer
Yes 2 ReceptID Integer
Yes 3 Regelnr Integer
Yes 4 ReceptItem Text 50
Yes 5 Hoeveelheid Decimal
Yes 6 Percentage Decimal
Yes 7 ExtraOmschrijving Text 150
Yes 8 ToonAlsVolumeJN Boolean
Yes 9 ToonTussentotaalJN Boolean

I need a method inside this table which will return me the
highest integer value of the field Regelnr...

This function should receive a filter, namely the ReceptID

So for a given receptID i need the highest Regelnr

How do you do this?

Xypher

Like so...

tableVar.RESET;

//Sort by Regelnr (make sure this key exists)
tableVar.SETCURRENTKEY(Regelnr);

//Filter through only specified Receipt ID
tableVar.SETFILTER(ReceiptID,'%1',specific_ReceiptID_var);

//Find last record (highest integer)
IF tableVar.FINDLAST THEN
  MESSAGE('The highest integer record for ReceiptID %1 is: %2!',
          tableVar.ReceiptID,tableVar.Regelnr);

kriki

This would be faster, but you need the index:

recTheTable.RESET;
recTheTable.SETCURRENTKEY(ReceiptID,Regelnr);
recTheTable.SETRANGE(ReceiptID,specific_ReceiptID_var); //Avoid SETFILTER when possible, but use SETRANGE
IF recTheTable.FINDLAST THEN
  MESSAGE('The highest integer record for ReceiptID %1 is: %2!',
          recTheTable.ReceiptID,recTheTable.Regelnr);

If you don't have the index, but you have another index that starts with ReceiptID, you can also use that, but the code gets somewhat more complex, but you avoid a new index:

intRegelnr := 0;
recTheTable.RESET;
recTheTable.SETCURRENTKEY(ReceiptID); //takes the first index that starts with "ReceiptID"
recTheTable.SETRANGE(ReceiptID,specific_ReceiptID_var); //Avoid SETFILTER when possible, but use SETRANGE
IF recTheTable.FINDSET THEN
  REPEAT
    IF intRegelnr < recTheTable.Regelnr THEN
      intRegelnr := recTheTable.Regelnr;
  UNTIL recTheTable.NEXT = 0;
MESSAGE('The highest integer record for ReceiptID %1 is: %2!',
          recTheTable.ReceiptID,intRegelnr);

This last is slower in the reading then the first, but it avoids the overhead of an extra index that makes it slower for writing.

tompynation

great works nice... thanks