Using SharePoint Data in InfoPath Forms: How Does it Work?

One of the best things about working for Bamboo is getting to learn something new every day. It reminds me of just how little I really know, something my mother told me constantly when I was 16. (Mom, if you're reading this, you were RIGHT!)

I recently wrote an article about using Bamboo's Workflow Conductor to approve expense reports. In the SharePoint world, you can't have expense reports without an InfoPath form. So, a bit daunted by my ignorance, I opened InfoPath for the very first time. Thank goodness for InfoPath's sample form templates and all those helpful blog and online documentation writers out there, or that use case would never have been written!

To return the favor, I want to share some of the very basic but useful things I learned about using InfoPath forms in SharePoint. Hopefully some other InfoPath newbie finds it useful.

Publishing InfoPath Forms to SharePoint

I figured out pretty quickly that InfoPath form templates get published to a SharePoint Forms Library (I stumbled across the Bamboo Expense Reports library in our SharePoint portal), but how does that happen and how does SharePoint know about all the form fields? It turns out that InfoPath walks you through the whole process using the Publish feature in the File menu. Thank you, Microsoft, for this handy article: Publish a form template to a server running Microsoft Windows SharePoint Services.

You get to pick your destination forms library (or create a new one), and you also get to pick which fields in your form that SharePoint knows about. Whatever fields you pick during publishing get added as fields in the SharePoint Forms Library. Since the workflow I was building needed to know about some names and the expense report total, I added all of those fields.

InfoPath Publishing Wizard

After I published my form, the columns magically appeared in SharePoint – cool! My panic was ebbing a bit…

Populating an InfoPath Drop-Down List Box with Choices from a SharePoint List

…and then I realized: What if the names people type in the Name text boxes provided by that sample InfoPath expense report form don't match names of actual users in SharePoint? My workflow needed real names to work, so now what? Well, InfoPath has an answer for that, and it's called Look up values from an external data source, which is an option in the Data tab of the field's Properties. This lets you populate a drop-down list box with values from a SharePoint list, such as the User Information List (which is what I used). Here's how:

  1. First, right-click the Text Box field that holds the name, and choose Change To > Drop-Down List Box.
  2. Right-click on the form field and choose Drop-Down List Properties.
  3. In the Data tab, click Look up values from an external data source, and then click Add…
  4. In the Data Connection Wizard, click Create a new connection to: Receive data, and then click Next.
  5. Select the option to receive data from a SharePoint library or list, and then click Next.
  6. Type the URL of your SharePoint site, and then click Next.
  7. Select the User Information List, and then click Next.
  8. Select the fields you want to have access to – I just needed Name – and then click Next.
  9. Choose whether you want to Store a copy of the data in the form template (I didn't), and then click Next.
  10. Enter a name for the connection (I left the default), and then click Finish.

Back in the Drop-Down List Properties, the User Information List was now listed in my Data Source box. Not quite finished….

  1. Click the Select XPath icon next to the Entries field.
  2. Expand the folders until you see the :Name field, select it, and then click OK.

All that just to populate two boxes in the Properties window…whew! Here's what it looked like when I finished:

 

The Microsoft Office Online site has an excellent video that walks you through this: Demo: Populate a drop-down list box from a SharePoint list.

After doing all that, I clicked OK to save the changes, and then clicked the Preview button in the toolbar to see if it worked. You don't know how excited I was to see a list of all my SharePoint users in the Name box. Woohoo!

Drop-Down List Populated From SharePoint Data

Filtering SharePoint List Data

On to my workflow! And, oops…I don't really need people's display names for my workflow, I need their account names. I could have just added the Account field to the User Information List data source (and I did that, from Tools > Data Connections) and used that instead of the Name field, but people usually don't know their managers' account names. I did a little digging and found an MSDN forum post that gave me a great way to let users pick friendly display names, which InfoPath would then use to populate a hidden form field with the corresponding account name:

  1. Add a Text Box field to the InfoPath form (Insert > Text Box).
  2. Right-click the text box and choose Text Box Properties…
  3. On the Data tab, click the Function (fx) button in the Default Value section.
  4. Click the Insert Field or Group… button.
  5. In the Data source drop-down list, select User Information List.
  6. Expand the folders until you see the fields in the User Information List.
  7. Click the :Account field (you did add it from Tools > Data Connections > Modify, right?), and then click Filter Data…
  8. In the Filter Data box, click Add…
  9. In the Specify Filter Conditions box, select Name in the first field, is equal to in the criteria field, and Select a field or group in the third field.
  10. In the Select a Field or Group box, change the Data source to Main (this is your InfoPath form fields).
  11. Click the field that contains the drop-down list of display names you created earlier, and then click OK.
  12. Click OK in all the dialog boxes (shown below) to return to the Text Box Properties.

 InfoPath Filter Conditions

InfoPath Formula

InfoPath Text Box Properties

Hiding Form Controls (Just for Fun)

I wanted the field hidden, since users didn't need to see it, so back in my Text Box Properties:

  1. In the Display tab, click Conditional Formatting.
  2. In the Conditional Formatting box, click Add…
  3. In the condition builder section, leave the field name selected in the first box, and then select is present in the criteria box.
  4. Check the box Hide this control, and then click OK.
  5. Click OK to return to the Text Box Properties, and then click OK to save.

 Hidden Form Controls

Click Preview one more time to make sure I can't see the field containing the account name anymore – success! Finally, I walked through the File > Publish wizard one last time, making sure to add my new hidden Text Box field to the list of fields to publish, and I was ready to go. Users could create their expense reports in a Forms Library; Workflow Conductor had the account names it needed for lookups; and users picked from a list of real names in the expense report form. Mission accomplished.

Curious to see how it all turned out? Check out the Expense Report Approval Workflow Use Case!