Solved

Creating ID field at runtime

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mobile Keyboard covers the display of the TMemo 3 113
Strange behavior when a form is closed 6 74
Dynamically Created Query 3 77
DBGrid or StringGrid ? 6 131
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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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