SharePoint Integration

Re: InfoPath 07 & SharePoint Reporting

zeroecho — Mon, 17 Nov 2008 16:10:26 GMT

Glad it was useful to you, I've reused the basic principle a couple of times to enable InfoPath forms with repeating tables to be able to be reported on by SharePoint users via Views and Export to Spreadsheet (we aren't a SQL heavy company so no SQL reporting)

Just out of interest, has anyone experienced slow rendering times for InfoPath browser based forms?

For me it takes about 4 seconds to do an "insert new item" command in IE7, that's on a Core 2 Duo T7700 @ 2.40GHz with 2GB RAM, on an older Single Core ~3GHz PC with 1GB RAM that action takes about 8 seconds.

When doing the same form, same command using FireFox on the Core 2 Duo it takes approximately 1 second to execute, supposedly (not tested myself but a colleague in USA did) he gets a 2 second delay to execute the command from Chicago using IE8 beta.

Anyone got any tips to improve the responsiveness of browser based forms for older PCs? (I guess this should probably have gone in a new thread!)

Re: InfoPath 07 & SharePoint Reporting

Kousoku — Mon, 17 Nov 2008 14:15:04 GMT

Thanks for that mate. The code was very useful.

Re: InfoPath 07 & SharePoint Reporting

zeroecho — Fri, 14 Nov 2008 12:12:12 GMT

         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);

                #region Node path strings
                //If you're node structure changes you need only change this code for the most repeatable strings
                string dateString = "";
                string dayString = "";
                string onString = "";

                string timesheetDetails = "/my:TimesheetDetails";
                string employeeInfo = timesheetDetails + "/my:EmployeeInformation";
                string timeReporting = employeeInfo + "/my:TimeReporting";
                string perActivity = timeReporting + "/my:PerActivity";
                #endregion

                #region Loop Variables
                //Information to be retrieved
                string date;
                string hours;
                string department;
                string employeename;
                string weekOf;
                string location;
                string taskActivity;
                string activityDescription;
                string jiModNumber;
                string customerName;
                string newCustomer = "";
                string paNumber;
                string billable;
                string billablehours;
                string billingRatehours;
                string rates;
                string overnight;
                string overnightclaim;
                int overnightamount;
                string overtime;

                XPathNodeIterator locNodes;
                XPathNodeIterator taskActivityNodes;
                XPathNodeIterator activityDescNodes;
                XPathNodeIterator jiModNumberNodes;
                XPathNodeIterator customerNameNodes;
                XPathNodeIterator paNumberNodes;
                XPathNodeIterator billableNodes;
                XPathNodeIterator overtimeNodes;
                XPathNodeIterator billablehoursNodes;
                XPathNodeIterator billingRateNodes;
                XPathNodeIterator hourNodes;
                XPathNodeIterator dateNodes;
                XPathNodeIterator ratesNodes;

                overnightclaim = domNav.SelectSingleNode(employeeInfo + "/my:ONClaim", NamespaceManager).Value;
                overnightamount = int.Parse(overnightclaim);
                #endregion

                while (activity.MoveNext())
                {
                    for (int i = 0; i < 7; i++)
                    {
                        #region Select Day Strings
                        //Change the day strings once
                        switch (i)
                        {
                            case 0:
                                //Select Monday
                                dateString = "my:MDate";
                                dayString = "my:Mon";
                                onString = "my:monON";
                                break;
                            case 1:
                                //Select Tuesday
                                dateString = "my:TDate";
                                dayString = "my:Tue";
                                onString = "my:tuesON";
                                break;
                            case 2:
                                //Select Wednesday
                                dateString = "my:WDate";
                                dayString = "my:Wed";
                                onString = "my:wedON";
                                break;
                            case 3:
                                //Select Thursday
                                dateString = "my:ThDate";
                                dayString = "my:Thu";
                                onString = "my:thuON";
                                break;
                            case 4:
                                //Select Friday
                                dateString = "my:FDate";
                                dayString = "my:Fri";
                                onString = "my:friON";
                                break;
                            case 5:
                                //Select Saturday
                                dateString = "my:SaDate";
                                dayString = "my:Sat";
                                onString = "my:satON";
                                break;
                                //Select Sunday
                            case 6:
                                dateString = "my:SuDate";
                                dayString = "my:Sun";
                                onString = "my:sunON";
                                break;
                        }
                        #endregion

                        #region Select Information
                        #region Static Information

                        department = domNav.SelectSingleNode(timesheetDetails + "/my:Department", NamespaceManager).Value;
                        employeename = domNav.SelectSingleNode(employeeInfo + "/my:EmployeeName", NamespaceManager).Value;
                        weekOf = domNav.SelectSingleNode(employeeInfo + "/my:WeekOf", NamespaceManager).Value;
                        #endregion

                        locNodes = activity.Current.SelectDescendants(perActivity + "/@my:Location",this.NamespaceManager.DefaultNamespace,true);
                        location = locNodes.Current.SelectSingleNode("my:Location", NamespaceManager).Value;
                        locNodes.Current.MoveToNext();

                        taskActivityNodes = activity.Current.SelectDescendants(perActivity + "/@my:TaskActivity", this.NamespaceManager.DefaultNamespace, true);
                        taskActivity = taskActivityNodes.Current.SelectSingleNode("my:TaskActivity", NamespaceManager).Value;
                        taskActivityNodes.Current.MoveToNext();

                        activityDescNodes = activity.Current.SelectDescendants(perActivity + "/@my:ActivityDescription", this.NamespaceManager.DefaultNamespace, true);
                        activityDescription = activity.Current.SelectSingleNode("my:ActivityDescription", NamespaceManager).Value;
                        activityDescNodes.Current.MoveToNext();

                        jiModNumberNodes = activity.Current.SelectDescendants(perActivity + "/@my:JIModNumber", this.NamespaceManager.DefaultNamespace, true);
                        jiModNumber = activity.Current.SelectSingleNode("my:JIModNumber", NamespaceManager).Value;
                        jiModNumberNodes.Current.MoveToNext();

                        customerNameNodes = activity.Current.SelectDescendants(perActivity + "/@my:CustomerName", this.NamespaceManager.DefaultNamespace, true);
                        customerName = activity.Current.SelectSingleNode("my:CustomerName", NamespaceManager).Value;
                        customerNameNodes.Current.MoveToNext();

                        paNumberNodes = activity.Current.SelectDescendants(perActivity + "/@my:PANumber", this.NamespaceManager.DefaultNamespace, true);
                        paNumber = activity.Current.SelectSingleNode("my:PANumber", NamespaceManager).Value;
                        paNumberNodes.Current.MoveToNext();

                        billableNodes = activity.Current.SelectDescendants(perActivity + "/@my:Billable", this.NamespaceManager.DefaultNamespace, true);
                        billable = activity.Current.SelectSingleNode("my:Billable", NamespaceManager).Value;
                        billableNodes.Current.MoveToNext();

                        overtimeNodes = activity.Current.SelectDescendants(perActivity + "/@my:Overtime", this.NamespaceManager.DefaultNamespace, true);
                        overtime = activity.Current.SelectSingleNode("my:Overtime", NamespaceManager).Value;
                        overtimeNodes.Current.MoveToNext();

                        billablehoursNodes = activity.Current.SelectDescendants(perActivity + "/@my:BillableHours", this.NamespaceManager.DefaultNamespace, true);
                        billablehours = activity.Current.SelectSingleNode("my:BillableHours", NamespaceManager).Value;
                        billablehoursNodes.Current.MoveToNext();

                        billingRateNodes = activity.Current.SelectDescendants(perActivity + "/@my:BillingRate", this.NamespaceManager.DefaultNamespace, true);
                        billingRatehours = activity.Current.SelectSingleNode("my:BillingRate", NamespaceManager).Value;
                        billingRateNodes.Current.MoveToNext();

                        hourNodes = activity.Current.SelectDescendants(perActivity + "/@my:HoursPerDay/" + dayString, this.NamespaceManager.DefaultNamespace,false);
                        hours = activity.Current.SelectSingleNode("my:HoursPerDay/" + dayString, NamespaceManager).Value;
                        hourNodes.Current.MoveToNext();

                        dateNodes = activity.Current.SelectDescendants(employeeInfo + "/@" + timeReporting + "/" + dateString, this.NamespaceManager.DefaultNamespace,false);
                        date = activity.Current.SelectSingleNode(timeReporting + "/" + dateString, NamespaceManager).Value;
                        dateNodes.Current.MoveToNext();

                        ratesNodes = activity.Current.SelectDescendants(perActivity + "/@my:rates", this.NamespaceManager.DefaultNamespace, true);
                        rates = activity.Current.SelectSingleNode("my:rates", NamespaceManager).Value;
                        ratesNodes.Current.MoveToNext();

                        if ((overnightamount > 0) && (overnightclaim != ""))
                        {
                            overnight = domNav.SelectSingleNode("/my:TimesheetDetails/my:Overnights/" + onString, NamespaceManager).Value;
                            overnightamount -= 1;
                        }
                        else
                        {
                            overnight = "";
                        }

                        #endregion

                        #region AddCAML
                        XPathNavigator batch = DataSources["AddCAML"].CreateNavigator();

                        batch.SelectSingleNode("/Batch/Method/Field[@Name='Title']", NamespaceManager).SetValue(date);
                        batch.SelectSingleNode("/Batch/Method/Field[@Name='Department']", NamespaceManager).SetValue(department);
                        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='Billable']", NamespaceManager).SetValue(billable);
                        batch.SelectSingleNode("/Batch/Method/Field[@Name='Hours']", NamespaceManager).SetValue(hours);
                        batch.SelectSingleNode("/Batch/Method/Field[@Name='TotalBillable']", NamespaceManager).SetValue(billablehours);
                        batch.SelectSingleNode("/Batch/Method/Field[@Name='OvertimeRates']", NamespaceManager).SetValue(rates);
                        if ((overnight != null) &&( overnight != "") )
                        {
                            batch.SelectSingleNode("/Batch/Method/Field[@Name='OvernightStays']", NamespaceManager).SetValue(overnight);
                        }
                        else
                        {
                            batch.SelectSingleNode("/Batch/Method/Field[@Name='OvernightStays']", NamespaceManager).SetValue("0");
                        }

                        if ((billingRatehours != null) && (billingRatehours != "") )
                        {
                            batch.SelectSingleNode("/Batch/Method/Field[@Name='BillingRate']", NamespaceManager).SetValue(billingRatehours);
                        }
                        else
                        {
                            batch.SelectSingleNode("/Batch/Method/Field[@Name='BillingRate']", NamespaceManager).SetValue("0");
                        }
                        if ((overtime != null) && (overtime != ""))
                        {
                            batch.SelectSingleNode("/Batch/Method/Field[@Name='Overtime']", NamespaceManager).SetValue(overtime);
                        }
                        else
                        {
                            batch.SelectSingleNode("/Batch/Method/Field[@Name='Overtime']", NamespaceManager).SetValue("false");
                        }
                        if ((hours != "0.00") && (hours != "0") && (hours != ""))
                        {
                            DataConnections["Web Service Submit"].Execute();
                            domNav.SelectSingleNode("/my:TimesheetDetails/my:Lock", NamespaceManager).SetValue("1");
                            domNav.SelectSingleNode("/my:TimesheetDetails/my:SubmittedToOpenAccounts", NamespaceManager).SetValue("1");
                            DataConnections["Main submit"].Execute();
                        }
                        else if ((overnight != "0") && (overnight != "") && (overnight != "false"))
                        {
                            DataConnections["Web Service Submit"].Execute();
                            domNav.SelectSingleNode("/my:TimesheetDetails/my:Lock", NamespaceManager).SetValue("1");
                            domNav.SelectSingleNode("/my:TimesheetDetails/my:SubmittedToOpenAccounts", NamespaceManager).SetValue("1");
                            DataConnections["Main submit"].Execute();
                        }
                        #endregion
                    }
                }
            }
            catch (Exception ex)
            {
                string error = ex.Message;
            }

            try
            {
                XPathNavigator domNav = MainDataSource.CreateNavigator();

                string employeeName = domNav.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:EmployeeName", NamespaceManager).Value;
                System.Net.Mail.MailMessage MailMsg = new System.Net.Mail.MailMessage();
                MailMsg.Subject = "Timesheet approved";

                string managerEmail = domNav.SelectSingleNode("/my:TimesheetDetails/my:ManagerEmail", NamespaceManager).Value;
                string employeeEmail = domNav.SelectSingleNode("/my:TimesheetDetails/my:EmployeeInformation/my:UserEmail", NamespaceManager).Value;

                MailAddress sentfrom = new MailAddress(managerEmail);
                MailMsg.To.Add(employeeEmail);
                MailMsg.From = sentfrom;
                MailMsg.IsBodyHtml = true;
                string URL = "http://server/formlibrary";
                string HREF = "<a href=\"";
                string FormName = domNav.SelectSingleNode("/my:TimesheetDetails/my:FormName", NamespaceManager).Value + ".xml";
                string ApprovalURL = HREF + URL + FormName + "?&Source=http://server/formlibrary&OpenIn=Browser\">Click here</a>";
                MailMsg.Body = "The following timesheet has been approved by your manager: " + ApprovalURL;
                SmtpClient sendmail = new SmtpClient();
                sendmail.Host = "mailserver";
                sendmail.Send(MailMsg);
            }
            catch (Exception x)
            {
                logMessage(x.Message, EventLogEntryType.Error);
            }
            e.CancelableArgs.Cancel = false;
        }

Was my code in the end, with an associated CAML file that looks like this:-

<?xml version="1.0" encoding="UTF-8"?>
<Batch OnError="Continue">
    <Method ID="1" Cmd="New">
          <Field Name='Title'></Field>
          <Field Name='Department'></Field>
          <Field Name="EmployeeName"></Field>
          <Field Name="WeekOf"></Field>
          <Field Name="Location"></Field>
          <Field Name="TaskActivity"></Field>
          <Field Name="ActivityDescription"></Field>
          <Field Name="JIModNumber"></Field>
          <Field Name="CustomerName"></Field>
          <Field Name="NewCustomer"></Field>
          <Field Name="PANumber"></Field>
          <Field Name="Hours"></Field>
          <Field Name="Billable"></Field>
          <Field Name="TotalBillable"></Field>
          <Field Name="OvertimeRates"></Field>
          <Field Name="OvernightStays"></Field>
          <Field Name="BillingRate"></Field>
          <Field Name="Overtime"></Field>
     </Method>
</Batch>

You can rename the Title column to something else within sharepoint but the CAML needs to be called "Title".

All my sharepoint columns are just Single Line text fields or Booleans, but it may work with other types, I have no requirement for that so haven't tested it.

Hope that helps, sorry my code isn't 100% commented as it was a case of hack together ASAP to get the system up and running. My only qualm is that if one line of XML fails when submitting the "submitted" box comes up so the manager thinks its gone through to the list whereas only half of it may have, but that's only been an issue when I've made schema changes and existing forms have had a single piece of missing xml data.

Re: InfoPath 07 & SharePoint Reporting

Kousoku — Fri, 14 Nov 2008 09:47:15 GMT

Hi,

Just wondering if you ever got to the bottom of this with C# code? I am now facing a similar problem and was wondering if you could help please?

Thanks.

Re: InfoPath 07 & SharePoint Reporting

zeroecho — Thu, 06 Dec 2007 17:36:27 GMT

The problem was two fold a)I have a .ToString() where I didn't need one, and b)Don't enable submit as a string !!

I've got it to post to a list, but if there are three lines in a repeating row, its posting the top line three times to that list instead of unique lines, something going wrong with my XPathNodeIterator I think!

Re: InfoPath 07 & SharePoint Reporting

zeroecho — Thu, 06 Dec 2007 16:21:49 GMT

I was a little confused about this, I know theres something wrong with either my CAML OR the sharepoint list. Created By and Modified By are on my list, but I can't remove them (or can I?)

I am confused as to what needs ' ' around it and what needs " " around it within the CAML - some examples I've seen only use ' ' for everything, but your example only has ' ' around 'Title'. Is there a right or wrong way for this?

The fact that my form is collecting double's and dates for certain fields and I'm just storing them as strings within the List doesn't matter does it?

Here's my CAML

<?xml version="1.0" encoding="UTF-8"?>
<Batch OnError="Continue">
     <Method ID="1" Cmd="New">
          <Field Name='Title'></Field>
          <Field Name='EmployeeName'></Field>
          <Field Name='WeekOf'></Field>
          <Field Name='Location'></Field>
          <Field Name='TaskActivity'></Field>
          <Field Name='ActivityDescription'></Field>
          <Field Name='JIModNumber'></Field>
          <Field Name='CustomerName'></Field>
          <Field Name='NewCustomer'></Field>
          <Field Name='PANumber'></Field>
          <Field Name='Mon'></Field>
          <Field Name='Tue'></Field>
          <Field Name='Wed'></Field>
          <Field Name='Thu'></Field>
          <Field Name='Fri'></Field>
          <Field Name='Sat'></Field>
          <Field Name='Sun'></Field>
          <Field Name='Billable'></Field>
          <Field Name='BillableHours'></Field>
     </Method>
</Batch>

And heres my List columns

ActivityDescription	Single line of text	Item
Billable	Single line of text	Item
BillableHours	Single line of text	Item
CustomerName	Single line of text	Item
EmployeeName	Single line of text	Item
Fri	Single line of text	Item
JIModNumber	Single line of text	Item
Location	Single line of text	Item
Mon	Single line of text	Item
NewCustomer	Single line of text	Item
PANumber	Single line of text	Item
Sat	Single line of text	Item
Sun	Single line of text	Item
TaskActivity	Single line of text	Item
Thu	Single line of text	Item
Title	Single line of text	Item
Tue	Single line of text	Item
Wed	Single line of text	Item
WeekOf	Single line of text	Item
Created By	Person or Group
Modified By	Person or Group

Re: InfoPath 07 & SharePoint Reporting

KristyKay — Thu, 06 Dec 2007 16:02:30 GMT

zeroecho:

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?

I did experience something similiar. The problem was one of the columns in my SharePoint list was a lookup. Instead of using the value of the lookup, you have to use the ID.

Are any of your columns in SharePoint look-ups? Or are they all text fields?

Re: InfoPath 07 & SharePoint Reporting

zeroecho — Thu, 06 Dec 2007 13:25:03 GMT

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?

Re: InfoPath 07 & SharePoint Reporting

zeroecho — Tue, 04 Dec 2007 16:57:38 GMT

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?

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!

Re: InfoPath 07 & SharePoint Reporting

Patrick Halstead — Tue, 04 Dec 2007 06:41:35 GMT

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...

Re: InfoPath 07 & SharePoint Reporting

Matt Faus — Mon, 03 Dec 2007 20:31:46 GMT

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

Re: InfoPath 07 & SharePoint Reporting

zeroecho — Mon, 03 Dec 2007 15:47:26 GMT

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.

Re: InfoPath 07 & SharePoint Reporting

KristyKay — Mon, 03 Dec 2007 15:29:48 GMT

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. :)

Re: InfoPath 07 & SharePoint Reporting

zeroecho — Sun, 02 Dec 2007 15:19:04 GMT

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.

Re: InfoPath 07 & SharePoint Reporting

Karsten Brocksieper — Sun, 02 Dec 2007 13:11:11 GMT

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:

5. ...

6. For the s0:listName parameter assign the list name node as the value.

7. ...

HTH,