Solved

DBGrid Calculated fields

Posted on 2002-05-13
11
402 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now