Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating ID field at runtime

Posted on 2011-03-09
4
Medium Priority
?
737 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 38

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

715 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