InfoPath Dev

I'm trying to make a repeating table to track the number of Help desk submits on our SharePoint site per department on the sql database.

The fields are:

Created: Date help desk ticket was created

Version: This will always be 1

Priority: This tells whether it's High, Normal or Low priority

Below is what I want the table to look like

              Jun-09    Jul-09    Aug-09   Sept-09   Oct-09    Nov-09    Dec-09    Totals

High         #            #

Normal     #            #

Low          #           #

 --------------------------------------------------------------------------------------------------------------------

Totals

If someone could come up with a sql statement or code that I could use I would appreciate it.

Hi g21cincy and welcome!

Thanks for posting. Not sure exactly what your config is so I'll ask some questions first:

1. Are you using SQL behind SharePoint or just SharePoint (with it's SQL)
2. Does your form have an XML data source or was it created from SQL?
3. Are you trying to get a report in your SharePoint library that lists the above data?
4. What's the relationship between one form and a row in the table? Is it many-to-one? Seems that way since it looks like you want to aggregate.

Assuming you are using SharePoint and you have a separate SQL database behind SharePoint, you can use SharePoint Designer (SPD) to create a data view that shows the table like you want. To create the SQL tables, you will need to create at least two tables. One primary table that lists each form submitted, and another that has the children (if forms can have repeating data). Then, you'll want to create a view that aggregates and set up SPD to connect to that SQL view. That'll solve the server side of your solution.

To get the data into SQL, you'll need to either create your form from the SQL database or use a Web Service. If you already have a form and lots of XML files, I recommend the latter. Qdabra provides free Web Services trials for you to connect your existing forms to SQL with very little effort (and it's cheap if you decide to go forward with that solution). Of course, you can also use a heavy hand approach of writing a lot of code in your form that submits to SQL, but that's going to be expensive from a development angle. I wouldn't recommend putting code in your form.

Hope this provides some info. Good luck!