Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 2 of 2 total
Thread Self-referencing data with calculated fields
Thu, Sep 6 2018 3:16 PMPermanent Link

ooptimum

I have a db table holdind self-referencing data (using Adjacency list pattern). The table's structure is like this:
Id: integer;
Name: string;
parentId: integer;
In my project I have a dataset referencing this table with an additional calculated field:
parentName: string;
The question is how to calculate it properly? If I use OnCalculateRow event handler like this:

procedure TMyDB.MyTableCalculateRow(Sender: TObject; Column: TDataColumn);
begin
 if Column.Name = 'parentId' then
 begin
   MyTable.InitFind;
   MyTable.Columns['id'].AsInteger := Column.AsInteger;
   if MyTable.Find then
     MyTable.Columns['parentName'].AsString := MyTable.Columns['Name'].AsString;
 end;
end;

I get error message: '"Id" is read-only and cannot be modified'. I guess it's because OnCalculateRow fires on each row during loading data, and not all data is available yet at any such call excluding last row's case, so we cannot perform find. Also I cannot recalculate this field iterating through whole table in dataset's AfterLoad event handler, as Find method also changes dataset's row pointer. I think I can utilize second datasets referencing the same table to solve this problem, but I'd like to avoid this if possible. How do you handle tree-like structures with calculated fields?
Wed, Sep 12 2018 11:57 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com


<< I get error message: '"Id" is read-only and cannot be modified'. >>

Yes, this is because only calculated columns can be modified during the firing of the OnCalculateRow events.

<< How do you handle tree-like structures with calculated fields? >>

I would recommend using a join in the server-side dataset definition to provide this information. The calculated columns really aren't designed for performing lookups.  Is that possible in your case, or is there an "updateability" issue with the dataset if you use a join ?

Tim Young
Elevate Software
www.elevatesoft.com
Image