?
Solved

DBGrid Calculated fields

Posted on 2002-05-13
11
Medium Priority
?
476 Views
Last Modified: 2010-04-04
Hi experts,

I need your help.

There is a master-detail form using tblOrder and tblPositions.

tblOrder - master table
Order ID
...

tblPositions - table
PosID
OrderID
ItemID
Quantity
and two calculated fields:
UnitPrice (:=tblItemUnitPrice) //Would be possible here to use directly the tblItemUnitPrice field??
PositionPrice (:=UnitPrice * Quantity)

The tables are connected, MasterSource is srcOrder connected to tblOrder and MasterField is OrderID. There is a 3rd table containing the items data so UnitPrice too.  

tblItem
ItemID
UnitPrice
...

In the Positions DBGrid are listed the positions of the selected order. To calculate the two calculated fields I used the event tblPositionsOnCalcFields but no data appear in the calculated columns of the DBGrid.

Which event(s) have I to use because the cells have to be recalculated after changing the cell's value or changing the recordset (caused by changing the OrderID)?

Thanks for your help! With best regards

Janos
0
Comment
Question by:kacor
  • 6
  • 4
11 Comments
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 7005641
If I look at it quickly, you do the right thing.

Is it possible to post your code? Maybe it is something in the code that is wrong/missing.

0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 7005652
How did you create those calculated fields ?
From within the FieldsEditor ?

Maybe it is better to create  Lookup-Field (UnitPrice),
and  Calculated-Field (PositionPrice).

The Lookupfield can easily be created from within the FieldsEditor. If you are in this editor, just press Ctrl-N to create a new field. Give a name, type and length of the field. Then select the Lookup RadioButton.

The last thing you have to do is define the lookup.
This is done by setting :
  - KeyFields (= field of you PositionTable ItemID)
  - DataSet (tblItems)
  - LookupKeys (ItemID)
  - ResultField (ItemDescription)

It's now easy to drag and drop this field onto your form.
If you do this a new LookupCombobox will be created on your form. Now you give your user the possibility to choose an itm, using its description. Internally the ItemID will be filled in you field itemID of Table Positions.


For you calcualted field, you really should post your code because there I can't see what's wrong.

Best regards,
The Mayor.
0
 
LVL 10

Author Comment

by:kacor
ID: 7007851
Hi, The Mayor,

thanks for your help, sorry for the late answer but I had an urgent to do. I remade in a simplified version all again, and the calculated results are now displayed. I don't know what happened. Now the displayed Unitprices in DBGrid are the same for all items (nearer: they are equal with the UnitPrice of the Item last read in). I don't know how can I make this calculation for each item separately?

The code is as follows:
procedure TDataModule1.tblItemCalcFields(DataSet: TDataSet);
begin
   tblItemUnitPrice.AsFloat := tblArticleUnitPrice.AsFloat;
   tblItemItemPrice.AsFloat := tblItemUnitPrice.AsFloat*tblItemQuantity.AsInteger;
end;

Do you have any suggestions?

wbr

Janos
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Expert Comment

by:oraelbis
ID: 7007931
try:
  tblItemUnitPrice.AsFloat := tblArticleUnitPrice.AsFloat;
  tblItemItemPrice.AsFloat := tblArticleUnitPrice.AsFloat*tblItemQuantity.AsInteger;

Or you can use TQuery component instead of the tables Position and Items (from your first description) with SQL like:
select
 P.*,
 I.UnitPrice,
 (I.UnitPrice * P.Quantity) as PositionPrice
from
 Positions P,
 Item I
where
 P.ItemId = I.ItemId

Or denormalize yuor Position table with adding to it UnitPrice and PositionPrice fields und updating them using database trigger(if database support triggers) or additional code on Item.AfterPost, Position.BeforeInsert events. This can increase perfomance on large tables, but you must write some additional code.
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 7008746
I stil don't understand why you declared this UnitPrice-Field as a Calculated Field.

I suppose you automatically want to have the UnitPrice-Field from table 'tblPostitions' filled with the UnitPrice of table 'tblItems' whenever a record is added to tblPositions, don't you ?

If this is the case then I would recommend to create the UnitPrice-Field in your table as a regular field and fill it from the OnNewRecord-Event of table tblPositions.
There you can run a query which looks in tblItems for the UnitPrice of the Item you want to add and put it in the Value-property of the new tblPostitions-record.


Hope te be of some help.
0
 
LVL 10

Author Comment

by:kacor
ID: 7009442
Thanks oraelbis,
I will test what you proposed


Hi, The Mayor,
my original goal was automatically add the unit price to record, that's right so yet I have some ideas from you and oraelbis to test :-))

After tests I will tell my solutions

Kind regards

Janos
*********************************************************
Support bacteria - it's the only culture some people have
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 7010319
Maybe if you give me your email-address, I can make you a little demo (With an Access-DB).

I'm not sure if I'll be able to do it today, but I'll try.
0
 
LVL 10

Author Comment

by:kacor
ID: 7012042
Thanks for your support
My e-mail address is as follows:
szjano@vnet.hu

Janos
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 7013014
If I have some spare time during the day, I'll set up a little demo and send it to you.

I'll be back ...
0
 
LVL 6

Accepted Solution

by:
wimmeyvaert earned 280 total points
ID: 7013414
Hi Janos,

I just sent you a little demo I uickly made.
It's a master-detail application (Order/OrderLines).
The OrderLine-Table contains a LookupField (in table Items) to automatically let the user select an item when adding an OrderLine-record.

I also added a Calculated Field (Total) to automatically calculate the total unitprice.

I also fill the UnitPrice with the one, found in the related Items-Record.

Hope to be of some help. If you have any questions, please let me know.

The Mayor.
0
 
LVL 10

Author Comment

by:kacor
ID: 7014996
Thanks

wbr

Janos
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This is an update to some code that someone else posted on Experts Exchange. It is an alternate approach, I think a little easier to use, & makes sure that things like the Task Bar will update.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 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