Advanced String Functions in SharePoint Workflows? YES WE CAN!

Building a Dynamic String in a SharePoint workflow is severely limiting. You can only use placeholders, and forget about expressions like LEFT(), RIGHT(), REPLACE() or SUBSTRING(). It’s almost worthless.

Someone has a CodePlex project for a handful of expressions, but it’s not fully built, has little documentation and hasn’t been updated in forever. So what to do, what to do?

We are fortunate that there’s another little joint on codeplex from Christian Glessner, called iLove SharePoint Designer Actions 1.0 . If you haven’t seen it, you’re missing out. It adds several Workflow actions to your toolkit, but before you ask…no there isn’t a String Function control. It has something better. A little gem called “Execute SQL”.

This is the kind of thing that happens when you mash up two seemingly different ideas and force them into the same space, like peanut butter and chocolate. Who needs a paltry set of string functions when you have the full arsenal of a SQL Server at your disposal? We don’t need to actually access a database, but we use the facility in a standard SELECT statement to let SQL do the work for us. Nifty, eh?

OK, let’s get to it.

1. Download the bits from CodePlex here.
2. Install on your SharePoint site.
3. On your SQL Server:
    * We need to create an ID, but we don’t actually have to provide DB access, since this exercise is about string manipulation
   1. Create an login called StringBuilder. Give it a Password.
   2. Add the login to the Server Role: public
   3. Ensure Grant permissions are enabled to connect to the database engine
4. Open SharePoint Designer 2007
5. Open a site and Create a New Workflow
6. Create a New Workflow Variable with Name=ConnectionString and Type=String
7. Create a New Workflow Varible with Name=MyString and Type=String
8. Create a New Action : Set Workflow Variable, and select ConnectionString
9. Set the value to:
   Password=PasswordYouChose;Persist Security Info=True;User ID=StringBuilder;Data Source=YourSQLServerNameorIP
10. Create a new action: Execute SQL
    Provider: Sql.Data.SqlClient      (that’s the default, just leave it)
    Connection: Variable: ConnectionString      (select the Workflow Variable we created earlier, called ConnectionString)
    Result: Variable: MyString
    SQL: Enter the SQL String to satisfy your formula. Here is an example to grab the first 5 characters of a List Item field:
    Select LEFT(‘[%ListItem: StringToManipulate%]‘,5)
    IMPORTANT NOTES!
    1. You MUST use the [Add Lookup] feature to select SharePoint fields (Workflow variables, Current Item fields, etc.). Do NOT just type the field.
    2. You MUST use a single quote on either end of the string to be sent to the SQL engine
    
    EXTRA BONUS EASTER EGG:
    In case you are wondering…Yes! You can construct very complex SQL Statements, use cursors, and declare additional variables as needed. Go ahead and make multi-line SQL statements if that’s your bag. Or, provide your user account access to a SQL Database and drop a view or stored procedure there that you can access.
11. In your next Action…do something with the result now stored in the MyString variable. Update a list item, stuff it in a String Builder, or Send it in an Email, thanking Christian for such a wonderful add-in. Ok, if you do that, don’t tell him I sent you. I don’t know how he feels about unsolicited emails.