I would set up an Event that checks the record when it is saved, and verifies that it has a value and that it is a unique value (check against existing values in the table).   If it doesn’t succeed it could show a message and prevent the save of the record by the user.

Over time the check to ensure uniqueness  might run slow depending on how large the dataset is, so something to keep an eye on.

Nick

Hi @asjaus ,

I was looking recently to see if there is something we could do to force a unique field value into a persistent field.

It may not be the best approach but if you have access to the oracle database - I created a new sequence in Oracle. I then created a new custom menu of type PL/SQL Block. Inside the block I put something similar to the following:

declare
  attr varchar2(1000);
  objid varchar2(1000);
  info varchar2(1000);
  objversion  varchar2(1000);

cursor c1 is 

select b.objid, b.objversion, {SEQUENCE}.nextval as ID
  from ifsapp.{CUSTOM_VIEW} b
 where b.objid = &OBJKEY;

 begin

 for r in c1 loop
   begin
 ifsapp.client_sys.Clear_Attr(attr);
 ifsapp.client_sys.Add_To_Attr('CF$_ID',r.ID,attr);
 IFSAPP.{CUSTOM_VIEW}.MODIFY__(info, r.objid, r.objversion, attr, 'DO');

  end;

  end loop;
 end; 

and for the conditions I put the ID (the field I wanted a new sequence number in) to not contain anything and the objkey to contain something.

In this instance the user would go to the CLU page create a record - the ID would be blank. They would then right click on the page and they would see a generate ID button. When click and applied - the option would then disappear as the ID would have a value and the condition above would no longer be valid to show.

Kind Regards,

Dan

The desired functionality cannot be achieved directly through the IEE. However, we can implement a solution using PL/SQL Developer to accomplish your goal in the background. 

With PL/SQL Developer, we can create a procedure that enforces the uniqueness of the record creation for the persistent field and ensures that it does not contain blank or NULL values. This can be achieved by defining appropriate constraints. 

You can use the following ORACLE commands via PL/SQL Developer to do the needful.

ALTER TABLE MTG_COMPANY_PERSON_CLT MODIFY ( CF$_MTG_EMP_NO NOT NULL);
CREATE UNIQUE INDEX <index_name> ON MTG_COMPANY_PERSON_CLT(CF$_MTG_EMP_NO);

current version: Apps 10 UPD12