How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ? - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

Last post 11-01-2010 04:13 AM by kirankanth. 16 replies.
Page 1 of 2 (17 items) 1 2 Next >
Sort Posts: Previous Next
  • 10-29-2010 04:38 AM

    How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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

    --
    Sohaib Khan
  • 10-29-2010 04:47 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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.

    Vishal Srivastava
    Senior Software Engineer
    SDG Software Pvt Ltd
  • 10-29-2010 04:51 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    How can i do this through Filter option ?

    --
    Sohaib Khan
  • 10-29-2010 05:30 AM In reply to

    • kirankanth
    • Not Ranked
      Male
    • Joined on 10-25-2010
    • India, Hyderabad
    • Posts 6

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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 

     

  • 10-29-2010 05:36 AM In reply to

    • kirankanth
    • Not Ranked
      Male
    • Joined on 10-25-2010
    • India, Hyderabad
    • Posts 6

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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.

  • 10-29-2010 05:39 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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

    --
    Sohaib Khan
  • 10-29-2010 05:39 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

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

    YES

    --
    Sohaib Khan
  • 10-29-2010 05:47 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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.

    Vishal Srivastava
    Senior Software Engineer
    SDG Software Pvt Ltd
  • 10-29-2010 05:54 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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.

    --
    Sohaib Khan
  • 10-29-2010 06:06 AM In reply to

    • kirankanth
    • Not Ranked
      Male
    • Joined on 10-25-2010
    • India, Hyderabad
    • Posts 6

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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

  • 10-29-2010 06:06 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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.

    Vishal Srivastava
    Senior Software Engineer
    SDG Software Pvt Ltd
  • 10-29-2010 06:40 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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.

    --
    Sohaib Khan
  • 10-29-2010 06:44 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    kirankanth,

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

    --
    Sohaib Khan
  • 10-29-2010 06:50 AM In reply to

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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

    --
    Sohaib Khan
  • 10-29-2010 09:47 PM In reply to

    • kirankanth
    • Not Ranked
      Male
    • Joined on 10-25-2010
    • India, Hyderabad
    • Posts 6

    Re: How to filter the drop down in InfoPath while using SQL stored procedure through SQL webservice ?

    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

Page 1 of 2 (17 items) 1 2 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.