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
4. Right-click the newly added group "Countries" and
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
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.
15. In the FormEvents_Loading(),. add following
//Populate country drop down
16. The code for AddCountries() is as following:
web = site.OpenWeb();
list = web.Lists["Countries"];
listitems = list.Items;
XPathNavigator nav =
XPathNavigator newNode =
newNode = nav.Clone();
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.
After you set the values, you have to insert this cloned
node in the nodes hierarchy. You do this using the "InsertAfter()
"newNode" is passed as a parameter to the "InsertAfter()"
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.
You can download the form and complete application code
Download completed InfoPath form and application code (ZIP format)