InfoPath Dev

Gurus, I am new to Infopath and hope you all can provide me with much needed guidance. I am working on a form to capture resource skills and individual job experiences for my company. I created a form and published it on our share point site. As per my plan once the employee submit his form to SharePoint, I will then export all these forms to excel as an input to my dashboard. Please suggest me if I am choosing the right way or if there is any other better option available. Secondly, while using repeating tables in infopath combines all the data in one column when I export it to excel via sharepoint. Is there any way which will allow me to get these data in separate columns in SharePoint or in excel? Also, the date format I need is just years and months but no specific date. Is there any way to customize the date format as the individual experience may come from several years back and people may find it difficult go back every month to reach to that specific year. Any help on these questions will be highly appreciated! Regards, Pendse

Hi Pendsep and welcome,

You have stumbled upon one of the biggest challenges of InfoPath and SharePoint. SharePoint handles flat table data. InfoPath is more powerful and supports hierarchical XML data. Unfortunately, you can't promote repeating data to separate SharePoint columns. You can hardcode the columns and have the form set them, but it's not a scalable solution.

This problem has been around since 2003 and it was the initial reason why we developed our database accelerator tool (DBXL) for InfoPath. DBXL let's you easily map the data to SQL. Nowadays, we have client versions which install locally and allow for reporting. You can report on the XML data stored in SQL even if you don't have a database set up. That's because SQL understands XML much better than SharePoint does and we have tools in DBXL (Report Builder) that let you create queries and export the results to Excel. Here's a sample blog that shows how to use Report Builder: http://www.infopathdev.com/blogs/mel_balsamo/archive/2011/06/30/how-to-use-report-builder-to-build-queries-and-retrieve-data-from-dbxl.aspx. See step 16 where it says export to Excel. You can just stop there as the remainder shows how to pull this data into an InfoPath form to enable form-to-form communication.

Let me know if you want a quick demo. Bottom line: you can't do what you want in SharePoint without spending a lot of time and money. Our tools are very low cost and will help you get this working in less than an hour.