InfoPath Dev

I am setting up a timesheet store using InfoPath and SharePoint.

The timesheet form consists of a repeating table for information such as task/hours spent per day/location/customer.

We're trying to roll it out across MOSS to remove the need for all users to install InfoPath and so they can access it everywhere. I'm having a problem in that the management team want to be able to run reports based off the information gathered from the timesheets such as "Details per customer" "details per individual" "details per customer in a date range"

I have a problem in that the repeating table gets merged into a single field with linebreaks in between the individual task data which is awful to work with for reporting.

We thought about trying to get around the single field problem by creating two data submits, but can't quite work out a way which will do it properly, a single "overview" infopath library which the form gets submitted to, and then when a new line on the repeating table is created (not sure how to submit from a "new row" button!) then the previously entered row gets submitted to an "Indepth" sharepoint library which creates a new row per activity rather than per Timesheet like the overview one.

I've been reading through other posts that all the experts say should submit to an SQL database to do anything complex, and that makes sense, but I thought SharePoint stores the information in an SQL database already? Also MOSS comes with a report template, is there no way to link an InfoPath library with the report centre so it reads from the XML files directly instead of the sharepoint list (thus removing the merge problem for the repeating table)?

I also noticed there is a XML query web part, but to use this would require an XSLT form and a single XML document of all the timesheets which I'm not sure how I would get and ideally I would like these reports to be generated automatically per week/month and using SPDesigner I was unable to make a workflow which would do this per week automatically. Could automated reports be done per week using a VS2005 workflow (I've not got it installed to try but another of our SharePoint team does)

Ideally I'd like

(1)the form to be filled out in the browser, and stored in a sharepoint list but

(2) when submitting the form, it submits to

(3)sharepoint list for approval

(4)once approved it gets submitted to our financial system for billing/financial report generation (PROGRESS db)

(5)reports run based upon the xml files for management

Has anyone tried to do something similar and got a good flow working?

Thank you in advance for any advice you can give!

I feel for you on pursuing a path and then getting stuck like this.  I have not really seen anything easy for reporting with SharePoint.  I have heard of people being able to create SQL reports on list columns but there are definitely limitations.  We have done this many times using the Database Accelerator since then all of the data you want is in SQL and it can handle the repeating tables.  If you would like to see a demo of our timecard solution let me know.

I've also been working on a timesheet form.  I can tell you what I've done.  Like you,  I have a repeating table that contains tasks and hours.  Basically I wanted each row in the repeating table to translate to one item in a customized SharePoint list.  I was able to accomplish this using code.  So I publish my form to a Form Library.  This library is just a holding spot for the form.  As people fill out the forms, I don't save them in the form library.  Instead, I add the information from the InfoPath form to a customized SharePoint list.  If there are 6 rows of data on the InfoPath form, the result is 6 rows of data in the SharePoint list.

If you think this might be helpful, let me know what additional information you need and I'll try to provide it.  (I'm very new to both InfoPath and SharePoint.)

Hello,

I have read this thread with interest because I have the same problem in many projects. Like the others I often have an IP form with a repeating table and I need the content for reporting. But indeed the problem with repeating tables is that they are merged together in a sharepoint list.
I have tried the "Enesys RS Data Extension" (http://www.enesyssoftware.com) which is a data extension for "MS SQL 2005 Reporting Services" but IMHO it is very expensive... (BTW: they offer a lightweight 'Community Edition' for free which is good for less requirements).

@KristyKay: Please, can you share a bit more (maybe code snippets) about how "...to add the information from the infopath form to a customized sharepoint list..." especially for the '... repeating table part...'

Thank you very much

Karsten

I am intrigued to see how KristyKay has done this, having reread the reply I hope isn't doing it via asp.net form with some cutting and pasting custom code to fill in the asp page based on the xml :-\

My colleague who I've roped into giving me a hand with this is currently trying to query each element of the repeating row and write a workflow which uses xpath to then create a seperate list for those fields in the repeating rows which are then tied to the header information WeekOf, Department, Name etc. I still don't think thats the solution though.

 
its just a shame sharepoint lists don't support a collapsable sublist per item as that would be the ideal solution here...

I'm using InfoPath 2003 & SharePoint 2007.  The code is written in JScript.  And this assumes you already have the list created in SharePoint.  Here's how I added items to a list in SharePoint:

First, you need to obtain the GUID for the SharePoint list and add that as a field on the InfoPath form.  (I've made it a hidden field):  

1. Go to your list in SharePoint and go to the List Settings
2. The GUID is in the URL.  The URL should say http://sharepointserver/listedit.aspx?List={GUID}

NOTE: The GUID will start with %7B and end with %7D.  You can ignore these.  And you'll need to replace the %2D with a "-".  For example, if the URL says ?List=%7BA5336FAC%2DE1A4%2D46D4%2DBE03%2D983F508D066C%7D, you'd put A5336FAC-E1A4-46D4-BE03-983F508D066C on your form.

Second, here's some information about the Data Connections that I have on my form:

• AddCAML - receive data from XML document.  This is what the CAML looks like:

<?xml version="1.0" encoding="UTF-8"?>
<Batch OnError="Continue">
    <Method ID="1" Cmd="New">
        <Field Name="name of SP column"></Field>
        <Field Name="name of SP column"></Field>
    </Method>
</Batch>

You'll have a <Field> entry for every column in your SharePoint list.  Make sure you get the name correct!

 

• Submit - submit data to SharePoint's UpdateListItems web service.  I've "borrowed" text from Matt Flaus that tells you how to do this:

1. Choose Data Connections from the Tools menu, and then click Add.
2. Select Submit Data, click Next, select To a Web Service, and then click Next.
3. Type the location of the lists.asmx file on your SharePoint site. This is usually located at http://sharepointserver/_vti_bin/lists.asmx.
4. Click Next.
5. Select UpdateListItems from the list, and then click Next.
6. For the s0:listName parameter assign the list name node as the value. (Kristy's Note: List name node is the field that contains the GUID for the list.)
7. Highlight the s0:updates parameter, click the data source button, select the AddCAML from the data source drop-down list, highlight the /Batch node, and then click OK.
8. Choose XML Subtree, Including Selected Element from the Include drop-down list, and then click Next.
9. Click Finish.

Now on to the code.  On my form, I have a repeating table called Activity with two fields - activity-name and activity-total.  And in Sharepoint, my list has two columns - Activity and Hours.  activity-name maps to Activity and activity-total maps to Hours.  Here's the code I'm using:

var root = XDocument.DOM; 

var activities = root.selectNodes("/my:TimeCard/my:week/my:Activity"); 

for ( var activity = activities.nextNode(); activity != null; activity = activities.nextNode() ) {
   var activityName = activity.selectSingleNode("my:activity-name").text;
   var hours = activity.selectSingleNode("my:activity-total").text;
  
   var batch = XDocument.DataObjects["AddCAML"].DOM;
 
   batch.selectSingleNode("/Batch/Method/Field[@Name='Activity']").text = activityName;
   batch.selectSingleNode("/Batch/Method/Field[@Name='Hours']").text = hours;
  
   //Submit the details to update the list
   var submit = XDocument.DataAdapters["Submit"];
   
   try {
      submit.Submit();   
   }catch (err) {
      XDocument.UI.Alert("There was an error submitting the information for " + actvityName);  
   }
}

 

Note: this is only to add items to a list in SharePoint.  If you want to update, it's a similar process except the CAML is slightly different.  Notice the Cmd is Update instead of New.  And you have to include the ID column (to identify which item) and any columns you want to update.

<?xml version="1.0" encoding="UTF-8"?>
<Batch OnError="Continue">
    <Method ID="1" Cmd="Update">
        <Field Name="ID"></Field>
        <Field Name="name of SP column to be updated"></Field>
    </Method>
</Batch>

So on my InfoPath form, users can add or update items in a SharePoint list.  The top half of the form is a repeating table that is populated with information that is already in the SharePoint list.  I allow the user to update only one of the columns (i.e the fields are read-only on the InfoPath form except for one).  Under this table, I have an update button.  Then the bottom half of the form allows users to add new items to the SharePoint list.  And beneath this table, I have a Submit button.  So I have four data connections - one for the AddCAML, one for UpdateCAML, one for the Update button and one for the Submit button.  The Update button and Submit button look exactly the same (i.e. they're both submit data to SharePoint's UpdateListItems web service).  And I think you could somehow combine the two CAMLs into one data connections but I didn't have the energy to figure out how to do that.

If anything is unclear or you have questions, let me know.  I hope I've helped someone.

That's brilliant thanks for posting, it must have taken sometime to write!

One question for you though, if I create a submit button that submits via code, it will only let me do it in C# or VB, not JScript as it is a form that will be filled out via a webbrowser. Where abouts do I put the JScript code?

Thank you again for posting that, its really helpful!

Hi KristyKay,

I´ve gone through your last answer and I think your solution is very helpful for my work.

Thank you very much for the given code.

@zeroecho:

HTH,

kbrocksi,

Thanks but I figured that bit out earlier when I read the article it was taken from. The list name node is never mentioned to be the same thing as the GUID in kirstkay's article.

My form isn't browser enabled so I was able to use JScript.  I think you'll have to translate it to  C# or VB.  I know nothing about C# or VB so I'll be no help to you.  :)

Ahh rightyo, thats no problem, I suspected as much. It's ok I think I can translate it to C#. Thank you again for posting that solution, If I translate it to C# I'll post it here for others to use also. 

Here is another tutorial on submitting list items to SharePoint: http://www.infopathdev.com/blogs/matt/archive/2006/02/02/Add-SharePoint-List-Items-with-InfoPath.aspx

Note: the above tutorial does not require code and accomplishes the same thing. We are coming out with a tool in the next few weeks that will let you map InfoPath forms to SharePoint lists using a simple configuration. Stay tuned to this channel...

How do you map your existing complex xml data source to that CAML for submission without using code? I don't think you can do it.

i.e. in my scenario I don't particularly care whats already on the list, I just want the user to be able to submit to it. The list has already been created, I've created my CAML which contains all the columns found in that list but now I need to map my fields to that CAML?

public void FormEvents_Submit(object sender, SubmitEventArgs e)
        {
            try
            {
                XPathNavigator domNav = MainDataSource.CreateNavigator();
                XPathNodeIterator activity = domNav.Select("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity", NamespaceManager);

                while (activity.MoveNext())
                {
                    string department = domNav.SelectSingleNode("/my:TimesheetDetails/my:Department", NamespaceManager).Value;
                    string employeename = domNav.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:EmployeeName", NamespaceManager).Value;
                    string weekOf = domNav.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:WeekOf", NamespaceManager).Value;

                    string location = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:Location", NamespaceManager).Value;
                    string taskActivity = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:TaskActivity", NamespaceManager).Value;
                    string activityDescription = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:ActivityDescription", NamespaceManager).Value;
                    string jiModNumber = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:JIModNumber", NamespaceManager).Value;
                    string customerName = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:CustomerName", NamespaceManager).Value;
                    string newCustomer = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:NewCustomer", NamespaceManager).Value;
                    string paNumber = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:PANumber", NamespaceManager).Value;
                    string monhours = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:HoursPerDay/my:Mon", NamespaceManager).Value;
                    string tuehours = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:HoursPerDay/my:Tue", NamespaceManager).Value;
                    string wedhours = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:HoursPerDay/my:Wed", NamespaceManager).Value;
                    string thuhours = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:HoursPerDay/my:Thu", NamespaceManager).Value;
                    string frihours = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:HoursPerDay/my:Fri", NamespaceManager).Value;
                    string sathours = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:HoursPerDay/my:Sat", NamespaceManager).Value;
                    string sunhours = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:HoursPerDay/my:Sun", NamespaceManager).Value;
                    string billable = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:Billable", NamespaceManager).Value;
                    string billableHours = activity.Current.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:TimeReporting/my:PerActivity/my:BillableHours", NamespaceManager).Value;

                    XPathNavigator batch = DataSources["CAML"].CreateNavigator();

                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Department']", NamespaceManager).SetValue(department.ToString());
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='EmployeeName']", NamespaceManager).SetValue(employeename);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='WeekOf']", NamespaceManager).SetValue(weekOf);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Location']", NamespaceManager).SetValue(location);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='TaskActivity']", NamespaceManager).SetValue(taskActivity);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='ActivityDescription']", NamespaceManager).SetValue(activityDescription);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='JIModNumber']", NamespaceManager).SetValue(jiModNumber);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='CustomerName']", NamespaceManager).SetValue(customerName);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='NewCustomer']", NamespaceManager).SetValue(newCustomer);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='PANumber']", NamespaceManager).SetValue(paNumber);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Mon']", NamespaceManager).SetValue(monhours);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Tue']", NamespaceManager).SetValue(tuehours);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Wed']", NamespaceManager).SetValue(wedhours);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Thu']", NamespaceManager).SetValue(thuhours);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Fri']", NamespaceManager).SetValue(frihours);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Sat']", NamespaceManager).SetValue(sathours);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Sun']", NamespaceManager).SetValue(sunhours);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='Billable']", NamespaceManager).SetValue(billable);
                    batch.SelectSingleNode("/Batch/Method/Field[@Name='BillableHours']", NamespaceManager).SetValue(billableHours);
                    
                    DataConnections["Web Service Submit"].Execute();
                }
            }
            catch
            {
            }
           
            e.CancelableArgs.Cancel = false;
        }

The form now submits without error, but there is no data on the other side... very strange. Will investigate it tomorrow, I'm determined to get this working so I can start the next task... pulling useful information from the forms into reports and getting approvals etc!

 The form is posting with a success message but the data is still not there. I've tried following the comments under the article here http://blogs.msdn.com/infopath/archive/2007/03/26/submitting-to-a-sharepoint-list.aspx from people with the same problem. Did you have this problem at all Kirsty?