Home » Developer & Programmer » Forms » Check if details exist before saving master record.. (Forms 11g)
Check if details exist before saving master record.. [message #628056] Wed, 19 November 2014 10:09 Go to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
I have a form that has a master block that lets you enter multiple records. When one of these master records has a "code" field populated with a specific value, the details block needs to have at least one row entered in it as well (you can enter more than one detail row... if not for that, this would be easy to do).

So I've been spinning my wheels to try and find a way to have the form check to see if details exist before saving a master row to the database. I've tried a variety of things. I've mostly been trying to figure out a trigger where this would make sense and to be able to iterate or count the rows sitting in the detail block for that specific master record that is about to be saved.

I can't use GO_BLOCK within the pre-insert, pre-update, commits, etc, of the master record, as that is not a legal function to call in these triggers. So that prevents me from being able to do anything I've thought about (using LAST_RECORD, or checking the value of a field in that block against null, or using COUNT_QUERY against this block).

Does anyone have any ideas? Is this possible? Of course the big problem is it's sort of a "chicken before the egg" thing, where you can't have any detail committed in the database of course without the master existing first, and the requester of this change wants the form to enforce that details have been entered (at least one row) if the master has been saved as a specific code.

I appreciate any help anyone can give, but I'd like to avoid anything too overly complicated, if possible (if it gets to be something that will be a lot of work, I'll push to not have this requirement in place).

Thanks!
Re: Check if details exist before saving master record.. [message #628059 is a reply to message #628056] Wed, 19 November 2014 10:37 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
One option would be to use a calculated field in each block that lists the number of rows in the block. Then you can easily check the value of this calculated field in the Pre-Insert trigger of each block or even in a Module level Pre-Insert (since you don't need to call a restricted built-in when evaluating the calculated field in the trigger). This is not that complicated and not that much code...

Craig...
Re: Check if details exist before saving master record.. [message #628061 is a reply to message #628059] Wed, 19 November 2014 10:58 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
OK I'll take a look at that.. thanks for the suggestion. There's one thing I'm not sure about re: this, but I'll take a stab at it first and see if I can make it work this. I'll post back later.

Thanks again
Re: Check if details exist before saving master record.. [message #628072 is a reply to message #628056] Wed, 19 November 2014 16:32 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
OK I finally got this count column working to an extent. When you click through records, the count updates how it should. If you add/remove detail records (even without commit), the count updates as it should. That's perfect.

Here's the problem though... the master block has multiple records shown. My pre-update (or post-update trigger) is where I put the code to check this count value before committing into the database.

If I have 4 records (for example) on screen populated showing and I go and do something like this:
- Update the code on row 3...
- Click on row two
- Hit save..
..

What happens is, it will use the details count of row 2 in the pre(or post)-update trigger where it verifies this count. It seems that this isn't the right place to do this, or I can't do this. Is there somewhere else I can put the trigger?

Hopefully I explained the problem well enough, but basically the problem is that if the row you're updating isn't the same row as where you clicked when you hit save, then it doesn't work.
Re: Check if details exist before saving master record.. [message #628075 is a reply to message #628072] Wed, 19 November 2014 16:57 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
Actually I have one thought, I'll try. That is to use the WHEN-NEW-RECORD-INSTANCE trigger of master block.. if they switch records, it will check the count and force them to put in some details first if count is 0. I'll see if that works for me. I think this will miss scenarios, possibly.. not sure. but it is definitely a step in the right direction.


EDIT: OK, that sucks, that didn't work, because "WHEN-NEW-RECORD-INSTANCE" fires after the record has changed, meaning it's now looking at the next record (irrelevant to the situation).. maybe PRE-RECORD? Will try that.

[Updated on: Wed, 19 November 2014 17:00]

Report message to a moderator

Re: Check if details exist before saving master record.. [message #628153 is a reply to message #628075] Thu, 20 November 2014 08:42 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Quote:
EDIT: OK, that sucks, that didn't work, because "WHEN-NEW-RECORD-INSTANCE" fires after the record has changed, meaning it's now looking at the next record (irrelevant to the situation).. maybe PRE-RECORD? Will try that.

Actually, the When-New-Record-Instance: Fires when the input focus moves to an item in a record that is different than the record that previously had input focus. Specifically, it fires after navigation to an item in a record, when Oracle Forms is ready to accept input in a record that is different than the record that previously had input focus. This trigger fires whenever Oracle Forms instantiates a new record.

If you want your code to execute when a user navigates to the block as well as when they navigate to a new record in the block, perhaps a combination of the When-New-Block-Instance trigger and the When-New-Record-Instance trigger is what you need. Just write a procedure that can be called from both triggers.

Craig...
Re: Check if details exist before saving master record.. [message #628159 is a reply to message #628153] Thu, 20 November 2014 10:40 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
I might not have made the new problem clear. If the user enters a master row in and then clicks to go out of that row, I want to check the record that they are leaving to see whether it had details records. "WHEN-NEW-RECORD-INSTANCE" wasn't working for me because it was checking if the new record you clicked into had details, not the one you clicked out of. I think WHEN-NEW-BLOCK-INSTANCE would have a somewhat similar problem for me (it would check for details in the new block, not the old block).

I tried a bunch of stuff, including pre-record/post-record triggers. Post-record was about as close to what I needed as it seemed to get, but it still had issues. I think I'm throwing in the towel on this one after trying a bunch of stuff. Might revisit it later, but I think I'm spending too much time on it right now.

I appreciate you trying to help out though Craig.. thanks.
Re: Check if details exist before saving master record.. [message #628177 is a reply to message #628159] Thu, 20 November 2014 16:25 Go to previous message
mughals_king
Messages: 391
Registered: January 2012
Location: pakistan
Senior Member
i used PRE-INSERT trigger in Master block and i found 100% Ressult is your requirement is different thn this below code?? just see in the image.

DECLARE
    A VARCHAR2(20);
BEGIN
   :SYSTEM.MESSAGE_LEVEL := '20';
         A := GET_RECORD_PROPERTY(1,'a1_dtl',STATUS);
IF NOT FORM_SUCCESS THEN
         A := 'NEW';
END IF;
   :SYSTEM.MESSAGE_LEVEL := '0';
IF A = 'NEW' THEN
    MESSAGE('Enter Detail Rercord First');
    MESSAGE('Enter Detail Rercord First');
 RAISE FORM_TRIGGER_FAILURE;
 END IF;
END;


Regard
Mughals

[Updated on: Thu, 20 November 2014 16:59]

Report message to a moderator

Previous Topic: Display customer Bill_To_Address fonts in red colour
Next Topic: Records getting deletd if not selected
Goto Forum:
  


Current Time: Thu Dec 14 04:54:20 CST 2017

Total time taken to generate the page: 0.01794 seconds