Solved

Creating ID field at runtime

Posted on 2011-03-09
4
734 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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