InfoPath Dev

Hello everyone!

I'm using the Database Enumeration Service to populate a dropdown in a form. I have followed these two documents to learn about the Database Enumeration Service.

• How To - Sort and Filter Database Results with DatabaseEnumerationService (http://www.infopathdev.com/files/folders/dbxl/entry22514.aspx)
• Using Database filtering to Populate Drop-Down Menus (http://www.infopathdev.com/files/folders/dbxl/entry19826.aspx)

I've used the service succesfully to populate a dropdown in a form, and I am now interested in filtering my results. However, I am confused about how to go about this. I have built my query and saved it as an xml file, but I am not sure how this query gets incorporated into Infopath and/or the Data Connection Wizard.

Thanks for any help!
Ernesto

Hola Ernesto!

I agree this can be a little tricky.  The problem is the simplicity of the Data Connection Wizard in InfoPath.  It has a hard time describing all but the top levels of the SOAP package used for it's parameters.  It's great that you have your XML query built, but now you have to inject it in the right place.  To do this, you have to edit the source files of your template.

1. Open your template in Design mode.
2. From the file menu, select "Save as Source Files", or "Export Files" in 2003.
3. Save the files to a location on your disk that you can easily remember.
4. Open this folder in Windows Explorer, and open the XML file corresponding to your new data connection in a text editor, such as Notepad. 
1. If you used to default values in the Data Connection Wizard, this will be named "GetColumnsForRows2b.xml"
5. Inject your forumluated queryXml node into the /dfs:myfields/dfs:queryFields/tns:GetColumnsForRows2b/tns:queryXml node.
6. Save this file, right-click the manifest.xsf in the Designer, and then select Design.
7. Republish your forms back into an XSN, and try it out.

Now, filtering should work, but if you look at the DOM for your EnumDB connection in the Data Source taskpane you will see that the nodes you injected under the queryXml node are not displayed.  This is a bit confusing, because the XML is still actually there, it's just that the designer does not recognize it.  InfoPath does not validate XML against its corresponding XSD for secondary data connections, which allows this discrepancy to exist.  The primary problem is that the only way you can access those nodes via rules or other declaritive logic in the form is by manually typing the XPaths.  To remedy this, and allow full use of the nodes under your queryXml node, you will have to update the appropriate XSD file to add the nodes you injected into the XML.

I hope this helps, let us know how things turn out!

Hi Matt, I followed your steps and understand what the process is, but after I inject the xml into the data connection's .xml file the query fails to connect. Here's my query:

<query>
 <columns>
  <column name="ID"/>
  <column name="Name"/>
  <column name="Code"/>
  <column name="ClientId"/>
  <column name="PrimaryContact"/>
  <column name="StartDate"/>
  <column name="TotalBudget"/>
  <column name="Billable"/>
  <column name="Active"/>
  <column name="DocId"/>
 </columns>
 <sort>
  <column name="Name" order="DESC"/>
 </sort>
</query>

I am attaching screenshots of the Infopath error message returned.

My first guess would be that there is something wrong with the query itself, because without the query above the service works perfectly. From the documents listed in my original post, I understand that all the columns need to be listed in the query, even if the filtering/sorting performed does not involve all the columns. Still, is there anything that stands out as incorrect in the query above?

Thanks again for your help.

The queryXml parameter to GetColumnsForRows2b is a string.  You will need to encode the xml into a string (replacement of < with &lt; > with &gt; etc...) or use the DOM apis to set the node text value which will encode the value for you.

Success! Thank you!

Does filtering work in a similar manner for the other 3 methods? (i.e. other than GetColumnsForRows2b)

Thanks!

Only the methods with a queryXml parameter allow you to filter and sort the results.  Therefore, the GetColumnsForRows2 method does allow this, while the others simply return everything in the table.

Thanks for the follow up, Matt.

I'm attaching the screenshots of the Data Connection Wizard for GetColumnsForRows and GetRowsForColumns. These include filtering options. However, according to your response, I understand these are not used. Is that correct?

I also notice that GetColumnsForRows2 and 2b differ only in the whether the XMLquery is considered a string (2b) or not (2). I don't see any further differences, but let me know if there are.

Thanks,
E

Sorry for the double reply; couldn't attach two documents in one post.

Ah yes, now I remember.  You can actually do rudimentary filtering.  columns is the list of columns that you want to return.  filterColumns are the columns that you want to filter on.  filterCriteria are the values for each of the columns.  The number of filterColumns nodes must match the number of filterCritiria nodes.

 The following SOAP payload would generate the below SQL query.

columns

-- Col1

-- Col2

filterColumns

-- Col3

-- Col4

filterCritieria

-- "Matt"

-- "Faus"

SELECT Col1, Col2 from <database>.<table> WHERE Col3 = 'Matt' and Col4 = 'Faus'

Excellent, I'll give it a shot. Thanks!