Filtering results of an SQL data connection query

fcoulter — Mon, 19 May 2014 14:51:15 GMT

I am creating a data connection to an SQL server. The stock data connection tools works fine, except the table I'm connecting to has 48K rows, and grows constantly. I'd like to filter the amount of data that is sent. As such, I have a couple of questions. I don't want to use code in my solution. I don't control the SQL database. There is no SOAP defined for the data base.

First, the data I'm querying has a Year field, called "Year". (A remarkable lack of creativity, I know.) I know that the earlier versions of InfoPath did not allow for adding a parameter into the SQL query. Is this still true for InfoPath 2013? This is not a deal changer, just would make my life easier.

If I can add a parameter, how do I do so?

Second, a quick refresher on SQL query language. What is that I need to add to the code to get only those items with Year = "2014"? How about if I want the year to equal either "2014" or "2015"? How about if I want to filter by year and by a different field (Active = "1")?

Finally, is there a way to have two different SQL queries defined, using the same field codes, etc.? I'm imagining the form working as follows:

Two buttons appear on the top of the form. One for 2014, and the second for 2015.
When a button is clicked

One of two different data queries fires off, requesting either the 2014 or 2015 data. (Basically, identical queries, except one tests for Year = 2014, while the other tests for Year = 2015
The buttons disappear, and the rest of the form appears

The rest of the form uses either the 2014 or 2015 query.

The multiple queries would lessen the traffic the most, but isn't at all essential. Even if I can limit the size of the data table to only two years, it will make the form much more responsive. I can then use in-form filtering to bring the size of the data to something even more workable.

Thanks in advance for your help. (Even if all you do is point me in the right direction, it's still helpful.)

SQL Database Integration

Filtering results of an SQL data connection query