DBGrid Calculated fields

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
LVL 10
kacorretiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wimmeyvaertCommented:
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
wimmeyvaertCommented:
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
kacorretiredAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

oraelbisCommented:
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
wimmeyvaertCommented:
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
kacorretiredAuthor Commented:
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
wimmeyvaertCommented:
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
kacorretiredAuthor Commented:
Thanks for your support
My e-mail address is as follows:
szjano@vnet.hu

Janos
0
wimmeyvaertCommented:
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
wimmeyvaertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kacorretiredAuthor Commented:
Thanks

wbr

Janos
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.