| Master-Detail Questions [message #482279] |
Wed, 10 November 2010 16:14  |
nathanvijay77
Messages: 22
Registered: October 2008
|
Junior Member |
|
|
I am relatively new to forms and very new to master-detail. version is 6i
I have 3 requirements that I cannot find solution:
1. My detail form has part number as one of the fields and I want to make sure the user can enter a part number only once. How can I make sure user cannot enter it more than once?
2. I want to limit number of detail records for every parent record to 8. How can I accomplish this?
3. I have approval flags at both master and detail. If the user tries to approve the master with some/all detail records unapproved, system should display error message and make the user approve the detail before approving the master.
Appreciate your help.
|
|
|
|
| Re: Master-Detail Questions [message #482333 is a reply to message #482279] |
Thu, 11 November 2010 01:40  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
- The simplest way is to create unique index (that would be a database level). It wouldn't let you save duplicates. However, it isn't very user-friendly because - if you choose to commit at the end - you won't even know you entered the same value twice.
There is another option that requires some coding. I'll try to explain it. An example is based on DEPT table that belongs to Scott's schema. This is a simple tabular form. I want to restrict duplicate department numbers (DEPTNO column).
Under Program Units node, create a package:PACKAGE pkg_idx_t IS
TYPE idxby_type IS TABLE OF dept%rowtype
INDEX BY BINARY_INTEGER;
idxby_tab idxby_type;
END;
PRE-BLOCK trigger select records that already exist in the database (so that you couldn't enter duplicates regarding existing records):begin
for cur_r in (select rownum rn, deptno from dept)
loop
pkg_idx_t.idxby_tab(-cur_r.rn).deptno := cur_r.deptno;
end loop;
end;
Finally, WHEN-VALIDATE-ITEM trigger on the DEPTNO item. It checks whether the newly entered value already exists in the index-by table; if it does, raise an error:declare
row_idx pls_integer := pkg_idx_t.idxby_tab.first;
begin
if pkg_idx_t.idxby_tab.count = 0 then
null;
else
loop
exit when row_idx is null;
if pkg_idx_t.idxby_tab (row_idx).deptno = :dept.deptno then
message('It already exists');
raise form_trigger_failure;
end if;
row_idx := pkg_idx_t.idxby_tab.next(row_idx);
end loop;
end if;
pkg_idx_t.idxby_tab (:system.cursor_record).deptno := :dept.deptno;
end;
- Create a WHEN-CREATE-RECORD trigger:
if :system.trigger_record > 8 then
message('No more records!');
raise form_trigger_failure;
end if;
- How do you approve the master record? If you click a button, then - in a WHEN-BUTTON-PRESSED trigger - loop through all detail records and check whether flag is set. If not, raise an error.
[Updated on: Thu, 11 November 2010 01:40] Report message to a moderator |
|
|
|
| Re: Master-Detail Questions [message #482376 is a reply to message #482333] |
Thu, 11 November 2010 03:49 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
For the first point I prefer to use post.
Stick a call to post in the when-new-record-instance trigger. Then whenever the user navigates between records their current data changes are applied to the database but not committed (so if they choose not to save changes they will be rolled back). Then just use a select against the table from one of the WHEN-VALIDATE triggers to see if that pk value has already been used.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
