Tag: tips-&-tricks

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

# Help for your keyboard users

Apex’s Blue Responsive Theme 25 is a great theme for building a user-friendly website, and unlike many other themes which make the item labels clickable to get help, it renders little question-mark icons to the right of each item that has a help message defined.

One issue with this design, however, is that a keyboard-savvy user (such as myself) hates to switch between keyboard and mouse – so they Tab between each field as they fill in a form. With this theme, however, those little question-mark icons are in the tab order, so the user has to Tab twice between each field. If they’re not looking at the page they might forget; and if one item doesn’t happen to have a Help icon, they have to remember to only Tab once. All of this adds up to a poor user experience.

To fix this, we simply tell the browser to move the Help icons out of the tab order – and yet again, jQuery comes to the rescue as we can simply pick out all the elements with the class itemHelpButton and set their tabindex to “-1”:

`\$(".itemHelpButton").attr('tabindex',-1);`

Put this code in the page attribute Execute when Page Loads – when the page loads, this will set the tabindex on all the help icons on the page, so now the user can tab through the page without interruption.

# Next/Previous buttons from Interactive Report results

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.

# Show an animated “Please wait” indicator after page submit

My application normally responds to button clicks with sub-second performance, but there were a few operations where users can initiate quite long-running transactions (e.g. up to 15 seconds long in one case where it was hitting an eBus interface thousands of times).

When the user clicks the button, I want the page to show a “Please Wait” message with an animated running indicator (I won’t call it a “progress bar” even though it looks like one, because it doesn’t really show progress, it just rotates forever) until the page request returns.

To do this I added the following to my application, based largely on this helpful article.

1. Add a HTML region on Page 0 (so it gets rendered on every page) at Before Footer, with:

```<div id="runningindicator">
<div id="runningindicator-img"></div>
</div>
```

2. Add the following to the global CSS file for my application:

```div#runningindicator {
display: none;
background-color: #FFF;
border: 1px solid;
border-color: #CCC;
position: absolute;
top: 100px;
left: 50%;
margin-left: -110px;  /* the half of the width */
}
div#runningindicator-img {
background-image: url(/i/processing3.gif);
background-repeat: no-repeat;
width: 220px;  /* the exact width of the image */
height: 19px;  /* the exact height of the image */
}
```

3. Add the following to the global javascript file for my application:

```function run_long_request (request, warnmsg) {
if (!warnmsg || confirm(warnmsg)) {
// disable all buttons on the page
var btns = \$("a[role='button']");
\$x_disableItem(btns, true);
\$("div#runningindicator").show();
apex.submit(request);
}
}
```

4. Change the button:

Action = Redirect to URL
URL Target =

```javascript:run_long_request('APPROVE',
'Are you sure you wish to approve this transaction?');
```

When clicked, the button runs my javascript function which first prompts the user to confirm, and if they do, it disables all the buttons on the page, shows the running indicator, and submits the request (which might be the name of the button, for example).

If I omit the second parameter, the function skips the confirm popup and submits straight away.

Known Issue: the animated gif doesn’t seem to animate in IE8. So far I haven’t worked out how to solve this, except to burn IE8 with fire and extreme prejudice. I’ve tried using setTimeout to delay showing the div but it stubbornly stays frozen.

EDIT: thanks to Peter Raganitsch who alerted me to a simpler option, that doesn’t need the region or the CSS, and animates in IE8:

```function run_long_request (request, warnmsg) {
if (!warnmsg || confirm(warnmsg)) {
apex.submit({request:request,showWait:true});
}
}
```

Mind you, building this sort of thing from scratch was a useful exercise to learn the CSS and javascript tricks necessary. And another thing re-learned: there’s almost always a simpler way.

# Add a Dynamic Total to a Tabular Report

I have a Tabular Report with an editable Amount item. When the page loads, the total amount should be shown below the report; and if the user updates any amount on any row, the total amount should be updated automatically.

Note: this method does not work if you have a tabular report that might have a very large number of records (as it relies on all records being rendered in the page at one time).

1. Make sure the report always shows all the records. To do this, set the Number of Rows and the Maximum Row Count to a large number (e.g. 1000).

2. Add an item to show the total, e.g. P1_TOTAL_AMOUNT. I use a Number field, and add “disabled=true” to the HTML Form Element Attributes so that the user won’t change it.

3. Examine the generated HTML to see what ID is given to the amount fields in the tabular report. In my case, the amount field is rendered with input items with name “f04” and id “f04_0001”, “f04_0002”, etc.

4. Add the following code to the page’s Function and Global Variable Declaration:

```function UpdateTotal () {
var t = 0;
\$("input[name='f04']").each(function() {
t += parseFloat(\$(this).val().replace(/,/g,''))||0;
});
\$s("P1_TOTAL_AMOUNT",t.formatMoney());
}```

This strips out any commas from the amounts before parsing them as Floats and adding them to a running total; it finally formats the total using my formatMoney function and updates the total amount item.

`\$("input[name='f04']").change(function(){UpdateTotal();});`

To prime the total amount field when the page is loaded, I have a Before Header process that calculates the total based on a simple query on the table.

Now, in my case I want to have two running totals: one for “Cash” lines and another for “Salary” lines. My tabular report renders a radio button on each record which the user can select “Cash” or “Salary”. So instead of just the one total amount field, I have two: P1_TOTAL_CASH and P1_TOTAL_SALARY. The radio buttons have hidden input items with the value, rendered with id “f05_nnnn” (where nnnn is the row number).

My UpdateTotal function therefore looks like this:

```function UpdateTotals () {
var sal = 0, cash = 0, amt, rownum, linetype;
\$("input[name='f04']").each(function() {
amt = parseFloat(\$(this).val().replace(/,/g,''))||0;
// determine if this is a Cash or Salary line
rownum = \$(this).prop("id").split("_")[1];
linetype = \$("input[id='f05_"+rownum+"']").val();
if (linetype == "SALARY") {
sal += amt;
} else if (linetype == "CASH") {
cash += amt;
}
});
\$s("P52_TOTAL_SALARY",sal.formatMoney());
\$s("P52_TOTAL_CASH",cash.formatMoney());
}```

`\$("input[name='f05']").change(function(){UpdateTotals();});`
`\$(document).on("change", "input[name='f05']", function(){UpdateTotals();});`