Home » Developer & Programmer » Forms » validate data in details block before inserting into database (10g)
validate data in details block before inserting into database [message #653233] Wed, 29 June 2016 13:47 Go to next message
lucky123
Messages: 12
Registered: June 2016
Junior Member
Is there a way to validate data before inserting into database in oracle forms??

Requirement is to not to allow to save the data if specific data for the item does not exist.

Ex: In the details block there is an item called name_code. user entered 4 records for that item name_code.

when the user click on save the record should get saved only if one of the 4 records has item_code value as 'OTHER'.

how can we achieve this requirement.Please suggest

Re: validate data in details block before inserting into database [message #653243 is a reply to message #653233] Thu, 30 June 2016 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 21616
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are two basic triggers for validation: WHEN-VALIDATE-ITEM and WHEN-VALIDATE-RECORD. However, none of these can do what you are looking for as you want to validate the whole set of records. Therefore, you'll have to use a loop and navigate through all records in a form, searching for ITEM_CODE = 'OTHER'.

PRE-INSERT would be a good choice, only if it supported restricted built-ins (as you'd have to use NEXT_RECORD in the loop). However, as it does not, maybe KEY-COMMIT could be used instead. Unfortunately, you can avoid its execution; for example, if user wants to exit the form, he'll be asked if he wants to save the changes. If he says "Yes", records will be saved without KEY-COMMIT trigger being executed.

Hopefully, there are other options and someone will suggest them. I can't think of any currently ...
Re: validate data in details block before inserting into database [message #653248 is a reply to message #653243] Thu, 30 June 2016 02:52 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I'm aware there is no way to do this. As LF says such a check would have to be done at the point of saving and the only relevant trigger - KEY-COMMIT - can be bypassed.
Why exactly does one of the four records have to have that value?
Re: validate data in details block before inserting into database [message #653274 is a reply to message #653248] Thu, 30 June 2016 13:58 Go to previous messageGo to next message
lucky123
Messages: 12
Registered: June 2016
Junior Member
Hello ,

Thank you for the replies.Issue resolved.I used global variable in WHEN_VALIDATE_ITEM to store the item value status.And checked the global variable value in PRE_INSERT.
Re: validate data in details block before inserting into database [message #653300 is a reply to message #653274] Fri, 01 July 2016 03:15 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you set the global variable when one of the records is set to OTHER and only allow insert when once that happens?
Re: validate data in details block before inserting into database [message #653462 is a reply to message #653300] Wed, 06 July 2016 07:45 Go to previous messageGo to next message
lucky123
Messages: 12
Registered: June 2016
Junior Member
yes.The global variable is set to 'Y' when record(one of the item in that record) is set to 'OTHER'.The record has three columns .so we need validate only one column data.In PRE_INSERT checking the lobal variable status and inserting data.
Re: validate data in details block before inserting into database [message #653820 is a reply to message #653462] Tue, 19 July 2016 08:48 Go to previous messageGo to next message
linlasj
Messages: 98
Registered: August 2005
Location: Linköping
Member
Sounds like a good idea.
You can walk thru the detail block as well in the PRE-INSERT och maybe even in some VALIDATE-xxx-XXx trigger.
Re: validate data in details block before inserting into database [message #653821 is a reply to message #653248] Tue, 19 July 2016 08:48 Go to previous message
linlasj
Messages: 98
Registered: August 2005
Location: Linköping
Member
THere is PRE-trigger that can be used.
Previous Topic: How to disable Delete icon on menu in oracle forms
Next Topic: Forms 11g, Arabic and numbers
Goto Forum:
  


Current Time: Fri Oct 30 06:03:57 CDT 2020