How to implement a Sequential List in database/ Insert a record in the middle.

{
Does anybody have an idea how to implement such a list in an elegant fashion?

Arthur Hoornweg
}

{
I had a similar problem with a list of delivery addresses that were
delivered in a particular order. The best way is to have a field that allows
you to insert a record between existing values.

==============================================================================
A SQL database has no intrinsic order so inserting between two records
has no meaning.
==============================================================================

What we did was have a long integer that incremented by say 1000 for each
record. When we wanted to insert between two records we just set this
integer to a value between the records we inserted. Then everything is
sorted by that integer and the list comes out the way you want it.

We also had a stored procedure that ran nightly and renumbered the field
back to 1000 increments. Unless there are an awful lot of inserts this works
well. If the range is not big enough then set it to 2000 or 5000 whatever.

Jeff Wright
}

Suppose a condition such that Primary key value once created should not be modified.

{
The best solution I have found to this is to use a floating point number for
the primary key. When you need to insert a record between two existing
records compute the value half way between the existing keys for the key of
the new record.

Bill Todd - TeamB
}

Easy to implement but cannot inserting more number of records because of 15 digits
restriction with float field type.


Here is the solution:

This function will get the fraction portion of a float
Example: If the value is '10.1026', this function will return '0.000'.

function getDeciFrac(aForValue: Double): String;
var
  tmpStr: String;
  i, DeciPos: SmallInt;
begin
  tmpStr := FloatToStr(aForValue);
  DeciPos := Pos('.', tmpStr);

  if DeciPos>0 then tmpStr := Copy(tmpStr, DeciPos+1, Length(tmpStr))
  else tmpStr := '';

  Result := '';
  for i:=1 to (Length(tmpStr)-1) do begin
    Result := Result + '0';
  end;

  Result := '0.'+Result;
end;

This function will compute a float value, which is in between first & second value.
Example: If the value is '10.10 & 10.11', this function will return '10.101'.

{
Does someone know a function which calculates the neighbours of floating point numbers

Volker W. Walter
}

Yes! with little modification this function can be used as Next an Previous
Floting point number.

function getInBewteenfFloat(FirstVal, SecondVal: Double): Double;
var
  DeciFracStr: String;
  tmpFloat: Double;
  runCount: SmallInt;
begin
  Result := 0;
  runCount := 1;

  if ((FirstVal=SecondVal) or
      ((FirstVal=0) and (SecondVal<=1))) then begin
    MessageDlg('Cannot be inserted !',mtError,[mbOK],0);
    Exit;
  end;

  if (SecondVal=0) then begin
    Result := Trunc(FirstVal)+1;
    Exit;
  end;

  if (FirstVal>0) then DeciFracStr := getDeciFrac(FirstVal)
  else DeciFracStr := getDeciFrac(SecondVal);

  while runCount<=15 do begin
    if (FirstVal>0) then begin
      tmpFloat := FirstVal + StrToFloat(DeciFracStr + '1');
    end else begin
      tmpFloat := SecondVal - StrToFloat(DeciFracStr + '1');
    end;

    DeciFracStr := DeciFracStr + '0';

    {
    You cannot compare floating point numbers (as Real) against an exact value.
    There are always errors due to loss of precision, caused by an limited
    number of binary digits (bits) used to represent a decimal value.
    }

    tmpFloat := StrToFloat(FloatToStr(tmpFloat));

    if (tmpFloat>FirstVal) and (tmpFloat      Result := tmpFloat;
      Exit;
    end;

    inc(runCount);
  end;

  if (Length(FloatToStr(Result))>16) then Result := 0;

  if (Result<0) then begin
    Result := 0;
    MessageDlg('Cannot be inserted !',mtError,[mbOK],0);
  end;
end;

You have to create one float field as RecPosNo in the table where you want to
insert record. Set RecPosNo as key field & also as first field along with the
existing key fields. (Employee.db which is available in '\Program Files\Borland\
Delphi 5\Demos\Data' is used for the demo.)

Full source code of this project is attached as a FloatIns.Zip along
with this article. Download & unzip this to a directory. Create an alias "FloatIns"
of paradox type,  Set path to \FloatIns\Data directory. Pl. go through BeforeInsert
& AfterInsert event of  QryEmployee in DataModule to know the use of above function.
Compile & Run FloatIns. Use button Insert to Insert the record in-between &
append to insert the record at the end.

Note: Do not allow user to enter/modify the values of RecPosNo or Primary key.
      Even then if you want this to be entered by the user, necessary care has
      to be taken to avoid duplicate records.

Dedicated to the participants in the borland.public.delphi.database.desktop &
objectpascal forum. Special thanks to Shaw for his proofing and suggestions.
Finally thanks to Bill Tod, who made me to think in this direction.