InfoPath, Excel Services, REST Web Service On premise works, online does not, ???

giustis5 — Wed, 30 Apr 2014 14:57:01 GMT

I am trying to create an InfoPath form that can calculate the number of days between two dates, the number calculated should take into account weekends, bank holidays and also any other dates I wish to specify.

I have gotten this to work using InfoPath 2010 on a SharePoint Server 2010 Enterprise on premise solution using a REST web service and an excel workbook which I dropped into a document library on SharePoint.

The same solution however does not work using Office 365 SharePoint and InfoPath.

When I go into sharepoint online and customise the form using InfoPath, I can add my REST web service

https://MYDOMAIN.sharepoint.com/rcoo/excel/lists/shared%20documents/WorkdaysCalculator.xlsx

Then I can add a Start Date and End Date and Days controls to my form and then add the following rule to the Start Date:

Condition - Start Date is not blank AND End Date is not Blank
Actions - Change the REST Service URL to:

concat("https://MYDOMAIN.sharepoint.com/_vti_bin/ExcelREST.aspx/rcoo/excel/shared%20documents/WorkdaysCalculator.xlsx/model/Ranges('A3')?Ranges('A1')=", ., "&Ranges('A2')=", End Date, "&$format=atom")
Query REST Web Service

Now I add a rule to the End Date control,

Condition - End Date is not blank and Start Date is not blank
Actions - Change the REST URL to:

concat("https://MYDOMAIN.sharepoint.com/_vti_bin/ExcelREST.aspx/rcoo/excel/shared%20documents/WorkdaysCalculator.xlsx/model/Ranges('A3')?Ranges('A1')=", Start Date, "&Ranges('A2')=", ., "&$format=atom")
Query REST Web Service

Now I set the default value of my Days control to be equal to the REST Service > entry > content > ns2:range > row > c > v.

Now if I do this on SP2010 using InfoPath 2010 ** OR ** on SP Online using InfoPath 2013, the steps above all do exactly the same thing, the difference is the output when using the list form.

On premise, I pick a start date (a Monday), I pick an end date (a Sunday) and the Days control populates with a number (5) - perfect

Online I repeat the above steps however at the point where the data source is queried, I get the following error:

XML data that is required for this form to function correctly cannot be found.

Any ideas why? The excel workbook lives in a sub site which was recognised by InfoPath when it was adding the REST Web Service, the user account im accessing with is an administrator and has full control over everything in SharePoint and office 365.