Servoy TIP: Getting The Current Quarter Date Range

I do a lot of applications where users want reports or lists that take a date in the current record and filter or find all the records "In This Quarter".

The calculation isn't all that difficult - but it's a cool time saver. Below, I've actually created 3 functions - so you can use the quarter start date, end date, or date range.


/**
 * Return the starting quarter date for a given date
 * 
 * @param {Date} inputDate
 * @return {Date}
 */
function getQuarterStartDate(inputDate) {
if(!inputDate) { return null; }
var inDate = new Date(inputDate);
var mo = inDate.getMonth() + 1; //remember JS months are 0-11!
var yr = inDate.getFullYear();
if(mo > 9) {
return new Date(yr, 10, 1);
} else if (mo > 6) {
return new Date(yr, 7, 1);
}  else if (mo > 3) {
return new Date(yr, 4, 1);
} else {
return new Date(yr, 1, 1);
}
}


/**
 * Return the ending quarter date for a given date
 * 
 * @param {Date} inputDate
 * @return {Date}
 */
function getQuarterEndDate(inputDate) {
if(!inputDate) { return null; }
var inDate = new Date(inputDate);
var mo = inDate.getMonth() + 1; //remember JS months are 0-11!
var yr = inDate.getFullYear();
if(mo > 9) {
return new Date(yr, 12, 31);
} else if (mo > 6) {
return new Date(yr, 9, 30);
}  else if (mo > 3) {
return new Date(yr, 6, 30);
} else {
return new Date(yr, 3, 31);
}
}

/**

 * Return a search string properly formatted for the start/end quarter dates

 * 

 * @param {Date} inputDate

 * @return {String}

 */

function getQuarterRange(inputDate) {

if(!inputDate) { return null; }

var sDate = getQuarterStartDate(inputDate);

var eDate = getQuarterEndDate(inputDate);

var theFormat = i18n.getDefaultDateFormat();

if(sDate && eDate) {

return utils.dateFormat(sDate,theFormat) +

"..." + utils.dateFormat(sDate,theFormat) + "|" + theFormat;

} else {

return null;

}

}

Servoy TIP: Changing A Form's Style On-The-Fly!

I had a need the other day to change the style that was displaying on a form. My first thought was to just change all the object properties via scripting (a HUGE pain!). But, with the help of the SolutionModel (and controller.recreateUI() - in version 5.2+) - it was a snap.

Let's say you have two styles: "defaultStyle" and "fancyStyle" defined in your resources. If you use this code:

var myForm = solutionModel.getForm(controller.getName());
myForm.styleClass = "fancyForm";
controller.recreateUI();

Done! Be careful here - depending on how you designed your styles - things could get "wonky" (margins are the big culprit)... but man, did I mention how much I LOVE this tool?!

Servoy TIP: How To Create a Number Suffix

Here's a tip for adding the number suffix - like 1st, 2nd, 3rd, 4th, etc:

/**
 * @param {Number} input - the number to get the suffix for
 * @param {Number} returnWholeString - values: 0 (or null) - just the suffix, 1 (or >0) - number and suffix
 */
function getNumberSuffix(input,returnWholeString) {
if(returnWholeString == null || returnWholeString == undefined || returnWholeString == 0) {
returnWholeString = 0;
} else {
returnWholeString = 1;
}

var output = "th"
var last = 0;

if(input%100 >= 11 && input%100 <= 13) {
output = "th"
} else {
last = Math.ceil(input%10);
if(last == 1) {
 output = "st";
} else if(last == 2) {
output = "nd";
} else if(last == 3) {
output = "rd";
}
}

if(returnWholeString) {
return input + output;
} else {
return output;
}
}

Example usage:

getNumberSuffix(21) returns "st"
getNumberSuffix(21,1) returns "21st"

getNumberSuffix(13) returns "th"
getNumberSuffix(13,1) returns "13th"


Pretty simple - but powerful!

Servoy TIP: Calculating Age

Calculating the age of something (or someone) is a task that I come across a lot. Here's a simple function that will help get you the basics:

function getAge(myDate){
  // myDate= new Date("7/12/1968")
  var now = new Date();
  var yr = now.getFullYear() - myDate.getFullYear();
  var mo = now.getMonth() - myDate.getMonth();
  if(mo < 0) {
  mo = myDate.getMonth() - now.getMonth();
  }
  var days = now.getDate() - myDate.getDate();
  if(days < 0) {
  days = myDate.getDate - now.getDate();
  }
  var agestr = yr + " years, " + mo + " months, " + days + " days";
  plugins.dialogs.showInfoDialog("Date DIff", agestr,"OK");

}

You can (should) enhance the function by checking the years, months and days for plurality and modifying the strings accordingly:

function getAge(myDate){
  // myDate= new Date("7/12/1968")
  var now = new Date();
  var yrTxt = "year";
  var moTxt = "month";
  var dayTxt = "day";

  var yr = now.getFullYear() - myDate.getFullYear();
  if(yr != 1) {
  yrTxt += "s";
  }
  var mo = now.getMonth() - myDate.getMonth();
  if(mo < 0) {
  mo = myDate.getMonth() - now.getMonth();
  }
  if(mo != 1) {
  moTxt += "s"
  }
  var days = now.getDate() - myDate.getDate();
  if(days < 0) {
  days = myDate.getDate - now.getDate();
  }
  if(days != 1) {
  dayTxt += "s";
  }
  var agestr = yr + " " + yrTxt + ", " + mo + " " + moTxt + ", " + days + " " + dayTxt;
  plugins.dialogs.showInfoDialog("Date DIFF", agestr,"OK");

}

Happy age calculating...

Servoy TIP: How To Trap For Modifier Keys

When you're developing your solution - there are times where you want to know if the user had any modifier keys pressed when they performed the action (drag and drop, right click, shift key down, etc.). Servoy returns a number for each key that is held down and, if you add up the numbers, you can tell which keys are down.

Luckily, there's a really straightforward to accomplish this - and it's all held in the event that triggered your method.

if ((event.getModifiers() & JSEvent.MODIFIER_SHIFT) != 0) {
   application.output("shift")
} else if ((event.getModifiers() & JSEvent.MODIFIER_ALT & JSEvent.MODIFIER_CTRL) != 0 ) {
   appliction.output ("alt and ctrl");
}

The operator is a bit operator - that's why you have to use this type of syntax - but since there are event constants (under Application -> JSEvent in the Solution Explorer) - it's easy to check for whatever key combination you want!

You can also tell what type of action the user was doing (as well as any key combinations) - by using the event.getType() function. Here's an example:

if (event.getType() == JSEvent.ACTION) {
   application.output("user clicked");
} else if (event.getType() == JSEvent.RIGHTCLICK) {
   application.output("user right clicked");
} else if (event.getType() == JSEvent.DOUBLECLICK) {
   application.output("user double clicked");
}

There are other even type constants you can take advantage of including: action, datachanged, focuslost, focusgained, none (for unknown types), ondrag, ondragover, ondrop and rightclick.

Servoy TIP: Leap Year Function

There may be times in your solution when you need to see if a certain year is a leap year. Here's the basic rules to determine if a date is a leap year or not:

A year will be a leap year if it is divisible by 4 but not by 100. If a year is divisible by 4 and by 100, it is not a leap year unless it is also divisible by 400.

So, as always is the case when you use JavaScript - there are many different ways to get this done. I've seen some very long functions that use  the "brute force" method to parse the date and check the year. Here's one example (modified for copy/paste use in Servoy 4+):

function isleap(yr)
{
 if ((parseInt(yr)%4) == 0)
 {
  if (parseInt(yr)%100 == 0)
  {
    if (parseInt(yr)%400 != 0)
    {
    application.output("Not Leap");
    return "false";
    }
    if (parseInt(yr)%400 == 0)
    {
    application.output("Leap");
    return "true";
    }
  }
  if (parseInt(yr)%100 != 0)
  {
    application.output("Leap");
    return "true";
  }
 }
 if ((parseInt(yr)%4) != 0)
 {
    application.output("Not Leap");
    return "false";
 }
}

However, there's a MUCH easier way to do the same thing:

function isLeap(yr) {
 return new Date(yr,1,29).getDate() == 29;
}

This function simply creates a new date with the year you pass in and February as the month (JavaScript months start at 0=January... yeah, I know - I HATE that as well!) and 29th as the day. JavaScript will auto-convert this to either Feb 29, yr - OR March 1, yr. So if the day that gets returned is the 29th - it's a leap year, if not, then it's not a leap year.

You can also test if your calculation is working - by looking at this list of all leap years 1800-2400.

Servoy TIP: Setting or Passing Multiple Variables At Once

Servoy methods are JavaScript (or Java) functions - and in today's tip I'm going to show you how to initialize multiple variables at once and how to use an array to send multiple values as parameters to a different method.

Let's start with the easy one first - initializing multiple variables to a single value. Now, keep in mind - there is no "right way" to do this - it's a matter of your personal preference and it also depends on how readable you want your code to be.

A best practice, and a good habit to get into, is to declare all the variables you're going to use in your method at the top of your method. This way, they're all in one place, and it's easy to assign default values. To demonstrate, here's some code:

var formName = null;
var showPreview = null;
var hasRecords = null;
var whatReport = "Customers";
var useDefaults = 1;

In JavaScript - rather than having each command on its own line, you can combine commands (that end with a semi-colon) into a single line. So we could reduce 5 lines to 1 - but it's much less readable:

var formName = null; var showPreview = null; var hasRecords = null; var whatReport = "Customers"; var useDefaults = 1;

So rather than putting all in a single line - you can reduce the top code to 3 lines - by defining all the variables with a single value into a single line:

var formName, showPreview, hasRecords = null;
var whatReport = "Customers";
var useDefaults = 1;

Now that we've set our variables - let's say we want to call another method and pass all the variables to that method. We could do it a couple of different ways - depending on how we define the incoming parameter list on our function.

For example - we have two methods - one called setVariables() and one called doAction(). The setVariables() function will just set the variables like we did above and then it will call to doAction() method:

function setVariables()  var formName, showPreview, hasRecords = null;  var whatReport = "Customers";  var useDefaults = 1;  doAction (formName, showPreview, hasRecords, whatReport, useDefaults);end function

Then when we define the doAction method - we define it like this:

function doAction( formName, showPreview, hasRecords, whatReport, useDefaults)
  //my actions here
end function

This is the best, and most readable way to accomplish passing the parameters. This will allow you to use all the variables in the doAction method just as they're specified and passed.

However, there may be times when you need to package more information into a single variable. Each variable you pass to a function does not have to be a single value - they can be any object that Servoy supports! That means you can pass arrays, record objects - even entire foundsets between methods! Here's an example:

function setVariables()
  var formName, showPreview = null;
  var hasRecords = forms.customers.foundset;
  var whatReport = "Customers";
  var useDefaults = new Array[1,'blue','green'];
  doAction (formName, showPreview, hasRecords, whatReport, useDefaults);
end function

I hope this will help you with your Servoy development efforts! What other tricks to you guys use when passing/setting parameters?

Servoy TIP: How and Why To Implement UUIDs As Your Primary Key

When setting up your database tables (if you create them in Servoy) - you know that Servoy automatically creates an integer unique record id that you can use when you relate data to/from another table. It's called a primary key. If you're working with datasources and tables that were created elsewhere (or you have a DBA that creates them for you) - they probably have a record id column that set to auto-number (also called an identity column) that auto-increments when a new record is created.

In most cases, an auto-incrementing integer primary key will be just fine.

However, there are some circumstances where that simple system will cause you huge headaches. Here's just a couple of them (if you have others - leave a comment):

• If you have a runtime solution that you want to synch back to a "main" system
• If you want to use database-level replication to have multiple instances of the same data
• If you have a SaaS solution where you have multiple customers entering data

All of these situations demand that you have something other than a simple auto-incrementing integer as your primary key. Enter the UUID. What exactly is a UUID? Here's the Wikipedia definition:

A UUID is a 16-byte (128-bit) number. The number of theoretically possible UUIDs is therefore about 3 × 10³⁸. In its canonical form, a UUID consists of 32 hexadecimal digits, displayed in 5 groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters (32 digits and 4 hyphens). 

For example:
550e8400-e29b-41d4-a716-446655440000

Luckily, Servoy makes it simple to create these types of columns. It's easiest to implement UUIDs when you're first starting off your solution - but it is possible to change over an existing solution (although it can be a ton of work).


If you're starting from scratch here's how to set up your primary key as a UUID:

Create a new table - I'm using a table called "customer" as an example. You'll see that Servoy automatically creates a column called "customer_key" that is defined as an auto-increment, integer, primary key:

The first thing you want to do is to change the "Type" to TEXT and set the length to 50;
Then from the "Sequence Type" choose "uuid generator";
Finally, make sure you check the UUID checkbox in the "Flags" section (Servoy 6 does this for you automatically):

Now you can go ahead and add whatever other columns you want - and save the table. When you go to create a related table (in this case I'm creating a new table called "Invoice") - you want to create a TEXT foreign key (to relate the two together) - and you want to also make sure you set the UUID flag on that field as well:

Now you can go through and build your relationships, and everything as you normally would - easy!

Now, what if you want to convert a solution that exists - that's using the "traditional" integer keys? My suggestion: don't.

Seriously.

It's a lot of work and there will be a huge amount of testing and data stuff to do to populate all the primary keys and foreign keys; go though all the references to the ID field in all your methods, valuelists and relations; etc., etc.

However, if you must - here's a brief overview on how to do it.

• Create a new TEXT column (length 50) in all your tables and make sure you check the UUID flag. I usually call mine something like TableName_UUID (e.g. customer_uuid, invoice_uuid, etc.);
• For each of your related tables - create a new TEXT column for each related key and check the UUID flag. (You might need a few of these columns - for example in an invoice line item that is linked to the invoice and to the product - you'll need two columns);
• In all your tables - populate the new UUID value. The easiest way is to use a form function like this:

function setPrimaryUUID() {
controller.loadAllRecords();
var fs = databaseManager.getFoundSetUpdater(foundset);
while (fsUpdater.next()) {
fsUpdater.setColumn('customer_uuid', application.getUUID());
}
fsUpdater.performUpdate();
}

• Once all those UUID values are set - you need to set all the foreign keys in all your related tables. Now you could use a function like the above and substitute the getUUID() function with the related value (e.g. myRelation.customer_uuid) - or you can just use good old-fashioned SQL to do the update on the backend directly (that's what I usually do). My SQL statement would look something like this:

UPDATE invoice SET invoice.customer_uuid = customer.customer_uuid WHERE invoice.customer_id = customer.customer_id

• Once that's all done - you want to EXPORT A COPY OF YOUR ENTIRE SOLUTION BEFORE YOU CONTINUE!
  
• The easiest way to change all the other references in your solution - is to just search for all occurrences of your old, integer field - using the built-in search tool. Click the little "flashlight" icon and you'll see the search dialog:

• After the search -you'll see all the places that reference that "customer_id" field (in this example). You can then just double-click each object in the list to make the change. You'll have to do this for every primary and foreign key field you create! (told you it was a lot of work!)
  
• Once you've made all your changes, then you need to thoroughly test your solution - because chances are some stuff will break.

As I said, it's a lot easier to implement UUID primary keys when you're first building your solution (same goes with internationalization!).

Servoy TIP: Creating a Timed Recurring Script

Have you ever wanted to create a method that would run at a certain interval within your solution? Maybe you're creating a messaging application that checks for messages every 30 seconds; or maybe you want to automatically generate PDF reports and email them every night...

Servoy TIP: Working With Record Objects

In the last Servoy Tip we explained how to assign a new record object to a variable. Once you have any record object referenced in a variable - there are cool things you can do.

Servoy TIP: How To Efficiently Create Records

This will create a new record as the first row, and will update the user interface. In most cases this is OK - however, there may be times when you want to add records and not update the user interface.

Servoy TIP: How To Reference A Relation in Code

OK - here's the first Servoy QuickTip - and it has to do with relations. When you're using relations in a conditional statement - and especially when you're using them in calculations - make sure you test for the existence of the relation first.