InfoPath 2010 and Repeating Tables
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
- Create a new form library form in InfoPath, “Expense Report”, with three text fields: Employee Name, Purpose, Billing Department.
- Publish the form to SharePoint, promoting all of the fields to columns in the library.
- Create a new site column called Expense ID, as a lookup field to the Expense Report library, specifically to the ID field.
- 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 - 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.
- 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.
- 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
- 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.
- In the Query String web part settings, set the Query String Parameter name to ExpenseID.
- 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.
- Set the InfoPath form web part (web part settings) to use the Expense Report library, with the submit behavior to leave the form open.
- Set the Expense Details web part view to show the default “all documents” view, which is the one you modified at step 7.
- 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.
- Filter the Expense Details web part so that the Expense ID is equal to the new ExpenseID parameter.
- Save. In the browser: Create a web part connection to filter data:
Provider: Query String URL Filter = QueryString
Consumer: Expense Report library = ID field
- Create a web part connection to send a parameter (NOT a filter):
Provider: Expense Report Library = ID
Consumer: Expense Detail = Expense ID parameter
- Create a web part connection from the InfoPath form web part, to “Get Form From”
Provider: Expense Report Library
Consumer: InfoPath Form Web PartBuilding the New Expense Detail Page
- 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.
- Set the InfoPath form web part to use the Expense Detail library, with the submit behavior to close the form.
- In the Query string web part, set the Query String Variable to be ExpenseID, just like in step 9.
- 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.
- 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
- 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 - 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. 😉
- 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=__ - 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.
- Click Click here to select a data source. Select Expense Report and click OK.
- 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.
- Change the paging to Limit to one item.
- 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.
- Delete the one field that you added to the DVWP at step 26. It should look like an empty white box now.
- With the cursor in that same spot where the field was, type CLICK TO ADD A NEW EXPENSE DETAIL
- Create a new parameter called ExpenseID, as a query string parameter.
- 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.
- 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 parameterTry out a new Expense Report
- 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
- 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.
- Also, in the web part settings, it’s important that you select Toolbar type: no toolbar.
- 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.
- Paste in the URL to your dashboard, with the ID of the current item as the parameter at the end. Click OK.
- 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.
- 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
Hi, how can I submit my infopath form? I want to have your book 😛 your blog and video clips were very very helpful! thank you Laura.
I got it! Excellent tutorial, super useful. I have found one area I would look to improve. After one submits the details of an Expense Report, I would try to redirect ppl to either the “Home site” or the ExpenseDashboard for the associated report. If I have time tomorrow I may look into solving that. Youtube link here: http://www.youtube.com/watch?v=cdTztVv5Qns also in CommentUrl… not sure what that is… Thanks a million for the post, must have taken a while to write!
Great Post. I’m doing something similiar and am completely stuck with using the DVWP Variable & DEFAULT VALUE needed to display all records. I have setup a variable – Query String. The records that have value of Yes or No. After I configure the variable – I leave the default value blank. If I then access the URL I see NO records returned unless I use the ?variable=Yes. What value can I set for the Default Value so that ALL records are returned if no variable is sent along with URL? Help!
Hi Laura, I was hoping to create a “repeating row” in a list for a similar Expense List. I am using SharePoint Foundation 2010 so InfoPath forms is not an option for me. Have you written any blogs in relation to how to do this..? I have found a great paid for option but I would prefer to create it myself if possible. Thanks Kelly
Eric, Great question, I was just trying to do that the other day, and it doesn’t seem to be possible with a List View Web Part. You’d probably have to use an old school data view web part, because then you’d be able to do custom XSLT for the filtering, like put an “OR” in there or something. Here’s the diff between the 2 types: http://www.youtube.com/watch?v=r2eODYHp73A Kelly, Yes, you can do all this and not use InfoPath, just use a regular ASPX form instead, for the main expense parent info.
Thank you for the excellent work around. I have couple of questions: 1- When is your new InfoPath 2010 book coming out? 2- If I use your solution as outlined here, how would I be able to transition it to our pronduction envirornment. Our governance model disallows using SharePoint Designer in a production envirornment. I was wondering to create your InfoPath solution in my dev environment using the tools you used here and then export it into a wsp file. Later open this wsp file in Visual Studio 2010, create the package so that it can be deployed in the production envirornment. Do you think it is possible to do that? Do you think every pieces of my dev SharePoint solution will be picked up in my export job? Please let me know your recommendation on this regard.
Ast, 1 – November 2nd. You can pre-order it on Amazon. 2 – I don’t know. I don’t know anything about solutions or WSP files because I’m not a developer, so I have no idea how you’d go about that. Sorry, I wish I could help.
Laura – great post and I’m having fun putting it together, however I’m getting stuck at the point where I add the blank Data View, select the data source and then drag the field into the web part … when I go to delete the field before entering the text for the link I either delete the whole web part, or somehow miss it and when I test I see the field value instead of my custom linked text, Any tips for how to delete that field? Thanks,
Thanks so much for this tutorial, this is something I have needed for quite some time. I am stuck at the data connection steps 8,9,10. I am using sharepoint designer 2010 and when I hit add connection I get a connection wizard that has source and target actions and I cant seem to find the screens that you have in your screen shots… I have highlighted the Expense Detail web part and click add connection. What am I missing?
Craig, What number are you stuck at? Stacey, The data connection is in InfoPath, not a web part connection in sharepoint.
Laura, Custom Hyperlinks – Steps 5-13; I’m able to add the blank data view okay and connect it to the library; I then add the field and things fall apart after that … Thanks
I think Stacey above is at the same place I am. I belive she intended to say web part connection not data connection. I do not see the same screens you have when trying to setup the web part connection in steps 8, 9, and 10
Has anyone got through this guide? There seems to be a lot of holes in it and I feel like I’ve wasted half a day trying to get through them. Especially around the webpart connections there seems to be mistakes or its just not explained so that I can understand it. At first glance it looks like a great guide but there are parts missing and whole steps that are just not documented. For example you cannot recreate the newexpensedetail.aspx page following those instructions you are missing complete steps like editing the query string filter and even doing that It doesnt work for me i get a schema error. Beginners should be wary of investing time into this guide. Good luck with your book.
It’s not a guide, it’s just a blog post. Not tested and documented the same way a book is. Valerie got through it and she also did a video of her completed work. I’ll look through your specific questions.
Sorry I didn’t mean to be overly critical, it’s just frustrating when you do research think you have a solution just to spend hours on it and not get it running cause there parts missing or not clear. I was a bit frustrated when I posted but to the point to help others if you do intend on going back and fixing the post: 1. Not a big deal but i think your missing a headin gin the first section. Your step numbers restart in the middle of the section. I believe you intended this to be two sections. 2. steps 8,9, and 10 cannot be done in designer with the screenshots that you provided. People need to know that they need to go to the page to reproduce those screenshots. 3. When telling people to add the connections to the webpart it would be nice if they know what web part on the page they should be making the connections from. This I think is a pretty big point of confusion (at least it was for me). 4. There is a step missing in the “building the new expense detail” section you have to provide the query string info before you can connect the web part. Personally even doing this i was getting a schema error and wouldnt let me connect the webpart to the form display webpart. This is pretty much my giving up point, I didnt go through the rest but there might be more stuff. While this is not a guide but a blog post or an article or just random words put together.. to the end user it looks like a walk through guide. I personally appriciate the effort you put into writing it, I just wanted to warn anyone attempting it that they need to have some basic understanding before getting started (I might have thrown in some frustrationed color and for that I appologize). Thank you for putting the guide/blog/whateva together!
Do you have any suggestions on how to convert your solution to a word report. I was looking at the way I have seen to link word fields to the list but the issue is if I use repeating tables it does not work. Any suggestions on how to make this into a nice word doc would be great
I am lost at 8,9,10 The current instructions are too vague for me. Does anyone have more detail regarding these steps?
Thanks guys for the suggestions on a couple of missing steps. I have made some clarifications and additions in red, and I also fixed the numbering. Especially thanks to David. This was an untested solution. It is also geared toward more advanced users who will already know about web part settings and connections. My InfoPath book is geared toward a different audience, and actually has very detailed (and tested) steps, assuming the reader does not know the product at all.
Awesome walkthrough! I’ve run into the glaring missing feature of SharePoint/InfoPath having the ability to do repeating tables/sections twice now in the last year, and this has turned out to be the only work around for it. That said, I am having an issue. It seems that when I click on a custom edit link as you describe in your how-to that I’m getting the expense report, but the associated expense detail is not coming up… If I unhide the expense library web part, I can see that passing a ?expense_id=XX to the aspx is indeed being handled by the library (the list of reports is filtered and it appears it’s being selected), but I can’t get expense details… only when I remove the filter manually and click the “select” myself for the expense report will I see the associated details… I’ve included an error message below that may or may not have anything to do with the error… your additional help is hugely appreciated. Thank you. An event log to be applied against a form was invalid. (User: Domain\user.name, Form Name: Expense Reports, IP: , Request: http://www.domain.com/sites/sitecollection/subsite/SitePages/ExpenseDetail.aspx?expense_id=52, Form ID: urn:schemas-microsoft-com:office:infopath:Expense-Reports:-myXSD-2011-11-17T22-48-47)
I kept getting an error about schema..i really would like to get this down..I will try this agian.
Hi guys, since the instructions are so long and complex, please let me know the step number where you are having an issue or error, so that I may better help you out.
I didn’t have specific issues with any particular step… even where the instructions are a little unclear, I was able to figure out what you were going for and proceed accordingly. I’m simply having an operational issue having completed the step-by-step.
Hi Laura, I am stuck at step 34, i am not clear what you mean in that step. I am getting redirected to the page Expensedashboard Page for new expense but its not pulling up the actual record that i submitted newly. Also the ExpenseID is not getting appended to the details link. FYI, i am able to submit or get to expense details for existing expenses but not for new one. Thanks in advance.
Mani, Hi, on your expense reports library web part on that page, please go into the view settings and make sure that the list is sorted by ID descending, so that the most recent ID is on top.
Laura – Your blog is great and I am glad i found it. Its been very helpful for a beginner like me. Right now, I am trying to create a voting interface using out of the box functionality of SP2010. I am not sure if this the right place to post this. Anyway, here is my problem.I am having trouble creating a nested repeating sections. Could you please guide me or let me know where can I find a good resource. thank you for your time.
Hi Tweety,
Infopathdev.com may be a good place to look around regarding questions like that.
hi laura
I’m following these steps religiously and stuck in step 23. In the expense report library if I click Add document link, the infopath form opens for me(naturally)…and even in any document library the link would be like Upload.aspx?List={}. From where did you get the link like FormServer.aspx?XsnLocation=…?
Also I came to your blog post after I saw your youtube video. The youtube video did have the link for this. I googled and found few incorrect links and finally found this one!
Kartik,
Instead of clicking Add document link, click the documents tab in the ribbon, and click the New Document button. This will bring up the new form, and this will get you the URL you need.
Thanks Laura. Is there a reason you chose form library rather than List template? Sorry if I’m missing something obvious.
Yes Kartik,
SharePoint list forms don’t allow you to add parameters.
Hi Laura, I love your videos and insight. I am using your repeating tables idea to essentially set up elegant functionality of individual recruitment projects for my recruitment company.
However I am stuck at step 21. as I do not fully understand that paragraph. The hyperlinks from the library and Dashboard that I see on my site look different than yours and I do not see a &SaveLocation nor a form&SOURCE anywhere within mine. Do I need to insert this text myself? I would really appreciate you help with this.
I have created a infopath form using repeating table. When i insert value in the form it should be store in a list at one time if i insert many values in repeating table it should get display in list
I am stuck at Step 34. You mention the URL in step 23, but the example is the URL from step 25. Which one should it be?
Thanks
NJ Dowell
Ok I am confused. Maybe because I do not know SharePoint that well yet, but in your video you say this works with SharePoint lists, yet my experience so far is that when a SharePoint library is made, it stays a library. Do you link the fields to lists to begin with or make the list then build the form around the existing list through InfoPath? This is two lists, right?
Also, I am with Christian on step 21. My URL looks nothing like your example (that is, including “Source”).
Additionally, when I click the link to add a expense detail, I get a blank page even though the URL looks right. Can I assume when you use “Expense ID” (with the space) you are talking about the ID field in the Expense Report, or is that a typo?
Thanks!
NJ Dowell
Christian,
Are you referring to step 23? When you go to your expense report library, and click Add Document, as if you’re filling out a new form, that’s when you grab the URL of that page from the address bar. The &source will always be there, and that’s the most important part.
NJ,
So sorry, I just corrected step 34 for you. Also, as stated in “example and components” at the beginning, this is two libraries, not lists. So, maybe I can assume that you guys are seeing something different because you used lists instead of libraries?
Curious… Will this work with InfoPath 2010 and SharePoint 2007?
Thanks for the reply, Laura.
Yes, I am trying to use a list instead of a library because it seems to sync better with excel for the pivot tables I need to create. More than anything, I am looking for a way to provide users with a clean interface for inputting data where the areas they need to fill out are filtered from the main list without them being able to change set fields, and I think your method will do that.
I am off to try this again…will let you know how it went!
Hi again Laura,
This definitely does not work with lists; at least not a list I am trying to load in datasheet view. Essentially, I have a pre-determined list I need to filter by department for people to submit statistical data. This is very close, but not quite what I need to do, so I will do some more digging.
Thanks for the solution though! I am sure it will help someone somehow!
John,
No because 2007 doesn’t have the infopath form web part.
NJ,
No, because list forms cannot do input parameters.
Hi Laura,
Excelent post, thank you. Just what I need for a different application. I’ve replaced the Expense Report for a Club Member Profile and the Expense Details for Personal Scores (e.g. the golf handicap over time). Would it be possible to have the newly added score copied into the Profile form (e.g. by triggering a workflow???), preferrably without any coding?
Jean-Pierre,
Hmmm, if you only want that parent form to just show that most recent score then, yes. Create a field called Score in the main form, and then when you go through the publishing wizard, pick that field to push up as a column. Check the box to allow it to be edited in datasheet (allows it to be edited from a workflow also). Then, when child items are created, you can make your workflow write directly to that field in the library.
Thank you very much Laura.
OKay I must be missing something… Because it states: The form cannot be submitted to the specified SharePoint document library. The document library already contains a file with the same name. A value in the form may be used to specify the file name. If you know what that value is, modify it and try submitting the form again.
An entry has been added to the Windows event log of the server.
Log ID:5567
Gina,
That just sounds like a problem with how your submit data connection was created. For the file name, it needs to be something that’s unique to each form, such as a concatenation of a few fields in the form
Laura,
I am stuck at step 21….not sure if i did something wrong back at step 5 or not…could you clearify step 5 for me (When publishing this form to SharePoint, promote all fields to be columns in SharePoint. Also, add the Expense ID to be an Input parameter)….
thanks again
Julian,
You did create this as a form library, and not a SharePoint list, right? When you first published it to SharePoint to the library, you went through a publishing wizard. One of those screens in the wizard asks you which fields to promote and has a section for parameters also. Click File… and choose Publish… and choose sharepoint server…
I was like Julian (needing clarification re: an Input parameter) but realized why I wasn’t seeing the Parameters section in InfoPath’s 2010 UI. It’s because the form was set to a Form Type of “InfoPath 2007 Filler Form” [Form Options >Compatibility> Form Type] and so this section is hidden.
Hope this helps others who may have “inherited” forms.
Hi Laura…avid fan for a number of years now! Question…what was the purpose of creating the Site Column ExpenseID as a lookup? You make no further mention of it. I couldn’t use it in the form creation within Infopath Designer. Just curious…and thaks for a great blog post.
Can you give more details and screenshots for step 35? I am not sure exactly what needs to be done. Thanks.
Hi Laura
Great info – thanks for putting this “pattern” out there.
[You did a great job at SPTechCon Boston by the way. :)]
I’m applying it in a project [parent form] and related software release [child form] scenario.
I’m at Step 36 and troubleshooting based on the results of my test.
– In the child form, my promoted “Expense ID” field is not finding the site column with the same name. (I have verified that my promoted column name and the site column name match. [Created both with no spaces in the name.])
– After publishing my child form, when I look at the form library settings, my “Expense ID” field has a data type of “Single line of text” rather than Lookup.
Any idea what might be wrong? [I have tried removing the promoted field, publishing the form, and re-promoting/publishing several times but can’t think of any other troubleshooting steps to take.]
Harry,
Steps 5 and 14 use the Expense ID column. It is important for the web part connection, so that each detail item knows which expense it goes with.
Pam,
it’s just another web part connection. If you use the browser, your screen will look something like the screenshot at step 16.
Eileen,
When you are going through the publishing wizard and selecting columns to promote, you have to specifically pick the already existing site column, and not let it create just a new column in the library.
Step 19 – Set the InfoPath form web part to use the Expense Detail library, with the submit behavior to close the form.
After I submit a new expense detail using the NewExpenseDetail.aspx page I receive an error that the form is closed. If I change the Submit Behavior to Open a New Form, the Expense ID is only captured when I add the 1st detail expense but not the subsequent detail expenses. What am I missing?
I would love to hear the solution for topic that Rio mentioned. I managed to reproduce your scenario in another scenario (Company,servers, server physic and logic). Still that one thing is missing. The first time form is rendered with parameter it saves well but if the form propertie is changed to open new form after submit it opens form without parameter so the next entered parameter in the form is null (expense detail form).
Secondly, is there a way to make this form close and go to the expense report page with same expenseid. So we dont have to go back.
Beautiful work and nicely explained 🙂
Rio and Aljosa, I think your questions are similar.
I have just in the past month discovered a workaround to that, it’s kinda hard to explain though.
Web parts:
1 – Main form’s list view web part
2 – main form as an infopath web part
3 – child expense detail as an infopath form web part
Connections:
Set #2 to get form from #1
Set #2 to sent a parameter of ExpenseID to #3 parameter.
Basically, passing the parameter straight from #1 to #3 only works for the first one you add, and then the parameter doesn’t get passed again after that. If you pass the parameter from one infopath form web part to another, it gets passed every time you add a child item. You can hide any of these web parts and it will still work fine.
Thank you Laura, I will try to reproduce the solution and get back to you 🙂
When I try to set #2 to send a parameter of ExpenseID to #3 parameter I receive the message – No Provider Schema.
Hi Laura,
I am stuck at one point in InfoPath 2010. Here is my requirement.
I would like to submit a data from repeating table to a SharePoint List. I created XML file and then bounded repeating table with it. I am able to insert data into controls and then even submit it to SharePoint List using submit with lists.asmx web service option UpdateList method. All fine.
Now the challenge for me is, How can I have something like showing only one blank row in repeating table with options to keep adding rows and at the end submit all entered row to a SharePoint List.
I am not getting the option to allow insert link in repeating table. It is disabled.
Any help would be greatly appriciated. I am badely stucked.
Rio,
The only time I’ve seen that happen is when you haven’t published any parameter columns when you go through the publishing wizard in InfoPath.
Malay,
Besides what they show in this article http://blogs.msdn.com/b/infopath/archive/2007/03/26/submitting-to-a-sharepoint-list.aspx any tweaks to that would require an entirely separate blog post.
Rio,
I got the No Schema error when I did not save the ExpenseDashboard.aspx in SPD after steps 13-14 if that helps you out any.
Laura,
I have been documenting each step I take while following your blog post and everything appears to be working correctly. However, my browsers do not exhibit the same behavior when entering Expense Details.
On your video the Expense Details Form appears to open into a new window, mine opens into the same browser window. And when I Submit the Expense Details form it shows “This form has been closed.” in the infopath webpart instead of opening a new Expense Details form as yours does.
I see that the CLICK TO ADD A NEW EXPENSE DETAIL url is the same, so it is most likely some other setting somewhere. I created a submit button on the Expense Detail form that has 2 actions, submit the data and then close the form. What am I missing?
I am running into one more issue:
If I add the connection to the Add New Detail data view web part it affects the connection with the Expense Detail web part and that filtered library list shows as empty.
When I remove the connection to the Add New Detail data view, the Expense Detail web part displays the correct filtered items.
Do you have any idea why that would be happening? I just redid this solution from scratch and am seeing the same behavior.
Hi, for steps 15, 16 and 17, what webpart(s) am I creating a connection on? I think I did this wrong because it’s not working for me.
Thank you!
Ian,
In the form web part settings, you can set it to “open a new form” after submitting, so it won’t say “this form has been closed”. Also, if you have a parameter set in your DVWP, and it’s got a filter set in it, then it will automatically receive any query string information without a web part connection.
Lisa,
For each of those steps, the “provider” is the name of the web part you create the connection from, and the “consumer” is the one you’re connecting to.
Thank you for responding Laura. I think I have it set up correctly but it still doesn’t work for me. I will give it another try. 🙂
Laura,
I’m having similar problems regarding step 19. I tried modifying it with what you stated on Aug 30 11:46 but I’m pretty sure I’ve hosed it completely.
What are the total web parts after the Aug 30th update and does any of that override step 19 through 22?
Thanks Much!
Hi Laura,
I am on steps 15 and 16 and I’m not sure where I am supposed to create the Web Part connections. Can you help with a bit more detail please?
BA,
The Web Part connections are done when editing your page in your browser.
Laura,
I GOT IT!!!
Below has all my web parts and their connections.
1) Expense Details List Get filter Values from Expense Report Form provider is ExpenseID and consumer is ID.
2) Expense Reports List (3 connections) Send row of data to Expense Report Form, Send row of data to Expense Details Form, Get filter Values from Url Query Filter provider is Query String and consumer is ID.
3) Expense Report Form (2 connections) Get form from Expense Reports List, Send data to Expense Details List provider is ExpenseID and consumer is ID.
4) Expense Details Form Get Data from Expense Reports List provider is ID and consumer is ExpenseID.
5) UrL Query Filter Sending filter values to Expense Reports List provider is Query String consumer is ID.
I believe this is all accurate, also I did test and it did supply multiple detail records!!
Laura,
Also I found out how to do open in new tab (in regards to step 31). In designer choose split mode and add
target=”_blank”
So when finished it looks like below
Click to add new detail
Thanks
Hi Laura,
I have moved further and am now on step 35 and like Pam need more details/screenshots. I don’t get options to send parameters, not sure where I should be trying to set the connection exactly. Nearly there…so please help 🙂
I am with most of the others. Stuck on Step 15. Not sure where to build the connections. Tried it for an hour and finally gave up. This is awesome and really want it to work, just cannot figure it out. Please help
Bob, you rock.
Everyone with questions about the web part connections, read his neatly compiled list. All the web part connections are just created in the browser, with the page in edit mode.
BA,
Step 35 states to create the connection from the expense report to the expense detail. Not sure what error or problem that you’re coming across.
Hi Laura,
I’ve completed the steps above, and while it looks as though the functionality is all working, for some reason the ‘child’ expense detail web part on the ExpenseDashboard isn’t displaying any items. I can see the title of the webpart, so it’s not hidden, so I assume I’ve messed up the connections somewhere.
Hey I am working on a project to creaet a submittal package log the submittals have one or more items that get sent to one or more reviewers. I already have list for the packages items and the review cycles for those items is there a way to use a list form to on a infopath webpart page to work like the expense detail I am just start to get into this level of Sharepont.
I used list as I need to create reports in excel if had to do it over I would of used forms but there are thusands of items and reviews at this point
Hi Laura,
This is a great solution I want to use. However, Our governance disallows use of Designer 2010. Is there any work-around to achieve this solution overall without SharePoint Designer 2010?
This is an amazing solution and stops having to wrestle with pesky repeating tables!
It all works for me – apart from I corrected a space in the ExpenseID column so the value from the expense library is being stored in the wrong column and I don’t know where this was set!
I have been through the above and can’t spot it!
Any pointers would be great!
Cheers,
Lou
Dermot,
Check the detail web part’s filter setting, to see what it’s filtering by. Double check that you’ve done steps 12-14 correctly.
Greg,
Sorry, but parameters aren’t available when using list forms. Sorry, but I don’t think there’s a way around that.
Steve,
I don’t think there’s a way to create the parameters without SharePoint Designer.
Lou,
Go through the publishing wizard again, and look at the promoted columns. If you need to change one, just delete it and go all the way through the wizard. Then, go through the wizard again and add it back. SharePoint gets very confused when you just change one.
Custom Hyperlink Step #23. In the Expense Report library, I click the Add Document button and the form opens in InfoPath. Now where will I find the form url? I do not see an address that looks anything like your example (ref: yoursite/_layouts/FormServer.aspx?XsnLocation=yoursite/ExpenseForm/Forms/template.xsn&). Any thoughts where or how I can find the correct url?
Art,
You need to set up your form so that it only opens in the browser, not InfoPath. In the library’s advanced settings, change the setting to force it to open in the browser.
Hi Laura, love the you tube vids they have helped me out a fair few times!
could you give some advise; is it possible to populate a repeating table on an input form?
The situation i have is that i have one list which i have created a multiple item input form on, so when i press submit it saves each row of the repeating table to the list (this works fine)
then what i need to do is query the same list to bring back data when it meets certain conditions.
i can do this no problem without a repeating table but im stumped at how to tell the repeating table how many rows to open by dynamically and to fill it with multiple rows instead of the same first row over and over.
I hope i have been clear and that you can help! 🙂
Laura,
I’m trying this in Sharepoint 2013 with SP designer 2013 and Infopath 2013. I can’t recreate the steps. I can’t find a way to filter the Expense Details since those fields never show up on the filter. Also have no idea how to make the custom hyperlink for Expense Details as this seems to be using design view. I don’t seem to be able to bring up the datasource after inserting an empty data view. Thanks in advance for any quidance
Laura,
I’m trying this in Sharepoint 2013 with SP designer 2013 and Infopath 2013. I can’t recreate the steps. I can’t find a way to filter the Expense Details since those fields never show up on the filter. Also have no idea how to make the custom hyperlink for Expense Details as this seems to be using design view. I don’t seem to be able to bring up the datasource after inserting an empty data view. Thanks in advance for any quidance
Hi Laura
I have created the custom link to create the new expense report, but it always opens Infopath Filler. If I just click add document in the library it opens in the browser just fine.
I changed the libraries advanced settings to Open in browser and the InfoPath Publish settings are open in browser?
Many thanks
Mike
Hi Laura
It seems the custom url was too long and was truncated (the open=1 was missing) Not sure yet why it was too long as its length is shorter than your example.
To make it shorter I removed the clientinstalled=true, which I noticed is not in your URL, now it does not open in the filler!
Mike
Hi Laura
When I create a second expense detail from the expense detail page, the expenseid is not passed to the form, so when the form is submitted it ends up in limbo and not connected to any expense report.
Did you have this trouble? I noticed the video was cut as you started to create a second expense detail.
Many thanks
Mike
Hi Laura,
I ran into an issue in attempts to follow your step-by-step instructions in this article post. I am trying to implement this particular procedure (more specifically, step #13) in this environment:
-SharePoint 2013
-SharePoint 2013 Designer
However, I cannot create a parameter in SP Designer 2013 as it underwent tremendous changes. In a major need of help. Any suggestions or workarounds?
-Anything will help
Thanks!
Hiren
Can you provide this example in SP13, InfoPath 2013 and SP13 Designer.
Greatly Appreciated!
James,
Nope, Microsoft removed the Design view in SPD 2013, so you can’t do this solution anymore unless you know how to navigate through the code view of the web page.
I have a question about infopath repeating table.when i working timesheet for the employee I used like 6 fields and 2 rows by repeating table. the table is look like:
Weekending | Mon. | Tus. | wed.| Thur.| Friday| Total week.
I added the formula on Total week Sum(Mon+Tus+…Fri),but how can I use the formula for Weekending to add 7 days when i click an insert item on the repeating table?
thank you!
I think I’ve nearly got this working… I’m trying to implement this for a Trade Authorisation form – one employee, multiple trades.
All steps have been completed, but once I submit a trade detail the following trade details aren’t saved with any Trade ID.
Also, if I create a new Trade Authorisation form the ‘Add New Detail’ link still uses the same Trade ID – it never changes.
What do I still need to do to get this working?
Thanks
Hi Laura,
Ok…. well since submitting a question yesterday, I have now got it working (kind of…!)
After I submit a detail from the detail web part page, the subsequent details do not have the ID.
But if I go back to the Dashboard and click the ‘Add Detail’ link, it works…!
I am happy with this… but I just need to find a way to gracefully close the whole detail window (not just the form) after the 1st detail has been submitted so it will force users back to the Dashboard. Or I need some help to figure out why it doesn’t work after the first submit.
Here is my configuration:
On the Dashboard page:
* Query String (URL) Filter
* Expense Form Library (hidden)
* Expense Form
* Expense Detail Library
* Expense Detail Form (closed)
* Data View Web part (Add New Detail hyperlink)
Web Part Connections (the letters show the connection pairings):
1) UrL Query Filter
* Send filter values to Expense Reports List – Provider: Query String / Consumer: ID (A)
2)
Expense Reports List (5 connections)
* Send row of data to Expense Report Form – Connection Type: Get Form From (B)
* Send row of data to Expense Details Form – Provider: ID / Consumer: ExpenseID (C)
* Get filter Values from Url Query Filter – Provider: Query String / Consumer: ID (A)
* Send row of data (parameter) to Add New Detail (DVWP) – Provider: ID / Consumer: ExpenseID (D)
* Send row of data (parameter) to Expense Details List – Provider: ID / Consumer: ExpenseID (E)
3) Expense Report Form (1 working connection)
* Get form from Expense Reports List (B)
4) Expense Details List
* Get Parameters from Expense Reports List – Provider: ID / Consumer: ExpenseID (E)
5) Expense Details Form
* Get Data from Expense Reports List – Provider: ID / Consumer: ExpenseID (C)
6)Add New Detail (DVWP)
* Get Data from Expense Reports List – Provider: ID / Consumer: ExpenseID (D)
No matter what I did, I was not able to set up the paired connection below (as mentioned by Bob on 11/16/2012 10:04 AM) – in Designer no columns were listed and in the browser I received the error ‘No Provider Schema’
Expense Report Form
* Send data to Expense Details List – Provider: ExpenseID / Consumer: ID (F)
Expense Details List
* Get filter Values from Expense Report Form – Provider: ExpenseID / Consumer: ID (F)
Can you please provide any suggestions to solve the detail issue?
Thanks
I think I’ve nearly got this working… I’m trying to implement this for a Trade Authorisation form – one employee, multiple trades.
All steps have been completed, but once I submit a trade detail the following trade details aren’t saved with any Trade ID.
Also, if I create a new Trade Authorisation form the ‘Add New Detail’ link still uses the same Trade ID – it never changes.
What do I still need to do to get this working?
Thanks
Laura, I looked forward with great anticipation to get repeating tables to work between SharePoint 2013 and InfoPath 2010. I am stuck on #13 as I installed SPD 2010 to get to the Design Mode. Unfortunately, I cannot get SPD 2010 to show in Design Mode, so I can set the ExpenseID, as a query string. Would you have the ‘code’ and specify where to place it, so I can move forward. Thanks.
If you want to query InfoPath repeating table data with Microsoft SQL Report Builder 3.0, SSRS, SSIS, Excel, Access, or Performance Point be sure to check out
http://infopathdb.codeplex.com
Over the years I’ve tried many InfoPath reporting options with PowerShell scripts, Scheduled Tasks, code behind, and multiple submit data connections. Having a real-time web service to merge XML data for easy query works well and I published that in case it can help others too.
Hi,
I need your help
I have used repeating tables in infopath and bind that form on sharepoint list.
I can add multiple records at a time:
For example when page loads:
Objective: ABC
Description: XYZ
Weightage: 20%
Comments: QWERT
then performed action:- Insert new item in a table
Objective: ABC 1
Description: XYZ1
Weightage: 60%
Comments: QWERT1
same again
Objective: ABC 2
Description: XYZ2
Weightage: 20%
Comments: QWERT2
final weightage = 100%
(This condition is given if the total weightage is equal to 100 then only your items will be submitted)
Now these items are stored in my list.
Now on edit, if i edit on any of the above item: I need all three items that i have created together to be shown on that page because I need that validation of weightage sum must be 100%.
So please suggest me what to do.. Can i use repeating tables on edit and can display data to user..
How to do that???
Please reply soon
Thanks in Advance
Mustafa
Hi Laura, thanks for the informative blog. I have just started diving into Sharepoint/infopath and I was wondering if this can be done without sharepoint designer. I was able to follow through the blog untill Sharepoint Designer sterp. SharePoint designer is restricted and don’t know if there are any alternatives available. Thanks for your help in advance.
If you want to query repeating table data with Microsoft SQL Report Builder 3.0, SSRS, SSIS, Excel, Access, or Performance Point be sure to check out http://infopathdb.codeplex.com
Over the years I’ve tried many InfoPath reporting options with PowerShell scripts, Scheduled Tasks, code behind, and multiple submit data connections. Having a real-time web service to merge XML data for easy query works well and I published that in case it can help others too.
Like Michael, I am stuck on step #13. Unable to view the web parts in design view of SPD 2010. I can’t add the query string parameter the way it is mentioned in Step #13.
Is there a work around for this? Would be nice to incorporate it in the solution I’m trying.
Thanks,
Swetha.
If you’re stuck, please watch my new, hour long video explaining how to do this solution. http://www.youtube.com/watch?v=MPWCzGHKuQs
Hi Laura,
Thanks a lot for wonderful video and blog post. Could you please direct me how parameters can be added while using SharePoint 2013/ Office 365 and SharePoint Designer 2013 as there is no design view in SPD 2013. Unfortunately like many other folks I could not move ahead from step 13 :(.
Any help on working with SharePoint Designer 2013 would be highly appreciated.
Thanks a lot!
Hi Laura – Thank you for the post. Would it be possible to export an Infopath repeating table to Excel (OOTB with no code solution)?