Servoy TIP: 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: 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 (much like my FileMaker Tip on the same subject)


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: 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!).

Do you already use UUIDs as primary keys in your solutions - or just the integer keys? Drop a comment below to chime in!

Servoy TIP: How To Automate Sending Mail from Gmail

I recently needed to create some functionality to send mail via my Gmail account from within Servoy. It sounds pretty straight forward, but it turned out that there was one parameter that tripped me up. If you don't include this line of code - it won't send email, no matter what you try:

mail.smtp.starttls.enable=true

Took me a little while of digging on the Internet and trying stuff to get it to work. So, for this week's tip - I thought it might be a good idea to share the code:

function sendEmailUsingGmail()
{
       var properties = new Array();
       properties[0] = 'mail.smtp.host=smtp.gmail.com';
       properties[1] = 'mail.smtp.port=587';
       properties[2] = 'mail.smtp.auth=true';
        properties[3] = 'mail.smtp.username=';
       properties[4] = 'mail.smtp.password=';
       properties[5] = 'mail.smtp.starttls.enable=true';
       
        var msgText = 'test message';
        var success = plugins.mail.sendMail('tosomeone@gmail.com', 'fromsomeone@gmail.com', 'subject', msgText, null, null, null, properties);
        if (!success) 
        {
              plugins.dialogs.showWarningDialog('Alert','Failed to send mail','OK');
        }
}

Enjoy!