InfoPath Dev

I've found bits and pieces of this all over the internet without the final piece of the puzzle.  I have two Lists from SharePoint: Vendor & Vendor Equipment.  I want to create a cascading dropdown using the value selected from vendor to create the secondary data source from Vendor Equipment.  I found how to retrieve the data as if it were XML (http://server/site/subsite/_vti_bin/owssvr.dll?Cmd=Display&List={listnumber}&XMLDATA=TRUE) and created a changed event to re-query using the filter value (code follows). 

The last piece of the puzzle is the filter of my dropdown doesn't work because the value I'm filtering on is a lookup field from SharePoint.  So while the vendor I've picked is "Company ABC" the value in my secondary datasource is "1;#Company ABC".  I'm thinking after doing the q.Execute(); I need to somehow update the attributes of the xml to equal the vendor name, but I can't find how to do this.  

Does anyone have the solution for this, or can point me to the right article?

        public void vendor_Changed(object sender, XmlEventArgs e)
        {
            SetVendorDetails();
        }

        private void SetVendorDetails()
        {
            FileQueryConnection q = (FileQueryConnection)this.DataConnections["VendorEquipmentListXML"];
            string OriginalFileLocation = string.Copy(q.FileLocation);
            q.FileLocation = q.FileLocation + "&FilterField1=Vendor&FilterValue1=" + GetVendorValue();
            q.Execute();
            q.FileLocation = OriginalFileLocation;
        }

        private string GetVendorValue()
        {
            XPathNavigator nav = this.CreateNavigator();
            string filterValue = (string)nav.SelectSingleNode("/my:requisitionPriceQuote/my:vendor", this.NamespaceManager).ValueAs(typeof(string));
            return filterValue;
        }

Hello Welcome to the Forums,

Sometimes if we copy the data from another file or word document to XML Editor then it will give some special characters like as you are. For removing these special character or unknown character we have to copy the complete xml data to notepad and check with new characters available then remove it. And again copy from notepad to XML Editor.

If so those special character still there we have to remove that node complete and try to write one more time in XML Editor File.

Thanks for the help.

I discovered that this code appends data onto the existing xml, so I've added code to clear it out so I don't need to strip the special characters out for the filter because the only data will already be the correct set.  Plus I had an issue with needing to add a security certificate to allow the code to execute.   If anyone is interested the code I added is as follows:

        private void ClearVendorValue()
        {
            MessageBox.Show("Clear Vendor Value Executing");
            XPathNavigator myNavigator = this.DataSources["VendorEquipmentListXML"].CreateNavigator();
            XPathNodeIterator nodes = myNavigator.Select("//rs:data/z:row", NamespaceManager);
            while (nodes.MoveNext())
            {
                nodes.Current.DeleteSelf();
            }
        }

ClearVendorValue();  ADDED BEFORE q.Execute(); IN SetVendorDetails()

Hello,

Glad you got it working. Thanks for sharing your solution with us!

 I'm doing something similar except that instead of using a filter I change FileLocation to switch to a different view  based on the selection. The reason I can't use filter is because I have multiple values in my lookup column.

Cascading drop-down is working fine except that after the form is submitted only the selected value is showing up in the drop-down - instead of the set of values for that selection.

Any suggestions?

thanks

 I had something similar. My drop downs were populated by a secondary data source and the filter was working fine. But when I saved the form and opened it again, only the selected value was there.

To get around this problem, I used a rule and the trigger for it was loading of the form. You have the option of adding rules when you load up the form. Tools-->FormOptions-->OpenAndSave-->Rules.

In this rule I queries the datasource which was populating my first drop down. So everytime I opened the form, the drop down was populated with the all the option but also retained the selected value when opened from a saved state.

Hope it helps.

 Thanks for your reply. Just to make sure that we are using same terminology when yo say 'first drop down'. The value in the second (cascading) drop down is changed when you switch the value in the first. Right?

What exactly do you do in your rule? Based on your solution I did added a Rule then added an  Action "Query using a data connection"  and for the data source I selected the data source for the first dropdown. But it didn't fix the problem.

I also tried to  add an action "set a field's value" of the first drop down to the value of the data source - but then when you open the form the first value is always  selected in the first dropdown and the form doesn't save the data of the cascading dropdown after opening submitted form.