Comparing input in multi-record form, to previous entries [message #685938] |
Fri, 29 April 2022 15:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/30e0da7443169c9205db1ebc81f7f79d?s=64&d=mm&r=g) |
kkaschke
Messages: 4 Registered: April 2022
|
Junior Member |
|
|
I am new to Oracle Forms. I'm currently working on a form that needs some validation and I'm unsure how to do this. I have a multi-row, multi-column form for inputting records to be inserted into a table. If the policy number currently exists in the table, the form pulls in the matching insured name. There can be multiple entries for the same insured in the form.
![/forum/fa/14618/0/](/forum/fa/14618/0/)
However, if the record is new, then there is no matching insured name and the user must input the name manually.
What I need to do is perform some validation so that, if the user enters a name that doesn't match the new entry, forms will alert the user that the name doesn't match the previous entry for the new insured name.
I.E. 858585 currently exists so it pulls the name. 989898 doesn't exist so the user enters the name manually. A couple rows down the new entry is used again. When the name is typed, it must match the previous name entered with 989898.
I have attempted this by creating a POST-CHANGE trigger on the policy number. The logic is, if the policy number is not found, the insured name is left blank.
DECLARE
CURSOR cur_get_policy_nbr IS
SELECT mntrans.policy_nbr policy_nbr,
mntrans.insured_nm insured_nm
FROM mn_outstanding_trans mntrans
WHERE policy_nbr = :BL_MEMO_DTL.policy_nbr;
BEGIN
IF :BL_MEMO_DTL.policy_nbr IS NOT NULL THEN
OPEN cur_get_policy_nbr;
-- LOOP
FETCH cur_get_policy_nbr
INTO :BL_MEMO_DTL.policy_nbr,
:BL_MEMO_DTL.insured_nm;
IF cur_get_policy_nbr%NOTFOUND THEN
:BL_MEMO_DTL.insured_nm:= NULL;
else NULL;
END IF;
CLOSE cur_get_policy_nbr;
END IF;
:GLOBAL.POLCY_NBR:=:BL_MEMO_DTL.POLICY_NBR;
:GLOBAL.INSURED_NM:= :BL_MEMO_DTL.INSURED_NM;
END;
I then have a WHEN-VALIDATE-ITEM on the named insured to try to used the global variables to validate the data on subsequent entries, but that validation doesn't work. It doesn't catch the change in the insured name on the new entries since they don't exist in the table, I'm guessing.
IF get_block_property('BL_MEMO_DTL', current_record) <> 1 AND
:GLOBAL.POLCY_NBR = :BL_MEMO_DTL.POLICY_NBR AND
:GLOBAL.INSURED_NM <> :BL_MEMO_DTL.INSURED_NM THEN
message ('Name is different on same policy number');
message (' ');
RAISE form_trigger_failure;
else NULL;
END IF;
Any suggestions on how to accomplish this? Sorry this is so long, I'm trying to be thorough.
Thank you,
Kristina
|
|
|
Re: Comparing input in multi-record form, to previous entries [message #685945 is a reply to message #685938] |
Sun, 01 May 2022 13:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
If I understood you correctly, you should POST each row you enter into the table.
Oracle
POST:Writes data in the form to the database, but does not perform a database commit. Oracle Forms first validates the form. If there are changes to post to the database, for each block in the form Oracle Forms writes deletes, inserts, and updates to the database. Any data that you post to the database is committed to the database by the next COMMIT_FORM that executes during the current Run form session. Alternatively, this data can be rolled back by the next CLEAR_FORM.
In other words: if you POST, query you run (to check whether certain information already exists in the table or not) will be able to see values you previously entered, but you can still change your mind and rollback instead of commit, and none of previously posted data will be permanently stored into the database.
|
|
|
|
|