Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using SharePoint Web Services to Fill Out an InfoPath Form


By:   |   Read Comments   |   Related Tips: > Sharepoint

"SQL Server in The Cloud" - click to register for this free webcast


Problem

When filling out forms users tend to use nicknames, initials, or non-given names to represent their birth names. If the form is an official document then this is not acceptable. To fix this issue, the creator of the form can automatically fill in the name portion of the form with the name of the person who is filling out the form. This can be accomplished using web services.

Solution

This solution will show you how to automatically fill in the name of the person filling out the form. For example purposes I will use the Travel Request Form to demo the solution.

Open your˙InfoPath form in designer view.

Creating Data Connection

First a connection needs to be made to web services. To do this click on Tools-> Data Connections...

data connections

A new window will open like the picture below. Click On Add...

data connections

Select the radio button next to Receive data. Then click Next >

data wizard

Select the radio button next to Web service. Then Click Next >

data connection 4

Next we are asked to input the URL of the web service. You may be wondering what exactly is the web service. A quick and simple definition is that it is a way to grab information from SharePoint remotely to be used in web applications, forms, etc. For more information on what information can be accessed using web services check out this link: http://msdn.microsoft.com/en-us/library/bb862916%28office.12%29.aspx.

The URL to access web services is: http://address of sharepoint site/_vti_bin/UserProfileService.asmx. So for instance if the address of my SharePoint site is http://mycompany.com then to access web services I would use http://mycompany.com/_vti_bin/UserProfile.asmx?wsdl. We are putting UserProfile at the end because this is the service that pulls the user information from SharePoint. See the link above for other services. If you type the address above into a browser you will see something similar to the below picture...

web service details
user profile service

Click Next >

Next we are presented with a list of methods to choose from. Select the GetUserProfileByName method and click Next >.

At the next screen leave the default value and click Next >

Click Next at the next screen. Do Not check the check Box.

Lastly you can leave the default name that appears on the last screen. Make sure to check the box next to "Automatically retrieve data when form is opened". Click Finish.

We have now completed creating a connection to the web service. Now the fun really begins.

Accessing the Web service

On your form right click on your name field and select Text Box Properties. Click on the fx button.

text box properties

Next we are provided with the Insert Formula box. Click "Insert Field or Group"

insert formula

In the Data Source box change the drop down from "Main" to "GetUserProfileByName" Note: If you changed the name of the data connection on the last step of the creating the data connection then your drop down box will reflect the name you changed it too.

Drill down to the "Value" item... See the picture below.

select a field

Next click on Filter Data... We have to filter the data in order to choose from the 46+ possible values of the GetUserProfileByName. Some possible values available are: FirstName, LastName, Office, etc. In our case we need the FirstName.

Note: There is not a value for "FullName" so we will have to concatenate or put together the FirstName and LastName to make the "FullName". More on that to come.

On the Filter Data Screen click on Add...

filter data

Now we need to tell InfoPath what to filter for.

In the first drop down box select "Select a field or group..." Drill down until your screen resembles the one below. Then Click Ok

select a field

Leave the middle box set to "is equal to". Next select the last drop down and select "Type Text..." Type "FirstName" in the box. This is the name of the property we are filtering for.

specify filter

Your screen should resemble the following... Click Ok

filter conditions

Click OK on the Filter Data Screen

Your screen should resemble the one below. If it doesn't please go back and check where you made a mistake.

insert formula

Click Ok until you get back to your form. If you preview the form you should see the First Name of the user in the box. However, we still need the last name.

To accomplish this we will need to write a function. Now don't freak out. We are going to let InfoPath do it for us.

Our function will resemble the picture below...

insert formula

If you go back up to the step where we clicked "Insert Field or Group.." Instead we will first click Insert Function... From here click all on the left hand column and then click "concat" on the right hand column. Click OK.

Now we have the box with a formula similar to the one we need.

insert formula

Double click the first field where it says to "double click to insert field"

Follow the steps to filter for the FirstName. When you double click you will see what step you are to follow above. Once you ge the FirstName click on the next double click to insert field item and do the LastName.

Preview the Form and you should see something that resembles the below picture.

travel request
Next Steps
  • Use this process to automatically fill in other parts of the form for your users.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Rebecca Jones Rebecca Jones

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools