Tuesday 12 December 2017

Interactive grid validation

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:

 
 
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

16 comments:

  1. Thank you this was helpfull

    ReplyDelete
  2. If a user select two duplicates values without saving it does not work???

    ReplyDelete
  3. Is 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.

    ReplyDelete
  4. Hi SLino,
    Thanks 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.

    ReplyDelete
  5. @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.
    @SLino, any idea how to validate against nonsubmitted rows?

    ReplyDelete
  6. Fab. Just what I needed. Thanks very much.

    ReplyDelete
  7. How to handle this if there are more than one Interactive Grids in the same page? Thanks in advance.

    ReplyDelete
  8. it's an helpful example.thank you.

    ReplyDelete
  9. This example works great when you have only 1 reference in the table.

    In 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.



    ReplyDelete
  10. how to validation without save button which means validation message through while enter duplicate values.

    ReplyDelete
    Replies
    1. I added a post on how you could check for duplicates using pure JS so it does not do Server side validation.

      Check it out on https://lschilde.blogspot.com/2020/04/interactive-grid-validation-duplicated.html

      Delete
    2. 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.

      Delete
  11. I 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...

    ReplyDelete
    Replies
    1. Could post on IG with JS check on duplicate rows help? I am not sure I understand your use case

      Delete
  12. To check duplicates among entered records you may use APEX_COLLECTION api.

    1) 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;

    ReplyDelete