Advanced String Building Expressions in SharePoint Workflows with SQL

This is a follow-up article to Advanced String Functions in SharePoint, YES WE CAN!, only this time I’ve added some screenshots and a practical example.

To paraphrase the previous article, 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.

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?

Problem Scenario for the Example:

In this advanced string manipulation for a SharePoint field, I want to extract the field description from a Lookup field. If you know your SharePoint, you’ll know that internally it stores the reference to the lookup as ID#;DESCRIPTION.

So let’s say I want to strip out the Description portion and stuff into another field. Why? Just because and let’s leave it at that.

In my example, there is a SharePoint list named Events, and there is a Lookup Field named Zip. (It holds a lookup to a Zip Code field <– non-US folks, call that a Postal Code)

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.Aint T-SQL great? Access the raw power of a server with no real access to a specific database required. How, you ask? Read on.
4. Create a login called StringBuilder. Now give it a Password. Something secret. Hurry, my eyes are closed.
   1. Add the login to the Server Role: public
   2. Ensure Grant permissions are enabled to connect to the database engine
5. Open SharePoint Designer 2007
6. Open a site and Create a New Workflow
7. Create a New Workflow Variable with Name=sSQLConnString and Type=String
8. Create a New Workflow Variable with Name=sZipCode and Type=String
9. Create a New Action : Set Workflow Variable, and select sZipCode
   1. Set the value to:
      Current Record:Zip
      * Well, that’s my example…you will most likely have another list, and another field to manipulate
10.  Create a New Action : Set Workflow Variable, and select sSQLConnString
    1. Set the value to:
       Password=PasswordYouChose;Persist Security Info=True;User ID=StringBuilder;Data Source=YourSQLServerNameorIP
       
11. Create a new action: Execute SQL with the following settings
    Provider: Sql.Data.SqlClient      (that’s the default, just leave it)
    Connection: Variable: sSQLConnString   �
    Result: Variable: sZipCode
    SQL: Enter the SQL String to satisfy your formula. Here is an example to grab only to juicy bit from the lookup field: 
    

    Let’s just take a minute to pause at the image above. That’s ok. I’ll wait. Get a tissue if you start to drool a bit at the screen. This is a simple little example, but illustrates not only what you’ve been aching to do with Strings within SharePoint, but further demonstrates the raw power now at your disposal from within a workflow.
    
    

12. 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. Notice the [%Variable: sZipCode%] bit…that’s what I mean. Use the [Add Lookup] button, do NOT type it.
    
    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. Kick of a transaction and get on with your bad self.

    2. You MUST use a single quote on either end of the string to be sent to the SQL engine so it is syntactically correct.

13. That’s it for this example.
14. 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.