Filter SQL data connection in InfoPath

Question

In InfoPath I retrieve a data from SQL view with 2000 records, to show a specific fields in the InfoPath form based on the current SharePoint site URL.

I face a performance issue to load all 2000 records when the form open.

I need to filter the data connection based on the current SharePoint site URL during configure SQL data connection in InfoPath. instead of retrieve 2000 record only retrieve a one filtered record!

How can this be done?

Answer 1

Unfortunately, you can't set a filter during creating a SQL data connection in infopath.

you can only get the data then filter each field in your form with your criteria, In your case I think there is a function in InfoPath called SharePointURL() that you can filter with it, meanwhile, it will require binding all the data on load.

but there is a workaround to get filtered data by

• Creating a web service that receives your data from your view.
• Create a Web Service data source.
• Set parameter for the Web Service function.
• Do the Web Service Call
• Set field values for each field from the web service data source.

You can check the detail steps with image at InfoPath and Web Service data connection