Home » Developer & Programmer » Forms » How to update detail records when master record changes (Forms [32 Bit] Version 6.0.8.11.3)
How to update detail records when master record changes [message #328227] Thu, 19 June 2008 07:14 Go to next message
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.
Re: How to update detail records when master record changes [message #328292 is a reply to message #328227] Thu, 19 June 2008 11:26 Go to previous messageGo to next message
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)

Re: How to update detail records when master record changes [message #328317 is a reply to message #328292] Thu, 19 June 2008 13:40 Go to previous messageGo to next message
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).
Re: How to update detail records when master record changes [message #328590 is a reply to message #328227] Fri, 20 June 2008 10:15 Go to previous messageGo to next message
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 301 times)
Re: How to update detail records when master record changes [message #328880 is a reply to message #328590] Mon, 23 June 2008 03:53 Go to previous messageGo to next message
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.



Re: How to update detail records when master record changes [message #330335 is a reply to message #328227] Sun, 29 June 2008 13:37 Go to previous messageGo to next message
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 278 times)
Re: How to update detail records when master record changes [message #330381 is a reply to message #328227] Mon, 30 June 2008 00:46 Go to previous messageGo to next message
itech
Messages: 168
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.
Re: How to update detail records when master record changes [message #330401 is a reply to message #330335] Mon, 30 June 2008 01:41 Go to previous messageGo to next message
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.
Re: How to update detail records when master record changes [message #337164 is a reply to message #330401] Wed, 30 July 2008 01:31 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: send email with attachment in from 6i using smtp server
Next Topic: Insert/Update by Oracle form
Goto Forum:
  


Current Time: Fri Dec 09 04:00:02 CST 2016

Total time taken to generate the page: 0.18974 seconds