UPDATED: How to pull in 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.

UPDATED: How to pull in SharePoint lookup Additional Fields into 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”.

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  7. Select the fields you may wish to bring in from this list. Click Next.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  8. Give your data connection a name and click Finish.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  2. Click on fx; Insert Field or Group; Show Advanced View
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  3. Select your new connection from the dropdown (“KPI_Definitions_NewConnection” in my example).
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  4. Expand dataFields, select the Field you wish to put in the Calculated Value box, and then click Filter Data.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  5. Click Add, then select “ID” from the first dropdown in the Specify Filter Conditions dialogue.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  6. Leave “is equal to” in the 2nd dropdown. In the 3rd dropdown, click “Select a field or group”.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  7. In the new window, select the “Main” connection from the dropdown.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  8. Expand dataFields and select the lookup field as it is named in List A (in my example, it is called “KPI”).
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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).
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

Repeat this process for each lookup “additional” field that you wish to bring in. I now have the KPI:Target field on my form.
UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath 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)

Now you may run the design checker or attempt to publish the form to SharePoint. (To run Design Checker, click on File -> Design Checker.) In some cases, InfoPath will fail the design check or give you an error when it attempts to publish. If you do not have an errors, continue to Part D below.
UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  2. Copy the XPath formula and make sure it is in your clipboard
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  3. Click Cancel
  4. Delete the Calculated Field placeholder
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath formUPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  2. A Rules pane opens on the right side of the window. This is where we can add Form Load rules. Click New -> Action
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  7. Click Add, then in the first dropdown, leave your lookup field selected (in my case, “KPI”).
  8. Leave the 2nd dropdown “is equal to” and in the 3rd dropdown, click “Select a field or group.”
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  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.
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  11. Click OK until you’ve closed all the open dialogue boxes. The final Rule Details dialogue box appears like so in my example:
    UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form
  12. The rule has now been set. Publish the form, and try just viewing a list item. The calculated field(s) should now populate.

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form


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)

NOTE: The solution as it’s listed here is for EDIT mode forms. If you would like this to work in READ mode forms, follow the same instructions below but then you will have to do an additional couple of steps (listed at the bottom of this post).

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” and in its List Settings (below) you can see I did a lookup to a list called “KPI” and I brought in some additional fields from this list, such as KPI Category, Description, Owner, Team, etc. In this tutorial, I will lead you through how to add the “KPI: Team” additional field to an InfoPath form.

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

Notice below, in our InfoPath form editor, we see “KPI“, but we don’t see any of the additional fields outlined above such as “KPI:Team“.

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

 

Follow these instructions in order to add lookup “additional” fields to your InfoPath form.

1. Add a new data connection

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

2. Add… / Receive Data/ SharePoint library or list

3. Select the list that you have done the lookup to (Target list). In my case, the list is called “Marketing KPI Definitions”. Choose all the fields that you wish to bring in from this list. Name the connection, and click finish.

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

4. Add new Calculated Value to form

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

5. Fx / Insert Field or Group… / Show advanced view

6. Select new data connection from dropdown

7. Expand dataFields

8. Select field you want to bring into form, do NOT click OK

9. Click ‘Filter Data…’ at bottom of window

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

10. Add…

11. Select the lookup ID from the Source List in the first dropdown – in my case “ID”

12. Click ‘Select a field or group…’ from the dropdown on the right.

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

13. Select the ‘Main’ data source from the top dropdown.

14. Select the lookup ID as it is named in the Target List (‘Main’)

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

15. Click OK / OK / OK / OK / OK / OK

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

UPDATED: How to pull in SharePoint lookup Additional Fields into InfoPath form

READ Mode

This solution should work in EDIT mode. However, if you would like the additional fields to appear when you view the InfoPath form in READ mode, follow the steps below:

If you want the fields to display when you are in Read mode, you will need to set up an InfoPath Rule on “Form Load” that sets the lookup field equal to the lookup field’s value. This post shows you where to find the Form Load rules: http://www.bizsupportonline.net/infopath2007/infopath-basics-how-to-populate-form-when-opened-loaded.htm

  1. Make a new form load rule and set the condition to something like “None – run when form opens”. 
  2. For example, let’s say you used “ClientName” as the primary lookup field for List B (so all the additional fields are brought up when you select the ClientName). In the rule, under “Run these Actions”, set the field “Client Name” equal to ClientName’s value.

Stay Up-to-Date with the Latest in Custom Software With Brainspire's Monthly Newsletter

CTA Headline 1

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Quis enim lobortis scelerisque fermentum dui faucibus in.

Call to Action  

CTA Headline 2

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Quis enim lobortis scelerisque fermentum dui faucibus in.

Call to Action