InfoPath Dev

Hi,

 I'm brand new to InfoPath (started using it yesterday). I've been tasked with a project and I had several questions on how to do this correctly in InfoPath or if InfoPath even makes sense as an application to help me accomplish this. My dept. will be consolidating metrics data from various LOBs across the organization, each LOB having a unique set of metrics. What I'm trying to accomplish is this: I want the contact person in each LOB to, on a monthly basis, go to their group's form (which I'm planning on parking in Sharepoint) and to fill out results for each metric. They would select the report date (Month/Year) and the LOB. They would then fill out the results for the month for each metric. I also want a column with the metric's target and threshold (bandwidth); however, I'd like this to be a default value since targets should be consistent from month-to-month unless it is decided that it needs to be adjusted. So the layout of the form will look something like:

 Report Date: December, 2010

LOB: Manufacturing

 Metric                                Month Result                   Target              Threshold

Metric 1 Desc                        500 (filled out)                    450                     350

Metric 2 Desc                         90%                                 95%                   85%

and so on....when this is submitted I'd like to export the form data to excel so that the data I get back basically looks like this, but with the report date and LOB repeated in each row. What I can't figure out is:

-Playing around with repeating tables, but don't know how (or if it's possible) to set a default number of rows that are pre-populated with the Metric description, target, etc

-Don't know if it's possible to have different formatting in these rows (some metrics may be whole numbers, some %'s, etc)

-I set this up w/o using repeating tables where basically the only controls I had were the results for each metric along with the report date and group. This clearly was wrong because when exporting to excel it gave me one row of data across the top. It excluded Target, Threshold, etc because I didn't set up controls for them (like I said, just found out about InfoPath).

Any suggestions on what I need to do here?

 Thanks,

NM

Hi NM,

Welcome to the InfoPath Dev forums.

InfoPath can easily be used to accomplish what you need to accomplish.

There are several ways to actually do this with InfoPath and depending upon your specific needs one way or another may be the best.

Here are some comments:

1. If you want this to be in one single form for each LOB and you are using a repeating section in InfoPath you will find it hard to report on that data directly out of SharePoint, exported to Excel.  SharePoint doesn't play nice with the repeating data form InfoPath

Possible Solution A: get the form data into SQL using a web service like our DBXL web service.  We have a server and a client version.  With the client version you could just install on your local machine and pull down documents from SharePoint to report on out of SQL (you could even use Excel to pull the SQL data).  I have a demo video here that shows you how this is done, it's really not hard.

Possible Solution B: upon submit have the repeating data in your form submitted to a seperate SharePoint list.  Each row in the form would be it's own flat record in the SharePoint list.  You can then export that list from SharePoint to Excel and easily do pivot tables etc.  This does require code to do though.  You can write your own code to  map the data from InfoPath to the SharePoint list or you could look at our qRules product which already has the code to do this.

Possible Solution C: You could consider using a single form for each month, non repeating.  This way your data for each form is in the SharePoint library in a flat way.  Just use an ID system for each LOB so that when you run reports you can link the seperate forms together for each LOB.  This is probably the simpliest and easiest approach for someone new to InfoPath but may not be the best user experience with a new form every month.

Regarding the different formatting for the rows - you could handle that by having a drop down that allows users to chose what type of metric they are reporting, give them some fixed choices and then you could use conditional formatting in InfoPath to show/hide the right field type for the drop down seleciton that was made.  Note that you will end up with extra fields in SharePoint and thus in Excel reporting for this because the InfoPath data type has to be set for each field (you can't change the data type via the drop down, just which version of the metric to show).

I hope that helps get you started but I do think InfoPath and SharePoint are great tools for this solution.

Ted

Thanks, Ted. This is very helpful and I'll be looking into a couple of your suggestions. As a follow up to option 3, I'm a little confused on how to properly set up the non-repeating form in a way that I can export data into Excel in the structure that I want. Can I set up a form where I have already populated each metric description that I want filled out? If the only thing I want the user to populate is the result of the metric itself, how do I build this? Should there be a text box with a unique field name for each metric's result? This is what I had set up when I was initially playing around with InfoPath, but found that the Excel export returned a single row of data rather than a row for each metric. Hopefully this makes sense and I'm sure this is a very basic question, but I'm just trying to understand the application a little better. Thanks again for your help!

Hi NM, you should use a regular table in InfoPath, non repeating.  Name the field in your form for what the metric is, that way the column in SharePoint will export that name to Excel for reporting.  Once exported to Excel you can do piviot reporting on the data.  The data should be presented horizontally in Excel from SharePoint. (left to right) not top to bottom.  Make sense?