Tuesday, February 22, 2011

Formula to detect duplicate cells in Excel

For when you have a column with values that may or may not contain duplicates. Sort the column A-Z etc and apply the following formula to the column next over.

=IF(C2=C3, "copy", "unique")

Monday, February 21, 2011

UltraEdit macro: go to next/previous white space

In UltraEdit, you can control what delimiter characters are used when you double click or use control+left/right - do it through the Advanced > Configuration dialog, as per below.


I got rid of the underscore character so that my copy/cut/paste macros would work on variable names that include it.

However, sometimes I really just want to jump to the next white space character, ignoring all the delimiters. Most commonly I want this when I am editing something with/a/lot/of/paths. Instead of hitting control+right arrow nine times, I would much rather hit a keyboard shortcut once.

So, here are the two shortcuts I use for this. I map this one to Control+Alt Right Arrow.
InsertMode
ColumnModeOff
HexOff
PerlReOn
Find RegExp " |\t|
"
Key RIGHT ARROW
Key LEFT ARROW
I map this one to Control+Alt Left Arrow.
InsertMode
ColumnModeOff
HexOff
PerlReOn
Find RegExp Up " |\t|
"
Key LEFT ARROW
Key RIGHT ARROW

Windows: one clipboard is not enough!

Too often I have found the need for more than one clipboard in Windows (a clipboard is what you use when you copy, cut and paste). There are various application specific solutions. UltraEdit has 9 inbuilt clipboards (control 0-9, 0 being the default, Windows clipboard). Eclipse has a very cool multi clipboard plugin. But I want something that will work across all applications.

Then I found out about the amazing Autohotkey utilities created by Florian Winkelbauer when they were written up on LifeHacker: DropPub Sends Any File to Dropbox’s Public Folder and Copies the Link to Your Clipboard. DropPub is a brilliant utility to send a file to your DropBox public folder, but the utility that I found more interesting was 4Clip - which gives you 4 extra clipboards linked to F1-F4.

The basic functions are: select some text and press F1+c to copy, F1+v to paste, or F2, F3, F4 - each F key is linked to a different buffer or clipboard. It means you can now "keep in memory" five things at once - the normal Windows clipboard plus four more pieces of text linked to F1 - F4.

Best thing to do is put the exe (or a shortcut to it) in your Windows Startup folder (C:\Documents and Settings\...your username ...\Start Menu\Programs\Startup) so that it will start up automatically with Windows. It's a small utility - taking 4.6 kb on my Windows XP. See the readme.txt included to see other functions.

Florian gives you a zip file including the source and exe file, but the exe is all you really need. I have been using this pretty much daily since I read about it on LifeHacker. But when I asked Florian if he could make it use all the F keys (12 clipboards) he responded within hours with 12Clip.zip (link gone for now). So, extra props to you Florian - thank you very much!

Insert a date into any program using Autohotkey on Windows

Update. Friday 8 March 2013, 01:00:40 AM. If you like a date picker that outputs any date/time in any format, check out Generic Date Picker Version 2 by Paul Moss - and my adaptation of it in response to a request below by Sanjay Mehta.

I previously wrote a post on how insert a date into various programs using built in short cuts. Well, since then I have discovered the joys of AutoHotkey scripts for windows, which let you do just about everything. So, here is an AutoHotkey script for inserting a date - what I like most about it is that you can adjust the format of your date so easily. This one uses control+F12.

; - Insert Date Time stamp
^$F12::
   FormatTime, xx,, dddd d MMMM yyyy ; This is one type of the date format
   FormatTime, zz,, hh:mm:ss tt ; This is one type of the time format
   SendInput, %xx%, %zz%
Return

The script above will insert a date like this: Monday 21 February 2011, 12:03:26 PM. See the AutoHotkey Help page on FormatTime to see how to make your own format.

Thanks to Mama on the AutoHotkey forum: Input system date and/or time, when u press a hot key.

To use this, you must install AutoHotkey and set up a script in which you can include this snippet.

Wednesday, February 16, 2011

Lucene search fails against decimal numbers in dotCMS

Update: 17/02/2011 11:30:17 AM, Christopher F. Falzone shows how to filter out extraneous results.

When running a Lucene Query, searching/filtering by any number with decimals doesn't work against a field with float data type. For example, if a float field has a value of "3.0" or "3.24", searching for "3.0" or "3.24" respectively will not work. Any search term that has a decimal point will fail against a float field.

This affects both the Admin UI when searching for or filtering content, and in a front end page when running a macro like #pullContent() for example.

This bug affects dotCMS prior to version 1.9.2. The bug report for it is filed under Can't Filter Content by Field Stored as Decimal. I stumbled across this bug in my own project work against dotCMS 1.7 and reported in the Yahoo forum post Searching for decimal via Lucene?

There is no fix apart from either upgrading (ugh) or porting across the fixed Java code (if you can find it and if there are no other dependencies). Instead, I used the following work-around in my code: rip off the decimal portion of the number and tack on an asterisk to do a wild card search using only the integral portion of the number. For example, see below.

#set($indexVal = $floatSearchTerm.indexOf("."))
#if($indexVal > 0)
   #set($floatSearchTerm = $floatSearchTerm.substring(0, $indexVal))
#end
#pullContent("+text2:$floatSearchTerm*" "0" "text1")

This means that instead of searching for "3.0" or "3.24", I will be searching for "3*" in both cases. Sure, you might be getting more results, but at least those results will include the one you need. One sticking point is if or how do you tell the user that you have changed their search term? Perhaps you might re-display their search term as "3" instead of "3.0" or "3.24". In my case, I am not doing anything and hoping that the users will be satisfied just by getting relevant results.

Note that in terms of filtering content in the Admin UI, just leave off the decimal portion and the Admin UI will add the wild card to the end of your search term by default.


Update: 17/02/2011 11:30:17 AM. Christopher F. Falzone posted in the dotCMS Yahoo forum an update noting that it is not too hard to cycle through your results and remove those results that don't exactly match the original search term. Here is the code he adjusted to do that.

#set($origFloatTerm = $floatSearchTerm)
#set($indexVal = $floatSearchTerm.indexOf("."))
#if($indexVal > 0)
   #set($floatSearchTerm = $floatSearchTerm.substring(0, $indexVal))
#end
#pullContent("+text2:$floatSearchTerm*" "0" "text1")

#foreach($content in $list)
   #if($content.floatField.equals($origFloatTerm)
   ...
   #end
#end

Of course, this won't work if you are relying on #pageContent(), but I will address that with a later post.

PostgreSQL - current transaction is aborted, commands ignored until end of transaction block

When running SQL against PostgreSQL, I sometimes stuff up my SQL and get an error. I can't just fix my SQL and re-run the query though: I get this error instead:

ERROR: current transaction is aborted, commands ignored until end of transaction block
[SQL State: 25P02]

I have found two ways around this. Either disconnect and re-connect to clear this error, or run the command rollback;

dotCMS SQL to see history of a contentlet item

Here is some SQL that will output the history of a particular contentlet item. You need to know the identifier for the record (which you can see from the Admin UI).

select c.*
from contentlet c
inner join inode 
   on inode.identifier = 11235 
   and inode.inode = c.inode
order by c.title, c.inode;

Tuesday, February 08, 2011

dotCMS multi-select - don't use commas

When creating the CODE (value/label pairs) for a multi-select in dotCMS, make sure not to have any commas in the labels or values. It screws up search.

Thursday, February 03, 2011

Using SELECT field values from a dotCMS Structure to build a client side SELECT

Update. 6/02/2011 1:51:57 PM. Added detailed explanation of the code sample.

Sometimes you create a Structure with a SELECT or MULTISELECT whose value/label pairs you want to use on the front end too. For example, let's say I have a Structure called Book with a SELECT called "medium" that has the following values (CODE):

Hardback|0
Softcover|1
Audio|2
eBook|3

Now I want to build a book search page with medium as a criteria (as a SELECT). I could write the SELECT with hard-coded values, but what if I ever need to add new medium options to the Book Structure? I would like my search page to update itself without me having to do it - so instead I will create the SELECT with options drawn from the values stored in the Book#medium field.

There is no Lucene query that I know of that will query field values - but the SQL to do so is easy. First, you need to know the inode for the field. Use SQL such as below to find the inode.

select inode, structure_inode, field_name
from field
where field_name like '%medium%';
  1. Use like '%medium%' if you are not entirely sure of what the field is called (percentages are wild cards in SQL). If you are sure, use = 'medium' instead.
  2. Remember that SQL is case sensitive.
  3. Remember that field names do not have to be unique across the entire database, so the above query might give you multiple results. That's why you select the other values as well - to help you identify which result is the one you actually want.

Once you have that, the Velocity code to build a SELECT from the field values is easy.

#getSQLResults('select field_values from field where inode = 49640')
#if($UtilMethods.isSet($SQLError))
   <p>Unable to load values. Please report this to the site administrator.</p>
#else
   <select>
      #set($labelsValuesField = $results.get(0).field_values)
      #set($labelsValuesArray = $labelsValuesField.split("\r"))
      #foreach ($labelValue in $labelsValuesArray)
         #set($labelValueArray = $labelValue.split("[\\|]"))
         #set($label = $listTool.get($labelValueArray, 0).trim())
         #set($value = $listTool.get($labelValueArray, 1).trim())
         <option value="$value">$label</option>
      #end
   </select>
#end

Some explanation for the above code.

#getSQLResults('select field_values from field where inode = 49640')
#if($UtilMethods.isSet($SQLError))
   <p>Unable to load values. Please report this to the site administrator.</p>
#else

Run SQL to retrieve the text used to generate the SELECT OPTION tags (on the back-end and now in our front-end). If the SQLError variable is set, something went wrong and there is no use trying anything further. Warn the user appropriately.

   <select>
      #set($labelsValuesField = $results.get(0).field_values)

The SQL we ran will only ever return 1 result, which will occupy the first position in the results object.

      #set($labelsValuesArray = $labelsValuesField.split("\r"))

The text used to generate the SELECT OPTION tags is first separated by newline characters (\r). Each line contains the label and value for one OPTION. Thus, we create an array where each cell in the array stores a single line.

      #foreach ($labelValue in $labelsValuesArray)

Go through each entry in the array we just created i.e. iterate over each line.

         #set($labelValueArray = $labelValue.split("[\\|]"))

Each line has two values - the first being for the OPTION label and the second being for the OPTION value. They are separated by the pipe character (|). Create another array where the first cell is the label and the second cell is the value.

         #set($label = $listTool.get($labelValueArray, 0).trim())

Get the contents for the first cell - the label.

         #set($value = $listTool.get($labelValueArray, 1).trim())

Get the contents of the second cell - the value.

         <option value="$value">$label</option>

Put the label and value together to create an OPTION tag within the SELECT.

      #end
   </select>
#end

Don't bother with standards compliance in email templates

My friend Ketan was writing an email template: being diligent, he uses DIVs for layouts, not TABLEs. Then he finds that Outlook Webmail presented in IE8 (not Outlook Standalone) screws it all up! The only fix is to use TABLEs. Oh well.

UltraEdit macro to help output debugging Velocity code in HTML

UltraEdit is my text editor of choice, and right now I am working with Velocty scripting for dotCMS. Like Bash or jQuery, it uses the dollar sign $ to denote variables. Frequently I want to debug some variable by outputting it's value in HTML. For example:

<p>Rob here with variableName [$variableName]</p>

I do this often enough that I have created an UltraEdit macro to make is easier. Make sure you have the variable name (e.g. variableName copied to the clipboard and then run this macro. It will ouput variableName [$variableName]

InsertMode
ColumnModeOff
HexOff
Paste
" [$"
Paste
"]"

If you appreciate this, you might like to have a look at the other UltraEdit macros I use.