Home » Developer & Programmer » Forms » Populating the Results in the item :Problem
Populating the Results in the item :Problem [message #137319] Wed, 14 September 2005 11:36 Go to next message
hamdard
Messages: 37
Registered: July 2005
Member
Hi,
I'm trying to solve this problem from many hours but of no avail. Anybody please help.

I have a master and detail form.

I have one number column, total_price in the master form.

I have 3 number columns in the detail form.
say, price, discount, total.
the total column is calculated automatically whenever user enters the discount and moves out of the discount item.

As detail block is a multi-record block, user can enter the prices more than once.

I need to calculate the SUM(total) in the detail block and populate this sum to the total_price in master form.

I'm applying this logic.
Post-Text-Item trigger on total (detail-block)
:GLOBAL.g_total := 0;
	IF :System.CURSOR_RECORD = 1 THEN
		:GLOBAL.g_total := :my_detail_block.total;
		:my_master_block.total_price := :GLOBAL.g_total;
		ERASE('GLOBAL.g_total');
	ELSIF
		:SYSTEM.CURSOR_RECORD = 2 THEN
		:GLOBAL.g_total := :my_detail_block.total;
		:my_master_block.total_price := :my_master_block.total_price + :GLOBAL.g_total;
		ERASE('GLOBAL.g_total');
	ELSIF
		:SYSTEM.CURSOR_RECORD = 3 THEN
		:GLOBAL.g_total := :AD_CONTRACT_DETAIL.nett_price;
		:my_master_block.total_price := :my_master_block.total_price + :GLOBAL.g_total;
		ERASE('GLOBAL.g_total');
	END IF;

This code is only working when user creates the new record. But when the user updates the record, you can see that this code will not work.

Please suggest me a proper way. I want the total_price column in the master form always show the sum of total column in detail block. Whether the user inserts a new record or updates the record.

Please help.

Thank You.

Report message to a moderator

Re: Populating the Results in the item :Problem [message #137367 is a reply to message #137319] Wed, 14 September 2005 20:02 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I don't know which version of forms you are using.

If you are using 6i or later then you can use the 'Calculation' facility to populate your detail-record 'total' column.

I don't know whether that can be used to populate your your master-record 'total-price' field. (I may get to experiment today, but then again I may not.)

Traditionally, to populate your master-record 'total-price' you would have a 'select' statement which summed ALL the detail records ALL the time.
select sum(total)
  into :my_master_block.total_price 
  from detail_tbl
 where first_key = master_tbl.first_key;
This way if you have 30 detail records you don't have to worry that only 10 have been displayed.

You have a few choices as to in which trigger you place this code. Maybe in the When-Validate-Item of the detail 'total' or perhaps at the When-Validate-Record for the block. I would experiment by placing a 'message' statement in my triggers and see which ones fire as I navigate up and down the detail list and also during the post-query phase as you DON'T want the detail 'total' populated EVERYTIME you retrieve the records.

David

Report message to a moderator

Re: Populating the Results in the item :Problem [message #137506 is a reply to message #137367] Thu, 15 September 2005 07:19 Go to previous messageGo to next message
hamdard
Messages: 37
Registered: July 2005
Member
Hi,
Thanks alot Mr. David.
Before posting my question I tried what you have suggested and I tried it in different ways but it was not working properly.
Anyhow, after posting my question, I was trying to solve it and I got the solution.
I created a new item in the detail block and made it a SUMMARY item and calculated the sum for my required item into this new item. and then in post item trigger, I just copied the value from the newly created item to the total_price item in master form and it's working like a magic.
Now, no matter if the user is entering a new record or modifying an existing record, the total_price item in the master form will be synchronized with the resultant sum of the price in detail block.

Thank you again so much for your precious reply.

regards.

Report message to a moderator

Re: Populating the Results in the item :Problem [message #137637 is a reply to message #137506] Thu, 15 September 2005 17:45 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
You're quite right in that the code I gave would not work. Where I had 'master_tbl.first_key' it should have been ':my_master_block.first_key'.

David

PS "it's working like a magic" should be "it's working LIKE magic". 'Magic' is a verb, not a noun. It can also be used as an adjective.

Report message to a moderator

Re: Populating the Results in the item :Problem [message #137643 is a reply to message #137637] Thu, 15 September 2005 18:59 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I was just reading the Forms 6.0 reference manual (again) and there is a 'Summarized Item' property defined in it which should handle your requirement. A restriction is that the block containing the summarized item is "If the summarized item does not reside in the same block as the summary item, the summary item must reside in a control block with the Single Record property set to Yes."

Read the whole entry for a better idea.

David

Report message to a moderator

Re: Populating the Results in the item :Problem [message #137847 is a reply to message #137643] Sat, 17 September 2005 10:48 Go to previous messageGo to next message
hamdard
Messages: 37
Registered: July 2005
Member
hi,
Thank you sir for correcting my english.
You'r right. I checked the Summary Item and the solution is working now.

Thanks again.

Report message to a moderator

Re: Populating the Results in the item :Problem [message #355539 is a reply to message #137637] Fri, 24 October 2008 17:51 Go to previous messageGo to next message
wdv12345
Messages: 2
Registered: October 2007
Location: Tegucigalpa
Junior Member
Just a brief grammar lesson for you both:

Magic is not a verb, but an uncountable noun and adjective. The expression "It's working like magic." is correct, though. Look at this other sentence: Magic is what he does for a living.

djmartin, thanks a lot for your advice at every time we have bugs messing around our programs.

Report message to a moderator

Re: Populating the Results in the item :Problem [message #356208 is a reply to message #355539] Thu, 30 October 2008 00:01 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I stand corrected. Thank you.

David

Report message to a moderator

Previous Topic: data not displaying
Next Topic: how to check the input value : number or string?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 10 00:49:00 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.