Solved

Creating ID field at runtime

Posted on 2011-03-09
4
718 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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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:
ewangoya earned 500 total points
Comment Utility

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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now