OraFAQ Forum: Forms » Master detail form [merged 3]

Home » Developer & Programmer » Forms » Master detail form [merged 3] (6i)

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

Master detail form [merged 3] [message #524944]

Tue, 27 September 2011 14:44

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

Hi All,

I am new in doing a oracle form at work using 6i. need a master detail form with master data block with one field which is LOV.

As soon as user selects the data the corresponding details need to displayed.

My current got the master block to work with the Lov but if there 2 records in the table for the same master id it only displays one can someeone help me.

Also the other requirmement the user select the data from master table in master block and insert a new record or update exisiting record in child table

how to accomplish that

thanks for your help

Report message to a moderator

Re: Master detail form [merged 3] [message #524958 is a reply to message #524944]

Wed, 28 September 2011 00:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Let Wizard create both master and detail blocks. It will create all necessary program units (triggers, procedures; relations) which will make most of your problems (inserting, querying, updating, deleting) vanish.

As of list of values and "immediate data fetch", this is how it goes:
- enter query mode
- open list of values
- select a value
- execute query

Report message to a moderator

Re: Master detail form [merged 3] [message #524963 is a reply to message #524958]

Wed, 28 September 2011 01:44

Baranor
Messages: 83
Registered: September 2011
Location: Netherlands

Member

Also, if one of the records has a value that's not in the LOV, forms does not display the record.

Report message to a moderator

Re: Master detail form [merged 3] [message #524985 is a reply to message #524963]

Wed, 28 September 2011 03:56

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Baranor wrote on Wed, 28 September 2011 08:44

Also, if one of the records has a value that's not in the LOV, forms does not display the record.

Hm. Are you sure of that?

MHE

Report message to a moderator

Re: Master detail form [merged 3] [message #524989 is a reply to message #524985]

Wed, 28 September 2011 04:04

Baranor
Messages: 83
Registered: September 2011
Location: Netherlands

Member

Well, at least it didnt for me Wink

Wink

I've written several data conversions from other systems to ours, and we have oodles upon oodles of LOV's in our system... when records were missing, oft it turned out that a value in DB did not correspond with possible values in the LOV. Didn'tg et an error message either, they just did not display. Deleting the value, or updating the LOV solved it. I find this to be one of the most annoying "bugs" in forms 6i... not sure if it still exists in 10+. MApping of other values might help there, but I can't 100% recall if the LOV's have that option... I tend to work from designer.

Report message to a moderator

Re: Master detail form [merged 3] [message #524990 is a reply to message #524989]

Wed, 28 September 2011 04:05

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I'm tempted to try this. I know this is an issue when using list boxes but LOV's? Anyway: I get back to this.

MHE

Report message to a moderator

Re: Master detail form [merged 3] [message #524992 is a reply to message #524990]

Wed, 28 September 2011 04:10

Baranor
Messages: 83
Registered: September 2011
Location: Netherlands

Member

Ho hum... I hope I didnt get my terms messed up. Hahahaha. That would cause a few laughs. Let me check, rather quickly.

List item - Pop list. That's what I mean. Must be the same as a List Box.

However, for an List of Values with a Record Group, without 'validate from list' I had a record disappear on me too. Strangely enough. Not a week ago.

Report message to a moderator

Re: Master detail form [merged 3] [message #524999 is a reply to message #524992]

Wed, 28 September 2011 04:58

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I've tested it quickly with a copy one of our existing applications (so I'm not at liberty to share the FMB, sorry) and I found no problem at all.

Basically I did this:
- a simple multirecord form ( single block, 10 records) containing some codes and descriptions.
- I added one of the hidden audit fields to the canvas of the form.
- I added a LOV to the item. This LOV had "validate from LOV" property set to false and it selects from our user table.
- I compiled and deployed to our test system

I'm sorry I can't share the form with you but I couldn't verify your statement. I'm interested because we have some forms where the LOV's are used to suggest values but not enforce them.

MHE

Report message to a moderator

Re: Master detail form [merged 3] [message #525005 is a reply to message #524999]

Wed, 28 September 2011 05:51

Baranor
Messages: 83
Registered: September 2011
Location: Netherlands

Member

Yeah, the validation property fixes that. My mistake there, bit o miscommunication... LOV's and list items get mixed up in my mind sometimes Wink

Wink

Report message to a moderator

Re: Master detail form [merged 3] [message #525025 is a reply to message #525005]

Wed, 28 September 2011 06:50

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Laughing

It's quite a relief to see that my idea of LOV's is still valid.

MHE

Report message to a moderator

Re: Master detail form [merged 3] [message #525032 is a reply to message #524992]

Wed, 28 September 2011 08:15

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Baranor wrote on Wed, 28 September 2011 05:10

List item - Pop list. That's what I mean. Must be the same as a List Box.

However, for an List of Values with a Record Group, without 'validate from list' I had a record disappear on me too. Strangely enough. Not a week ago.

And I thought it was just me or my version.
Yes, I had this happen and couldn't figure out why my execute query was not returning any rows until I fiddled with the List Item and found the problem.
And yes, Forms 10g exhibits the same behavior.

[Updated on: Wed, 28 September 2011 08:18]

Report message to a moderator

Re: Master detail form [merged 3] [message #525069 is a reply to message #525032]

Wed, 28 September 2011 13:02

Baranor
Messages: 83
Registered: September 2011
Location: Netherlands

Member

Ah great... well, it makes sense in a weird way, I mean the list item doesnt know what to display, but I'd prefer a nasty error to no record... if you've just converted 20.000 records and 40 are missing, its hard to spot them Wink

Wink

Report message to a moderator

Re: Master detail form [merged 3] [message #525393 is a reply to message #525069]

Sun, 02 October 2011 04:42

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

It might happen with a text item with an LOV with validate from list set, when the text item isn't a DB item. Assuming the item is populated by post-query, if the value isn't in the LOV you'll get an error. An errors that occur will post-query processing is running will prevent the record from displaying.
If it's a DB item that shouldn't happen as validate from list shouldn't fire.

As for list items - Form Builder help does say that'll happen (somewhere). If you set the mapping of other values property it won't.

Report message to a moderator

Re: Master detail form [merged 3] [message #525424 is a reply to message #524944]

Mon, 03 October 2011 01:03

alexmoore
Messages: 24
Registered: October 2011

Junior Member

hey,doing work with master and detail form is quite complicated with as a beginner. there is so many validations must be required for it and somehow knowledge of sql/plsql is must be required .

Report message to a moderator

Re: Master detail form [merged 3] [message #525494 is a reply to message #525424]

Mon, 03 October 2011 08:55

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

Hi

I am still not able to do the same. My text item is DB item

Can someone please help me if there any steps i should do

Report message to a moderator

Re: Master detail form [merged 3] [message #525544 is a reply to message #525494]

Mon, 03 October 2011 13:23

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Did you follow Littlefoot's advice?

Report message to a moderator

Re: Master detail form [merged 3] [message #525557 is a reply to message #525544]

Mon, 03 October 2011 13:42

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

See when i open the form from apps the user clicks in master block text item and lov appears the select a value and then corrospending details block should populate. I wrote the post query trigger and asign teh value now the issue is if the details has 2 records it only displays one. Also when the user is not able to enter data in the details block table

Report message to a moderator

Re: Master detail form [merged 3] [message #525562 is a reply to message #525557]

Mon, 03 October 2011 13:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't know what you did; how many forms (especially master-detail ones) did you create so far?

I suggest you to use Data Block Wizard. Don't be ashame, people use it frequently. Smart guys and girls from Oracle put a lot of effort into it so that you (we) wouldn't have. Forget about what you've done so far, create a brand new form. Use a wizard, follow its suggestions. If your data model is well made (constraints and stuff), a master-detail form will run p e r f e c t l y. As I said, all its functionalities will be there, absolutely no need for further coding.

Once you make it work, feel free to make it fancy (such as: create lists of values, rearrange items, create POST-QUERY triggers to populate non-database items, whatever). Save frequently, compile & test frequently.

Because, the way it is now, it is all wrong. Your words should have been:Me pretending to be you

See, when I open the form from apps, the form is in "enter query" mode. User navigates to the master block's text item and opens list of values, then selects a value and "executes a query" - corresponding detail block's records are populated.

Report message to a moderator

Re: Master detail form [merged 3] [message #525569 is a reply to message #525562]

Mon, 03 October 2011 14:44

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

This is the first form i am working on

I create the blocks with wizard only

IS there any particular steps i should do i appericate your guidance

Report message to a moderator

Re: Master detail form [merged 3] [message #525605 is a reply to message #525569]

Tue, 04 October 2011 02:10

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"Particular steps" might depend on what you have. If tables (you use to create a master-detail form) are in relation in the database (so that detail (child) table has defined foreign key(s) that point to master (parent) records), then the Wizard is smart enough to do everything on itself. Otherwise, you need to specify columns (items) you'll use to enforce relationship between these two blocks.

I can't think of any other steps necessary to create a working master-detail form using the Wizard.

Report message to a moderator

Re: Master detail form [merged 3] [message #526530 is a reply to message #525605]

Tue, 11 October 2011 08:33

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

hi

I have tried n number of times to do through wizard.

My scenario i have 2 tables in the database

First table has 2 columns with one being the primary key
second table the primary key of first is foreign key but no primary key.

I open the template fmb. then deleted the master block and detail block. create new datablocks through the wizard and create the relation.

help pelase

Report message to a moderator

Re: Master detail form [merged 3] [message #526540 is a reply to message #526530]

Tue, 11 October 2011 09:11

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

And what is it doing that it shouldn't? Or not doing that it should?

Report message to a moderator

Re: Master detail form [merged 3] [message #526543 is a reply to message #526540]

Tue, 11 October 2011 09:18

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

i have lov on the master block that selects the data it should populate the detail block but it does not

Report message to a moderator

Re: Master detail form [merged 3] [message #526545 is a reply to message #526543]

Tue, 11 October 2011 09:21

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

alos i would like to insert the data from the detail block in the table not able to save it

Report message to a moderator

Re: Master detail form [merged 3] [message #526550 is a reply to message #526545]

Tue, 11 October 2011 09:34

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

neworaclest wrote on Tue, 11 October 2011 15:18

i have lov on the master block that selects the data it should populate the detail block but it does not

That's not standard functionality and is difficult to implement. I'd get the basics working first.
So to get data - go into enter query mode. Enter some criteria. Press execute query.

neworaclest wrote on Tue, 11 October 2011 15:21

alos i would like to insert the data from the detail block in the table not able to save it

Are you getting an error?

Report message to a moderator

Re: Master detail form [merged 3] [message #526562 is a reply to message #526550]

Tue, 11 October 2011 10:06

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

If i make the master block field as textitem and press f11 it does not return any data

the error insert is duplicate value

Report message to a moderator

Re: Master detail form [merged 3] [message #526563 is a reply to message #526562]

Tue, 11 October 2011 10:11

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Are you getting an error when you press F11?

As for the insert error - stop trying to insert data you've already inserted.

Report message to a moderator

Re: Master detail form [merged 3] [message #526570 is a reply to message #526563]

Tue, 11 October 2011 10:40

neworaclest
Messages: 9
Registered: September 2011
Location: canada

Junior Member

well it not duplicate value and there is no primary key in detail table

Report message to a moderator

Re: Master detail form [merged 3] [message #526572 is a reply to message #526570]

Tue, 11 October 2011 10:48

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then it's probably failing to insert into the master table and never getting as far as the detail table.
If when you get the error you click on the help menu and then display error - you'll get a pop-up box showing the exact oracle error.
See what it says.

Report message to a moderator

Previous Topic:	Java in Forms: Exception_.toString
Next Topic:	Numeric input in forms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 23:18:29 CST 2025