Home » Developer & Programmer » Forms » How to Check for a unique value in a text item in a multi-record block (Forms 10)
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
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?

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579574 is a reply to message #579573] Wed, 13 March 2013 06:47 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post each record as it's entered. Then you can just use a select to check.

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579578 is a reply to message #579573] Wed, 13 March 2013 07:23 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
My dear i think you are trying to do something like that 

DECLARE 
	A NUMBER(1):=0;
	F_NAME VARCHAR2(35);
BEGIN 
	F_NAME:=:EMPNO;
	FIRST_rECORD;
	WHILE :EMPNO IS NOT NULL LOOP
		IF F_NAME=:EMPNO THEN 
			A:=A+1;
		END IF;
		NEXT_RECORD;
	END LOOP;
	PREVIOUS_RECORD;
	IF A=2 THEN 
		MESSAGE('DUPLICATE RECORD');
		MESSAGE('DUPLICATE RECORD');
	ELSE 
		NEXT_ITEM;
	END IF;
	END;

hope u got something

best regards
Mughal

[Updated on: Wed, 13 March 2013 07:29]

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579579 is a reply to message #579573] Wed, 13 March 2013 07:42 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
Hi cookiemonster,

Which trigger would you use to perform the commit and check?

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579581 is a reply to message #579579] Wed, 13 March 2013 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
post, not commit.
when-new-record-instance is the best bet.

Report message to a moderator

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 messageGo to next message
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?

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579585 is a reply to message #579584] Wed, 13 March 2013 08:29 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You wouldn't do the duplicate check in WNRI, that's what when-validate-item is for.

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579588 is a reply to message #579585] Wed, 13 March 2013 08:38 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
Thanks for your help so far cookiemonster, sorry could you explain in a bit more detail excatly what you would do as i am newish to forms.

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579591 is a reply to message #579588] Wed, 13 March 2013 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
WNRI - issue post
WVI - Have a select that checks for a record with the same value in the DB. If found blank out field in current record and raise an error message.

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579593 is a reply to message #579591] Wed, 13 March 2013 08:55 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
I think i understand, one final question .. How do you issue a post within the WNRI trigger

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579597 is a reply to message #579593] Wed, 13 March 2013 09:03 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
post is a keyword, like commit, just type it.
You may want to wrap it in an IF statement that checks the block_status so that it doesn't try to do anything if the user hasn't made any changes.

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579600 is a reply to message #579597] Wed, 13 March 2013 10:03 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
Youre suggestion worked. I would like to still Brush any records that have been changed so that user still thinks that they have changes pending the Save button. Would you know how i acheive this

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579606 is a reply to message #579600] Wed, 13 March 2013 10:54 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
cornwall wrote on Wed, 13 March 2013 15:03
I would like to still Brush any records that have been changed so that user still thinks that they have changes pending the Save button.


Not entirely sure what you mean by that.

Report message to a moderator

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 messageGo to next message
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?

Report message to a moderator

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 messageGo to next message
cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579713 is a reply to message #579609] Thu, 14 March 2013 03:20 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
Thanks for your help, the form is doing as expected, except now when you change a record you get the message "FRM-40404: Database apply complete. 1 records applied.

Can you somehow hide these apps messages from appearing to the user?

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579715 is a reply to message #579713] Thu, 14 March 2013 03:26 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Set message level to a higher value; for example:
:system.message_level := 20;
post;
:system.message_level := 0;

Report message to a moderator

Re: How to Check for a unique value in a text item in a multi-record block [message #579716 is a reply to message #579715] Thu, 14 March 2013 03:47 Go to previous message
cornwall
Messages: 36
Registered: June 2009
Member
I added your suggestiona nd all is working fine.

Appreciate your help.

Thanks,

Report message to a moderator

Previous Topic: how to display current record?
Next Topic: ORA - 6502 (numeric or value error string) , FRM - 40735
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 15:46:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.