This content has been marked as final. Show 23 replies
I followed an example set by Andy to create radio buttons on tabular form last month. You can access Andy's example on the following url:
I tested the above example for radio buttons, you might be able to create check boxes as well with some minor changes.
Have a look at: Re: creating a yes/no radiogroup....if selected then YES, else NO in tabular - this refers to: [http://apex.oracle.com/pls/otn/f?p=55041:18] which deals with selecting a primary employee on the EMP table
Honestly speaking, I am pretty new to apex and have no experience using collection before. I tried the link that you mentioned. But I am not getting it. I think I need some guideline where to create which process.
Here are the steps that I followed so far.
1. I created the collection in before header
2. I created the report based on the collection.
3. Add two buttons "Add New Rows" and "Update"
4. Add process to add collection members to execute in "Add New Rows"
My difficulties are
- in the report, I see the records from the table, but the radio button is not selected for the primary contact record.
- When I click "Add New Rows", no new records are created
So, I think, I am not creating the process in the proper place or I don't understand some logic. Can you please guide me through this? Thanks.
OK - I've made a few changes to your pages.
On page 1, I have added a process that checks for the collections and deletes it if it exists. This is so that when the user clicks an Edit link on a customer, they do not get any old collection data.
On page 2, I've updated the Before Header process to only create the collection if it does not exist - this is needed to allow you to submit the page and/or add new records without clearing the collection.
The page, given these changes, now works ok. The only thing left is to add in the process to update the actual table with the selected item. Hopefully, my code in the other thread will guide you through this, but let me know if you need further help.
Thanks you for guiding me through this. I know that you have been very patient with me. But I am still having problem. I have added two after submit processes to Update Collection items and Save the data to the database, following the code that you provide in the previous forum link. But got null for all the fields except primary key field and customer id field(foreign key field). Can you please let me know where I did wrong?
here is my Update_Collections process
for x in 1..apex_application.g_f01.COUNT
v_c001 := apex_application.g_f01(x);
if v_c001 = :P2_SELECTED_ID then
v_c003 := 'Y';
v_c003 := 'N';
p_collection_name => 'C_CONTACT_INFO',
p_seq => v_c001,
p_c001 => apex_application.g_f01(x),
p_c002 => apex_application.g_f02(x),
p_c003 => v_c003,
p_c004 => apex_application.g_f04(x),
p_c005 => apex_application.g_f05(x),
p_c006 => apex_application.g_f06(x));
here is my Save_Contacts process
delete from contacts where customer_id = :P2_CUSTOMER_ID;
insert into contacts (ID, NAME, PRIMARY_CONTACT, PHONE , STATUS, CUSTOMER_ID)
select c001, c002, c003, c004, c005, :P2_CUSTOMER_ID
where collection_name = 'C_CONTACT_INFO';
Both are after submit process. I also added success message to check and see all the processes are being executed. I saw both update and save processes being executed according to success message. Can you please guide me where I did wrong? Thanks Andy.
The issue is with the apex_item.hidden() items - these can not be used as separate columns but must be concatenated before a displayable item. For example:
it should be:
SELECT APEX_ITEM.HIDDEN(1,FIELD1) FIELD1, APEX_ITEM.TEXT(2,FIELD2) FIELD2 FROM TABLEX
I think that you had hidden the first FIELD1 column which means that Apex does not render it as part of the page, so the f01 items are not available to your code. Using the second method, means that the item is part of the page, even though it is hidden from the user, and is, therefore, available to your code.
SELECT APEX_ITEM.HIDDEN(1,FIELD1) || APEX_ITEM.TEXT(2,FIELD2) FIELD2 FROM TABLEX
I'll update your page to handle this and get back to you
OK - have a look at your page now.
I have made a few of changes:
1 - Updated the tabular form's SQL to:
So all HIDDEN items are concatenated in front of displayable items.
2 - Updated your Update_Collections process to be unconditional. This is needed as the page can be submitted by one of three ways - Save, Update and Add Row - and the collection needs to be updated for all three
3 - Changed P2_SELECTED_ID to remove the source settings
4 - Created a new page process (PL/SQL, On Load - Before Header):
This just ensures that P2_SELECTED_ID has a value where possible - if no contact for the selected customer is flagged as the primary, this code will do that. I have assumed here that you require each customer to have a primary contact - if not, you could remove the IF tests. I have found that setting the value of a page item that is not directly connected to a database column is best done using a page computation or process.
DECLARE vID NUMBER; BEGIN SELECT MIN(ID) INTO vID FROM CONTACTS WHERE PRIMARY_CONTACT = 'Y' AND CUSTOMER_ID = :P2_CUSTOMER_ID; IF vID IS NULL THEN SELECT MIN(ID) INTO vID FROM CONTACTS WHERE CUSTOMER_ID = :P2_CUSTOMER_ID; IF vID IS NOT NULL THEN UPDATE CONTACTS SET PRIMARY_CONTACT = 'Y' WHERE ID = vID; END IF; END IF; :P2_SELECTED_ID := vID; END;
The page now seems to function as I think you need it to - but let me know if not
I surely come back with new question!
Now, I am trying to change the status column to check box. I can see the correct status the check box in the form. But when I tried to add new rows or update anything, I am getting "No data found" error.
here is my query
APEX_ITEM.HIDDEN(6, C006) || apex_item.checkbox(7,C001, null, C005) INACTIVE
where collection_name = 'C_CONTACT_INFO'
this is the updated Contact_Collection
IF NOT APEX_COLLECTION.COLLECTION_EXISTS('C_CONTACT_INFO') THEN
apex_collection.create_or_truncate_collection(p_collection_name => 'C_CONTACT_INFO');
for y in (select ID,
decode(STATUS, 'I', ID, null) status,
where CUSTOMER_ID = :P2_CUSTOMER_ID)
p_collection_name => 'C_CONTACT_INFO',
p_c001 => y.ID,
p_c002 => y.NAME,
p_c003 => y.PRIMARY_CONTACT,
p_c004 => y.PHONE,
p_c005 => y.STATUS,
p_c006 => y.CUSTOMER_ID);
here is my updated Update_Collection process
FOR X IN 1..APEX_APPLICATION.G_F01.COUNT
V_C001 := APEX_APPLICATION.G_F02(X);
IF V_C001 = :P2_SELECTED_ID THEN
V_C003 := 'Y';
V_C003 := 'N';
if APEX_APPLICATION.G_F07(X) = APEX_APPLICATION.G_F02(X) THEN
V_C005 := 'I';
V_C005 := 'A';
P_COLLECTION_NAME => 'C_CONTACT_INFO',
P_SEQ => APEX_APPLICATION.G_F01(X),
P_C001 => APEX_APPLICATION.G_F02(X),
P_C002 => APEX_APPLICATION.G_F04(X),
P_C003 => V_C003,
P_C004 => APEX_APPLICATION.G_F05(X),
P_C005 => V_C005,
P_C006 => APEX_APPLICATION.G_F06(X));
I did not make any changes to Save_Contacts process.
OK - you've come across a common issue with browsers. A checkbox is only submitted with the form IF it is ticked, otherwise it is ignored completely. This means that if only one is ticked, your G_F07 array will only contain ONE value. The error that you are seeing is because you have more items in your G_F02 array than in the G_F07 one and the code has run out of G_F07 values. This also means that it is difficult to tell which checkbox has been ticked - it could be the first, second or last one, but the G_F07 array will still only contain one value.
There are ways around this, though they can get complicated. Do you need to use checkboxes or would a Yes/No select list do?