February 26, 2013

Lookup Selected boxes: how to get "is empty" or "is not empty"

Whilst using SharePoint Designer to create a workflow I needed a way to check if a lookup column value was 'blank' or 'not blank'. This is not possible with Lookup type columns, but annoyingly and confusingly it is possible with other data types.
Figure 1
SharePoint only provides two operator options on lookup columns "Equal" or "not Equals". The problem is SP Designer will not allow you to simply enter a comparison value of "" or "blank" or "null".

This trick is to convert your Lookup value into a different data type, a string variable.
 
Example: I have a List containing a column called Projects, which is a lookup to another List (of projects info).
 
Figure 2

As you can see in Figure 2: First put your lookup value into a string variable.

Then use the string in your 'if' statement. You can now check if you value 'is empty' or 'is not empty'.



12 comments:

  1. I have a look up column in my library, i want to set the column default value as "-----" (blank nothing empty). How can we achieve this. Please suggest me.

    ReplyDelete
  2. Thanks for this, I spent a good day in the hunt as to understand why it wasnt working. Many thanks

    ReplyDelete
  3. Man! I like what you do from your shed. Thanks!!

    ReplyDelete
  4. That is beautiful, I spent a whole afternoon trying to create ways of comparing to lookup table. Thank you

    ReplyDelete
  5. Thank you , I spent a whole afternoon trying to do comparisons on a lookup column.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. that's not possible with multi select lookup field , Is there similer way to do it
    Thanks in advannce

    ReplyDelete
    Replies
    1. After using the "Set Workflow Variable" step, use "Replace Substring in String" as many times as needed to replace any special characters with a null string, then use Trim string. If the multi-select lookup field value is blank, your modified string variable should then equal null.

      Delete