Formatting negative dollar amounts in ColdFusion

Have you ever tried to format a number into a negative dollar amount with cents like -$1,000.00?

If you have, I’m pretty sure you experienced some weird things with ColdFusion that you thought “should” work.

First off, let’s take a look at dollarFormat():

value = dollarFormat(-1000);

This will give the following:  ($1,000.00)

Close, but instead of a minus sign before the $ sign, the whole amount is between parenthesis (mainly used to signify a negative amount in accounting).

Since dollarFormat() only takes 1 argument, that’s about all we can do with that.

Let’s try numberFormat():

value = numberFormat(-1000, ‘$_,___.__’);

This gives the following: $-1,000.00

Close, except for some reason the minus sign is after the $ sign instead of before it. I tried different variations passing in different format masks to numberFormat() but couldn’t make it format the way I want -$1,000.00

Adam Cameron has a solution:

value = createObject(“java”, “java.text.DecimalFormat”).init(“$##,####0.00;-$##,####0.00”).format(javacast(“double”,-1000));

He suggests to “use Java’s DecimalFormat which allows one to provide a formatting mask for both positive and negative numbers.”

The result is: -$1,000.00 which is exactly what I wanted! YAY!

Remove Link Text in Bootstrap Print View

Whenever you try printing a page that has a Twitter Bootstrap theme and there is an anchor tag linking somewhere, the link is converted into text and displayed when printed. This can be annoying if you don’t want to print the links.
Here’s how you remove this:

In bootsrap.css there’s a line:

@mediaprint{
...
  a[href]:after {
    content:" (" attr(href)")";
  }
...
}

This is what’s displaying the text after each link. Simply change the content to “none” and that should do it like so

@mediaprint{
...
  a[href]:after {
    content:none;
  }
...
}

Now you don’t have annoying links displayed in your printouts!

ColdFusion – No Caching Pages

Have you ever wanted to prevent the user from clicking the browser’s “Back” button to go back to a previous page?

This usually happens after some processing has been like making a payment or logging out of the system.

For instance, if a user logs out of your system and they click on the browser’s back button, the previous page where they would need to be logged in would still load.

This is because the browser caches the page so even if all your session variables are destroyed the page still loads as if you were still logged in visually only.

Of course if you click on a link that directs you to another “must be logged in” page it’ll perform the usual “You must be logged in” logic.

The way to prevent this from happening is not to try and disable the browser’s back button (that’s a no-no), but instead we should prevent the browser from caching the page.

This can be done easily in Coldfusion by adding the following lines to your code:

<cfheader name=“cache-control” value=“no-cache, no-store, must-revalidate”>
<cfheader name=“pragma” value=“no-cache”>
<cfheader name=“expires” value=“#getHttpTimeString(now())#”>

Voila! Now when the user clicks back, since we told the browser not to cache the page, it will make the normal request to the server and the normal session verification occurs and the cached page will not display.

Datatables – Fixed Header

I’m sure most developers already know about data tables: http://datatables.net/

It’s a really awesome jQuery plugin that allows you to quickly sort/search your HTML table. It’s really easy to use and there are tons of examples in the site.

To use data tables simply create your HTML table with proper THEAD and TBODY tags then apply data tables to your table:

$(document).ready(function(){

    $('#myTable').dataTable();

});

There are also several plugins to make data tables even more functional and amazing!

One thing that constantly came up in my projects is that when users display a lot of records per page or if they have lower resolution screens then they have a problem remembering which columns contain what data when they scroll down the table since the header is off screen.

I searched for solutions and found several plugins that surround the data table in a DIV and make the DIV scrollable. It works but doesn’t look that nice visually.

 

One useful plugin I found is the Fixed Header  plugin. It’s easy to implement.:

$(document).ready( function () {

    var table = $('#myTable').dataTable();

    new $.fn.dataTable.FixedHeader( table );

} );

This plugin floats the table headers to the top of the window when scrolled so even when you scroll down the page the header is always visible.

Another great thing about this plugin is that it works without using data tables as well.

Go check it out!

CFScript – Converting an Excel Sheet to a Query

I just posted about an issue I had when importing an Excel file to the database here.

In that project I used a function written by Silverink Consulting. The article is called CFSpreadsheet in cfscript. The hard way and I recommend you check it out.

You just add the function, call it with:

resultQuery = ExcelToQuery(params.thefile);

and it just works!

Just wanted to document it here since I found it very useful.

CFWheels – Model Not Returning Correct Results in Loop

The other day I was working on a project for a client and they wanted an import functionality that would allow them to upload an  Excel file and have the system save the data in the database.  The Excel file  contained duplicates and of course I don’t want duplicate records so I would check whether it already exists in the database before I insert it.
Each time I cleared the data and imported the file it would ALWAYS add all data including duplicates. I would convert the file to a query using a function that I got from here by Silverink Consulting since CFScript didn’t have a way to use CFSpreadSheet. Then I would loop through the query and check whether or not the record exists before adding it.   All the logic makes sense and I couldn’t figure out what I did wrong.

Here’s a simplified version of the code:

// convert file data to query
data = ExcelToQuery(params.theFile);

// loop through each record
for(person in data){
// check if person exists
    person = model(“Person”).findOne(where=”name = ‘#trim(person.name)#'”);
if(NOT isObject(person){
// add person
person = model(“Person”).new();
person.name = trim(person.name);
person.save();
}
}

Turns out that CFWheels caches duplicate queries during a lifetime of the request. Since the query is in the loop and in the same request, it cached.

So if the name was “Bob” obviously it would return false initially since it doesn’t exist in the database.

When it encounters “Bob” again you would think it would find it but since the query is cached it still returns false, therefore, the duplicate record is inserted.

The way to get around this is to ensure the query is not cached. The way to do this is add reload=true in the .findOne()

  person = model(“Person”).findOne(where=”name = ‘#trim(person.name)#’, reload=true”);

Now if we import the file it correctly detects the duplicate and does not insert it. Problem SOLVED!

ColdFusion – Using SQL String in CFQUERY with Quotes

Today a coworker was frustrated over a problem that seems like it should work.

He was using a SQL string and using that inside a CFQUERY like so:

<cfset sqlString = “SELECT id, name FROM users WHERE name = ‘John’ ” />

<cfquery name=”myQuery” datasource=”myDsn”>
#sqlString#
</cfquery>

This looks like it should work but it doesn’t. Any single quote will be changed to two single quotes so when the query actually executes, the query looks like this:

SELECT id, name FROM users WHERE name = ”John”

At first glance it looks like John is surrounded by double quotes but it’s not. It’s surrounded by two single quotes.

This obviously throws an error complaining about the double single quotes.

The solution is to replace the double single quotes with a single quote like so:

<cfquery name=”myQuery” datasource=”myDsn”>
#ReReplace(sqlString, “””, “‘”, “all”)#
</cfquery>

It’s hard to see what’s going on so I’ll expand the replace code

  #ReReplace(sqlString, “””, “‘”, “all”)#

Note that the double single quotes are surrounded by double quotes and the single quote is surrounded by double quotes.

We are passing sqlString to the ReReplace function and telling it to find all double single quotes and replacing them with single quotes.

Voila! It works!

Hope this saves someone  from pulling out their hair trying to figure out why it’s not working.

CFWheels – Set NULL Values

Haven’t posted in awhile so I thought I’d post this little tidbit in case people find it useful.

People might try to set fields to NULL in CFWheels by passing in “NULL” to the model but that actually doesn’t work.

The way to do it is to simply set the field to an empty string which will be interpreted as NULL by CFWheels.

This came in handy today when I had to overwrite some fields and set them to NULL in one of my projects.

That’s it! Enjoy!

Railo Automatically Encoding URL

A few days ago I posted about implementing Social Signin/Login via OAuth and ColdFusion. I had everything working on a ColdFusion but once I moved the code to Railo I had a problem.

Turns out that Railo automatically encodes the URL of an http call. This caused a problem when calling the LinkedIn to retrieve a profile record after authentication.

LinkedIn says to use the following format to make a call to their Profile API to get custom fields from profile details:

http://api.linkedin.com/v1/people/~:(first-name,last-name)

As you can see the URL contains a ~ which indicates that we want to get data for the current user and this is what causes the problem. The fields in the brackets specify which fields to return.

When you make a call using this URL the response you get is that the profile couldn’t be retrieved based on the field because the ~ gets encoded to %7E and that string was used to search instead of ~.

Seems like other people were having similar issues such as this post by theCFGuy with Railo auto encoding URLs.

The quick and easy solution is to set the encoding to “false”.

In CFScript use the http() service:

httpService = new http();

httpService.setEncoded(“false”);

In CFML use cfhttpparam when doing a cfhttp call:

<cfhttpparam type=”URL” encoded=”false” name=”something” value=”some_value” />

I’m sure there are other URLs that cause similar problems so hopefully this saves some people some time as it took me awhile to realize that the issue was the URL automatically being encoded in Railo.

OAuth with Coldfusion (Facebook, LinkedIn, Google)

Today I was given the task of implementing signup/login for one of our sites via social media (Facebook, LinkedIn, Google, etc).

Signup/login via social media is becoming popular for most sites. Some sites even only provide logging into their sites via social media.

Why use social media signup/login?

• most people already have an account to a popular social media site
  • signing up/logging in is a breeze since they don’t need to fill out registration forms
• you can pull information from their account
  • depending on how much info you request and how much they authorize, you can get a lot of info about them and this can be really beneficial
• reduced spam
  • you don’t have to worry about this as much since social media sites already handle this for you

Now that we know why we should include social media login to our sites, let’s talk about how to implement it in ColdFusion.

Ray Camden, one of the ColdFusion gurus, has an amazing article on how to implement OAuth authentication with the big three social media sites (Facebook, LinkedIn, Google).

His step by step instructions are clear and easy to follow. I was able to set up OAuth with my site pretty quickly and I encourage you check them out!

OAuth with Facebook

OAuth with LinkedIn

OAuth with Google