Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DBGrid Calculated fields

Posted on 2002-05-13
11
Medium Priority
?
464 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
[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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

609 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