Have SharePoint Tickets, but Can’t Get on the Train?

InfoPath makes data collection easy – build a form, publish to SharePoint, and BINGO – your users fill them out. When users save or submit, the form promotes values to SharePoint and it’s easy to create custom views on that data or export it to Excel for further analysis.

But, there’s a problem. You can only promote fields that are not repeating, and most forms have repeating data – in other words, the data world isn’t flat either. Promoted properties for repeating data limit you to aggregating or picking one item. How are you going to get multiple items data fields out of your form? Reporting today is all about “pivoting” data tables. How do you export more than one table from InfoPath?

Out-of-box Solutions

SharePoint comes with two out-of-box solutions, but both are brittle:

• Map your data to one or more SharePoint lists using CAML
  • Pros:
    • No code
  • Cons:
    • Tricky configuration,
    • Doesn’t work for parent-child,
    • Maintenance cost when things change
  • How-to links:
    • http://www.youtube.com/watch?v=4oQ7QdpYdx8
    • http://sharepointknowledgebase.blogspot.com/2011/10/updating-sharepoint-list-by-using.html
• Write to a database and use ShP dataview web part
  • Pros:
    • No code
  • Cons:
    • Can’t convert existing InfoPath forms
    • Doesn’t support browser forms,
    • Tightly coupled to database means limited data structure,
    • Can’t migrate document data when database changes
  • How-to links:
    • http://office.microsoft.com/en-us/sharepoint-designer-help/web-parts-for-views-and-forms-in-sharepoint-designer-2010-HA101805424.aspx#_Toc245608913
    • https://www.nothingbutsharepoint.com/sites/eusp/Pages/how-the-data-view-web-part-can-enhance-the-quality-of-your-life.aspx

Custom Code Solutions

Of course, you could just delegate to your developer and have them:

• Write custom code to submit your data to multiple lists
  • Pros:
    • Enables complex mappings with many lists
  • Cons:
    • Creation cost – requires developer resource
    • Maintenance cost – brittle solution will break when things change
  • How-to links:
    • http://msdn.microsoft.com/en-us/library/cc162745.aspx
    • http://stackoverflow.com/questions/7613058/submitting-repeating-infopath-table-to-sharepoint-list-in-browser-enabled-form
    • http://jaliyaudagedara.blogspot.com/2011/03/submitting-data-to-sharepoint-2010-list.html

More Flexible Approach

Yes, there’s a better way – actually a couple! First off, let’s look at a more flexible approach.

• Use Qdabra’s qRules plugin to enable form mapping to SharePoint lists
  • Pros:
    • No code needed,
    • Supports complex mapping – multiple lists, parent child,
    • Easy to update when things change,
    • Batch tool means you can map existing documents
  • Cons:
    • New tool to learn,
    • Small purchase required (but there’s a free trial),
    • Won’t work in Office 365 environments,
  • How-to link:
    • http://www.infopathdev.com/blogs/hilary/archive/2012/05/30/qrules-submittosharepointlist-walkthrough-for-v4-2.aspx

Most Scalable

But, what happens when you have thousands of forms with tens of thousands of repeating data items? SharePoint gets indigestion. So, here’s a second solution to overcome those limits and still use SharePoint as your dashboard:

• Use Qdabra’s DBXL Web Service to map form data to SQL database / Azure
  • Pros:
    • No code,
    • Flexible when things change,
    • Supports cloud scenarios (Office 365),
  • Cons:
    • Requires server install of product or connection to generic Azure service,
  • How-to link:
    • http://www.infopathdev.com/files/folders/sql_integration/entry74107.aspx

Learn More

Qdabra offers short video tutorials and in depth how-to hands-on-labs. Check out the following links:

• YouTube Video highlighting the above techniques:
  • More Flexible – qRules Plugin
    • http://www.youtube.com/watch?v=WftCp_EbcCU
  • Most Scalable – DBXL Web Service:
    • http://www.youtube.com/watch?v=nPhyS2CtScc