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