InfoPath Dev

I have three lists, the first list is a list of locations.  The second list is a Point of Contact list, this list has a multiple selection of the locations this person is responsible for.  The third list is a data tracker.  Inside that data tracker, which selects the location, I want to show the points of contact responsible for that area. So I go in and set my data connection in list 3, to list 2, and include the column (which is multiple choice) which is a lookup to list 1.  When I look then to the Query fields, the lookup is not one of the fields that can be queried. 

So the next step was to take the value of the multiple choice selection, and put them in a multi-line text box (thinking I could query it that way), when I brought the data connection back in however, again this was a field that I could not query. 

The next step then was to put it to a single line text value, I was able to query that field, but I was unable to query a specific part of it (if location had an id of 12345 but the string looked like 12341, 12342, 12343, 12344, 12345) I could not get results to return, but if I queried the entire string, (12341,12342,12343,12344,12345) I was then able to return the results. 

So how do I Query an item, that is multiple choice, based on lookup multiselect, or text multiline, or text single line that would work for just the 12345 query part of this?  I am not trying to post data back, only retrieve essentially all the points of contact that would be related to id 12345 from the Points of contact list.

Any help would be greatly appreciated. 

Are you using Office 365? If not, REST may work for you for this...

Not using O365. How would I go about doing this? Haven't had a reason to use REST because of Data Connections, so know almost nothing about it. The goal is to be able to show 4-5 entries (persons) into the form as you would be able to do via the Query method including some other information like email, phone etc. All of this is at the 2nd list. Any guidance you can provide would be greatly appreciated.

In InfoPath, we can use REST for a data connection. I usually start in the browser to make sure I have the URL right & am getting back the data I need to see. Here's a sample REST URL: http://SERVER/_vti_bin/listdata.svc/LISTNAME?$filter=Title eq 'TEST' - here's a blog post with some additional info: http://johnliu.net/blog/2012/4/26/infopath-2010-query-using-the-rest-interface.html

What I would do is first figure out the URL that brings back all my list data and take a look at the returned data in the browser - how is the multiselect data returned? Does it contain what you need to query on? Then, I usually refine my URL with a hardcoded filter, still in the browser. Once I know I have my syntax down, I add a data connection to my form (REST) without the filter, set to not run on load, then add the filter via a rule. It sounds WAY harder than it is. :)

 So I started working through the process and was able to get the url path that would give me the XML that included the entire string like this

https://(MySite)/_vti_bin/listdata.svc/MyList?$filter=FacilitySites eq '793,790,792,795,1023,1021'     (by the way, these are the id's that is associated with the person's location)

However, the user in the InfoPath form is going to select the actual location (data connection to list one that houses the location (ex. id value 793)

When I try to get the following link it comes up empty, I'm assuming because it cannot find an entry with just 793.

https://(MySite)/_vti_bin/listdata.svc/MyList?$filter=FacilitySites eq '793' 

So I'm assuming I need to change the operator? But to what? Essentially I have 2500 entries that could have a mixture of strings put together but of that 2500 entries, I'm guessing 7 of them have a combination that include the 793.

I have tried this on a multi-select lookup column (didn't expect to work) a multi-line column (didn't expect to work) and a single line text column (kind of expected it to work).

Thanks for the help on this one gang! 

 

 Yup, this is about the operator. So here is a reference:

 http://www.odata.org/documentation/odata-version-2-0/uri-conventions/ 

Section 4.5 on that page will help you. You might be able to use indexof:

http://services.odata.org/Northwind/Northwind.svc/Customers?$filter=indexof(CompanyName, 'lfreds') eq 1

But that may be an issue if you have '793' and '2793'

 Was able to get this resolved by followingBen Stegink's blog

https://www.benstegink.com/sharepoint-list-rest-query-filter-contains/

Pretty simple solution can be run on Single line of text or multiple lines of text.

https://(MySite)/_vti_bin/listdata.svc/MyList?$filter=substringof('1636',FacilitySites)   

NOW,

Going back to Hilary's path, I went through and set it up after getting to this point, however, there should be four entries upon execution of Query, (i.e. John Doe, Jane Doe, Jack Doe, Smiley Doe) but all I am getting back on the return is John Doe.  So how do we get the other three to return back? 

Oh, good, you got the query worked out! So - in the browser, you are getting back all 4 but in your InfoPath form, you are only seeing one item returned?

 Good point on the '793' vs '2793' will update the workflow to make a six digit identification 'S00793' or 'S02793'  (put a letter in front so that there are no issues with a prefix '0' in the system.

 Nope it was on me.  I wasn't bringing in the repeating section the way that needed to be brought in.  I have fixed it. 

I did have a little trouble with step six resetting the REST URL based on the string feature, I am sharing that for future reference to help someone else out 

concat("https://MySite/_vti_bin/listdata.svc/ListName?$filter=substringof('", PostCodeStep1Field, "'", ",FacilitySites)")

Thanks everyone for the help! Greatly appreciated!

Thanks so much for updating - glad you have this working. REST is great because you can have much more complex comparisons than a regular list connection.