This content has been marked as final. Show 26 replies
I would have it create a record in an Apex collection after submit, then read the collection record back into your page items (and delete the collection/record) in a before header process - when the collection record exists. You can put anything you want into the collection columns.
I don't get exactly what you are doing but yes when you try to pass values etc the string comes after # will be lost. One work around i use is to replace any special characters before the point where it loses the value and after getting the value to the right page etc then replacing back to original character.
So what you should do is ot replace # with say underscore and once you got the value again replace back to #
use the Pl/sql replace function
Hope this helps. If this still does't solve your problem write in detail what you are trying to do.
Great -- Thanks for the quick responses! I'll try the APEX Collection approach first and see how it goes ... Thanks - Gail
Please update your forum profile with a real handle instead of "user12099650".
Greetings Everyone - I'm using APEX 4.1 and have implemented a "Copy Record" button that copies selected fields from the record back to the same page to create a new record. This is done using branching (got the idea from this forum!)It's not a good one...
and setting the items (e.g., P2_NAME,P2_ADDRESS1,P2_ADDRESS2, etc...) with these values (e.g., &P2_NAME.,&P2_ADDRESS1.,&P2_ADDRESS2., etc...)As well as this problem you'll hit similar ones with other URL-reserved characters, and could hit problems with limits on URL length (IE only allows for about 2K).
It has worked perfectly in other APEX applications -- up until now. The issue is that the new database contains several fields that contain the "#" character. When the "#" character is encountered during the copying process, any data after the # are not copied (but all of the data before the # are copied OK).
Is there a way to get around this? Removing the "#" characters is not an option, as it is part of numerous manufacturer part numbers that we track.Do it properly using a page process.
Any guidance is appreciated. Thanks in advance for your help - Gail
Actually, I did update my handle to "gmac" before I created this thread ... Not sure why it created the thread using the old, creepy handle ... Thanks for the suggestions - Gail
Greetings Bob37 - Can you please expand on your suggestion below? I haven't worked with APEX Collections before (I just reviewed the documentation, but am still a bit confused about the steps to implement) ...
"I would have it create a record in an Apex collection after submit, then read the collection record back into your page items (and delete the collection/record) in a before header process - when the collection record exists. You can put anything you want into the collection columns. "
Here are the steps as I see them (Please make any corrections or fill in things if I have missed them!):
1. I should start by deleting the collection if it exists by putting the APEX_COLLECTION.DELETE_COLLECTION procedure in the "Processes" section of my APEX page to run before the header to get rid of the collection
2. Then use APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY where the query contains a select statement that has the clause "where id = :P2_ID" so that it copies the correct record into the collection. This code should go in the "Process" section of my APEX page and run after Submit.
3. Then I should access the collection by querying the view APEX_COLLECTIONS and putting those values into my page items (essentially, my form). Then the user would save the new record.
Is that the correct process or have I missed something? Thanks for your help - Gail
Hi Gail,1 person found this helpful
I haven't tested this code but it should work with changes to accomodate your actual page items
I envision it working this way
This is the Before Header PL/SQL Process
The after submit PL/SQL process to write the collection record is:
BEGIN IF wwv_flow_collection.collection_exists('MY_COL') THEN -- Get the record I wrote using Apex_Collection.Add_Member in the after submit process -- if the collection exists and put the data into the page items SELECT c001, c002, .... INTO :P1_ITEM1, :P1_ITEM2,..... FROM apex_collections WHERE collection_name = 'MY_COL' AND SEQ_ID=1; -- Toss the collection wwv_flow_collection.delete_collection('MY_COL'); END IF; END;
BEGIN -- Even though the collection shouldn't be there, get rid of it if it is. IF wwv_flow_collection.collection_exists('MY_COL') THEN wwv_flow_collection.delete_collection('MY_COL'); END IF; -- Create the Collection apex_collection.create_or_truncate_collection('MY_COL'); -- Add one record to the collection loading the page item data into the VARCHAR2 columns -- Note there are 5 columns available for numbers, a clob, and a blob, see below -- SEQ_ID (See collection columns list below) will be set to 1 apex_collection.add_member('MY_COL', p_c001=>:P1_ITEM1, p_c002=>:P1_ITEM2,... ); **** Edit **** This could also be loaded using the Create collection from query,which after re-reading your original request is likely what you're looking for END;
Edited by: Bob37 on Aug 15, 2012 10:54 AM
The collection record consists of these columns COLLECTION_NAME NOT NULL VARCHAR2(255) SEQ_ID NOT NULL NUMBER C001 VARCHAR2(4000) C002 VARCHAR2(4000) C003 VARCHAR2(4000) C004 VARCHAR2(4000) C005 VARCHAR2(4000) ... C050 VARCHAR2(4000) N001 NUMBER N002 NUMBER N003 NUMBER N004 NUMBER N005 NUMBER CLOB001 CLOB BLOB001 BLOB XMLTYPE001 XMLTYPE MD5_ORIGINAL VARCHAR2(4000)
Thanks, Bob ... This is certainly enough to get me started in the right direction ... Thanks for your help ... I'll let you know how it works out ... Gail
Bob - I tried implementing the code that you provided (filling in my item names, collection, etc.), but I can't get it to work ... I left me "Copy Record" button in place (which submits the page) and changed the branch on it to return to my original page ...
Here's what should happen:
1. I start out on page 30, which contains an Interactive Report
2. The user clicks the "Edit" button for the desired record and moves to pg. 31, which contains the populated data-entry form for the selected record
3. The user can edit the record if they like, and then click "Apply Changes" to save the edits to the database
4. There is also a "Copy Record" button on that page that the user can click to copy certain fields of the existing record to a new record back on that same page (as originally described in my post, this worked fine until we came across a record that had a "#" character)
5. Only the selected fields (e.g., NOT serial number) should be shown in the form for the new record.
6. The user could make additional edits, as needed and then click "Save" to save the new record to the database
Here's what actually happens with your revised code in place:
1-3, same as above
4. Clicking the "Copy Record" button (without Clear Cache on the branching) causes the page to blink, but all of the records are exactly as before (including the records like SerialNumber that I did not include in the Collection code) ... If I check Clear Cache in the branching back to this page, I get a blank form
Clearly I am missing something ... I tried several variations, but haven't been able to come up with anything that works ... The code compiles, so it appears that I have all of my Collections columns and page items matched up correctly ... Thanks for your patience in helping me work through this ... Gail
Ok, so you have a Copy Record button on page 31. Clicking it submits the page and you return to page 31 where you expect to have all page items cleared and then selected page items/fields repopulated from the collection.1 person found this helpful
You do need the clear cache on the branch for page 31 to clear the serial number and any other desired fields.
I don't know exactly when during page rendering the cache gets cleared.. one would think it would be before anything happens, So,
I wonder if the Before Header process to repopulate the page items needs to be reset to be an After Header process. You can edit the process and make that change.
You can also run the page in debug and watch as page items get cleared and reset. This would also validate that the collection is getting populated so the data is there to rest the page items. You could also comment out the deletion of the collection in the After Header process temporarily and create a simple classic report region for test purposes that just does SELECT C001, c002, c003 FROM Apex_Collections WHERE Colllection_name = 'MY_COL' and see that the collection both exists and is populated.
If you would try that.
OK - Here's what I did:
1. I put the "Clear Cache" back in the branching
2. Changed the first code block to run after header
3. Added classic report region with simple query, as suggested
4. Ran in debug mode and examined the results
Here are the results when clicking the "Copy Record" button:
1. The form gets cleared out -- completely blank
2. The report gets populated, and with the correct values from the collection
3. Debug review confirms that the first code block runs and session state shows the value being saved into the page item
(e.g., ..Session State: Saved Item "P31_MANUFACTURERPARTNUMBER" New Value="123456" )
4. After it lists the session state for all of the items that are part of the collection, then it just lists all of the regular page items and if they are popup_lov, text_field, etc.
5. Towards the end, there is "..Execute Statement: select c001, c002 ... from apex_collections where collection_name='MY_COL' "
So - it seems that I am close, because the collection is being created and populated properly... It just isn't putting the data back into the form after it is cleared out ... Thanks for all of your help - Gail
You're welcome.1 person found this helpful
So, bullet point point 3 is where the SELECT INTO is happening and View Debug shows the value changing for the page items being loaded ?
And bullet point 5 is where the select is occurring for the classic test sql report ?
When the form is blank, I'd expect to see somewhere in the debug output after point 3 when they show as "Session State: Saved Item... " that the page items are being reset to "".
When you ediit the page items; What is the Source Type of the page items? (static, database, ...) and are they set Source Used to Always, Replacing existing values, or Only when Null ?
Are there any other processes ?
Mainly, if the item are null when the form region is rendered, I'd really expect to see somewhere in the debug output where they're being cleared out.
** Edit, I think I'm confused about what is happening in # 5 with the Select from collection.. Is that the report? It seems from re-readig the report is running first, then the page items get loaded, then along comes another select in bullet point 5.. ?
Edited by: Bob37 on Aug 15, 2012 4:20 PM
** Edit # 2, I would uncomment out the deletion of the collection and make sure the report is running first to list the contents of the collection before loading the page items and deleting it. That would get your setup as close as possible to how you'll have it for production. Then once it's working, remove the report and it's ready to go.
Edited by: Bob37 on Aug 15, 2012 4:26 PM
OK - checked out the debug again ... there is no reference (after my point 3), that indicates that the records are being cleared out (or reset again) ...
As suggested, I checked the definitions of the individual page items ... here are the settings (likely for all of the items, although I only checked a few and they were all the same) :
Source Used: "Always, replacing any existing values in session state"
Source Type: "Database Column"
Maybe that's it ... I'll do some testing and let you know ... Gail