InfoPath Dev

I'm using InfoPath 1020 and a browser based form.

In SQL i have a table of Sales Opportunities details. I have created a form based on the Database Template using that table as the main data source.

When I query the data by Opportunity Name, it returns multiple records as there are several opportunities with the same name.

To fix this I am trying to create two query fields. The first drop down shows the Company Names and the second would only show the Opportunity Names for that company.

But I cannot figure out how to filter the Opportunity Name field based on the data in the returned by the Company Name field.

The data source for the Company Name includes a RecID. This same RecID is also in the Opportunity Details table. I tried filtering the data in the Opportunity Name field by the RecID but this returns ALL the Opportunities no matter what Company Name is in the first drop down.

Any suggestions on how to filter the data would be appreciated. Thanks.

Not being able to dynamically parameterize a SQL query is a limitation with out-of-box InfoPath. It is one of the main reasons why we (Qdabra) created DBXL Web service and added support to qRules to do T-SQL queries. You could write your own code, or try one of those gap-fillers. qRules is a common library that includes the code you would need to do it. To use it, you just set a rule and it can then create a parameterized query for you. This is probably the easy approach since qRules trial is free and we can quickly help you with that. DBXL Web service is much more powerful and simple to use but it also requires a server install which may be an issue for you.

If you can't consider third party products I suggest searching this forum for code snippets. There have been many over the years. That will require that you write code. We can also provide an hour of development support if you need it.