InfoPath Dev

Hi,

I'm working with Drop Down List in InfoPath Forms. The data source which i'm using is SQL Webservice which contains SQL stored procedures references.

I've followng tables

Countries (ID, CountryName)

Cities (ID, CountryID, CityName) >> CountryID has foreign key relationship with Counties(ID)

Then, I wrote 2 stored procedures.

SP1: CREATE PROCEDURE [dbo].[GetCountries] SELECT ID, CountryName from Countries

SP2: CREATE PROCEDURE [dbo].[GetCities]  @CountryID int 

            SELECT ID, CountryID, CityName FROM Cities WHERE CountryID = @CountryID

I've created an Blank InfoPath form. Connected with webservice through Data Source.

Then, I dragged first Drop Down List Control named Country. Defined the stored procedure for the Drop Down and it's populating the data fine. (Value: ID, DisplayName: CountryName)

I dragged another Drop Down List for City. Defined the stored procedure. (Value: ID, DisplayName: CityName)

On preview, it is showing the values for Country but not for City. The intension is to filter the City drop down on the basis of Country selection

Can anyone please let me know how to filter the drop down in the infopath ?

Thanks

If you are working with browser enabled infopath forms, the only way to achiever cascading dropdown boxes boxes is by managed code. If you are working with non browser forms, you can achieve this functionality by using filter option.

How can i do this through Filter option ?

hi,

i hope your are using 2 dropdowns and you want to filter data in the second dropdown based on first dropdown selection, to achieve this

right click on second dropdown and select Dropdown List box properties, locate Entries: field under Data tab click on the button beside the field, a new window will be opend there you can find Filter Data... at the bottom left corner using that you can filter your data.

You can't filter using this method if your are filling this form using browser.

Regards,

Kiran 

sorry not read question properly, are you filling 2 dropdowns while loading form i mean did you checked Automatically retrive data when form is opend.

Kiran,

The filter method which you've told me works only when a single table contains values for both drop downs. But, it doesn't work when i pick data for DropDown1 from Table1 and data for DropDown2 from Table2 (Table1ID is also present).

Also, Please let me know how can we do this through code ?

Thanks

" did you checked Automatically retrive data when form is opend."

YES

If you can tell us whether you are using browser enabled infopath form or non browser forms it would be lot easier for us to post a reply that can solve your problem.

Initially, I'm looking for Non-Browser enabled forms. But, for my further research, i need to know with Browser enabled forms.

It would be great if you please let me know for both options one by one. But, the priority is Non-Browser enabled form.

clear me 1 thing, is your query to fill second dropdown needs any parameter i mean, is your query looks as same like this

select col1,col2,col3 from table2 where table2.masterID=table1.ID

if you are retriving data from webservice like this for dropdown2 then no need of coding you can achieve it via rules.

for dropdown2 first unchek automatically retrive data when form is opend,

click on dropdown1 choose rules, rule 1 condition dropdown1 is not blank

in actions select 'set fields value ', for field click on the button beside it then new window 'Select a Field or Group' will open in the top dropdown instead of Main choose your webservice data connection for second dropdown, you will find queryfields there expand that and select the query field, you will be returned to the Action window, for Value click on fx and choose first dropdown, click on ok, and add another Action as Query for data new window 'Rule Details' will be opend, for Data connection dropdown choose the data connection which fills second dropdown.

Regards,

Kiran

OK, with non-browser enabled forms you need to have a table where there should be some relationship between the two columns, it should be something like this
India - Mumbai
india - Kolkata
India - new Delhi
Australia - Canberra
Australia - sydney etc,
    this data should be there in one table, then you can filter dropdown on the basis of selection in other dropdown.Select a dropdown and set its default value to cities when filter values in table is similar to what selected in other doropdown.

For browser forms, you are required to have VSTA on your system, you can install that by going to Control Panel, select Add or remove programs, select Microsoft Office, expand it and select .Net programmability support to be running from your system. Let me know if you want code too for this.

kirankanth,

Thanks for your quick response. It works fine now. But, there is a small problem. On Preview, when i select DropDown1 Value, it filters DropDown2. But, when i re-select the DropDown1 Value, it shows the ID of the last selected value in the DropDown2 and the filtered values. When i select any of the DropDown2 value, it removes the ID of the last selected value from DropDown2.

Just for Information, At the time of Data Connection, i gave the Sample Text value to 1. If i don't give the Sample Value then DropDown2 dataField property doesn't provide show any column to show.

kirankanth,

OR simply we can say that it doesn't refresh the DropDown2 if i select another value from DropDown1

princevis,

Yes please, it would be great if you can provide me some code which filters DropDown2 on the selection of DropDown1 value.

But, the code access WebService by adding either WebReference or ServiceReference. And, then i can access stored procedures.

Thanks in advance

Hi Sohaib,

good it worked for you,

as i am using browser enabled, so in case of refreshing data, go to dropdown1 properties, under Browser forms tab select Always it worked for me. 

Regards,

Kiran