How to Check for a unique value in a text item in a multi-record block [message #579573] |
Wed, 13 March 2013 06:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cornwall
Messages: 36 Registered: June 2009
|
Member |
|
|
I have a multi-record block with several text items.
On one of the text items i want to enter a value and then check whether the same value for the item has already been entered on any other records within the block.
If it has already been entered then i want to display a message and null out the field.
I have tried using app_record.for_All_records from a when-validat-item trigger but this does not work as you get a 'FRM-40737 Illegal restricted procedure go_block in when-validate-item trigger ' error
Any ideas how to acheive this?
|
|
|
|
|
|
|
Re: How to Check for a unique value in a text item in a multi-record block [message #579584 is a reply to message #579581] |
Wed, 13 March 2013 08:19 ![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) |
cornwall
Messages: 36 Registered: June 2009
|
Member |
|
|
If i did it the way you suggest, how would you change the duplicated value back to null.
Say you have 3 records in the block all with null values for the field that you need to check for duplicates.
If you update the text item on record 1 to a value say 'ABC', when your cursor moves to the next record (record 2) the WNRI Trigger would post the changes to the database, then perform the duplicate check and find none. (so far so good).
If you enetered the same value for the item on record 2 and then navigated to either record 1 or 3, the WNRI trigger would again post the changes to the database, peform the duplicate check again which would flag up the duplicate. How would you revert the last updated value back to null?
Would you need to store the blocks record number for record 2 in a parameter and then update this records field to null again?
|
|
|
|
|
|
|
|
|
|
Re: How to Check for a unique value in a text item in a multi-record block [message #579608 is a reply to message #579606] |
Wed, 13 March 2013 11:02 ![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) |
cornwall
Messages: 36 Registered: June 2009
|
Member |
|
|
Because i am now commiting any changes directly to the database when the user navigates to a new record, when they try to save the changes in Apps, a message will say 'No changes to save'. I would still like the user to see the message "X records updated".
Can i somehow commit the database still but afterwards mark the record status back to CHANGED so that Apps still thinks a change is pending?
|
|
|
Re: How to Check for a unique value in a text item in a multi-record block [message #579609 is a reply to message #579608] |
Wed, 13 March 2013 11:09 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
post != commit.
Post instructs the form to apply the changes to the DB, but not commit them.
Like if you do an insert in sqlplus, the insert is done but not committed until you explicitly issue a commit. In the meantime the new row is only visible to your session.
The form knows you haven't commited and if the user tries to exit the form without pressing save it'll ask them if they want to save changes.
|
|
|
|
|
|