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

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

 

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 2nd dropdown. In the 3rd 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)

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.

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 2nd dropdown “is equal to” and in the 3rd 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)

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.

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“.

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

1. Add a new data connection

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.

4. Add new Calculated Value to 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

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.

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

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

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.

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.
Like what you see? Share!
Email this to someone
email
Share on LinkedIn
Linkedin
Tweet about this on Twitter
Twitter
Share on Facebook
Facebook
Share on Google+
Google+

7 thoughts on “UPDATED: How to pull in SharePoint lookup “Additional Fields” into InfoPath form

  • Hello,
    I’m using Office 365 with SharePoint 2013. I have followed your steps to add the “additional fields” in edit mode, but I am not following the steps listed when displaying them in Read mode. I’m not sure if the instructions were for an older version. Is there any way you can help? I am quite a novice, but your instructions above were easy enough to follow. Thank you!

  • This article is great and I have saved it to my personal training file for ongoing reference!

    I do have a small issue though – my second lookup field (the one in the calculation) is a date field. The calculation is pulling both date and time. I only want the date – any ideas about how to filter out the time in that calculated, secondary field? Thanks

  • This article was great. Thank you very much. When not using infopath, the reference automatically inserts a link to the other list item. Is there a way to do this with infopath? I pulled in a select number of fields, but I would like to give my users the option of clicking on a link that takes them directly to the item in the other list.

  • Thank you so much, this is brilliant! I’ve been looking for instructions and couldn’t find anything as clear and informative as this.

Leave a Reply

Your email address will not be published. Required fields are marked *