Home » Developer & Programmer » Forms » Master-Detail data displayed using LOV (Oracle Forms 10g)
Master-Detail data displayed using LOV [message #496664] Tue, 01 March 2011 18:10 Go to next message
Vachaun22
Messages: 10
Registered: March 2011
Junior Member
I'm extremely new to Oracle all together. I'm working through Guide to Oracle 10g book and have run into a problem.

I have a master-detail form set up. I have an LOV set up on to be able to pick a record from the master table, but it doesn't automatically update the details data block.

What should I be looking for to do that? I thought that maybe I could just call the ON-POPULATE-DETAILS trigger, but apparently that can't be done.

Would it be possible to add a trigger to the field on post change to basically execute the same code in ON-POPULATE-DETAILS? That seems kind of odd to do that, but so far that's the only idea I can come up with at this point...

Suggestions as to what I'm missing? Thanks

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496681 is a reply to message #496664] Tue, 01 March 2011 22:31 Go to previous messageGo to next message
Velcroman
Messages: 5
Registered: February 2011
Junior Member
POST_CHANGE triggers are redundant to maintain compatablility with earlier versions of Forms.
I think you need to re-assess the need to select a value through an LOV attached to the master table that will populate the Details table.

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496697 is a reply to message #496681] Wed, 02 March 2011 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I understood, this might be one way to do that:
- enter query mode in master block
- from LoV, choose a value
- execute query

If master-detail relationship is correctly set (wizard will do that for you, if you used it), both master and detail blocks will be populated with data. Of course, if there are no detail records for chosen master record, detail block will remain empty.
However, if querying is not what you are looking for, what do you do, then? Insert new records? If so:
- populate master block items (LoV or not, doesn't actually matter)
- go to detail block
- all items (that are used to maintain relationship) will also be populated in detail block. Some of them might be hidden, but they will also get their values.
If none of above, could you explain it once again, please?

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496752 is a reply to message #496664] Wed, 02 March 2011 05:19 Go to previous messageGo to next message
Vachaun22
Messages: 10
Registered: March 2011
Junior Member
I used the wizard to create the master-detail blocks.

When I just click on the buttons in the default toolbar to just execute a query that pulls all records from the master table, and step through them the details block updates.

But I have an LOV attached to a field in the master block to be able to select a single record from the master table. However, when I select that single record, the details block doesn't update.

According to the book I have, using

GO_BLOCK( '[block name]' );
EXECUTE_QUERY;

should cause the details block to query and update. However, I can't find a trigger that allows the GO_BLOCK procedure to be executed.

This was actually a problem on a recent test in one of my classes, and we were to use an LOV but have the details update when the value from the LOV is selected. I have yet to figure out how to do it.

Thanks for your help so far.

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496769 is a reply to message #496752] Wed, 02 March 2011 06:41 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have to follow steps I suggested; once again:

  • in master block, enter query mode. If you don't do that (enter query mode) and select value from a LoV, it - actually - means that you are inserting a new master record.
  • go to text item, open list of values, select one of values
  • execute query

Option you mentioned (that uses GO_BLOCK and EXECUTE_QUERY) is used when "master" block is, actually, a control block (i.e. is not based on a table or a view). Then enter_query / execute_query won't work, so you have to explicitly go to detail block and execute query.

Note that - before you execute query - you have to specify which records to fetch. It can be done in PRE-QUERY trigger (block level), such as 
:detail_block.deptno := :control_block.deptno;   -- the one you selected from an LoV
or using SET_BLOCK_PROPERTY and specifying its ONETIME_WHERE clause.

As GO_BLOCK is a restricted procedure, you can't use it in all triggers. If your use a keyboard for navigation, KEY-NEXT-ITEM on LoV text item would do the job (it would fire when you select a value and press <Enter>):
go_block('detail_block');  -- no square brackets! as in your example: '[block name]'
execute_query;

[Updated on: Wed, 02 March 2011 06:41]

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496785 is a reply to message #496664] Wed, 02 March 2011 07:09 Go to previous messageGo to next message
Vachaun22
Messages: 10
Registered: March 2011
Junior Member
I see what you are saying now about the Enter Query - LOV - Execute Query now.

When I perform the actions in that order, it works as expected.

So I guess my next question would be, is there a way to capture the invocation of a LOV window to put the form in enter query mode without having to manually hit the button before pulling up the LOV?

I guess I'm just trying to automate as much as possible to alleviate some of the responsibilities from an end user's perspective.

Oh, and the brackets in my last post were an indication of a variable name, I know they aren't to be there, but I wasn't using a real block name in the post.

Thanks again for all your help.

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496792 is a reply to message #496785] Wed, 02 March 2011 07:53 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
So I guess my next question would be, is there a way to capture the invocation of a LOV window to put the form in enter query mode without having to manually hit the button before pulling up the LOV?

As far as I can tell, no if master block is a data block. Because, although you can enter query mode programatically (for example, using KEY-LISTVAL trigger (which opens list of values) using ENTER_QUERY built-in), once you enter query mode, all other statements are just ignored and form waits for your input.

If master block is a control block, then re-read what I said in the second part of my previous message.

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496794 is a reply to message #496792] Wed, 02 March 2011 07:56 Go to previous messageGo to next message
Vachaun22
Messages: 10
Registered: March 2011
Junior Member
The master block is a data block.

I wonder what my professor was expecting then on our exam. Oh well.

Thanks again for all the help.

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496797 is a reply to message #496794] Wed, 02 March 2011 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you find out what he's after let us know, I'm curious.

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496800 is a reply to message #496797] Wed, 02 March 2011 08:15 Go to previous messageGo to next message
Vachaun22
Messages: 10
Registered: March 2011
Junior Member
Definitely will do.

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496873 is a reply to message #496800] Wed, 02 March 2011 17:08 Go to previous messageGo to next message
Vachaun22
Messages: 10
Registered: March 2011
Junior Member
Ok, so basically the professor just wanted us to create a button on the form that used GO_BLOCK followed by EXECUTE_QUERY. Seems like a cheating way to do it to me, forcing a user to click a button to update the entire form. Much prefer your solution of putting the form in query mode, then using LOV, then executing.

Thanks again.

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496876 is a reply to message #496873] Wed, 02 March 2011 17:46 Go to previous messageGo to next message
Velcroman
Messages: 5
Registered: February 2011
Junior Member
The Professor? - sounds like you work for a uni - I write software for University Office of Research. It's known as 'RHESYS' Research and Higher Education SYStem (www.rhesys.com.au).
You may well like the solution suggested as a more sensible way to provide the facility but you have to remember "The Professor" is your client and the client is ALWAYS right! (wonderful).
If you can not convince the professor that your solution is better, then do it his way and in a little while you will be asked to implement your resolution (probably).

Report message to a moderator

Re: Master-Detail data displayed using LOV [message #496985 is a reply to message #496876] Thu, 03 March 2011 08:36 Go to previous message
Vachaun22
Messages: 10
Registered: March 2011
Junior Member
Ah, sorry about the misunderstanding.

I don't work at the University, I'm attending classes there. We don't use Oracle where I work, we use other SQL servers. I only took this class so I had an opportunity to work with Oracle some.

Report message to a moderator

Previous Topic: how can i populate record using LOV value
Next Topic: Incorrect result on assignment statement
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 16 13:04:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.