OraFAQ Forum: Forms » How to update detail records when master record changes

Home » Developer & Programmer » Forms » How to update detail records when master record changes (Forms [32 Bit] Version 6.0.8.11.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to update detail records when master record changes [message #328227]

Thu, 19 June 2008 07:14

apaun
Messages: 4
Registered: June 2008

Junior Member

Hello!
I need a solution for updating all detail records in a master-detail form when a value in the master record is changed.

I have a master-detail data block as follows:

Master
MASTER_LINE_ID CURRENCY CURRENCY_RATE

Detail
MASTER_LINE_ID PRICE_IN_EURO PRICE_IN_CHOSEN_CURRENCY

All the items in the above blocks are database items.

The detail block item PRICE_IN_CHOSEN_CURRENCY = PRICE_IN_EURO* CURRENCY_RATE and it should update automatically when the user changes either PRICE_IN_EURO or CURRENCY_RATE.

When the user changes the currency_rate in master data block (and several detail records exist) I want to automatically update the contents of PRICE_IN_CHOSEN_CURRENCY item for ALL records in the detail block.

I searched the forum for a solution to this problem but couldn’t find one so any help would be appreciated.

Report message to a moderator

Re: How to update detail records when master record changes [message #328292 is a reply to message #328227]

Thu, 19 June 2008 11:26

Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium

Member

it's not that difficult
in your detail block write a when-validate-item
on your price-in-euro field with something like this in it :

:currency_euro.price_in_chosen_currency := :currency_euro.price_in_euro * :currency.currency_rate;

now since database items can be calculated items as well
change the calculation mode in your detail block for price_in_chosen_currency to Formula
and insert the following formula :
:currency_euro.price_in_euro * :currency.currency_rate

it's that simple Smile

as soon as your user navigates out of curreny_rate in your master block all details records will be changed

(it's me again, no code no trigger and still doing more than most people in forms Smile

)

Report message to a moderator

Re: How to update detail records when master record changes [message #328317 is a reply to message #328292]

Thu, 19 June 2008 13:40

apaun
Messages: 4
Registered: June 2008

Junior Member

Martin thanks for replying to my post, the solution you gave is good but incomplete.
In addition to what you explained, I also want when the user changes CURRENCY_RATE in the master record and several detail records already exist, to automatically update the PRICE_IN_CHOSEN_CURRENCY item OF ALL the existent detail records.
Using a WHEN_VALIDATE_ITEM trigger on CURRENCY_RATE (master record) and setting the value of PRICE_IN_CHOSEN_CURRENCY from within this trigger results in only one detail record being updated. I should loop through all the detail records and update them but to do this I need to use GO_BLOCK ('detail') witch is not allowed in a WHEN_VALIDATE_ITEM trigger.

Also, from what I have read, database items can not be calculated items (if I use calculation on a database item I get an error when I try to compile the form).

Report message to a moderator

Re: How to update detail records when master record changes [message #328590 is a reply to message #328227]

Fri, 20 June 2008 10:15

Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium

Member

you should have read my solution more carefully, where does it state that you need a WVI trigger on currency_rate ???
you need one in your detail item price-in-euro to solve half your problem
the other half is solved by using calculated items
gives us your compilation error

a database item can be a calculated item, type formula
if you don't believe me, have a look at my fmb

Attachment: currency.fmb
(Size: 92.00KB, Downloaded 1300 times)

Report message to a moderator

Re: How to update detail records when master record changes [message #328880 is a reply to message #328590]

Mon, 23 June 2008 03:53

apaun
Messages: 4
Registered: June 2008

Junior Member

Hello Martin,

I have to disagree with you on using calculated items as database items.

I ran the form you attached and yes, form builder lets you put calculations on a database item, even compiles the form without error, but when you save the data in the form, the item that has calculations doesn’t get saved in the database (in our case PRICE_IN_CHOSEN_CURRENCY field from CURRENCY_EURO table).

In the attached image you can see two screenshots that prove that as well as a screenshot from FORM BUILDER’s help that states “Calculated items cannot be database items”.

I need to save the calculated items in the database (even if it’s not the brightest idea but it’s a requirement I have to conform to).

To run your form I created a sequence and added for the ID item’s initial value property :SEQUENCE.CURRENCY_SEQ.NEXTVAL value.

As for the compilation error I made a mistake for wich I apologize. I don’t get a compilation error, but when I try to execute query to populate the data blocks I get “FRM-41370: Cannot modify calculated item %s.%s.”.
My form runs on HP-UX operating system. I saw that on Window I don’t get that message (but I don’t get the calculated item saved either).

The WVI trigger on currency_rate was not a misunderstanding of your solution, was just the only other way of updating detail calculated items I could figure besides using calculated items.

Attachment: Screenshots.jpg
(Size: 120.90KB, Downloaded 879 times)

Report message to a moderator

Re: How to update detail records when master record changes [message #330335 is a reply to message #328227]

Sun, 29 June 2008 13:37

Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium

Member

looks like I made a fool out of myself Smile

sorry about that

write a WVI on the Rate field that creates a timer to loop through your detail block

or have a look at my new form :

Attachment: currency.fmb
(Size: 100.00KB, Downloaded 1220 times)

Report message to a moderator

Re: How to update detail records when master record changes [message #330381 is a reply to message #328227]

Mon, 30 June 2008 00:46

itech
Messages: 173
Registered: May 2008
Location: Fsd, Pakistan

Senior Member

there is another work out.

in the detail block, create another display item(non database), write the formula in it. that multiply by the rate column of the master.

in the when validate record trigger of the detail block you can assign the value of displayed item to the database column. so that the values will be inserted/updated in the database when committing changes.

additional u should make the actual column visible NO

if u need further assistance. do inform.

Report message to a moderator

Re: How to update detail records when master record changes [message #330401 is a reply to message #330335]

Mon, 30 June 2008 01:41

apaun
Messages: 4
Registered: June 2008

Junior Member

Hello,

Martin thanks, this time you hit bull's eye, it's just what I needed! You didn't made a fool out of yourself, it's just a really strange behaviour of Forms.
This solves my problem, no further assistance needed.
Thanks again.

Report message to a moderator

Re: How to update detail records when master record changes [message #337164 is a reply to message #330401]

Wed, 30 July 2008 01:31

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

I haven't read this thread very closely but couldn't you use a timer that does an 'update' in the database, a 'standar.commit', then 'go_block' to the subordinate block and do an 'execute_query'?

David

Report message to a moderator

Previous Topic:	send email with attachment in from 6i using smtp server
Next Topic:	Insert/Update by Oracle form

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 10:01:18 CST 2025