Tuesday, November 20, 2007

Importing Data from AD to InfoPath 2007 Form Template (Code free)

Hi all,
Several of my customers have learned about the new feature of MOSS that includes publishing InfoPath forms to the server and thus allowing users that does not have InfoPath installed to fill in forms with no need for distributing nor purchasing InfoPath for each client.
One of the things they all have in common is that they all work in a Microsoft Active Directory environment and wish the form to load Meta data from the current user and to fill in the form with that data.

Now, InfoPath and AD does not have a method for creating a direct connection, but since all of our users also utilize the Microsoft Office SharePoint Server (MOSS) forms server – this means they can take advantage of other features and capabilities of MOSS for this problem.
MOSS connects to your AD easily and builds a user profile based on the data stored in AD for each user. This part is easy enough to perform so I will not elaborate further on that here.
Once your MOSS “knows” your users, you can make use of its web services to retrieve all users list, a specific user profile and also the current user profile.
Here I will demonstrate how to create a form that loads current user profile data as stored in AD without writing any bit of code.

The only thing that needs to be done is to define a connection to a certain web service that already exists in your SharePoint site out-of-the-box (OOTB).

Step 1: Create a blank form template
1. Click file->design a form template

2. Select “Blank”

3. Click OK

Step 2: Setting up the web service connection

1. Create a new data source connection.

Go to tools –>Data connection

2. In the ”Data connections” dialog Click on Add

3. Select create a new connection to - Receive data Click next

4. Select Web service as the source type

5. Insert the Following web service name: http://<servername>/_vti_bin/userprofileservice.asmx?wsdl

6. Replace <servername> with your SharePoint server name and click next

7. From the operations drop down list select GetUserProfileByName

8. In MOSS, calling this operation without sending a user name will return current user’s profile.

9. Click next keeping the defaults in every screen until you can hit Finish

10. Close the Data connection window.

Step 3: viewing and choosing the AD details we wish to import:

Now, we need to insert the AD information to the correct fields. To do so, we first need to see what kind of data we can use.
Since the users profile may include different properties according to software installed on your organization and other dependencies, the web service results returns a set of “name-value” collection that we can use in order to get re results we need.
So – first, we have to find the “name” of the property we need and use it to get its value from the web service.
Here is how it is done:

1. To see all the available fields click on View -> Data source

2. In the Data source drop-down select the one we just created.

3. Expand the DataFields container node and select the following fields in the results:

4. In the drop-down menu on the selected nodes select repeating table and place it in the form.

5. This created a repeating table with all data returned by the web service. To see all the AD fields available to you, Click preview in the tool bar.

6. Now, locate the property you need and copy its name

7. Return to the design mode by clicking close preview.

Step 4: placing the AD details in the controls:
Now the only thing left for us to do is place the field we chose earlier in step 3 and define the control to show that field’s info.

1. In the form double-click the control you wish to fill

2. In the control properties click on

to open the function editor

3. Then click on insert Field or group.

4. In the next window select to data source you created

5. Expand all the folders under dataFields until you can select the Value node.

6. This inserts one of the “values” we got in the web service response. Now we have to make it “filter” the values by the property name we want.

7. Click on filter Data button. In the pop up window click on add

8. In the next window select the following options

9. And select the “name” node from our service data source:

10. Click ok. Then Select type text and enter the property you copied in the previous section as the value

11.Click OK to confirm and close all the pop-ups.

12. Click preview in the tool bar to see the results.

Now your form displays the user preferred name as it was entered in the AD without writing 1 line of code!

Your document must be fully trusted in order to execute the web service correctly. To do so please check the tools-forms options form security options.

This is a great demonstration how several Microsoft products can be combined to create excellent customer-specific solutions with just utilizing the capabilities of the OOTB features.

Post by: Adi Lebovich and Shai Petel.



Martin Hipfinger said...

thx for this great tutorial. i don't need it at this time, but it's fine to know how to easily integrate a moss webservice.

Marco Diaz said...

thanks for you post is very interesting.
I have a blog about infopath too.
be free to visit it

Olav K. Aukan said...


Do you have any suggestions for how to populate a drop-down list with users from a given sharepoint group?

I know there is a webservice method that returns all the users in a group, but I can't make it work as a data source in InfoPath (ie. I can't bind a control to it since it's not repeating)

I'm trying to do this with a browser-enabled form btw.

Anonymous said...

i have a similar problem, i want o have a drop-down-list in an infopath-form where can pick out the name or - better - the mail-adress of users from the AD. Do you have any suggestion how to solve this?

Johannes Br√ľntrup

Anonymous said...


I am bit worried about credentials each time web service is called? Is it the default credentials of logged in user or anonymous?


viji said...

Its very interesting to know aout fetching records from Active Directory.I followed your steps but i could not grasp initial step in step 4, that is
Step 4: placing the AD details in the controls:
Now the only thing left for us to do is place the field we chose earlier in step 3 and define the control to show that field’s info.

"Step 4: placing the AD details in the controls:
Now the only thing left for us to do is place the field we chose earlier in step 3 and define the control to show that field’s info."

How do i place the field and which field and how.Please reply me immediatly.

Thank you

Ken said...

Thanks to you a whole lot of opportunities popped up for me.

Like everyone else, I do have a follow on question...

What if I just want to retrieve the data once? In the Form the 'Requestor' can get auto-populate based on profilename...but then when someone else opens the form, bam, new 'requestor' inserted into the field.

Anonymous said...


is userprofileservice.asmx a default file in sharepoint?



Anonymous said...

Sorry for asking this question... i know the answer :)

I have a different problem though... After following all the steps and previewing the IP form within my development box (windows server 2004/moss). I getting this message "infopath contacted a data source to recive data. the form will load without the reques data". Any thoughts of this problem. I have my IP form Security Trust to "Full".

Valtteri said...

'Once your MOSS “knows” your users, you can make use of its web services to retrieve all users list, a specific user profile and also the current user profile.'

I would like to retrieve all users list to a droppdown list box entries. How can I handle that? Which is the right web service to call? And in which location?


Shai Petel (Ben Shooshan) said...

Hi Joel,

userprofileservice.asmx is a part of MOSS out of the box web services.

This entire article requiers to have MOSS installed and not just WSS - if this is your case you are covered!

jason said...

Works great, but when i try to open the Function Editor, it is greyed out. I'm assuming something to do with security on the AD Data Connection. However I can run and see the AD data. Any Ideas?

Anonymous said...

Hi, I have the same issue the fuction editor is greyed out ?

Anonymous said...

For non-developers this is a very easy and quick solution. Thanks

Anonymous said...

Is there a way to utilize this webservice with browser forms that dont support filtering?


Prasad said...

This is not AD. This is using Sharepoint web service.

Fougere said...

Has anyone figured out the problem with the function button being grayed out? I would really appreciate getting some help with this!

Ginni said...

How can i retrieve the user information by manually putting his name?
That is,I want that all the information about the user should come automatically once i write only his name in the name text box.

Anonymous said...

Awesome Post...Thnx a lot

Anonymous said...

I have the same problem with the function button being grayed out. Has anyone resolved this? Thanks

Anonymous said...

I worked out the gray function button problem. You need to add a text field to the form. This was overlooked in the otherwise very useful post (cant get it to work in browser though, hopefully resolvable later). Thanks for posting

Kenny said...

It's really helpful! But I have a problem that some of the fields in the repeating table (e.g. Department) cannot get value from AD while some can. Why? How can I solve it? (I'm sure the fields are filled-in in the AD)

Shai Petel said...

Hi Kenny,

This example take the info from MOSS User Profiles, so if the AD has the info but it was not synched to SharePoint you might not get the values...
You should look at the SSP in MOSS for the user profiles and make sure these properties are synched to AD. Than preform a full import to the user profile to see if the problem was resolved.

Good luck!

Anonymous said...

I would like to retrieve all users list to a droppdown list box entries. How can I handle that? Which is the right web service to call? And in which location?

Jeff said...

Thanks for these great tips. I had bookmarked this page some time ago and all looked fine. Am I the only one, but now there are dead links for most of the screenshots.

Anonymous said...

Does anyone know if I do this with MOSS2007 and Infopath 2003. Or do I need Office 2007 to do it.

Anonymous said...

how to add a text field to allow the fx function to work?

Kyle said...

It works great for me, however, the big ugly repeating table keeps showing up on my form. Is there a way to hide it?

Anonymous said...

Greate work dude, thank you very much

MJM917 said...

Thanks for the info. How would I pass a value to this so I could retrieve another users info. For example the current user's manager as identified in the SP profile?

Renee said...

This sounds great. However, when I try to pull the data, I get an access denied error. Couple of questions, what rights do I need to tell the Windows Admin to give me. Is there a read only right that will work. Secondly, does everyone using this form need the same rights, or only the person initially setting it up?

Kevin Graves said...

it worked perfect. had been looking for a while trying to figure this out. So to my surprise, this blog is accurate and correct. As Sharepoint pulls the AD info, I can now pre-populate all my company forms with the employee name and email.


Hoff said...

Thank god for Google and this excellent tutorial...

It works like a charm pulling the AD data to my form. However, more wants more :-).

After publishing my form to MOSS I switched the setting on for opening the form in the internet explore.

This is now causing my next challenge/head ach. When opening the form in "IE" I get the following error:

An error occurred accessing a data source.
An entry has been added to the Windows event log of the server. Log ID:5566

Do you have any idea what is causing this error?

Harald Hoff

me said...

Hi. This is indeed useful, thank you. I am also interested in how you can make use of web services to retrieve an all users list; you mentioned that it is possible to do that, but how? I would like to populate a drop-down list in infopath with all the names of users that are part of a certain distribution list in AD.

Shai Petel said...

Sure Dan,

Its only a matter of what method you call in the web service, and how you bind it to a drop down control.

Should not be hard.

Fraser said...

Excellent post - very helpful and well explained

Anonymous said...

I've created a form through InfoPath that is going to be published on SharePoint. What I need to do is input an employee number in one field and then populate the employee name in another based off of the employee number entered on the first field from Active Directory. I hope you can help as I have been struggling with this all day.

Shai Petel said...

Not sure I follow,

Employee number is a field you have in your users SharePoint profile?

I would start by creatign a demo form that returns all info from the profiles web service and validate the data is correct there.

after that, it should be rather easy binding the contrls to load user name based on selected user ID.

Anonymous said...

Shai your tutorial has been very helpful...I'm still very new to Infopath so please bare with me. I created a field called Emply# (this number is entered by a manager). What I'm looking to do is based off of the Emply# I want to pull the Emply Name from AD directory to populate my second field on the form called Emply Name. To take this a step further the Manager also inputs their Emply# on the form and again it would be nice to have the manager name populated in a different field. Using your tutorial I can pull the current user but having issues getting the other information.

Anonymous said...

Thank You! This has been EXTREMELY helpful!

I looked at several other sites and even MicroSoft You Tube demos for an Active Directory solution. Most other sites indicated complex solutions that required coding (which I can't implement in InfoPath 2007 using a FormServices/Browser-enabled form).

Your solution was complete and easy to follow! Thanks!

Senzo said...

I have a problem also, the last step when i clicked preview the name text box i inserted my value in just gave me this :
c3273dc2-72df-4692-b6eb-... i didn't write the whole thing though.
so what should i do.

Shai Petel said...

Hi Senzo,

Not sure I understand the problem, perhaps you can send me print screens and more info?

Anonymous said...

Hey..can anyone tell me how to get the current location from the active directory onto infopath...its urgent...the webservice does not provide with the user location.

Shai Petel said...

Hi there,

you do not need to specify something for the info path / web service.

once you configure your SharePoint SSP to import user accounts from AD, your user account profiles will be imported into the SSP.

The web service works with the SSP user profiles, not directly with AD.

Mei said...

Man you are awesome! I've been looking for this, and yours exactly worked! Thanks dude!

Mei said...

@Senzo : I think you put wrong formula on getting the MOSS user information. I expect you didn't put PreferredName but might be UserProfile_GUID so it returned you that value.

The Fx supposed to be Value[Name = "PreferredName"] if you try to get the Preferred Name.

Anonymous said...


is there also the possibility somehow to import other informations such as groups from AD into info path?

Shai Petel said...

Not sure what info exactly you are reffering to, but as long as you can find a web service (or build one) that can deliver that inforamtion you should be able to use it in infopath.

Personnel Forms said...
This comment has been removed by a blog administrator.
MDeveloper said...

Hi Shai,

Great post! Very helpful.
I see couple ppl asked about populating drop down list with users list but couldn't find any answer.

what web service i should use to get list of employees that reports the current logged in user and based on drop down list selection, other details like phone number, email, department, supervisor etc gets filled in for selected employee.

I am using SP2010 and InfoPath2010.

Thank you,

Shai Petel said...

Hi MDeveloper,
in SP2010 you have a lot more options with client side API that can help you with that.

Question is: how are you looking to implement the drop down?

Custom column, or are you looking to use it in an InfoPath form?

This post above talks about InfoPath, which is not recommended to use since it is being phased out by Microsoft.