Remove punctuation from string using Javascript

I’m a morning person, and my mind is usually sharpest on Monday or Tuesday mornings, so these are the best times for me to work on fiddly javascript stuff. Today was one of those mornings and here are the results, just in case I want to refer back to them later on.

I had many items dotted around an Apex application where the user is allowed to enter “Codes” – values that must be uppercase and contain no spaces or other punctuation characters – except underscores (_) were allowed.

To make things easier for the user, I wanted the page to automatically strip these characters out when they exit the field, instead of just giving validation errors (Note: I still included the validations, but the javascript just makes the process a bit smoother for the user doing the data entry).

My APEX application already has a global .js file that is loaded with each page, so all I had to do was add the following code to it:

function cleanCode (c) {
  return c.replace(/[^A-Za-z0-9_]/g,"");
}

$(document).ready(function() {

  //automatically remove non-code characters from
  //"edit_code" class
  $( document ).on('change', '.edit_code', function(){
    var i = "#"+$(this).attr("id");
    $(i).val( cleanCode($(i).val()) );
  });

});

EDIT: greatly simplified regexp based on the excellent contribution by Jacopo 🙂
EDIT #2: corrected, thanks to Sentinel

Finally, on each “Code” page item, I set the following attribute (or append, if other classes have already been added):

HTML Form Element CSS Classes = edit_code

For code items within a tabular form, I set the following column attribute:

Element CSS Classes = edit_code

Calculate Age in Javascript

I had a registration form in Apex which asks the applicant to enter their Date of Birth in a date item; I then needed to calculate how old they would be at the start of the event, which determines a number of rules, such as whether we need to obtain their parent’s permission.

In my first release I implemented this with a Dynamic Action which ran SQL something like this:

select round(months_between(start_date
                           ,to_date(:P1_DATE_OF_BIRTH,'DD-MON-YYYY'))
             / 12,1)
from events
where event_id = :P1_EVENT_ID;

This worked fine – it takes advantage of Oracle’s builtin support for date arithmetic. However, it was rather slow, because it needs to do a roundtrip to the database to run the query and return the result.

I wanted a pure javascript implementation to avoid the roundtrip, but my initial searches came up with a number of sub-par solutions involving extracting the year and month portions and applying simple arithmetic which did not take into account leap years.

Instead, I’ve gone with an easier solution taking advantage of the moments javascript package.

1. Add the path to moment.min.js in the File URLs attribute of the page. You could get your own local copy or point to the relevant file from a cdn: http://cdnjs.com/libraries/moment.js/
2. Add a function to the Function and Global Variable Declaration attribute of the page, which uses the moments object to convert the strings into date objects, and then call the diff method to get the number of years as a floating-point number, e.g.:

   function getAge() {
     var e = moment($v("P1_EVENT_DATE"),"YYYYMMDD")
        ,dob = moment($v("P1_DATE_OF_BIRTH"),"DD-MMM-YYYY");
     return e.diff(dob,'years',true).toFixed(1);
   }

3. Add a Dynamic Action to the Date of Birth item which calls getAge() and sets the value of the Age display item.

The result is a much quicker response and less load on the database. This is an intentionally simple example, you could do it in different ways to suit your situation (e.g. if you have multiple date items you need to handle on the same page, you might pass them as parameters to the function).

The moments javascript package has an impressive list of features, including pretty-formatting a duration (e.g. a client-side version of the SINCE format e.g. “3 years ago”) documented here.

Next/Previous buttons from Interactive Report results

What could be simpler than a set of “Next” and “Previous” buttons?

I love Interactive Reports, they make it easy to deliver a lot of power to users for very little development effort. However, with that power comes some complexity for building certain features not available in the base Apex toolset.

I had an IR with a fairly costly and complex query behind it, linked to another screen to view the details for a record. The users wanted to be able to view each record from the search result without having to click each record, go back to the Search, and click the next record, etc. Instead, they wanted “Next” and “Previous” buttons on the Single Record screen which would allow them to navigate the search results.

There are a few ideas on the web around how to implement this, and I considered two of them:

1. On the Single Record screen, run the query to determine what the next and previous records are in relation to the currently viewed record.

2. On the Search screen, instead of querying the tables/views directly, call a database procedure to store the query results in a collection; then, both the Search and the Single Record screens can query the collection.

Some problems with solution #1 include (a) the query might be quite expensive, so the Single Record screen may be too slow; and (b) if the data changes, the user might get unexpected results (“that record didn’t appear in my search results?”).

Another problem that both of these solutions share is that if the user has used any of the IR features such as custom filters, sort orders, etc. we won’t necessarily pick these up when determining what the Next/Previous records are. Certainly, the collection approach won’t pick these up at all.

Instead, I’ve gone for a different approach. Firstly, I’ve simplified the problem by dictating that the Next/Previous buttons will only allow the user to navigate the list of records they most recently saw on the search screen; therefore, if the results are paginated, we’ll only navigate through that page of results. If the user wants to keep going, they’ll go back to the Search screen and bring up the next page of results.

The solution is quite simple in concept but was a bit tricky to implement. The basic idea is that I encode the record IDs in the HTML generated by the report, and use some Dynamic Actions to grab the resulting list of record IDs, store them as a CSV in a page item, which can then be parsed by the View/Edit screen.

Here it is in detail, ready for you to try, critique or improve (in this example, my record’s id column is called ft_id, my Search screen is p23, and the Single Record screen is p26):

Part A: Save the IDs shown on the Interactive Report

1. In the Link Column on the interactive report region, add class="report-ft-id" to the Link Attributes.

2. In the Link Icon, add data-ft-id=#FT_ID# into the img tag.
I added this to the img bit because the Link Column field doesn’t do the #FT_ID# substitution, unfortunately.
3. Add the following javascript function to the page’s Function and Global Variable Declaration:

function ft_id_list() {
  return $(".report-ft-id >img")
         .map(function(){return $(this).attr("data-ft-id");})
         .get()
         .join(",");
}

This function searches for any records shown on the page by searching for the report-ft-id class, extracts from each one the img node’s data-ft-id attribute, maps these into an array, and then squashes that array down to a comma-separated list.

4. Create a hidden item P23_FT_ID_LIST which will store the resulting list.

5. Create an application item FT_ID_LIST which will be read by the Single Record page.

6. Create a Dynamic Action triggered by the event Page Load, which saves the list of IDs into session state by performing two actions:
(a) Set Value based on a JavaScript Expression, ft_id_list();, assigned to the item P23_FT_ID_LIST
(b) Execute PL/SQL Code which runs the code :FT_ID_LIST := :P23_FT_ID_LIST;. Make sure to set Page Items to Submit to P23_FT_ID_LIST and set Page Items to Return to FT_ID_LIST.
This dynamic action will only fire when the page is initially loaded.

7. Copy the Dynamic Action, but this time set the event to Custom and the Custom Event to apexafterrefresh. This way, whenever the user changes the rows shown in the report (e.g. by paginating, or changing filters or sort order, etc.), the list will be refreshed as well.

Part B: Add the Next/Previous buttons

8. Create some procedures on the database (e.g. in a database package) which take a list of IDs and a “current” ID, and return the next or previous ID in the list:

FUNCTION next_id
  (id_list IN VARCHAR2
  ,curr_id IN VARCHAR2
  ) RETURN VARCHAR2 IS
  buf     VARCHAR2(32767) := ','||id_list||',';
  search  VARCHAR2(100) := ','||curr_id||',';
  pos     NUMBER;
  new_id  VARCHAR2(32767);
BEGIN
  pos := INSTR(buf, search);
  IF pos > 0 THEN
    -- strip out the found ID and all previous
    buf := SUBSTR(buf, pos+LENGTH(search));
    -- chop off the first ID now in the list
    IF INSTR(buf,',') > 0 THEN
      new_id := SUBSTR(buf, 1, INSTR(buf,',')-1);
    END IF;
  END IF;
  RETURN new_id;
END next_id;

FUNCTION prev_id
  (id_list IN VARCHAR2
  ,curr_id IN VARCHAR2
  ) RETURN VARCHAR2 IS
  buf     VARCHAR2(32767) := ','||id_list||',';
  search  VARCHAR2(100) := ','||curr_id||',';
  pos     NUMBER;
  new_id  VARCHAR2(32767);
BEGIN
  pos := INSTR(buf, search);
  IF pos > 0 THEN
    -- strip out the found ID and all following
    buf := SUBSTR(buf, 1, pos-1);
    -- chop off all but the last ID in the remaining list
    IF INSTR(buf,',',-1) > 0 THEN
      new_id := SUBSTR(buf, INSTR(buf,',',-1)+1);
    END IF;
  END IF;
  RETURN new_id;
END prev_id;

9. Add two hidden items to the Single Record screen: P26_FT_ID_NEXT and P26_FT_ID_PREV.

10. On P26_FT_ID_NEXT, set Source Type to PL/SQL Expression, and set Source value or expression to next_id(:FT_ID_LIST,:P26_FT_ID), and similarly for P26_FT_ID_PREV to prev_id(:FT_ID_LIST,:P26_FT_ID).

11. Add buttons Next and Previous, with Action set to Redirect to Page in this Application, pointing back to the same page, but setting the P26_FT_ID to &P26_FT_ID_NEXT. and &P26_FT_ID_PREV., respectively.

This method means that it doesn’t matter if the query behind the report changes, or if the user adds filters or uses different saved reports; the Single Record screen doesn’t need to know – it just needs to know what the list of IDs the user most recently saw on the Search screen were.

Some downsides to this approach include:

• Server load – the dynamic actions on the report refresh, which causes it to do an ajax call to the database on every refresh of the IR. But at least it saves the View/Edit screen re-executing the query on every page load.
• Rows Per Page limitation – since we save the list of IDs as a CSV in a single string variable, we may have issues if the user sets Rows Per Page to “All” with a large result set – so we need to limit the Maximum Rows Per Page to about 3,000 (this assumes that all the IDs will be less than 10 digits long) to fit in the 32,767 char limit. YMMV.
• Duplicate records – this method assumes that the IDs shown in the report will always be distinct. If this is not true, the next/previous functions will not allow the user to navigate through the whole list.