@WonderLaura

Another name for this post could be “The most web part connections you’ve ever seen”, or “how to go insane in 41 easy steps”. Just kidding. I thought I’d whip this up ‘real quick’ the other day, and then it turned out to be 9 pages.

This post applies to the enterprise version of SharePoint 2010 and Office 365 only.

Here is my video recording introduction to this post, explaining the final result and how it works.

The Problem

If you’ve ever worked with repeating tables in an InfoPath form, you may have noticed how extremely useful they are, but then you may have soon realized the limits that inherently exist. Repeating tables are nice when we need to have a subset of information on a form. For example, when creating an expense report, there will be some basic information about the trip, and then a repeating table can be used for the itemized list of expenses. With this type of table, we can save “real estate” on the form by letting the users add as many lines as are needed in the itemized list of expenses.

Repeating tables are great EXCEPT for reporting and workflows… when we need to gather all of this information later, or run a workflow on each of the items in the table. The form fields regarding the basic trip information can each be promoted up to become columns in the SharePoint form library. Unfortunately, when we try to promote the fields in the repeating table, since the table is in a hierarchical information structure (a table within a list item), it is not easy to promote the fields to columns in SharePoint.

The progression of thought is usually to create a separate SharePoint list to contain all of this repeating information. The structure of data is then more of a database, with the main information about the expense being in one “table”, and the detailed expense information is in another “table”, with a relationship being created between the two tables. This is great for reporting and having all of that detail information more accessible, but the problem is that once a data connection is created in InfoPath, receiving information from that “detail” SharePoint list, you can’t write back to that SharePoint list to make additions or changes to the itemized sub-list of expense details. You can only view it. There is a great InfoPath blog that was written back in 2007, Submitting to a SharePoint List http://blogs.msdn.com/b/infopath/archive/2007/03/26/submitting-to-a-sharepoint-list.aspx, which has steps to walk you through the complexities involved in writing back to a SharePoint list from within a form library form. This is great, but very complicated, and many times too complex for typical business users who are creating forms.

The New Possibility

Now I’ve described the problem, and if you’re an avid form creator/developer this is very familiar. In this article, you will learn a new way to accomplish the repeating table concept, which can be done in InfoPath 2010 and the new InfoPath Form Web Part. This solution can be accomplished with the enterprise version of SharePoint 2010 (with forms services), and it can also be done in Office 365. The idea is that multiple SharePoint libraries and lists can be used, joined together using lookups, and put together in a nice mash-up of web parts on a page in SharePoint. This way, SharePoint lists are used as a replacement for repeating tables. You will need InfoPath Designer 2010 and SharePoint Designer 2010. Note that this walkthrough does take a bit of advanced knowledge of web part settings, and a bit of InfoPath experience. For example, when I say “create a form, publish it to a library and create a submit connection”, I’m not going to necessarily go through step by step instructions, a bit of knowledge is assumed. Although some SharePoint and InfoPath knowledge is a good prerequisite, there will be NO programming or code involved.

The Example and Components

An expense report will be the example used in this walkthrough. The fields used will be very simple, because the main concept that is being taught is not a complex form and fields, but the concept of how to create the relationship and an interface to the form on a web part page. There are actually several different ways to accomplish this type of solution, with different combinations of form libraries and lists. This example will be the simplest version. The components are:

• Expense Report – This is the main “parent” list of expenses, which will contain fields such as the employee and the reason for the trip. This will be created as a SharePoint form library.
• Expense Details – This is the “child” or sub-list. It will contain fields such as the cost of each individual expense, and the type (meals, airfare, etc.) This form will also contain a lookup column to the ID field in the expense report. This will need to be created as a form library form, because of the ability to pass parameters to this type of form.
• Expense Dashboard page – This will be a web part page, and will be used as the dashboard for each individual expense report. It will use a query string parameter to show the expense report and details for a report.
• New Expense Detail page – This will be a web part page that is used for new expense details. A query string parameter will be used to set the correct unique Expense ID.
• WebPartPages – A document library you can create, as a single place to keep the two web part pages that will be created.

Note: There are many possibilities here, so there can be one parent list, and several different child lists, it is not just limited to one child.

The Steps

Building the Forms

1. Create a new form library form in InfoPath, “Expense Report”, with three text fields: Employee Name, Purpose, Billing Department.
   
2. Publish the form to SharePoint, promoting all of the fields to columns in the library.
3. Create a new site column called Expense ID, as a lookup field to the Expense Report library, specifically to the ID field.
   
4. Create a form library form, call it “Expense Detail”, and add the following fields:

   Column Name	Column Type
   Expense Type	Text
   Amount	Decimal
   Expense Date	Date
   Expense ID	Text

5. When publishing this form to SharePoint, promote all fields to be columns in SharePoint. Also, add the Expense ID to be an Input parameter. Since you already created a site column called Expense ID, this promoted column will automatically utilize that existing site column because it will recognize that the column name matches it.
   
6. After publishing each form, be sure to create a Submit data connection, for submitting the form to SharePoint. Click to Quick Publish the form again.
7. Change the default view of the Expense Detail library. Show the following columns: Type, Name, Expense Type, Amount, Expense Date. In the Totals section, choose the Sum of the Amount field. For the Style, choose “Shaded”. This is done so that when the web part is placed on the web part page, we can quickly see this view, which will show the total expense amount for a given form.
   

   Building the Dashboard Page

8. In the browser: Create a new blank web part page called ExpenseDashboard.aspx, and add the following web parts to it in this order down the page:
   Query String (URL) Filter
   Expense Report (library)
   InfoPath Form Web Part
   Expense Detail (library)

   Note that the Expense Report library web part will be hidden later.

9. In the Query String web part settings, set the Query String Parameter name to ExpenseID.
   
10. For the Expense Report library, change the current view so that it is sorted by ID, descending. This is important because part of the way the solution will work when new expenses are created. The highest (most recent) ID needs to be at the top. This web part will be hidden later, so the displayed columns are irrelevant.
11. Set the InfoPath form web part (web part settings) to use the Expense Report library, with the submit behavior to leave the form open.
12. Set the Expense Details web part view to show the default “all documents” view, which is the one you modified at step 7.
13. Open the ExpenseDashboard.aspx page in SharePoint Designer 2010. Create a new parameter in the Expense Detail web part called ExpenseID, as a query string.
    
14. Filter the Expense Details web part so that the Expense ID is equal to the new ExpenseID parameter.
    
15. Save. In the browser: Create a web part connection to filter data:
    Provider: Query String URL Filter = QueryString
    Consumer: Expense Report library = ID field
    
16. Create a web part connection to send a parameter (NOT a filter):
    Provider: Expense Report Library = ID
    Consumer: Expense Detail = Expense ID parameter
    
17. Create a web part connection from the InfoPath form web part, to “Get Form From”
    Provider: Expense Report Library
    Consumer: InfoPath Form Web Part

    Building the New Expense Detail Page

18. In the browser: When we want end users to be able to click to add a new expense detail, we typically don’t want them to have to choose the name of the expense report lookup item, it should already be filled in. When a link is clicked, it should take you to a form that already knows which of the parent expense reports you’re working in. A new page will need to be created, called NewExpenseDetail.aspx, in order to use as the custom new detail. Create a new web part page, and add the following two web parts to it: Query String URL Filter, and InfoPath Form Web Part.
19. Set the InfoPath form web part to use the Expense Detail library, with the submit behavior to close the form.
20. In the Query string web part, set the Query String Variable to be ExpenseID, just like in step 9.
21. Create a new web part connection, by clicking the drop-down in the top right corner of the InfoPath form web part, to get form data from the Query String URL Filter.
    
22. When configuring the connection, the provider field name is QueryString, and the consumer is the Expense ID field. This is the only parameter available, because at step 5 it was set up to be an input parameter.
    

    Custom Hyperlinks

23. Link to create a new form: In the Expense Report library, click the Add Document button, so that the URL can be obtained. This URL will be customized so that as soon as someone creates a new expense report and submits it, they will be directed to the dashboard page for that new report. The URL will be structured like this: yoursite/_layouts/FormServer.aspx?XsnLocation=yoursite/ExpenseForm/Forms/template.xsn&SaveLocation=yoursite/ExpenseForm&Source=yoursite/ExpenseForm%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1
    Go grab the URL of the ExpenseDashboard.aspx web part page, and the SOURCE is what needs to be changed. Replace the url after &Source with the URL to the dashboard. Here’s mine:
    http://demo.contoso.com/expenses/_layouts/FormServer.aspx?XsnLocation=http://demo.contoso.com/expenses/ExpenseForm/Forms/template.xsn&SaveLocation=http%3A%2F%2Fdemo%2Econtoso%2Ecom%2Fexpenses%2FExpenseForm&Source=http://demo.contoso.com/expenses/webpartpages/ExpenseDashboard.aspx&DefaultItemOpen=1
24. Take this new URL that you created, and put it on the homepage of your site, or somewhere prominent, as the link to “Fill Out a New Expense Report”. When you click on this link, fill out a new report and submit it, you will immediately be directed to the custom “dashboard” page. Make it a pretty little graphic if you’d like. 😉
25. Now that new expenses can be created, new expense details need to be created. I’m going to walk through this next part in the simplest way, although there are several different places this hyperlink could possibly be added. There needs to be a CUSTOM new hyperlink for creating a new expense report detail. This will be the URL to your New Expense Detail web part page, with the expense parameter at the end:
    http://demo.contoso.com/expenses/Shared%20Documents/NewExpenseDetail.aspx?expenseid=__
26. This hyperlink will be added to the page using a data view web part. Open ExpenseDashboard.aspx in SharePoint Designer. Put the cursor in an empty web part zone, click the Insert tab, click Data view, and select Empty Data View.
27. Click Click here to select a data source. Select Expense Report and click OK.
    
28. Drag any one field over into the data view web part. It doesn’t matter which one. On the Design tab, choose Layout Table form style, and click OK to the warning.
29. Change the paging to Limit to one item.
30. For the web part properties, select a chrome type of NONE, and while you’re in there, change the name of the web part to ADD NEW DETAIL.
31. Delete the one field that you added to the DVWP at step 26. It should look like an empty white box now.
    
32. With the cursor in that same spot where the field was, type CLICK TO ADD A NEW EXPENSE DETAIL
33. Create a new parameter called ExpenseID, as a query string parameter.
    
34. Make this a hyperlink to that URL at step 25. This web part is going to receive its expenseID value NOT from the query string filter web part, but from the Expense Report library web part. It is important that both the expense detail library web part and this new web part both receive their parameter from the expense reports library. This is because when someone creates a NEW expense report and is redirected to the expense dashboard, the expense reports library will automatically be sorted to show the most recent one on top, therefore when you click to create a new expense detail, that new ID parameter will have been sent from the expense reports library’s most recent addition.
    
35. Either in SPD or in the browser, create a web part connection to send a parameter (NOT a filter):
    Provider: Expense Report Library = ID
    Consumer: Add New Detail = Expense ID parameter

    Try out a new Expense Report

36. Okay, so far, we can start from the home page, click a custom link to fill out a new form, fill out a new expense form, and then we’re automatically taken to the page where we can add the expense details in the bottom web part. AND, when we add expense details, those details are automatically associated with the correct expense report. Good times!
    

    Edit the Form Link

37. One more thing. We need users to be able to click the name of any one expense report, and this takes them straight to the web part page instead of to just the InfoPath form. Yet another custom hyperlink. Put that Expense Report library on the home page as a web part, and choose the following columns: Type, Created, Created By, Employee, Billing Department.
38. Also, in the web part settings, it’s important that you select Toolbar type: no toolbar.
    
39. Open this home page in SharePoint Designer, so we can modify this web part slightly. Basically, we’re going to formulate a hyperlink to take you to the dashboard for a form, so I’m going to put it on the “Created” field, but it can really go anywhere. Click the field you want to put the hyperlink on, and on the Insert tab, click Hyperlink.
40. Paste in the URL to your dashboard, with the ID of the current item as the parameter at the end. Click OK.
    
41. Don’t forget to hide the Expense Report web part on the ExpenseDashboard. We don’t need to see it, we just need it for filtering.
42. Done! Only 41 easy steps. :-S

Wow, this turned out to be more like a chapter in a book than a simple blog post. Speaking of books, I’d better get back to writing. Darvish Shadravan and I are currently writing Using Microsoft InfoPath 2010 with Microsoft SharePoint 2010 Step by Step.

Okay, so this was pretty long and complex, right? Still NO CODE, though. This is a great solution for being able to have multiple “repeating tables” without actual repeating tables inside InfoPath. And, that web part of “expense details” could easily have inline editing turned on, so that the items could quickly be modified right on the page. Multiple lists could be used, etc. Now that you know all the pieces and how the web part connections would work… go crazy.

I challenge you to go out and create this solution on your own, using this method. It doesn’t have to be a time off request, just whatever, but you get the idea. The reward will be that the first person to do this will get a free copy of my book Beginning SharePoint 2010: Building Business Solutions with SharePoint. You’ll have to prove you did it, though. A recording would be nice. 😉

Again, here’s the link to the video recording of me explaining the final result of this solution.

10/16/2013 UPDATE:I recorded an hour long video, showing how to build this solution