oracle-apex

How can I prevent add duplicate items to APEX_COLLECTION


I want to create laboratory tests order and I created apex collection , I need to check if test already exists in the collection I need to show message says

Test already exists .

I tried this code :

Declare 
v_seq_id number;
v_test_no number; 

begin 

if not apex_collection.collection_exists('TESTS') then 

apex_collection.create_collection('TESTS');
end if;

select seq_id , c001 into v_seq_id,v_test_no 
from apex_collections 
where collection_name='TESTS'
and  c001 = :P58_TEST_NO;
exception WHEN NO_DATA_FOUND 
THEN v_seq_id :=0 ; 


if v_seq_id != 0 THEN 
v_test_no := v_test_no ;
end if;
end;
apex_collection.add_member(

    p_collection_name => 'TESTS',
    p_c001 => :P58_TEST_NO , 
    p_c002 => :P58_TEST_NAME , 
    p_c003 => :P58_SECTION , 
    p_c004 => :P58_TESTPRICE , 
    p_c005 => :P58_TESTVAT , 
    p_c006 => :P58_TESTTOTAL
);

I created the collection then I need to check if test exists I need to prevent add this test another time and show message the test already exist

 if v_seq_id != 0 THEN 
    v_test_no := v_test_no ;
    end if;
    end;

see the image now its duplicate the test

enter image description here

how can I do that and thank you in advance

I created the code on dynamic action on the item P58_TEST_NO execute server side code , now I created the validation on same item P58_TEST_NO and its working when I click on SAVE ORDER button and submit the page, but I need it before that for example when I select test albumin it will add to collection if I select it again I need the validation on page item prevent and dont add the test again into collection

enter image description here

see image please albumin added 2 times I need to add one time only and prevent add it again when select the test not validate when submit the page and click SAVE ORDER button


Solution

  • You're not mentioning how this process is invoked so I'm assuming this is a form where the "Save Order" submits the page and this process is invoked in the "Processing" section. This is the "APEX way".

    To avoid duplicates, just create a validation of type "No rows returned" with source

    SELECT seq_id
      FROM apex_collections 
     WHERE collection_name='TESTS' AND
           c001 = :P58_TEST_NO;
    

    and error message "Test already exists ".

    Then in the page process just execute the call to apex_collection.add_member. If the validation fails the page processing will stop and this page process will not be executed.