Wednesday, November 30, 2016

My advice is to use the Advisor

I have noticed (too) often when talking to other APEX developers, that the Advisor functionality is not well known.

Although this is also described in a lot of Best practice  guides, the functionality is in my humble opinion highly underestimated!

The APEX Advisor

I actually don't know how long it's been there, but it is quite some time.
And it's all standard and included in the APEX Application Builder.
It is to be found under Utilities.

It kicks off following popup window

I recommend to have a look at the section Checks to perform:

It may well be possible that you have opted for whatever reason to not follow one of the pre-defined rules of the APEX Development team. You can chose not to check on those rules.

Usually I advice to select all checks and read the advice of the Advisor. You can always opt of course not to do anything with one of the remarks. Because one thing must be clear: your app can run very fine with several Advisor errors or warnings in it. You do not need to fix them all. But it is advised to solve as many as possible and you may learn something from the best practises according to the APEX Development Team. They're smart people ... believe me ;-)

Depending how large your app is - of course - running the Advisor may take some time.
In the end it provides you with a clear overview and with the option to navigate directly to where you need to be to fix the problem.

The result filter

The filter comes in very handy to keep an overview or to focus on one specific type of error (or warning).

An advisor error

I'm not walking you through every possible error an Advisor can identify. 
For each error/warning the Advisor will give you an overview of what he thinks is wrong or can be done better.
With the View button you navigate directly to the place in your application that concerns this hit.

He doesn't know everything

An example of errors an Advisor run is always indicating is the following:

What the Advisor cannot see here, is that this "EVENT_LINK" is part of a specific template. It's not necessarily a real error. So these errors can be ignored.

So okay, here you go. Hope to have convinced some developers to start using it. It's really a good practice to run it at the end (or beginning if you prefer) of every working day!

Happy to share ....

Tuesday, November 22, 2016

Interaction between two date fields made visible upon data entry

Interaction between two date fields

Have you ever been in a situation where you have two date fields and the second one is dependant on the first one? Sure you have!
Every time you do something with a start- and end-date ...

It's so very logic that the end-date must be later than the start-date. As long as we can't travel in time, this is a certainty.


As often, my examples originate from the HR field.
This example is a page in an APEX app that gathers information for a candidate employee.

The candidate category was identified as 'Fixed Term', which implies you have to provide a start-date and an end-date for the contract.
We're gonna focus now on the Date start and on the Date end.

In the example used, the Date start must be a date in the future. The Date end must be later then the Date start.

When you do nothing

It's obvious, when you do nothing, you allow input of all dates.

I'm writing this blog post on November 21st. You can see I'm allowed to enter a date in the past for a Date start:

Same thing for the Date end. Even worse: I'm allowed to enter (pick) a date before the Date start:

Which results possibly in something like this:

What about validations?

Yes, of course, you can use validations to force the user in entering the correct dates. But those validations are executed on page submit. That's a bit too late, don't you think? So read on, it gets interesting now!

Known and standard features of APEX

To start of, we're gonna use a standard APEX feature to make sure the Date start is a future date.
A small notice here: a "future date" in a data entry application may not be clear enough as a spec. Because when reading "future", it will be checked against the system date. So when entering a date today, save the information and update it tomorrow, this system date has obviously changed. So make sure you get the intention of your customer clear! In our example we simplify and acknowledge that we're validating purely against the system date.
First identify the Date start:

Go to the property panel and have a look at the Settings section:

Three options here: Highlighted date, Minimum date and Maximum date. They all operate similarly. Actually the APEX Help explains it quite clearly:

We're now making sure that we highlight the current date (sysdate +0 days).
Same for the minimum date. It will make sure that you cannot pick a date in the past!
Yes!! One down.

We're now at this stage:

You're unable to pick a date in the past!

Also note some other interesting properties for datepicker fields, like e.g. the Show other months (Y/N) property. I suggest to try them out, but it's not the subject of this post ...
You can now do the same for the Date end. However, it will only enforce the user to pick a date in the future. What if the Date start is entered for December 1st (remember, the system date right now is November 21st)? You can still enter November 22nd for the Date end. Not what we want!

Full solution step by step

We're gonna use some javascript coding to make the relation between the Date start and the Date end
However it's been coded in a way that it can be any two date fields on the same page.

Page level functions

In the page properties, navigate to the JavaScript section.

Two functions need to be included here:

function stringToDate(_date,_format,_delimiter) {
    var formatLowerCase=_format.toLowerCase();
    var formatItems=formatLowerCase.split(_delimiter);
    var dateItems=_date.split(_delimiter);
    var dayIndex=formatItems.indexOf("dd");
    var monthIndex=formatItems.indexOf("mm");
    var yearIndex=formatItems.indexOf("yyyy");
    var month=parseInt(dateItems[monthIndex]);
    var formatedDate = new Date(dateItems[yearIndex],month,dateItems[dayIndex]);
    return formatedDate;

function setMinAndMaxDates( fieldnameStartDate, fieldnameEndDate, maximumLengthInYears) {
    var minDateAsString = $x(fieldnameStartDate).value;
    var selectorEndDate = apex.jQuery("#" + fieldnameEndDate);

    if (minDateAsString == null || minDateAsString == '') {
        selectorEndDate.datepicker("option", "disabled", true);
    else {
        var minDate = stringToDate( minDateAsString, "dd-mm-yyyy", "-");
        // Determine maxDate by adding given amount of years to minDate:
        var maxDate = new Date(minDate);
        maxDate.setFullYear(maxDate.getFullYear() + maximumLengthInYears);

        selectorEndDate.datepicker("option", "minDate", minDate);
        selectorEndDate.datepicker("option", "maxDate", maxDate);
        selectorEndDate.datepicker("option", "disabled", false);

The first function (stringToDate) will need to hold the date format that you use. Generally accepted in my neck of the woods is the format "dd-mm-yyyy". So you will need to adapt this to your own setting. Or when you allow the user to pick the date format in a preference page, you will need to make this function more flexible as well!

The second function (setMinAndMaxDates) will actually add the needed attributes to the page items on the fly.
As you can see, the function accepts 3 parameters. The fieldnameStartDate and fieldnameEndDate refer to the page items you want to refer to. This makes the use of these functions very flexible. And you can just copy and past it over to any other page in your app.

Dynamic action doing the magic

As so often is the case, here again the dynamic action will do the magic!
In the use case described here, "something" will need to happen to the Date end when a date is picked in the Date start.
Logically the On change dynamic action is used on the Date start.

Following properties are set on Dynamic action level:

And because the functions are coded generically, the javascript in this TRUE action remains extremely simple:

setMinAndMaxDates("P3_DATE_START", "P3_DATE_END", 10);


This results in following behaviour:

Date start

As soon as you pick a date in the Date start datepicker, the Date end gets defaulted to the same date.

Date end

No dates can be picked before the Date start.
Here you go .... all done !!!

Oh no ... there is a "but" 

Yes, the user gets guided perfectly when using the datepicker. But he can still pick a date and then change it manually!

My conclusion

So it doesn't take away all your pains. But it does support your user in the best possible way. And because that user can still manage to make things wrong, you should also provide the validations on page submit.
But I hope you agree with me that it is a nice way to help the user with the data entry.


For this blogpost I must specifically thank my colleague Joost Hoevers, as he has coded the nice solution and made it perfectly re-usable! 

Thank you, 

Happe to share!

Tuesday, November 15, 2016

Did you know? Apps aliases must be unique cross workspace

... and Oracle isn't checking it for you!


this is just a quick note. Seems logical, but YOU have to take care of it!

Recently I uploaded an APEX export file from an app that my colleague had created.
I just wanted to check something he had done, so I uploaded the application in my own workspace. That's a workspace I use to quickly check something or do some research on.
It is however on the same database that my colleague is using to develop this particular application. And his customer is actually looking at that environment as well during the development phase.

So I uploaded the the export file and installed the app, accepting all the defaults there are when you import and install an APEX app.
But what I didn't know, is that APEX is NOT changing the application alias. Something that is done when you import an app that got exported from the same workspace. I believe it adds the new application ID to the alias.

But since this app came from a different workspace, it nicely installed it with the existing alias, causing the original app to fail.

So I just wanted you to know ... I will never forget this. And you may learn from my mista ... well ... from my experience! ;-)

Happy to share!