61019

Oracle ApEx Checkbox to Manipulate Other Values When Checked/Unchecked

Firstly, I am using the latest version of Oracle ApEx 4.1.1 and have the following standard report that has a checkbox selection so when clicked, would like to set another field with sysdate, including time.

When unchecked, would like to reset to NULL the date set in my previous statement as well as a comments fields

Report might look like:

select id, name, telephone, apex_item.checkbox2(10,id) as "Tick when Contacted", apex_item.display_and_save(20,:P2_DATE) as "Date Contacted", apex_item.textarea(30,:P2_COMMENT,5,80) as "Comment" from my_table

As an example of the above report, there might be a total of 10 records, so as each of the 10 record checkboxes are checked by the user, I would like to set the date field to sysdate, on the spot. I would also like the reverse done, when the checkbox is unchecked - set both date and comments to NULL.

Would appreciate any help with how to approach this and whether the above is possible via a Dynamic Action or straight via a javascript/on demand process.

Would definitely prefer the Dynamic Action approach if possible in order to learn new methods of performing my required task.

Thanks.

Answer1:

Example on EMP table:

select apex_item.checkbox2(p_idx => 1, p_value => empno, p_attributes => 'class="check_select"') empselection, apex_item.text(2, ename) empname, empno, ename from emp

Add a class to the checkbox item. This makes it easy to target with jQuery.

Create a new dynamic action, eg "checkbox click". <strong>Event</strong>: Click <strong>Selection</strong> Type: jQuery Selector <strong>jQuery</strong> Selector: .check_select (this is the class we added to the checkbox in the sql) <strong>Condition</strong>: JavaScript Expression <strong>Value</strong>: $(this.triggeringElement).prop('checked')

The condition is a check on the checkbox element to see whether it has been checked or unchecked. prop() tests a property of an element, and returns true or false in this case. When true, a true action will fire, else a false action.

<strong>True Action:</strong> <strong>Action</strong>: Execute JavaScript Code <strong>Code</strong>: $(this.triggeringElement).closest("tr").find("td[headers='EMPNAME'] input").val('test');

<strong>False Action:</strong> <strong>Action</strong>: Execute JavaScript Code <strong>Code</strong>: $(this.triggeringElement).closest("tr").find("td[headers='EMPNAME'] input").val('');

Both have no selection types since we need to target items in the same row as that of the clicked checkbox. There isn't really a way to do this through the apex selection possibilities. Target the column through the headers, and don't forget to target the input inside of the td (or: the textarea!)

When the dynamic action has been created, go back in. Under <strong>Advanced</strong> change the <strong>Event Scope</strong> to Live. This is necessary to account for pagination. If you don't change this, no actions will be bound to elements after pagination.

Then edit the true and false action, and <strong>uncheck</strong> Fire on Page Load.

Now to account for your date: Since you want the sysdate as a default value, i'd suggest to add a hidden item to your page, eg P9_DEF_DATE. As a <strong>source</strong>, use a <strong>PLSQL expression</strong>, and use SYSDATE. You can then use the value of that field as a default value, eg:

$(this.triggeringElement).closest("tr").find("td[headers='EMPNAME'] input").val($v('P9_DEF_DATE'));

Last note: blanking out a value through val: use val('') with <strong>SINGLE</strong> quotes! Won't work with double in my experience.

<strong>Some links for Dynamic actions docs:</strong>

    <li>example by oracle (shows how to use plsql block in a dynamic action): http://st-curriculum.oracle.com/obe/db/apex/r40/apexdynactions/apexdynactions_ll.htm</li> <li>Dimitri Gielis (some basics): http://dgielis.blogspot.be/search/label/Dynamic%20Actions</li> <li>Youtube tutorial by Dan McGhan: http://www.youtube.com/watch?v=9JAKMU8x5nI</li> <li>And there is always plenty to find on the web of course, and don't forget the OTN forums.</li> </ul>

    <strong>An example of an ajax process:</strong> So let's say that i want to update the COMM column of the employee i checked.

    Create a new process on the page of the report, give it an easy enough name and don't put spaces in it. For example, i'm running with "update_emp".

    update emp set comm = apex_application.g_x02 where empno = apex_application.g_x01;

    Add another true action to the dynamic action, type "Execute JavaScript Code".

    $.post('wwv_flow.show', {"p_request" : 'APPLICATION_PROCESS=update_emp', "p_flow_id" : $v('pFlowId'), //app id "p_flow_step_id" : $v('pFlowStepId'), //page id "p_instance" : $v('pInstance'), //session id "x01" : $(this.triggeringElement).val(), "x02" : '88', //just a bogus value to demonstrate }, function(data){ /*normally you'd handle the returned value here an example would be a JSON return with which to set item values*/ } );

    This javascript will post to the page, targetting the update_emp process. x01 and x02 are a sort of 'temp' variables. They exist so we don't have to submit page items all the time in order to post values to the session state. They are part of the apex_application package. So here i'm using x01 to store the empno (value of the checkbox, hence this.triggeringElement), and x02 to pass in the value for the commision. I hardcoded it here but you can get any value you'd like of course.

    $.post is a jQuery method posting to the page.

    When you're learning dynamic actions or trawling forums, you may come across another method to post to the page, namely htmldb_Get, and this is built in in the apex javascript files. I don't use it because it is not true Ajax (asynchronous), but Sjax (synchronous), and is not transparant enough <strong>in my opinion</strong>. There is i believe some GetAsync part to it, which is good and all, but you'll barely ever see it used in any example. Now get.get(); may look easy, and you can achieve the same result in 3 lines of code instead of my piece of js, consider it a good practice to implement real ajax calls. You might the wait to finish a process too much at times, and realise all those htmldb_Get calls in your applications aren't as fun as you think they were...

    Things are also more complicated with this report setup. With page items you can take another route: instead of executing a javascript block you can execute a plsql code block. There you can specify items to be submitted to session state (so they can be used in the plsql block) and items to have their value set by this plsql block. (sql blocks are run server side, which means referenced items need to have their values in session sate). I'd advice to play around with that aswell. That being said, you could do this with the report actions too, but you'd need to set up some bogus items to temporarily hold values which can then be submitted to session state. Again, i'm no fan of that so i didn't went with that (another 'imo' thus).

    Essentially, have a browser inspection tool such as firebug open. It allows you to inspect ajax calls made on pages, and provides a great way to look into this.

Recommend

  • TFS edit build log using custom activity
  • Chrome breakpoint on radio doesn't fire
  • Android onKey w/ virtual keyboard
  • How do I Dispose a HttpResponseMessage in my Web Api Method?
  • Git describe fails to return most recent annotated tag
  • C: Incompatible pointer type initializing
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • How do I exclude a dependency in provided scope when running in Maven test scope?
  • Database structure design with variable amounts of fields
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Asynchronous UI Testing in Xcode With Swift
  • Recording logins for password protected directories
  • Splitting given String into two variables - php
  • How do I fake an specific browser client when using Java's Net library?
  • How to get a value (ex: baseURL) in every Karate feature?
  • angularjs unit test when to use $rootScope.$new()
  • Check if a string to interpolate provides expected placeholders
  • Fill an image in a square container while keeping aspect ratio
  • How to model a transition system with SPIN
  • How to make Safari send if-modified-since header?
  • Weird JavaScript statement, what does it mean?
  • VBA Convert delimiter text file to Excel
  • ORA-29908: missing primary invocation for ancillary operator
  • Rearranging Cells in UITableView Bug & Saving Changes
  • How to pass list parameters for each object using Spring MVC?
  • How to disable jQuery.jplayer autoplay?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • How to stop GridView from loading again when I press back button?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • JaxB to read class hierarchy
  • Checking variable from a different class in C#
  • Django query for large number of relationships
  • Busy indicator not showing up in wpf window [duplicate]
  • costura.fody for a dll that references another dll
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Reading document lines to the user (python)
  • Binding checkboxes to object values in AngularJs
  • How can I use `wmic` in a Windows PE script?