Automatically Populate City and State in InfoPath Form Using External ZipCode Web Services
The other day one of my customers wanted to know if it was possible to auto-populate city and state fields in an InfoPath 2010 Form on a SharePoint Server 2010 farm environment when you put in a zip code. Of course I said “yes” and that I would figure it out. After searching on the Internet for quite a while, since I wasn’t sure exactly what key words I should use, I came across a great site with a lot of web services.
This web service site is https://www.WebserviceX.NET.
Figure 1:WebserviceX_01.jpg
As you can see, they have over 70 web services available. In the search box, I typed in zip to see what was available. The search results produced a link to “USA Zip code Information”.
Figure 2:WebserviceX_02.jpg
BINGO! Exactly what I’m looking for. Now to use this service in an InfoPath 2010 form on a SharePoint Server 2010 farm.
NOTE: The following detailed steps take into consideration those readers who do not know how to customize an InfoPath form within SharePoint or how to add SOAP Web Services to the form. If you just want the nitty gritty of how to use the USZip web service in the form, skip to this section.
For this demonstration, I created a “contacts” list in SharePoint called MyContacts. (Site Actions -> More Options -> Contacts) When the list gets created, choose either “Customize Form” or “List Settings -> Form Settings”.
Figure 3:WebserviceX_03.jpg
This will allow you to open the form in InfoPath 2010 so we can use the USZip web service.
Figure 4:MyContactsForm_01.jpg
Within InfoPath, click Data -> From Web Service -> From SOAP Web Service |
Figure 5:MyContactsForm_02.jpg |
In the Data Connection Wizard, type https://www.webservicex.net/uszip.asmx?WSDL and click Next |
Figure 6:MyContactsForm_03.jpg |
Select the operation “GetInfoByZIP” and click Next |
Figure 7:MyContactsForm_04.jpg |
In order to get the schema of the web service, we need to “Set Sample Value”. Click on the button and type in a 5 digit zip code, click OK, then Next |
Figure 8:MyContactsForm_05.jpg |
Click Next at the next screen as we don’t want to prepopulate a set value |
Figure 9:MyContactsForm_06.jpg |
We don’t want to store a copy of the data in the form template so click Next at the next screen |
Figure 10:MyContactsForm_07.jpg |
Uncheck “Automatically retrieve data when form is opened” and click Finish |
Figure 11:MyContactsForm_08.jpg |
NittyGritty
Now that we have added the USZip web service to the form, we need to change the zip code binding then query the web service for the city and state.
On the form, right click Zip/Postal Code and choose Change Binding |
Figure 12:MyContactsForm_09.jpg |
The “Text Box Binding” dialog box will open, click “Show advanced view”, and change the data source to GetInfoByZip (Secondary) |
Figure 13:MyContactsForm_10.jpg |
Using the GetInfoByZIP data source, expand queryFields until you can select USZip, then click OK |
Figure 14:MyContactsForm_11.jpg |
In the Design form window, under Fields on the right side, select “Show Advanced View” |
Figure 15:MyContactsForm_12.jpg |
Assuming the Zip/Postal Code is still selected from the previous step, the Fields should automatically be selected on USZip. If not, then change the Fields data source to GetInfoByZip (Secondary) and select USZip |
Figure 16:MyContactsForm_13.jpg |
Right-click USZip in the Fields section and choose Rules. In the Rules task pane, click New -> Action. You can either leave it to say “Rule 1” or change it to something meaningful like QueryZip as this rule will query the USZip web service. |
Figure 17:MyContactsForm_14.jpg |
In the Rules pane, click Add and select Query for Data. Change the data connection to GetInfoByZIP and click OK |
Figure 17:MyContactsForm_15.jpg |
In the QueryZip rule, click Add and choose Set a Field’s Value. In the Rule Details dialog box, click the button for the Field (1), change the Fields to Main (2), expand dataFields until you can select City (3) and click OK.
|
Figure 18:MyContactsForm_16.jpg |
Now that we have selected the field we want to put a value in, we need to create a function that will get that value for us. In the Rules Details dialog box, click the fx button (1). The Insert Formula dialog box will appear, click Insert Field or Group…. (2) In the Select a Field or Group dialog box, expand the GetInfoByZIP (Secondary) dataFields and choose CITY (3). Click OK |
Figure 19:MyContactsForm_17.jpg |
Click OK on the Insert Formula dialog box. Verify your Rule Details match the screen shot then click OK. |
Figure 20:MyContactsForm_18.jpg |
Follow the previous three (3) steps to set the field’s value for State/Province. a) You Add “Set a Field’s Value” b) Choose the Main dataField State/Province c) Set the Value to the GetInfoByZIP STATE dataField
|
Figure 21:MyContactsForm_19.jpg |
The Rules should now look like this: |
Figure 22:MyContactsForm_20.jpg |
Be sure to save your form so as to not lose any of your hard work. Go to the location of where the form was saved and double-click to open the form. Type in a zip code then press the Tab key and watch the magic work.
Figure 23:MycontactsForm_21.jpg
Caveats
In my testing, when designing the form and going to Preview (F5), the form opens but with errors. The only way I could get this to work was to open the form from the saved version. Below are the errors I received when trying to use the form in Preview mode.
Figure 24:PreviewMode_01.jpg
Figure 25:PreviewMode_02.jpg
Figure 26:PreviewMode_03.jpg
Figure 27:PreviewMode_04.jpg
Publishing the Form to SharePoint
I did what a lot of people will do when they publish the form back to the SharePoint list. From the InfoPath design page, I simply went to File -> Publish -> SharePoint List ensuring the correct list was shown. Next, I went to the MyContacts list in SharePoint and clicked “Add New Item”.
All of a sudden the form that WAS working isn’t.
Figure 28:NewItem_01.jpg
The following text is the full error details:
A query to retrieve form data cannot be completed because this action would violate cross-domain restrictions.
If this form template is published to a SharePoint document library, cross-domain access for user form templates must be enabled under InfoPath Forms Services in SharePoint Central Administration, and the data connection settings must be stored in a UDC file in a data connection library in the same site collection.
If this is an administrator-approved form template, the security level of the form must be set to full trust, or the data connection settings must be stored in a UDC file by using the Manage data connection files option under InfoPath Forms Services in SharePoint Central Administration.
An entry has been added to the Windows event log of the server.
Log ID:6932
Correlation ID:9e347c24-07ed-4df1-b8e9-48d0b4f18aac
In my case, the event was NOT added to the Windows event log but it probably wouldn’t have given me any more information anyway. The SharePoint ULS logs had the same error information as the dialog box. Looking at the dialog box error message shows two (2) possible reasons for the error along with resolutions. I marked in bold the key points.
(A)
If this form template is published to a SharePoint document library, cross-domain access for user form templates must be enabled under InfoPath Forms Services in SharePoint Central Administration, and the data connection settings must be stored in a UDC file in a data connection library in the same site collection.
(B)
If this is an administrator-approved form template, the security level of the form must be set to full trust, or the data connection settings must be stored in a UDC file by using the Manage data connection files option under InfoPath Forms Services in SharePoint Central Administration.
Since my InfoPath form is for a particular site collection, I don’t need full trust. All I need to do is enable cross-domain data access in Central Admin and add the data connection file to my site collections Data Connection Library. Simple, right?
InfoPath Forms Services is in the General Application Settings within Central Admin.
1) Browse to the InfoPath Forms Services and chose Configure InfoPath Forms Services, check the box to “Allow cross-domain data access . . .”
Figure 29:ConfigureService_01.jpg
2) If you don’t already have a Data Connection Library in your site collection, go to Site Actions -> More Options and choose Data Connection Library. In my case, my Data Connection Library is called Data Connections.
3) Browse to the Data Connections library and copy the URL
a. You can follow How to: Create and Use a Data Connection Library for this
4) Since I already created the InfoPath form and had the data connection already there, I opened the form then followed from step 7 in the section “To create a new data connection file in InfoPath” to convert the data connection.
5) After that, I went to MyContacts list and the form opens and works fine.
Click on the links for more information about the InfoPath Forms Service and Working with Data Connections.
A special shout out to one of my colleagues, Scott Heim, for getting me going in the right direction on this!
P.S. I have attached my MyContacts.xsn for those who might find this useful
Comments
Anonymous
January 01, 2003
Thanks Anne! This was just what we were looking for and your instructions were so easy to follow! Absolutely wonderful!Anonymous
May 16, 2013
Can't explain better! Thanks a lot and keep writing :)Anonymous
November 19, 2013
Hi Anne, thanks for an awesome article. Very easy to understand and follow. I have an issue though. After publishing the form to SharePoint, I get an exception saying cannot connect to remote server. In InfoPath filler it works fine though. Appreciate your help on this. Thanks.Anonymous
April 21, 2014
Thank you, Anne! This is exactly what I needed and this article explained it very thoroughly...thanks again! :DAnonymous
April 09, 2015
Information was good, I like your post.Looking forward for more on this topic.
http://staygreenacademy.com/sharepoint-videos-training-tutorial/">sharepoint tutorial videosAnonymous
January 15, 2016
Quick question, how to separate those querys when having a form with several addresses?Anonymous
February 20, 2017
I'm using the steps above modifying a list view. When I expand the GetInfoByZip data fields, I don't get a separation of city, state, zip, etc and you show. I just get one field -- NewDataSet.- Anonymous
February 20, 2017
I figured out my issue. The Default values when did not take when I setup the data connection.
- Anonymous