Solved

Creating ID field at runtime

Posted on 2011-03-09
4
729 Views
Last Modified: 2012-05-11
So I have a grid which is grouping records based on whether certain fields have filled in or not. The problem though is that certain records can fall into multiple categories. So, what I'm doing is using a SQL union to re-query and tag one instance of the record with a label ("Bid") and the second instance with another label ("Sale") and base grouping off of that.  

Anyways, up to this point everything works fine an dandy. However, now I have the problem of not having a 'unique' key field, since a single record might come up within two or three separate groupings. The best solution I can think of (though that may not be the case) is to just created a separate key field column at runtime which has a seed of 1 and auto increments from there, but, I'm not sure how to code it, or if is possibly a feature built into delphi which can do this for me.

Any help would be appreciated. :)
0
Comment
Question by:Bianca
4 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 35089083
depends on your database type

for instance, with oracle there is analytical functions
for this you could use rank() over or number()
probably using lead() over (or lag() over) too

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407
0
 

Author Comment

by:Bianca
ID: 35089188
Sorry, I guess I should have clarified myself a little more.

This field I want to create would be created at runtime (Formshow) and but NOT be saved to a table, simply used as a temporary key field since my actual ID field is no longer unique--basically a virtual field which is thrown away once the form is closed, since I can't think of any other way to set a key field...
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 35089312

You can create a calculated field on the dataset you are using
Right click on the dataset, click Fields Editor and add the fields you are expecting from your query plus an extra calculated field called ID

then create an event for OnCalcFields to assign the values when the query or dataset is opened

procedure TForm1.ADOQuery1CalcFields(DataSet: TDataSet);
begin
  ADOQuery1ID.Value := Dataset.RecNo;
end;


Having said that, its still much better to get the ID from your query
CalcFields.png
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35101286
Of course, you may also be able to create a uniqe "key"" field by adding a DataSource column to each of the queries with the "Bid" datasource flagged as B and the "Sales data source flagged as S.  Having done that, you can use the unique ID from each of the sources coupled with the DataSource field to provide the unique "Key".  This would let you provide a means for uniquely identifying the rows and even, if desired, updating (or otherwise modifying) the selected row without much of a change in the queries or in the way you are handling the data.

As Geert said, though, a lot of the answer may depend on the target RDBMS.  In some cases, knowing that bit of information ccould provide much cleaner answers.  (For instance, if it is SQL server 2005/2008, the UNIONed data could pass through one last, outer SELECT that includes a RoW_NUMBER function that assigns a unique ROW_NUMBER ID to each row. ;-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Strange behavior when a form is closed 6 68
Magic Software info 18 142
Reconfigure Delphi Install? 2 64
How to load 2 images in same column in Delphi 2 67
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question