Retrieving data from a SharePoint list
 
There can be different scenarios in InfoPath where programming can be needed. Professional applications cannot be complete without some sort of programming effort. InfoPath is designed in a way that makes creating forms very easy for the users. You can create complicated forms without coding anything but large organizations try to leverage full benefits of InfoPath and full benefits can only be had by adding value to the forms by using complex programming techniques. In this article, we are going to use a simple programming technique that will show you how to populate a drop down box in InfoPath form programmatically with data from a SharePoint list. Of course, there is a way to do it without programming, for example, you can use a data connection to populate the drop down but suppose you want to populate the drop down in a particular scenario or in other words, we can say that you want to populate it dynamically depending on business logic in the form. There can be literally hundreds of scenarios. The biggest advantage of using programming to achieve this goal is you don't have to worry that your data connection will break after deploying to the form to some other server which is a very common problem.

1. Open "user registration" form in InfoPath editor.

2. Add a new data source. Right-click "myFields" in data sources and click "Add".




3. Add "Countries" as a name. From "Type", select "Group" and check "Repeating" checkbox at the bottom and click OK. This will add a repeating group in the data sources.




4. Right-click the newly added group "Countries" and select "Add".

5. We will add two fields (elements). Add "Displayname" in the "Name" box. Leave all other options as they are.



6. Repeat steps 4 and 5 to add another field and name it "Value".

7. Right-click the "Country" drop down in the form and select "Drop-Down List Box Properties".

8. Select "Look up values in the form's data source" option in the "List box entries".



9. Click "Select XPath" button.



10. Select "Countries" (repeating group) and click OK.

11. "Value" and "Display name" will automatically be populated. Change the value in "Value" box by clicking the "Select XPath" button and selecting "Value". Click OK to close the properties.



12. Open VSTA by selecting Tools > Programming > Microsoft Visual Studio Tools For Applications.

13. Add a reference to "Microsoft.SharePoint.DLL".



The DLL is located in the following folder:

System Drive:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI

14. Add a reference in code.

using Microsoft.SharePoint;

15. In the FormEvents_Loading(),. add following function:

//Populate country drop down

AddCountries();


16. The code for AddCountries() is as following:

public void AddCountries()

{

try

{

SPSite site = SPContext.Current.Site;

SPWeb web = site.OpenWeb();

SPList list = web.Lists["Countries"];

SPListItemCollection listitems = list.Items;

XPathNavigator nav = this.CreateNavigator().SelectSingleNode("/my:myFields/my:Countries", this.NamespaceManager);

foreach (SPListItem li in listitems)

{

XPathNavigator newNode = null;

newNode = nav.Clone();

newNode.SelectSingleNode("/my:myFields/my:Countries/my:Displayname", this.NamespaceManager).SetValue(li["Title"].ToString());

newNode.SelectSingleNode("/my:myFields/my:Countries /my:Value", this.NamespaceManager).SetValue(li["Title"].ToString());

nav.InsertAfter(newNode);

newNode = null;

}

nav.DeleteSelf();

nav = null;

}

catch

{

}

}

17. Compile the project, publish the form and open it in SharePoint, here is what you should see:



18. This is the programmatic solution. You don't have to worry that your data connection will break on another server. Let's discuss the code briefly. This is not an efficient method of populating the drop down. We should have used SPQuery() object to get data from the list. SPQuery is fast and efficient and works very well with large lists but here our objective was not to achieve efficiency but to show how to fetch data programmatically. We get site context using the "SPContext". You can also hard code the site URL but that's not a good idea. We cannot hard code paths in professional applications. After creating the listitem collection, we read each list item from the collection in a loop. Before getting into the loop, we create a main node that points to the "Countries" repeating group. In the for loop, we create a clone of this node and add values to the "Value" and "Displayname" elements. "Value" and "Displayname" fields get their values from the list item.

newNode.SelectSingleNode("/my:myFields/my:Countries/my:Displayname", this.NamespaceManager).SetValue(li["Title"].ToString());

newNode.SelectSingleNode("/my:myFields/my:Countries /my:Value", this.NamespaceManager).SetValue(li["Title"].ToString());

After you set the values, you have to insert this cloned node in the nodes hierarchy. You do this using the "InsertAfter() method:

nav.InsertAfter(newNode);


"newNode" is passed as a parameter to the "InsertAfter()" method.

After you leave the for loop, you must destroy the parent node that was used to create cloned nodes. This is important to avoid duplicate entry of the parent node otherwise you will see first entry as duplicates.

nav.DeleteSelf();

You can download the form and complete application code for reference.

Happy programming!

Download completed InfoPath form and application code (ZIP format)