Protiviti / SharePoint Blog

SharePoint Blog

December 19
How to Properly Trim SharePoint Multi-Line Text Fields using Nintex In-line Functions

I was working on a project recently where we were doing some "light" ETL work with Nintex workflows, and the need came about to do some classic data validation while populating a set of SharePoint lists.  One of the classic validation checks we were doing on the incoming data was to check for the existence of any leading/trailing white spaces and remove them upon discovery.  This turned out to be not as straight forward a task as we had assumed.

Much like SQL, Nintex has a built in set of in-line functions for completing common tasks such as basic string operations (checking length, trimming whitespace, capturing substrings, etc...).  Nintex's function for trimming whitespace is aptly called "fn-Trim()".  For my workflow, I accessed this function by using the String Builder action.  The goal was to populate a workflow variable - "trimmedDescription" - employing the trim function as follows:

trimmedDescription = fn-Trim({WorkflowData:Description})

To validate my test, I ran the workflow against a small list of about 1000 items.  Looping through each item, my workflow would copy out the Description field, attempt to trim whitespaces and store the result inside the “trimmedDescription” variable to be printed out to screen using the "Log to Workflow History" activity. 

What I found, after starting my workflow and coming back to check it 10 minutes later, was that it had canceled out right in the middle of processing the list of 1000 records - only making it to item 515.  Because the workflow had run successfully for the first 514 items, I knew that there must be something in the data that was making my workflow choke.  Looking closely at the Description field, I noticed that no special characters were present.  This was puzzling, and seemingly inconclusive, so I did the logical thing and commented out the new piece of logic I had just added - the string builder action for returning a whitespace trimmed result - and reran the workflow.  This time the workflow executed with no errors.

Since my trimming function worked on 514 entries, and running my workflow without the trimming function seemed to work fine, I decided to look again - more closely this time - at the data.  The only significant difference I could detect between items 514 and 515 was that the Description field in item 515 was slightly longer, and included a comma to break up the text.  I thought to myself, "Since when are commas treated as special characters?"  Then it hit me that Nintex is running as a layer on top of the OOTB SharePoint Workflow Engine.  As a developer, I know that adding multiple layers of architecture sometimes means having to make tradeoffs in how you marshal data between the various tiers.  I also had a hunch that since the inline functions were being included “in-line”, as part of the final text string eventually parsed by Nintex, it wasn’t hard to imagine that something about the resultant string returned by the StringBuilder action might be causing some heartburn for the Nintex compiler.

My hunch was right.  Adding a comma to the data in item 514, and running the workflow again, caused it to fail one line earlier, and removing the comma from item 515 caused the workflow to run to list item 681 - where the next comma was "lurking" amongst a long set of characters in the targeted description field.

Using my google powers, I was able to find a post by Vadim Tabakman - a very knowledgeable Nintex Engineer - which identified my exact scenario of running into trouble using inline functions while working with text data that contained commas.  To my great surprise and relief, Nintex has already included a work-around.  The solution?  Wrap the text or workflow variable that may contain some commas with "{TextStart}" and "{TextEnd}" bookend tags.  This effectively tells the Nintex compiler that some special text is included that should not be confused as additional elements (or parameters) of the inline function that is being called. Without the tags, Nintex wouldn’t be able to tell the difference between the commas in my StringBuilder String and those embedded in my Description field. What ultimately resolved my issue was reconfiguring my StringBuilder action syntax as follows:

trimmedDescription = fn-Trim({TextStart}{WorkflowData:Description}{TextEnd})

I continue to be impressed by the Nintex Workflow product.  To be able to offer such a rich workflow maintenance experience on what is already a complicated architecture like SharePoint truly speaks to the attention to detail that they put into crafting this product.  As I like to joke, "Friends don't let friends build workflows without Nintex".  It really is a joy to be able to rapidly translate Business ideas with this product and quickly, efficiently move proof of concept work from development to production.  As consultants, time is always of the essence, so keeping frustrations to a minimum is always a priority! Good tools make all the difference. I'm happy that Nintex helps us accomplish our mission and I hope that you find this tip useful in your adventures with the product.  Don't forget to check out Vadim's post below (which goes into much more detail than my simple scenario).

Quick Launch

© Protiviti 2020. All rights reserved.   |   Privacy Policy