InfoPath Dev

Hello, so I have a 2010 form that is pulling from a SQL table a list of software and putting the information in a List Box (called Compare_Data) based on an entered computer name placed into a text box on same form (this is manually done by the user) and consequently there's a seperate SharePoint list with almost 120 entries of software names (that will grow) that we want to filter against. So basically any name in that SP list should not show up in our Compare_Data listbox. I know you can filter the view but there is a 50 item threshold before it stops working. And with a 120 and growing list this isn't an option. So my question is there any way I can compare the entire list? Basically have the form look at it as a whole and anything in the Software Name column gets filtered? FYI I do not have access to this SQL table nor did I create the form. As always thank you in advance for any help you can provide.

Hello everyone is there any answer to this question?

Hi - I'm not sure what you mean by a 50 item threshold. I'm guessing that you have the default view of the list set to only show 50 items, so InfoPath only returns those in the query? If your default view is set to a higher number, so all items display, you should get all items back in your form. You can also use a REST query for this, if necessary - but the software names list doesn't sound terribly large, so I'd probably just change the default view settings.

Hi Hilary, I apologize I mispoke there is no item limit on the list itself. What I initially meant is I'm using a filter on a listbox that is pulling data from a SQL table. I'm filtering that view using the "does not contain" filter with text. The text being the software names in the previously mentioned SP list. So in the SQl table the column is "SoftwareName" so it's show the item if "SoftwareName "does not contain" "Microsoft Visio" for example. There's only 50 times you can put one of those filters in. The form actually is not linked to that SP list at all. I actually got it past that number by adding the "and" condition but that way is clunky and since it's not comparing to the SP list at all it becomes a very manual process of going into the form and updating that filter anytime someone adds or removes from the SP list that has all the software names. So my thought was instead of all of those filters applied I can actually compare to the SP list from the form. That way anytime someone adds or removes a software name form the list the form will reflect that and filter without having to be modified. Does that make sense?

Okay. So here's what I'd do. I'd add a data connection to the list. Then you can use a simple XPath expression for your filter. I'm attaching a simple sample form - save the linked file locally, right click and select design to open in design mode. All my data in the sample come from XML file data connections - that's easier for a sample (since you can't connect to my SharePoint or SQL) but hopefully will get the idea across.

I named my data sources SharePoint and SQL - I'm guessing that in general your SharePoint list will be a subset of the SQL list - but at any rate, preview this and see if it is what you are looking to do. Then you can check the filter on the list box data source. :) 

OK so I created my XPath Expression using your example and not getting any errors on that. But it's not filtering. I apologize as I'm unfamilar with this form and am trying to reverse engineer what was done. So it looks like what they're doing is using a udcx file to get the info from the SQL table. Next there's a text box that is using that connection to pull the computer name. Then a button to run the query and THEN that software info gets dumped into the List Box I'm trying to filter using the SP list. Does that change anything for you in terms of the XPath expression?

Any chance you can attach a copy of your form to a reply under the Options tab?

Sure here you go.

I have the listbox in there three times. The second one is the one I put my filters in and the third one is the one being compared to the SP list with the Xpath Expression you gave me.

I changed the filter on the third list box to be like the one in my sample. See if this works.

Unfortunately it did not. I see you removed Value at the start of the string. Can I ask what did you replace it with exactly?

You were using does not contain. I changed that to = and then wrapped the XPath expression in a not() - you'll note that's what my sample does. Are the names between the SQL table and the SharePoint list exactly the same, including case?

Yes they are, actually. That make a difference in the expression now?

No - if they are the same, = should work. Sometimes I put the data I'm working on the form so I can see it. You may want to do that, drag the data from your list onto the form, and drag that display name from the data table onto the form - then try using conditional formatting to highlight rows in the SQL data that match or don't match. That can often help me find what I've got wrong in my filtering. :)

So I can't really do that as the column in that table numbers in the thousands. I really need to find a DBA to clean it up : ) But just a question if the second list box does work using all of those does not contain doesn't that also mean that the data does match up?