Updated: Pull SharePoint Lookup "Additional Fields" Into InfoPath Form

This article has been updated to include new instructions for InfoPath 3013.

Click here if you are looking for instructions for InfoPath 2010 or earlier.

Instructions for InfoPath 2013

When creating an InfoPath form for a SharePoint list, you may need to bring in SharePoint lookup fields into the form.

In this example, I have a list called “KPI Actuals” (aka List A) and in its List Settings (below) you can see I did a lookup to a list called “KPI_Definitions” (aka List B) and I brought in some additional fields from this list, such as KPI Target, Category, Description, and Team. In this tutorial, I will lead you through how to add the “KPI:Target” additional field to an InfoPath form.

In InfoPath Designer, we can see our KPI field listed in the Fields pane, but we don’t see any of the additional fields such as “KPI:Target”.

Follow the instructions below in order to add SharePoint lookup “additional” fields to your InfoPath form.

Part A – Adding a New Data Connection

First we need to create a new data connection to List B (KPI_Definitions in my example). You may notice that this connection already exists in InfoPath because of the lookup we made in SharePoint. Unfortunately, this connection only has the primary lookup field, none of the additional fields. So we need to create a new connection and bring in those additional fields from List B.

1. To create a new data connection, click on “Manage Data Connections” at the bottom of the Fields pane.
   
2. Click Add
3. Leave “Create a new connection to:”; “Receive data” selected; click Next.
4. Select “SharePoint library or list”, then click Next.
5. Put in your SharePoint URL, then click Next.
6. Select the list you made your lookup to (List B). In my example, I did a lookup to “KPI_Definitions” list, so this is the list I select. Click Next.
   
7. Select the fields you may wish to bring in from this list. Click Next.
   
8. Give your data connection a name and click Finish.
   
9. Close the data connection wizard.

Part B – Adding a Calculated Value Field

Second we need to add a Calculated Value to the form where we want our additional field to be displayed (KPI: Target in my case).

1. In the Home ribbon, click the dropdown arrow in the Controls section to reveal more Control options. Click on Calculated Value.
   
2. Click on fx; Insert Field or Group; Show Advanced View
   
3. Select your new connection from the dropdown (“KPI_Definitions_NewConnection” in my example).
   
4. Expand dataFields, select the Field you wish to put in the Calculated Value box, and then click Filter Data.
   
5. Click Add, then select “ID” from the first dropdown in the Specify Filter Conditions dialogue.
   
6. Leave “is equal to” in the 2^nd dropdown. In the 3^rd dropdown, click “Select a field or group”.
   
7. In the new window, select the “Main” connection from the dropdown.
   
8. Expand dataFields and select the lookup field as it is named in List A (in my example, it is called “KPI”).
   
9. Click OK until all of the open windows have been closed. The auto-generated formula should appear as below (except with your field names of course).
   

Repeat this process for each lookup “additional” field that you wish to bring in. I now have the KPI:Target field on my form.

Go ahead and preview it and make sure it works. If it does not work in the preview, it is likely that one of the steps above was not followed.

Part C – Fix Design Checker/Publish Issues (If Needed)

The design checker may say “control binding is not supported” or the error when publishing may tell you “Binding a non-repeating control to a repeating field or group is not supported in SharePoint list forms.” If this is what happens to you, try the following fix:

1. Click on the Calculated Field placeholder on the form, click the Control Tools -> Properties tab in the ribbon. Click the Control Properties button.
   
2. Copy the XPath formula and make sure it is in your clipboard
   
3. Click Cancel
4. Delete the Calculated Field placeholder
   
5. Now click in the empty cell and add a Calculated Field control again, but this time, just paste the formula you copied directly into the XPath formula box. Click okay.
   
6. Preview it and make sure it works.
7. Run the design checker again (or publish) and it should succeed.
8. NOTE: If you have to actually edit the formula (via the wizard), then the design checker may fail again. The intent here is to just copy the correct formula into the XPath box and then not have to mess with it. Messing with it may break some binding properties.

If you have any issues with this happening with any other calculated fields, give this fix a try.

Part D – Making Your Calculated Value(s) Show Up When Viewing the List Item

You may notice that this tutorial works when you are Adding or Editing a new list item, but your additional field(s) may be blank when just Viewing a list item. To fix this, we will add a rule the runs on Form Load that sets the lookup field equal to the lookup value. This may seem redundant (and to an extent it is), but doing this through a rule will cause the Calculated Field formulas to execute so that they aren’t blank when in View mode.

To implement this, follow the instructions below:

1. In InfoPath Designer, with your form opened, click Data in the ribbon then click Form Load.
   
2. A Rules pane opens on the right side of the window. This is where we can add Form Load rules. Click New -> Action
   
3. Give your Rule a descriptive name. I’m calling mine “Populate Calc Fields on Form Load”
4. By Run these actions, click Add -> Set a field’s value
   
5. Click the button next to the Field text area, then select the Lookup field from List A (in my case “KPI”), then click OK.
   
6. Click the fx button next to the Value text area, select Insert Field or Group, select your lookup field as it is named in List A (“KPI” in my case), then click Filter Data.
   
7. Click Add, then in the first dropdown, leave your lookup field selected (in my case, “KPI”).
8. Leave the 2^nd dropdown “is equal to” and in the 3^rd dropdown, click “Select a field or group.”
   
9. Click “Show advanced view” at the bottom, and select your new connection (to List B) from the dropdown.
10. Expand dataFields and click on “ID.” Click OK.
    
11. Click OK until you’ve closed all the open dialogue boxes. The final Rule Details dialogue box appears like so in my example:
    
12. The rule has now been set. Publish the form, and try just viewing a list item. The calculated field(s) should now populate.

I hope this has been helpful! Please let me know in the comments if this worked for you or if you have any questions.

Instructions for InfoPath 2010 (Original Article)