As you probably know lookup columns in Sharepoint are stored in this format “ID;#String” (record number & then the string value). I was trying to use a lookup value in a K2 process to make up the new sites URL when provisioning new sites. Obviously this was failing due to the way lookup columns are stored ie. illegal characters ID;#String.

You can get round this by using the K2 ‘Code Reference Event’

1) Add a ‘Code Reference Event’ to one of your activity containers on the K2 canvas.

2) Give the code event a name

3) Event Method Editor –> Call Constructor –> Add Reference –> Microsoft.Sharepoint

4) Now on the Select Constructor Screen: Microsoft.Sharepoint –> SPFieldLookUpValue –> Constructor(String fieldValue)

5) Assign your Sharepoint Lookup Column to the fieldValue parameter

6) Now map the LookupValue field to whichever field you wish to store the lookup string value in, set ‘Retrieve Value’ and tick ‘after’

7) Now the field you chose to store the lookup string value will contain just the string without the prefix ID;# and can be used throughout the rest of your K2 process.

More From pstilgoe

• K2 Blackpoint recommended specifications
• Does K2 Blackpoint (or Blackpearl) work in a HTTPS / SSL environment?
• K2 Blackpoint K2 Blackpearl does not support single sign on authentication

Ask pstilgoe To Recommend Your Posts

If you are using a webservice & a dataview to display list data across sites in Sharepoint 2010 you may come across this problem when displaying lookup columns.

A lookup field is stored in the format “ID;#Text”

When using a REST data connection the values returned for the lookup column is just the ID part of that item in the lookup list ie. “01″, I couldnt find a way how to return the text part of the lookup.

If you use a SOAP data connection it will retun the whole string ie, “01;#Blue”, so now we are getting the value but we dont want to display the the ID part or the ‘;#’ part. This is easily done changing the XSLT from

<xsl:value-of select="@ows_Colour_x0020_Status"/>

to

<xsl:value-of select="substring-after(@ows_Colour_x0020_Status, ';#')" />

Now in your dataview instead of displaying “01;#Blue”, you will just be displaying the text value “Blue”.

1) Get your Sharepoint list into XML format

2) Set up a data connection using the above XML file as your datasource

3) Now you need to configure rules and filters on your list box. Let’s say that your list box has customer name and you wish to fill a field with the customer phone. When you set your rule, you would first select Set a field’s value then select the field where you want the phone number.

4) To filter for the correct phone number, select the formula button next to the Value field. In the formula window, select Insert Field or Group.

5) Select your secondary data source and the phone number field. Select the Filter Data button. Select Add.

6) In the first drop down, select the customer name field from your secondary data source. Leave the middle one set to Is equal to. For the third drop down, select the field on your form that is bound to the control with the customer name in it.

At this point, what you are basically telling the rule is: Go get me a phone number. I want it to belong to the same customer as the one I just selected in my List Box.

If your datasource is fairly static, when creating the data connection for the XML file in the Data Connection Wizard, ensure that the option to Include the data as a resource file in the form template or template part is selected. This will enhance the speed of your data queries to your datasource significantly.

Steps taken from Hilary Stoupa over @ InfopathDev.com

More From pstilgoe

• How to auto populate InfoPath forms using the people picker
• Converting a Sharepoint List into XML | Sharepoint Consultant, Automation & Stuff
• Import data from CSV and SQL to a SharePoint list

Ask pstilgoe To Recommend Your Posts

At first glance this doesnt look possible OOTB but it is, just not very obvious!

The way to achieve this is to:

1) Create a list at say Site Collection level which contains a column with the data in you want to use on another site.

2) Now create a Lookup column at Site Collection Level which is pointing to the data from the above list that you want to use.

3) Now you can add that column, to any list, on any site, throughout the site collection.

More From pstilgoe

• Enable anonymous access on a Sharepoint site
• View PDF documents in a webpart
• How to easily move / copy list items from one sharepoint list to another

Ask pstilgoe To Recommend Your Posts