Thursday, March 20, 2014

Review: SharePoint 2010 - Designer Email Editor, Calculated Column - Left, Substring, Right

Summary

Cannot be done in the Email Editor, but there is a crappy workaround  with Calculated Field.

Backdrop

I did not have a say in the design of our group's SharePoint site. It was not designed using a normal form. I have found my way into the designer to play around with some of the functionality because I need a way to get a substring of a field. The 'developer' working on this does not seem to understand what a substring or text manipulation is. Since I am not familiar with SP, I am playing around to see if I could "point" him the right direction after I figure it out.

Details

There does not appear to be any text manipulation in the email editor. Alternate solution is to create a calculated column. 

I need to provide the last item in a comma delimited field. I found this was impossible if there were more than two items unless there were more parameters to FIND or a FINDLAST function. FIND will return the first comma while I need the position of the last comma. Without a loop, there is no way to navigate to the last comma. 

Fortunately since I am entering the data, I decided to enter the late item at the start of the field. Thus switching to the LEFT function and FIND the first comma. While writing the function code for this, I ran into a few unexpected errors.

The primary problem is that FIND returns an ERROR if a comma is not found. I was expecting a 0 or a -1 or a false. To get around this, I had to use ISNUMBER which then returned either a 0 or a false (documentation says Yes which I seem would be No for false).

At least this does not cause problems if there is an ERROR, the display will just be #ERROR#.

Reference

http://3sharp.com/blog/building-dynamic-strings-in-sharepoint-designer-workflows/
http://msdn.microsoft.com/en-us/library/bb862071(v=office.14).aspx