APEX and IG validation
Interactive grid validation
Check for duplicates
Something that come out today as a question - How to validate Interactive grid to check if your user typed primary key like EMPNO is not already in the table?
Catch was to do this only on insert as people will not be able to delete or update it afterwards.
Adding a validation to your IG is easy enough, creating one as:
Catch was to do this only on insert as people will not be able to delete or update it afterwards.
Adding a validation to your IG is easy enough, creating one as:
Where PLSQL Function body code is:
That is all. Notice the use of APEX$ROW_STATUS to detect if row is created.
4/4/2020 Part two of this post is available now IG with JS check on duplicate rows.
Happy APEXing,
Lino
Thank you this was helpfull
ReplyDeleteIf a user select two duplicates values without saving it does not work???
ReplyDeleteIs there a way to use APEX_$ROW_STATUS is JS? If the row status is update I need to set the values of WHO_UPDATED column, if the status is create I need to set the value of WHO_CREATED column.
ReplyDeleteHi SLino,
ReplyDeleteThanks for the clear explanation.
I tried this but this is not covering the case where the user enters the duplicates within the new(now adding) rows.
Is there a way to do this.
@Sunny, I've been trying to find the exact same IG Client side validation solution but no cigar. For new and updated rows (C and U), its fairly easy to validate against already submitted data. I'm guessing JS with for example "onBlur" with loop on records with rowstatus C and U. I'm just not that good in JavaScript.
ReplyDelete@SLino, any idea how to validate against nonsubmitted rows?
Fab. Just what I needed. Thanks very much.
ReplyDeleteHow to handle this if there are more than one Interactive Grids in the same page? Thanks in advance.
ReplyDeleteSet editable region
Deleteit's an helpful example.thank you.
ReplyDeleteThis example works great when you have only 1 reference in the table.
ReplyDeleteIn case there is an additional reference, it won't work.
Let me explain my case:
Every week an attendance sheet is entered using the EMPNO.
The week after when you have a new attendance sheet and also enter the EMPNO;
You will get 'This is duplicate' message when and employee (EMPNO) is in both attendance sheets (thus same table).
I tried using the page reference :P10_EMPNO but the validation than does not work on this.
How do you handle this?
thanks in advance.
how to validation without save button which means validation message through while enter duplicate values.
ReplyDeleteI added a post on how you could check for duplicates using pure JS so it does not do Server side validation.
DeleteCheck it out on https://lschilde.blogspot.com/2020/04/interactive-grid-validation-duplicated.html
Sir, I want this validation on one column. Live validation on one ITEMCODE in one row. If select same itemcode on next row then error message will show.
DeleteI select 2 rows, click submit, duplicate error shown..that's fine. But if i change one of them.. still duplicate error showing.. If i change both of them.. duplicate error showing.. even I reload the page again, and select only one row from previous selected, till duplicate error showing...
ReplyDeleteCould post on IG with JS check on duplicate rows help? I am not sure I understand your use case
DeleteTo check duplicates among entered records you may use APEX_COLLECTION api.
ReplyDelete1) define apex collection as a PL/SQL process executed before header:
begin
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'EMPLOYEES');
end;
2) use Validation like you do, but with collection instead of db table:
declare ln_count number :=0 ;
begin
if :APEX$ROW_STATUS = 'C' then
select count(*) into ln_count
from APEX_COLLECTIONS
where collection_name = 'EMPLOYEES'
and c001 = :EMPNO;
if ln_count = 0 then
APEX_COLLECTION.ADD_MEMBER ('EMPLOYEES'
,p_c001 => :EMPNO
);
else return 'This is duplicate';
end if;
end if;
end;