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!


  1. Hi Roeland, if you could register in apex.world with a profile picture, I could post your blog in the News section :-)

    1. Thanks Juergen! Meanwhile I've registered in APEX.world :-)

  2. Hey Roeland,

    Nice post, and well explained.
    To validate your date input on manual entry, you can write a small validation -on change of end date- in javascript that throws an alert when end date is before start date.


    1. Hi Vincent,

      thanks, very good addition ... that makes it more complete.