InfoPath Dev

  Hi. I'm new to InfoPath and DBXL but am trying to learn. I have a form with 2 dropdownlists, the second list is dependent on the first. Both ddl's are getting populated from external data sources (views in sql server being accessed using QueryDB). The first ddl has a numeric value field and a text display field. The second ddl is set up the same and its data source includes a column matching the value field from the first ddl. I'm using the GetColumnsXMLStrQuery method to return the data, which I created using the QueryDB query builder, but am missing something in the filter on how to match only the rows that match the id field from the first ddl. My xml line looks like for the second ddl's data source looks like this:

concat("<query database='#DBDev#' table='DBXLClientLocations' maxrows='100'><columns><column name='LocationCode'/><column name='LocationName'/><column name='ClientId'/></columns><filter><eq><column name='ClientId'/><value>", xdXDocument:get-DOM()/my:myFields/my:ClientName ,"</value></eq></filter></query>")

 

In the line above, the ClientName field is the first ddl where I'm trying to get the value. Also, if I hard code a number in there, it brings back the right data, so I expect I'm just missing something simple about the right way to reference the ddl value field.  Thanks for any help you can provide.

Bill

Hi Bill,

I see in your query that you are matching a column named ClientId to a field called my:ClientName. Is this correct? In the first dropdown, you probably want to display the Name but use the ID as the value, so that the filter match on the second dropdown works.

Does that make sense?

 ClientName is the name of the dropdownlist control. You are correct that I really want a ClientId field but don't see how I get the value from the ddl to that field or just pick out the value from the ddl to put in the xml statement. As I said, probably some simple thing I'm missing. Thanks for your help.

Could you attach a screenshot for the properties if your my:ClientName dropdown box?  You should have it set up so that the name is the Display Value and the Client Id is the Actual Value.  You can test whether it is working that way by displaying my:ClientName separately in a text box or expression box and checking whether it actually has a ClientId as its value when you select something.

 Thanks for that suggestion Jimmy. I get the value correctly. The problem I have now is with the xml for retrieving the values. But when using the xml string from DBQuery as below:

concat("<query database='#DBDev#' table='DBXLClientLocations' maxrows='200'><columns><column name='LocationCode'/><column name='LocationName'/><column name='ClientId'/></columns><filter><eq><column name='ClientId'/><value>", xdXDocument:get-DOM()/my:myFields/my:ClientName ,"</value></eq></filter><sort><column name='LocationName' order='ASC'/></sort></query>")

 

    I get an error   Data at the root level is invalid. Line 1, position 1. I tried changing the quotes and such. Any thoughts? Thanks

 

 

Could it be that you are trying to use GetColumnsXMLQuery instead of GetColumnsXMLStrQuery?

Thanks for pointing me in the right direction. Went through one of the samples again and saw that I was entering things in the wrong place. Got it working. Thanks.