Automatically Populate Fields from a Drop-Down Selection - Matt Faus
in

InfoPath Dev

Matt Faus

Automatically Populate Fields from a Drop-Down Selection

When creating a form that deals with data that has already been collected and stored it is very convenient to have a drop-down menu that will populate several other fields whenever a selection is made. One such example of this would be a form used for shipping items to clients. If shipment information has already been input and saved a drop-down that allows all of the information to be automatically recalled is very helpful in filling out the form every time thereafter.

In this task we will create a secondary data source that contains information about a list of clients and then enable a drop-down menu listing these clients to populate several other fields on the view. First, let’s make the secondary data source.

Create the secondary data source:

Copy the following XML text into a text editor, and then save the file as Clients.xml.

<?xml version="1.0" encoding="UTF-8"?>
<ClientInfo>
    <Clients>
        <Client>
            <ID>1</ID>
            <Name>JT Flights</Name>
            <Address1>2200 University Drive</Address1>
            <Address2>Suite 110</Address2>
            <City>New York</City>
            <State>New York</State>
        </Client>
        <Client>
        <ID>2</ID>
            <Name>Northern Lights Advertising</Name>
            <Address1>1234 Grand Way</Address1>
            <Address2></Address2>
            <City>Plano</City>
            <State>Texas</State>
        </Client>
        <Client>
        <ID>3</ID>
            <Name>On Target Sales Consulting</Name>
            <Address1>1704 S 108th Avenue</Address1>
            <Address2>Suite 213</Address2>
            <City>Seattle</City>
            <State>Washington</State>
        </Client>
        <Client>
        <ID>4</ID>
            <Name>Plush Designs</Name>
            <Address1>6099 Main Street</Address1>
            <Address2>Building 2</Address2>
            <City>Panama City</City>
            <State>Florida</State>
        </Client>
    </Clients>
</ClientInfo>

Add the secondary data source to the form template as a data connection:

  1. Design a new blank form.
  2. Choose Data Connections from the Tools menu, and then click Add.
  3. In the Data Connection Wizard, select Receive Data, and then click Next.
  4. Select XML Document, and then click Next.
  5. Click Browse, locate and select the Clients.xml file, click Open, and then click Next.
  6. Click Finish, click Yes, and then click Close.

Design the form:

  1. From the Layout task pane insert a custom table with 2 columns and 10 rows.
  2. From the Controls task pane add a drop-down list box to the first column of the first row.
  3. Add text box controls to the third, fifth, sixth, eighth, and tenth rows of the second column.
  4. Type the labels Name, Address, City, and State to match Figure 1.


Figure 1. The form design.

Revise the schema:

As you were adding controls in the previous section you might have noticed that InfoPath was automatically generating nodes in the schema to map these new controls to. Instead of the generated names that InfoPath gives these nodes, we want to rename them to make them easier to understand.

  1. Open the Data Source task pane, double-click the field1 node, and then change the name to ClientID.
  2. Click OK.
  3. Repeat steps 1 and 2 to change the names of the nodes according to the following mapping:
    1. field2 = Name
    2. field3 = Address1
    3. field4 = Address2
    4. field5 = City
    5. fiedl6 = State


Figure 2. The data source.

Fill the drop-down list with values from the secondary data source and add rules:

  1. Double-click the drop-down list in the view.
  2. In the Data Source section select Look Up Values From Data Connection, select Clients from the Data Connection drop-down list, and then click the XPath button to the right of the Entries field.
  3. From the dialog box that appears choose /ClientInfo/Clients/Client, and then click OK.
  4. Click the XPath button to the right of Display Name, select Name as the value to display, and then click OK.
  5. Click Apply, and then click Rules.
  6. Click Add, name the new rule Set Name, and then click Add Action.
  7. Select Set A Field’s Value from the action drop-down list, and then click the XPath button to the right of Field.
  8. Select /my:myFields/my:Name from the list, and then click OK.
  9. Click the formula button to the right of Value, click Insert Field Or Group, select Clients (Secondary) from the Data Source drop-down, select /ClientInfo/Clients/Client/Name in the list, and then click OK.
  10. Select the option to Edit XPath (advanced), modify the XPath by appending an XSL conditional statement at the end to match the following example.

xdXDocument:GetDOM("Clients")/ClientInfo/Clients/Client/Name[../ID = current()]

  1. Click OK three times.
  2. Repeat steps 6 through 11, while substituting the appropriate nodes, for the Address, City, and State fields.


Figure 3. Filling the drop-down list with options from the secondary data source.


Figure 4. Adding the rules to the drop-down list control.

Try it:

  1. Click Preview Form (or press ALT+P on the keyboard)
  2. Make a selection from the drop-down list on the right.
  3. Notice how all of the fields are automatically populated from the secondary data connection.


Figure 5. Using the form.

Published Apr 18 2006, 10:45 AM by Matt Faus
Filed under: ,

Comments

 

ollewessel said:

Nice tutorial, well explained.

Does this only work with XML source (I am referring to the getDOM statement) or also with Web Services?

My conclusion of this is also that to populate say 10-15 fields this way can only be done by applying these rules in every single field. Or is there a quicker method?

/Olle

August 3, 2007 2:28 AM
 

ZenMonkey said:

I have a form that I want to do something like what you've done here, except that the data is already in the form. Here's the scenario:

I have a form with two regions. The first region contains the customer information and the items ordered. The second region is for our shipping dept to fill in what was shipped.

Since there may be multiple items in the order, but not all of them may be shipped at the same time or using the same shipping method, the second region lists shipping method, tracking #, etc., along with what is in the package.

The shipping region is a repeating table that contains a second repeating table. The second repeating table is for listing what was in the shipment.

It would be ideal if the shipping dept could have a drop-down listbox that shows them the items from the order region, and then populates the line of the second repeating table with the details. (Line item #, Item Desc)

Any ideas?

September 14, 2007 11:38 AM
 

ZenMonkey said:

Actually, I figured it out by modifying the information you posted here. Thanks. Now, maybe one other question. In this "shipping region" if I select one of the ordered items to add to a shippment, it shouldn't be available in the drop-down for subsequent items in the shipment or subsequent shipments. Any idea how to filter items from the dropdown that have already been used?

September 14, 2007 12:30 PM
 

aznknites said:

Can that secondary data source be a different library?

October 29, 2007 11:21 PM
 

GabyFont said:

Hi,

I need to make something like this, but my sorce files are from MS Access, not xml. I have it almost done but when I try to choose anything in the dropdown menu, I can't change anything, it always select the same. I think that can be related to the statement:

xdXDocument:GetDOM("Clients")/ClientInfo/Clients/Client/Name[../ID = current()]

what should I do?

Tkyu

December 12, 2007 11:23 AM
 

GabyFont said:

I have figure it out, but now I'm having trouble setting the submito options to a Sharepoint web.

Any suggestions???

Tk You

December 13, 2007 7:02 AM
 

' + title + ' - ' + basename(imgurl) + '(' + w + 'x' + h +') said:

Pingback from  ' + title + ' - ' + basename(imgurl) + '(' + w + 'x' + h +')

March 5, 2008 8:12 AM
 

http://www.infopathdev.com/blogs/matt/archive/2006/04/18/Automatically-Populate-Fields-from-a-Drop_2D00_Down-Selection.aspx said:

March 21, 2008 11:56 PM
 

Kent Petty said:

Matt and all,

Thanks very much for the article on Auto populating fields from a drop-down selection.  I thought I actually had this figured out on my own, but the best I could ever do was to return the first instance of the fill field from the XML secondary data source.  

In my case, I want to populate a test box with a phone number based on a company selection in a combobox drop-down.  I think I have the XML file correct...very simple...here it is:

<?xml version="1.0" encoding="UTF-8"?>

<ProducerInfo>

<Producers>

<Producer>

<ProducerName>First Name</ProducerName>

<ProducerPhone>000-000-0000</ProducerPhone>

</Producer>

<Producer>

<ProducerName>Second Name</ProducerName>

<ProducerPhone>111-222-2222</ProducerPhone>

</Producer>

</Producers>

</ProducerInfo>

All I can ever get returned is the first phone number, "000-000-0000".

Any ideas?

Thanks,

Kent

June 2, 2008 4:38 PM
 

Michael Campbell said:

Matt....you are my hero. I am new to InfoPath 2007 and have spent 2 days trying to work this out and you easy to follow instructions have made me very happy!!! Thanks, michael

August 18, 2008 8:13 PM
 

Michael Campbell said:

I have used this code, but if I select a new person from the drop down list, the other fields do not update. How can I refresh the fields to reflect the newly select person?

October 7, 2008 7:24 PM
 

obsidian3d said:

I would like to do exactly this, but querying to a web service instead of an XML data source.  I have already enabled some basic drop-down fields linked to the service, but I don't want to load all the data for them and then filter.

November 21, 2008 8:30 AM
 

How do I make it so that data is filtered to multiple drop-downs? | keyongtech said:

Pingback from  How do I make it so that data is filtered to multiple drop-downs? | keyongtech

January 18, 2009 8:24 AM
 

Populate a field from a Secondary Data Connection from a query valueof a dropdown list | keyongtech said:

Pingback from  Populate a field from a Secondary Data Connection from a query valueof a dropdown list | keyongtech

January 29, 2009 10:07 PM
 

PadraigHarrington said:

I am using SharePoint as the secondary data source.  I am only able to return the first value in the SharePoint list that corresponds to the ID.

In SharePoint, I have SPField 1, SPField2.  I can pull SPField 1 into InfoPath no problem.  Now I want to populate another infoPath box with the value of SPField 2 when SPField 1 is selected.

Sample list in SharePoint.

SPFIELD1   SPFIELD2

1                 Red

2                 Blue

3                 Yellow

No matter what I choose in InfoPath for the SPFIELD1 value, IT ONLY WILL RETURN "RED."

Any ideas?  Thanks, Padraig.

April 1, 2009 8:28 AM
 

Infopath XPATH For Current Node In Repeating Table « Sladescross's Blog said:

Pingback from  Infopath XPATH For Current Node In Repeating Table &laquo; Sladescross&#039;s Blog

September 25, 2009 11:27 AM
 

Jason_2009 said:

@Padraig

I am having this same trouble did you ever get your form to populate correctly?

November 11, 2009 10:40 AM
 

warrtalon said:

Jason and Padraig, it works if you build it properly.  You have to set the field's default value and reference the SharePoint node that you want it to populate it with and apply a filter that sets the SP field to the field bound to your dropdown.  When you do it within a repeating table like that, it adds the current() function so that it knows to return data relevant to the current row instead of just the first row.  If you don't see "current()" in the statement after building the filter, then it's only going to return the first row.

November 11, 2009 10:45 AM
 

Jason_2009 said:

So, would you say that the example in this thread would be inaccurate for what we are trying to do with a SharePoint list?

Sorry I'm not really following what you are describing.

November 12, 2009 9:50 AM
 

warrtalon said:

No, it's accurate, but if it's not working for you, then you haven't set it up right.  The only way it shows the first result is if you don't have the current() piece of the filter statement working.  Look at Figure 4 above - see how it says current().  That's the part of the filter statement that tells the field to reference the current row and not just the first row of data.  You don't manually type it in, but if you build it properly, it automatically puts that in the filter statement.  I am assuming that in your example and Padraig's, you aren't seeing the current() function in your filter statement.

What you want to do is very easy and definitely doable with a SharePoint list, so you're going down the right path.  It works just the same as this XML example except the data schema is coming from a SharePoint list.  You can definitely get it working if it's setup properly.

November 12, 2009 12:44 PM
 

Jason_2009 said:

Well,

I really can't thank you enough sir.

Everything works great.

November 12, 2009 3:26 PM
 

warrtalon said:

Great, Jason.  Do you mind sharing what you changed to get it working?

November 12, 2009 6:23 PM
 

Jason_2009 said:

Basically I wasn't editing the Xpath.

Once I clicked on "Advanced" and added this statement:

xdXDocument:GetDOM("Clients")/ClientInfo/Clients/Client/Name[../ID = current()]

Everything worked fine.

I was assuming that this part:  [../fieldName

was an abreviation of something that only pertained to his schema which of course was incorrect.  Didn't realize that [../  was actually part of the syntax.

Thanks again for the help.  This was becoming a major thorn.

November 13, 2009 7:45 AM
 

Jason_2009 said:

On a side note,

Would taking this idea and applying it to adding an image from the current node to the form be possible?  Or can you just add text data?

November 13, 2009 8:22 AM
 

warrtalon said:

I don't think a data connection like that can pull in an image, but I haven't tried it either.

November 13, 2009 8:36 AM
 

Flashbag said:

Wow.!

March 30, 2010 3:22 AM
 

Dark Under Eye said:

Pingback from  Dark Under Eye

March 10, 2011 6:12 PM
 

traffic travis 4 professional said:

Pingback from  traffic travis 4 professional

April 20, 2011 2:52 AM
 

smcclendon said:

I am still having some trouble with this.

I think this is my problem:  

[../ID = current()]  

Can someone tell me about correct syntax? What is "ID" is that the name of the field name from the external data source?

May 26, 2011 7:48 AM
 

smcclendon said:

Got it....I had something wrong with my souce data.

Quick question tho..... Is there a way to set a default value of the drop down?

My form pulls user data from their LAN profile. I also have the matching data in my source file. I don't want the drop down box...I just want the form to crap the record from the source data that matched the LAN profile data.

Any help?

May 26, 2011 2:01 PM
 

Murphy2011 said:

Hello,

thank you for this great tutorial. It works very fine. I just have one problem, with fields that contain more than one value. In that case, either the first value is taken or - what is very funny - a new number-value is generated in the list of possible text-values of the column. Is there a possibility to also use "look-up" fields with many values?

Thanks for reply,

Murphy

August 23, 2011 11:06 PM
 

ktausher said:

I can get this to work wonderfully with an xml file - but when I try to attach it to a SharePOint list as the data source it gives me an error.  

I saw this post www.infopathdev.com/.../16494.aspx  

and they said to add @ signs - however that is not working for me and I wonder if perhaps it's different in 2010.   Help anyone?

Thanks!  Karen

September 21, 2011 3:22 PM
 

ktausher said:

I figured it out.  In SharePoint 2010 the fields are set up in two groups - query and data - so the field names are prefaced with d: or q:   In my case the XPath looked like this..  

xdXDocument:GetDOM("ClientInfo")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title[../d:ID = current()]

September 22, 2011 6:04 AM
 

increman said:

Wonderful! I have been searching for the solution for a half year...

December 26, 2011 3:40 AM
 

Como rellenar un campo con la selecci??n de un DropDown (Combo) | Curiosities said:

Pingback from  Como rellenar un campo con la selecci??n de un DropDown (Combo)   | Curiosities

December 6, 2013 8:21 AM
 

Dropdown fields will not populate | Zupp Answers said:

Pingback from  Dropdown fields will not populate | Zupp Answers

December 9, 2014 8:45 AM

About Matt Faus

Matt holds a BS in Computer Science and Software Engineering from the University of Texas at Dallas. During his studies, he helped Qdabra Software develop DBXL and InfoPath solutions. During that time he lived in Dallas, Seattle, and Valencia, Spain. He now works at Microsoft, developing for Hotmail.
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.