Hi Martin,

You can handle the first issue by following these steps:

Below examples, I am giving on the Employees table. My interactive grid having the following columns EMPLOYEE_ID, EMPLOYEE_NAME, etc.

Create one hidden item, for example, P12_EMPID, and turn the value protected property off.

Create a dynamic action on EMPLOYEE_ID on change event.

Create a True action as Set Value and set the following properties:

• Set Type: PL/SQL Expression
• Expression: :EMPLOYEE_ID
• Items to Submit: EMPLOYEE_ID
• Affected Item Selection Type: Items
• Items: P12_EMPID
• Fire on initialization: No (turn off the switch)

Create one more True action as Execute JavaScript code below the above action. Add the following JavaScript code in it:

apex.server.process('validate_empid',
{
  pageItems : "#P12_EMPID",
}
,
{
  dataType : 'text', success : function(data)
{
if(data != 'SUCCESS') apex.message.alert(data);
}
}
)

The above JavaScript code will call the Ajax process validate_empid by passing the P3_EMPID, which we are going to create now.

Click on the Process tab > Ajax Callback. Do right-click on it and select the option Create process. Set the following properties of it:

• Name: validate_empid
• Type: PL/SQL code (add the below PL/SQL code in the PL/SQL code field)

declare
  n_empid number;
Begin
  select employee_id into n_empid
    from employees where employee_id = :P12_EMPID;

  htp.prn('Employee already exists.'); 
exception
  when others then 
   htp.prn('SUCCESS');
End;

Now save the changes and run the page. Now if we will enter the employee id which already exists, it will give the error message using a dialog.

But this message is just to give an alert, you still have to write a validation to prevent the save duplicate employee id. Follow these steps to create the validation.

Do the right-click on the Employee_ID column in the interactive grid and select the option Create Validation and set the following properties:

• Type: No rows returned
• SQL Query: select 1 from employees where employee_id = :employee_id
  and rowid != nvl(:crowid, ‘0’)

Note: You should take the ROWID column in your interactive grid and rename as CROWID, so that you can use in the above validation query.

Now you know, how to create a JavaScript server process to call the Ajax callback process.

For the 2nd issue, you can create another Ajax callback process to check if the employee id exists in other tables or not. Call it using the JavaScript server process.

Hi Vinish
I just wanted to update you on the progress of this task. I’ve managed to implement my second requirement, solving a couple of issues on the way. It may not be the same solution that you may have proposed but very similar.

I wanted to check for a value that was already being used in another table so activated the Ajax code on the Delete button, having set a page item of the ROWID on the selection change of the region. All worked well except where I’d added a duplicate item of one that was being used in the other table, and then tried to delete the new item. The Ajax code ran because I was deleting something.

So, because the new item hadn’t been submitted to the database, its RowID was set as a temporary one (t1000 etc.), I was able to trap this and therefore delete it without checking about it being used in the other table.

So, thanks again for your help.

Martin