InfoPath Dev

Folks I'm stuck, I can get my Excel REST data into InfoPath 2010 no problem.   It's a range of B2:T2 about 15 or so cells form a Excel Row.  Where I'm stuck is trying to take that range of data returned in my Secondary Data Connection and assigning it to my Main InfoPath fields e.g. REST data B2 should be placed into Main data connection Field1 etc... 

I can bind my REST data (Secondary data source FV) to a repeating table and see all the data is coming back but I am not sure how to populate my Main fields with this returned data.  Any ideas would be much appreciated.  I know that I can call the REST data connecting single cell range 15 times and return each cell one at a time and place that cells data into my Main fields but I didn't want to call my REST data connection 15 times if I can avoid it.

One other very frustrating problem is that I can run my REST query in I.E. and my REST data connection in InfoPath Filler but I can not get it to work after I publish to SharePoint.  I would really like to run this as a Browser form in SharePoint if at all possible. 

 

Thanks

 

 

 Hi - you can get the specific values back using an index on the repeating row and cell.

For example, this gets the formatted value from the first cell in the third row of the returned dataset:

ns1:entry/ns1:content/ns2:range/ns2:row[3]/ns2:c[1]/ns2:fv 

You'll probably have to select the "edit XPath" checkbox in the formula builder dialog and add in the [n] yourself.

Are you using SharePoint Online (Office365?) I know Excel REST won't work in a browser form there. But if this is on-prem, you might be able to get it working if you disable loopback check on the server. 

Hilary your the best WOOT!  I've been looking for this answer for weeks online and you had it in a couple of minutes. 

2nd part; We are using a local SharePoint Enterprise and I'm not sure the computer security folks will allow disabling of loopback.  Any ideas of the security ramification of disabling loopback?  Is there any other way to get this to work like Excel Services or something else I'm missing.  FYI I did run across an article about Configure Web service proxy for InfoPath Forms Services (SharePoint Server 2010) https://technet.microsoft.com/en-us/library/ff621101(v=office.14).aspx but I don't know if this will help or not.

What we looking to do is to read an Excel spreadsheet (not a SharePoint list) to get our data into an InfoPath form and display on our SharePoint as a InfoPath browser form (preferred format).

Thanks again for all your help.

 What's the error you get when you try to query your Excel REST connection in the browser?

I'm not sure if there are any alternatives to disabling loopback for accessing SharePoint web services from a browser form.

Error dialog box is below.  Is the link to the Form Proxy of any use?

Thanks again 

 

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

An error occurred querying a data source.

Click OK to resume filling out the form. You may want to check your form data for errors.

 

Hide Error Details

XML data that is required for this form to function correctly cannot be found. An entry has been added to the Windows event log of the server. Log ID:5566 Correlation ID:64a087ec-aa43-4f13-87c9-d5ef4a6fd3c4

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

 The 5566 error is what I was expecting to see. If you give your server admins the correlation ID they should be able to get the full error back from the ULS logs. I don't know if that article on proxy will fix this - I suppose you can always give it a shot. Again, to the best of my knowledge, to use SharePoint web services (like Excel REST) in a browser form is going to require disabling loopback check. But if you learn something different, please post back w/ more info.

Hi Hilary, I am using these xPaths rules to get the values from my two columns named LOCATION and FULLNAME in a spreadsheet... set Field1 = xdXDocument:GetDOM("REST data")/ns5:entry/ns5:content/ns3:range/ns3:row/ns3:c[1] and set Field 2 xdXDocument:GetDOM("REST data")/ns5:entry/ns5:content/ns3:range/ns3:row/ns3:c[2]. I am trying to get FULLNAME into my main repeating table where LOCATION equals my dropdown list. I have tried different xPath combinations unsuccessfully and after watching one of your videos on Changing REST urls I thought I'd ask what my next steps should be.

I usually paste the query URL in the browser and inspect the returned XML to get a sense of the correct XPath - maybe try that? I don't see anything in your XPaths that indicates you are including a filter with a value from your main data source.

Thanks for the push. I got what I needed setting field1 to xdXDocument:GetDOM("REST Data")/ns5:entry/ns5:content/ns3:range/ns3:row[ns3:c/ns3:fv[1] = current()/../../../my:location][(count(current()/../preceding-sibling::my:group2) + 1)]/ns3:c[2]. Thanks again for all of your patience and help.

Awesome news! Glad you got it. If you aren't already using Notepad ++ (free) with the XML plugin, I recommend it. You can click on an element in XML and get the XPath, as well as test expressions. Obviously, the XML may have namespace prefix differences in your InfoPath data connection, but it is a good tool for some quick testing of whatever you are trying to get ahold of. :)