Web methods

Implement Static and Dynamic queries using QueryDB

ErnestoM — Tue, 23 Sep 2008 18:06:14 GMT

In the QueryDB User Guide we discussed the possibilities offered by this web service. This document is a tutorial where we illustrate the use of Query Builder in conjunction with QueryDB to create two types of queries: static and dynamic.

Generate queryxml for use with QueryDB

1. Open InfoPath, select Qdabra Query Builder and click on Fill out this form.
2. In the Data Sources table, enter the root URL to your installation of DBXL in the Web Service URL Prefix field.
3. The Web Service Name field is automatically populated to the default value, which is QdabraWebService. If you have chosen a different value, modify the content of the field.
4. For the InfoPath Form Template field, you only need to attach an InfoPath form if you are going to be using a field from the form to build the query. We will do this in a future example, but for now this can be left blank.
5. If you know it, enter the Database Connection String directly into the field. Alternatively, click on the Construct Now button to display a new section.

a. When using Windows Authentication, you only need to enter two fields:

i. Enter a Data Source (Server Name). This is the location of your SQL Server.
ii. Enter an Initial Catalog (Database Name). Note that this value will automatically be copied to the next table, Query Parameters.
iii. Click the Done button.

b. If you select SQL Server Authentication, a new section will open.

i. Enter a login and a password.

6. In the Query Parameters table:

a. Provide a Database Name if one was not provided in the connection string in step 5.
b. Enter the Table Name you wish to query.
c. Enter a value for the maxrows parameter in the Max Rows Count field. This will limit the number of rows that are returned by the connection.
d. Check the Distinct checkbox if desired.

7. In the Query Columns table, click on Insert return column.

a. Type in the name of the column. Alternatively, click on the Select Database Column icon. The database structure will open in the taskpane, where you can select the column desired.
b. Repeat as desired, adding the columns you wish to retrieve from the table. For this example, we will only return the ProjectName column.

8. In the Query Filter table, click on Insert comparison to create a new query filter.

a. Click on the Select Database Column icon and select a column from the data structure shown in the taskpane. For our example, we will select Client.
b. Select a Comparison Operator from the dropdown. For our example, we will select Equal.
c. In the Value or a column name field, enter Client1.

9. Click on Insert Logical Operator.

a. Select the logical operator OR from the dropdown.
b. Now repeat step 8, replacing Client1 with Client2.

10. In the Query Sort table, click on Insert sort column.

a. Click on the Select Database Column icon and select a column from the data structure shown in the taskpane. For our example, we will select Actual_length_months.
b. Select a Sort Order from the dropdown, in our case, we opt for Ascending.

11. Click on the Build Now button to generate the Query Strings. Copy the first two strings into Notepad.
Implement the static query
12. In InfoPath, click on File > Design a Form Template. Select a blank form and click OK.
13. Click on Tools > Data Connections, and in the dialog box, click on Add.
14. The Data Connection Wizard will now be displayed. Select Create a New Connection to
15. and Receive data. Click Next.
16. From the following screen, select Web Service and click Next.
17. Type in the URL for the QueryDB service, http://<server-name>/QdabraWebService/QueryDB.asmx and click Next.
18. In the following screen, select GetColumnsXMLStrQuery and click Next.
19. Double-click on tns:queryxml and paste the first query that was generated by Query Builder. This is the sample value, used by InfoPath to derive a schema. Click Next.
20. In the following screen, double-click on tns:queryxml and paste the second query that was generated by Query Builder. Click Next twice.
21. Ensure the checkbox for Automatically retrieve data when this form is opened is checked. Ensure the name for this data connection is GetColumnsXMLStrQuery. Click Finish. Click Close.
22. In the right hand pane called Design Tasks, click on Data Source and select GetColumnsXMLStrQuery from the dropdown.
23. Drag the node /dfs:myFields/dfs:dataFields/tns:GetColumnsXMLStrQueryResponse/tns:GetColumnsXMLStrQueryResult/Rows/row and drop it into the form as a Repeating table.
24. Click on File, then Save As, and save the form.
25. Click on Preview. If prompted by a Microsoft InfoPath Security warning, click Yes. This will display the results, listing the projects for Client1 OR Client2, sorted by the Actual_length_month column in ascending order.

Switch to a dynamic query

With a few additional steps we can implement a dynamic query that will call QueryDB after a user provides some missing piece of data. First we need to modify the form, then modify the query generated by Query Builder.

26. Delete the repeating table added to the form in step 14, above.
27. In the Design Tasks taskpane, click on Controls and then click on Text Box. A textbox will appear in the view.
28. Double click on the textbox and change the field name to client. Click OK to close the textbox properties window. Save the form.
29. Switch back to Query Builder and attach the form saved in step 17 to the InfoPath Form Template file attachment control.
30. Clear the Query Filter table by clicking on the down arrow and selecting Remove Logical Operator.

31. Click on Insert comparison and select the Client column for the Column Name. Leave the Comparison operator set to Equal.
32. For Value or Column Name, click on the Select Schema Node icon and select the client node.
33. Click on Build Now and copy the first two queries into Notepad.
34. Switch back to your form and select Tools > Data Connections. Click Modify for the GetColumnsXMLStrQuery.
35. Click Next twice and you will reach the Set Sample Value window for the Data Connection Wizard. Double-click on tns:queryxml and paste the first query that was generated by Query Builder. Click Next.
36. In the following screen, double-click on tns:queryxml and paste the same (first) query that was generated by Query Builder. Click Next twice.
37. Uncheck the checkbox for Automatically retrieve data when this form is opened and click Finish.
38. In the right hand pane called Design Tasks, click on Data Source and select GetColumnsXMLStrQuery from the dropdown.
39. Drag the node /dfs:myFields/dfs:dataFields/tns:GetColumnsXMLStrQueryResponse/tns:GetColumnsXMLStrQueryResult/Rows/row and drop it into the form as a Repeating table.
40. In the Design Tasks taskpane, click on Controls and then click on Button. A button will appear in the view.
41. Double click on the button and change the label to Query.
42. Click on Rules, then click on Add, and then click on Add Action.
43. Select Set a field’s value from the dropdown.
44. Click on the icon to the right of the Field textbox and select the queryxml node from the GetColumnsXMLStrQuery secondary data connection. Then click OK.
45. Click on the icon to the right of the Value textbox and paste the second query generated by Query Builder. Then click OK twice.
46. Click on Add Action.
47. Select Query using a data connection from the dropdown, making sure that the data connection to GetColumnsXMLStrQuery is selected from the Data connection dropdown, then click OK.
48. Click OK until all dialogs are closed. Save the form and click Preview.
49. Enter a client into the textbox and click the Query button. The results table will show the projects associated with that specific client.

Populate a dropdown

With a few extra steps we can use the data received from QueryDB populate a dropdown list:

50. Click on Close Preview to return to design mode for the form.
51. Delete the results repeating table.
52. In the right hand pane called Design Tasks, click on Controls and click on the Drop-Down List Box to get it in to the view.
53. Double click on the drop down to view the properties of the dropdown list.
54. In the properties dialog box, select Look up values from an external data source radio button.
55. Click the button next to the Field or Group text box. The Select a Field or Group dialog will appear.
56. Expand the data source, select the row repeating group and click OK twice.
57. Preview the form. Enter a client into the textbox and click the Query button. The dropdown will load the values pulled down via the QueryDB web service.

How To Call Active Directory Web Service Using Code

ErnestoM — Thu, 28 Aug 2008 20:39:14 GMT

Click Download, above, to obtain the C# code that enables you to look up and retrieve information from the GAL.

Web Service Developer Reference

ErnestoM — Thu, 31 Jul 2008 18:05:25 GMT

This document is meant as an SDK reference for developers that will be integrating DBXL Web Services with their InfoPath forms or any other application. The attached document describes all of the methods in each of the web services. This document is also included in the DBXL v2.2 installer.

Development Tips

Web Services with Ex Suffix

There are two versions of some of the web services, one of which has an “Ex” suffix. These “Ex” APIs differ from their “non-Ex” counterparts only in the way errors are handled. Methods defined in the “Ex” pages return a SoapException when an error occurs. This allows InfoPath’s internal mechanism to detect the error and display an error message. The “non-Ex” methods return a StatusInfo object with a Status node, of value true or false, and error messages. Calling the non-Ex methods requires form logic to check the Success indicator and handle any errors.

DbxlSampleDocumentService

This web service provides identical functionality to the DbxlDocumentService web service, except that everything that it returns is derived from the sampledata.xml file present for the form template of the Document Type queried. This is necessary for InfoPath to derive a schema for the form when there are no documents in the Document Type. Also, when there are documents with varying schemas in a particular Document Type (which is considered bad practice), InfoPath cannot derive a schema for the Content node, regardless.

References to this web service are automatically redirected to DbxlDocumentService whenever a form is published in DAT. This way, you can develop a form with the Sample Data, but when it is deployed it will use the actual data.

QdabraDBXL Processing Instruction

When resubmitting a Document that has already been assigned a Document ID, the QdabraDBXL Processing Instruction must be present in the XML. DBXL uses this PI to determine whether the Document being submitted is new or existing. If it finds the PI with the attributes specified correctly, then it will move the old version of that Document to the archives, and then insert the new Document.

Use the following example to construct the QdbraDBXL PI. The docid and doctype values should come from those defined in the DBXL Admin Tool.

<?QdabraDBXL docid="_int_" doctype="_doc_type_name_" name="_meta_data_" author="_meta_data_" description="_meta_data_" ?>

The codeless means to handle the insertion of the QdabraDBXL PI is to open the corresponding Document via Document Linking and Loading. When opening a Document from the web service, the QdabraDBXL PI is automatically injected.

There will be certain situations, however, that will require that you use code to inject the QdabraDBXL PI. There are two main scenarios where you will have to programmatically inject the QdabraDBXL PI. The first is when you want to allow users to intermittently save a form as they are filling it out. The second is when developing a catalog-style form, like the QdCatalogBase form. These two situations are covered later, in the Code Samples section.

Code Samples

It is recommended that you copy-paste the examples below into Visual Studio, as they will be much easier to read in an actual coding environment. These samples were created using Visual Studio 2005 and InfoPath 2003.

Using a Secondary Data Connection setup to Receive Data

The most common way to interact with the DBXL web services is to receive data from them. The following code sample shows the most common way to do this with code.

// Retrieve a handle to the secondary data connection. The string passed to GetDOM()
// is the name as for the data connection as defined in the InfoPath Designer.
IXMLDOMDocument3 domGetDocumentsByType = (IXMLDOMDocument3)thisXDocument.GetDOM("GetDocumentsByType");

// Setup the namespaces for the secondary data connection. For more information, see
// this HowTo: http://www.infopathdev.com/blogs/matt/archive/2006/02/02/Setup-Namespaces-for-a-Secondary-Data-Source.aspx
domGetDocumentsByType.setProperty(
    "SelectionNamespaces",
    " xmlns:my=\"http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-02-01T19-18-48\" "
    + " xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\" "
    + " xmlns:ns4=\"http://qdabra.com/webservices/\" ");

// Change the value of a parameter
domGetDocumentsByType.selectSingleNode("/dfs:myFields/dfs:queryFields/ns4:GetDocumentsByType/ns4:docTypeName").text = "PT_Timecard";

// Query the web service, which will use the new parameter value as defined above
WebServiceAdapter2 wsaGetDocumentsByType = (WebServiceAdapter2)thisXDocument.DataAdapters["GetDocumentsByType"];
wsaGetDocumentsByType.Query();

// Make sure the method invocation was successful
string success = domGetDocumentsByType.selectSingleNode("/dfs:myFields/dfs:dataFields/ns4:GetDocumentsByTypeResponse/ns4:GetDocumentsByTypeResult/ns4:Success").text;
if (success != "true")
    throw new Exception("Call to GetDocumentsByType failed.");

// Get values from the returned set
string docId = domGetDocumentsByType.selectSingleNode("/dfs:myFields/dfs:dataFields/ns4:GetDocumentsByTypeResponse/ns4:docInfos/ns4:DocumentInfo/ns4:DocID").text;

Calling SubmitDocument setup as a Receive Data Connection

There are two ways to submit documents into DBXL. The way that is documented in the Getting Started and other documents is to add a secondary data connection that is setup to Submit Data, but there will be situations (such as creating a catalog-type form) where you will want to programmatically work with the SubmitDocument web method. If you setup this web method as a "submit data" connection then it is very difficult to work with in code. The below code sample demonstrates how to use a SubmitDocument data connection as a "receive data" connection. The XML that is assigned to the xml parameter is trivial in this example, but when developing forms it will contain the entire XML of the form to be submitted. If it is a new document, it will not have the QdabraDBXL Processing Instructions, but if it is an update of a form that already has a Document ID, then the QdabraDBXL PI should be added to the top of the submitted XML.

// Retrieve a handle to the secondary data connection.
IXMLDOMDocument3 domSubmitDocument = (IXMLDOMDocument3)thisXDocument.GetDOM("SubmitDocument");

// Setup namespaces
domSubmitDocument.setProperty(
    "SelectionNamespaces",
    " xmlns:my=\"http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-02-01T19-18-48\" "
    + " xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\" "
    + " xmlns:ns4=\"http://qdabra.com/webservices/\" ");

// Alter the query parameter values
domSubmitDocument.selectSingleNode("/dfs:myFields/dfs:queryFields/ns4:SubmitDocument/ns4:docTypeName") .text = "PT_Timecard";
domSubmitDocument.selectSingleNode("/dfs:myFields/dfs:queryFields/ns4:SubmitDocument/ns4:xml").text = "<?QdabraDBXL docId=’3’ ?><Root><a>hello</a><b><c>1</c><c>2</c></b></Root>";
domSubmitDocument.selectSingleNode("/dfs:myFields/dfs:queryFields/ns4:SubmitDocument/ns4:name").text = "Matt";
domSubmitDocument.selectSingleNode("/dfs:myFields/dfs:queryFields/ns4:SubmitDocument/ns4:author").text = "Matt";
domSubmitDocument.selectSingleNode("/dfs:myFields/dfs:queryFields/ns4:SubmitDocument/ns4:description").text = "Latest timecard.";

// Query the web service
WebServiceAdapter2 wsaSubmitDocument = (WebServiceAdapter2)thisXDocument.DataAdapters["SubmitDocument"];
wsaSubmitDocument.Query();

Working with a Secondary Data Connection setup to Submit Data

When a secondary data connection is set up to submit data, the object model only supports a limited amount of access. The submission process can be initiated, and then the results can be viewed, but other than that there is not much else available.

// Submit the data connection as defined in the Data Connection wizard
WebServiceAdapter2 wsaSubmit = (WebServiceAdapter2)thisXDocument.DataAdapters["Submit"];
wsaSubmit.Submit();

// Inspect what was returned by the submit
IXMLDOMDocument2 domSubmitOutput = (IXMLDOMDocument2)thisXDocument.CreateDOM();
domSubmitOutput.async = false;
domSubmitOutput.validateOnParse = false;
domSubmitOutput.loadXML(wsaSubmit.OutputLocation.xml);
domSubmitOutput.setProperty( "SelectionNamespaces",
    " xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\" "
    + " xmlns:ns4=\"http://qdabra.com/webservices/\" ");

string submitSuccess = domSubmitOutput.selectSingleNode( "/dfs:dataFields/ns4:SubmitDocumentResponse/ns4:SubmitDocumentResult/ns4:Success").text;
thisXDocument.UI.Alert( submitSuccess );

Adding Exception Handling when Working with Ex methods

When using the Ex web methods, adding exception handling will allow you to handle exceptions gracefully when they are returned from the web service.

// Submit the data connection as defined in the Data Connection wizard
WebServiceAdapter2 wsaSubmit = (WebServiceAdapter2)thisXDocument.DataAdapters["SubmitEx"];
try
{
    wsaSubmit.Submit();
}
catch (Exception ex)
{
    thisXDocument.UI.Alert("SubmitEx method failed. " + ex.Message);
}

Client-side Injection of the QdabraDBXL Processing Instruction

The normal scenario when working with DBXL enabled forms is to open the form template, fill out the form, submit to DBXL, and then immediately close the form. If updates need to be made, the form is reopened from the web service (which injects the QdabraDBXL PI), edited, and then resubmitted. However, it is not always desirable to close the form after initial submission, and then reopen. To remedy this, you can inject the QdabraDBXL PI on the client with code. When doing this, it is best practice to also set the dirty flag to false immediately after a successful submission to the web service so that the user is not confused when they try to close the form and are prompted to save their form, even though it has been successfully saved to the DBXL web service.

1. Open the template.xml file for the form template you are developing.
2. At the very top of the XML, insert a blank QdabraDBXL PI between the other PIs and the root node of the form.

<?xml version="1.0" encoding="UTF-8"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:ProjectTracker_Timecard:-myXSD-2006-06-09T06-04-22" href="manifest.xsf" mce_href="manifest.xsf" solutionVersion="1.0.0.881" productVersion="11.0.6565" PIVersion="1.0.0.0" ?>
<?mso-application progid="InfoPath.Document"?>
<?QdabraDBXL?>
<my:Timecard></my:Timecard>

3. In the code that handles the submission of the form to the web service, add code that updates the PI of the current form to include the docId.

WebServiceAdapter2 wsAdapter = (WebServiceAdapter2)thisXDocument.DataAdapters["SubmitDocument"];
    wsAdapter.Submit();

    string docId = wsAdapter.OutputLocation.selectSingleNode("//ns2:docId").text;

    thisXDocument.DOM.selectSingleNode(String.Format("/processing-instruction()[local-name(.) = '{0}']", "QdabraDBXL")).text = String.Format(" docid=\"{0}\"", docId);

    // Make sure the user does not see the save dialog when closing the form
   thisXDocument.SetDirty(false);

To view the detailed description of each web method, please download the attached Word Document.

QueryDB User Manual

ErnestoM — Mon, 28 Jul 2008 18:51:00 GMT

This is the User Manual for the QueryDB web service. The document explains the operations available, as well as the possible filtering and sorting operations.

Note that all of the examples in this document will derive their data from the table in Figure 1.

Figure 1

Configuration

Before you can begin using QueryDB to query a SQL database, the database being queried needs to be added to the configuration/qdabra.dbxl/enumdb/databases node in web.config. The SQL server indicated during installation (where QdabraDBXL and QdabraSamples are located) is already configured to use QueryDB, but any additional databases must be added individually, like this: <database alias="DB_Alias" name="SampleDB" server="default" />.

Accessing a remote database

In order to allow QueryDB to access a database that lies on a different server, you must add server and database aliases to the web.config file. Look for instructions within the configuration/qdabra.dbxl/enumdb/servers and configuration/qdabra.dbxl/enumdb/databases nodes.

The database alias must be placed within pound signs when using QueryBuilder to build the queryxml. Note that you must also provide the requester machine read access in the remote database.

QueryDB methods

There are four methods within QueryDB, each of them allowing the user to query the database in different ways.

1. GetColumnsXMLStrQuery

GetColumnsXMLStrQuery returns an array of all columns of the specified rows of the specified table. The user must provide an xml query, generated by Qdabra’a Query Builder, which details database, table, retrieved columns, sorting and filtering.

Depending on the query generated by Query Builder, the user can enable rules within the form in order to use runtime data to affect the query. Of course the simple scenario is also possible, where Query Builder generates a static xml query.

2. GetDropdownEntries

GetDropdownEntries allows a user to retrieve a list of values and easily bind them to a dropdown control. The user must provide values for database, table and valueColumn. By default, the values will be sorted according to valueColumn in ascending order, or by displayColumn if provided. Besides the optional displayColumn, the user can also indicate sortOrder (ascending or descending) and a filterXML. Here is the full information regarding the parameters:

To illustrate the use of this method, we will query our sample data to list the ProjectName column, sorted by Estimated_length_months in descending order. The parameters required for this operation are seen in Figure 2 and result of that query is seen below, in Figure 3. Note that if no sortColumn and no sortOrder are provided, the method will automatically sort by valueColumn, ascending.

Figure 2

Figure 3

The filtering parameter, filterXML, will be discussed later on in this document. Note that, because this method accepts parameters for sorting in the Data Connection Wizard, the user must not use a sorting operation within the filterXML.

3. GetColumnsXMLQuery

GetColumnsXmlQuery is meant for use in code exclusively and behaves in the same way as GetColumnsXmlStrQuery.

4. DescribeDatabaseSchema

DescribeDatabaseSchema scans the schema in the specified connection string and returns an XML Document describing it. The dataSource parameter should be a connection string as passed to the SqlConnection constructor the .NET framework.

XML Query

When using GetColumnsXMLStrQuery, the user can perform fine-grained filtering by using a XML Query, which will have the following format:

The simplest query that can be used is one that will pull down an entire table from a given database. The format for this query is <query database='<database name>' table='<table name>'></query> and the result is seen in Figure 4.

Figure 4

While more fine-grained xml queries can be generated manually, Qdabra provides the Query Builder tool, which allows the user to easily generate a desired xml query. The Query Builder tool will be discussed in an upcoming section.

maxrows

The maxrows parameter allows the user to limit the number of rows that are returned by the query. This is especially useful when a query might return a large amount of results. The format used for this is <query maxrows=’x’>, where x is the number of rows to be returned.

Filtering

There are various types of comparison operations available for the <filter> node of queryXML.

a. Comparison operators: lt, le, eq, ne, ge, gt

The first set of comparison operators -namely <lt>, <le>, <eq>, <ne>, <ge> and <gt>- all use similar formats. The comparisons can take place between a column and a value, or between a column and another column. The examples below will illustrate some of the uses of these operators. Each sample will be accompanied by a table showing the result.

  <lt>
   <!—- lt stands for “less than”. In the example
below we compare a column and a value. -->
   <column name="Actual_length_months"/>
   <value type="int">12</value>
  </lt>

  <le>
   <!—- le stands for “less than or equal”. In the
example below we compare a columns and a value. -->
   <column name="Actual_length_months"/>
   <value type="int">18</value>
</le>

  <eq>
   <!—- eq stands for “equal”. In the example below
we compare two columns. -->
   <column name="Actual_length_months"/>
   <column name="Estimated_length_months"/>
</eq>

  <ne>
   <!—- ne stands for “not equal”. In the example
below we compare a column and an alphanumeric value. -->
   <column name="Client"/>
   <value type="varchar">Client1</value>
</ne>

  <ge>

<column name=" Actual_length_months"/>
<value type="int">12</value>
</ge>

  <gt>

<column name=" Actual_length_months"/>
<value type="int">18</value>
</gt>

b. Comparison operator: in

The use of <in> is slightly different from the above set of comparison operators. This operator requires the user to specify a column and at least one value. It then selects rows that contain the value(s) listed in the specified column.

The following example illustrates the XML query when using <in>:

  <in>
   <column name="Client"/>
   <values>
    <value>Client1</value>
    <value>Client2</value>
    <value>Client3</value>
   </values>
  </in>

c. NULL values

One useful and common filtering operation involves the search for NULL values in your database. In order to do this, we will need to use <ne> and <eq>.

NULL values can be excluded, as shown below:

<ne>
<column name="Client"/>
<value nil="true"/>
</ne>

NULL values can also be included:

<eq>
<column name="Client"/>
<value nil="true"/>
</eq>

d. Using LIKE and wildcards

The <like> filter can search for patterns in the data columns by using wildcard characters:

<like>
<column name="ColumnName"/>
<value>Pattern</value>
</like>

‘Pattern’ is the specific string of characters to search for in the column “ColumnName” and can include the following four wildcard characters.

% will search for any string of characters. For example, the pattern %1 will yield the following result:

A pattern consisting only of % will yield all results in the queried table.

An underscore (_) represents any single character. The example below shows the result obtained if we filter using ‘Project_’.

The square brackets can denote any single character within the specified range. For example, we can search based on a set; [1-3] would be equivalent to [123]. When the pattern is %[1-2] we will obtain all Client entries that send with the numbers 1 or 2.

The square brackets in combination with the carrot (^) will allow the user to search for any single character not within the specified range. To illustrate this, we implement the opposite filter (<value>%[^1-2]</value>) to the one shown above.

e. Using CONTAINS

The <contains> filter allows you to perform keyword searches in your database, in the same way you should use it in a SQL statement (see http://msdn2.microsoft.com/en-us/library/ms187787.aspx). You cannot use <contains> on columns that are not full-text indexed. Full text searches using allow you to also use the NEAR operation, as well as searching for phrases enclosed between double quotes, and wildcards (*) for prefix searches.

Finally, note that performing OR/AND operations within a <contains> filter produces better performance than performing the same operation across two filters. For example, we will obtain the same result with either of the filtering operations below, but the first will give you better performance.

Option 1:

  <or>
   <contains>
<column name="Client"/>
    <value>Client1</value>
   </contains>
   <contains>
<column name="Client"/>
    <value>Client2</value>
   </contains>
  </or>

Option 2:

<contains>
<column name="Client"/>
<value>Client1 OR Client2</value>
</contains>

Sorting

QueryDB’s GetColumnsXMLQuery and GetColumnsXMLStrQuery allow users to sort the results. When using multiple entries, the results will be sorted by each of the columns listed, in the order listed. Sorting by:

<sort>
<column name="Actual_length_months" order="ASC"/>
<column name="Estimated_length_months" order="ASC"/>
</sort>

Yields:

Using DBXL Resources

ErnestoM — Thu, 24 Jul 2008 23:24:36 GMT

The DBXLResource API allows users to use custom XML in their DBXL Document Type Configurations. As explained in the scenarios below, this grants the user to customization flexibility. The scenarios explain situations in which the administrator sets and controls the XML and situations in which the users are allowed to customize their XML resource.

In default DBXL v2.2 installations, the methods can be found in http://<servername>/QdabraWebService/DBXLResource.asmx. Note that users must have DocType Level Admin permissions to create, modify and delete resources.

SampleData

The following table shows the sample data that will be used throughout the scenarios in this document.

Scenario 1: Resource uses an existing doc id

In this scenario, an administrator creates two DocTypes, where one will manage settings for the other. The settings will apply to all users; therefore, AllowUserVersions will be set to False. This set up is useful for server migrations, when DocIDs are likely to change. Instead of hard coding the DocID value into the InfoPath form, the resource will allow the user to change the DocID, as necessary, using ModifyResource.

Follow these steps to set up this scenario.

1. Create the Configuration Document Type

a) Create a new, blank InfoPath form.
b) In the Design Tasks taskpane, click on Controls, then click on Multiple-Selection List Box.
c) Double-click on the control that was inserted in the view and change the Field name to projects.
d) In the List box entries section, select Look up values from an external data source and click Add. This will open the Data Connection Wizard.
e) In the first screen, the new connection will automatically be set to receive data. Click Next.
f) Select Web Service and click Next.
g) Enter the URL of the QueryDB service, which in default installations is http://<servername>/QdabraWebService/QueryDB.asmx. Click Next.
h) Select GetDropdownEntries and click Next.
i) Enter the parameters for your data connection. In our example, they can be seen in the screenshot shown in Figure 1.

Figure 1

j) When done, click Next twice, then click Finish, making sure that the checkbox for Automatically retrieve data when form is opened is checked.
k) Click the Select Xpath button to the right of the Entries field.
l) Expand the nodes and select the GetDropdownEntries/entry node. Click OK.
m) Click the Select Xpath button to the right of the Display name field, select the display node and click OK.
n) Click OK to exit the Multiple-Selection List Box properties screen.
o) Create a button that submits the form to DBXL. For instructions on doing this, see the document called <TITLE AND LINK HERE>.
p) Click on File > Save As and save the form to a local folder. For the purposes of this example, we are naming the form Config-Scenario1.xsn. A preview of the form might look as seen in Figure 2.

2. Create the Target Document Type

a) Right click on the projects control and select Change to > More.
b) Select Drop-down List Box and click OK.
c) Click on File > Save As and save the form to a local folder. For the purposes of this example, we are naming the form Target-Scenario1.xsn.
d) Close InfoPath.

3. Create the configurations in DAT

a) Open the DBXL Administration Tool, which in default installations of DBXL is located in http://<servername>/QdabraWebService/Forms/DAT/template.xsn.
b) Click on New Configuration.
c) Attach Config-Scenario1.xsn in the InfoPath Form Template field.
d) Name this ConfigDocType-Scenario1 and click Save. Click OK in the confirmation message, then click on Catalog.
e) Click on New Configuration.
f) Attach Target-Scenario1.xsn in the InfoPath Form Template field.
g) Name this TargetDocType-Scenario1 and click Save. Click OK in the confirmation message, then click on Catalog.

4. Add a document to the ConfigDocType

a) Click on the Open icon for ConfigDocType-Scenario1.
b) Check the boxes for projects number 1, 3 and 5.
c) Click Submit and then close the form.
d) In DAT, click Edit for ConfigDocType-Scenario1 and then click on the Documents tab.
e) Note the DocID of the document submitted. We will use this in the next step.
5. Create the resource
a) Create a new, blank form in InfoPath.
b) Click on Tools > Data Connection, and then click Add.
c) Select Receive Data and click Next. Then select Web Service and click Next.
d) Enter the URL of the Resource service, which in default installations is http://<servername>/QdabraWebService/DBXLResource.asmx. Click Next.
e) Select CreateResource and click Next.
f) Enter the parameters as seen in Figure 3, noting that the value for DocID may vary.

Figure 3

g) Click Next twice, then click on Finish and Close to exit.
h) To see the result of the query drag the Success node, /dfs:myFields/dfs:dataFields/tns:CreateResourceResponse/tns:CreateResourceResult/tns:Success, into the view.
i) Click Preview to execute the query. You will see the Success checkbox shown as checked. This means the resource has been successfully created. You can now close this form without saving.

6. Modify the TargetDocType to use the resource

a) Open the form TargetDocType-Scenario1.xsn in Design mode in InfoPath.
b) Double-click on the projects dropdown control in the view.
c) In the List box entries section, select Look up values from an external data source.
d) Click on Add. This will open the Data Connection Wizard.
e) In the first screen, the new connection will automatically be set to receive data. Click Next.
f) Select Web Service and click Next.
g) Enter the URL of the DBXLResource service and click Next.
h) Select GetResourceXml and click Next.
i) Enter the docTypeName and resourceName parameters and click Next. These must match the settings in Figure 3. You will be asked again to enter the same parameters.
j) Click Next, then click Finish, making sure that the checkbox for Automatically retrieve data when form is opened is checked.
k) In the Data Source dropdown, change the selection to GetDropdownEntries.
l) Click the Select Xpath button to the right of the Entries field.
m) Expand the nodes and select the /dfs:myFields/dfs:dataFields/tns:GetDropdownEntriesResponse/tns:GetDropdownEntriesResult/tns:entry node.
n) Click on Filter Data, then click on Add.
o) In the first dropdown, choose Select a field or group, then select the /dfs:myFields/dfs:dataFields/tns:GetDropdownEntriesResponse/tns:GetDropdownEntriesResult/tns:entry/@value node.
p) In the middle dropdown, leave the selection as is equal to.
q) In the third dropdown, choose Select a field or group.
r) Change the Data Source to GetResourceXml and select the /dfs:myFields/dfs:dataFields/ns1:GetResourceXmlResponse/ns1:resourceXml/my:myFields/my:group1/my:projects node, as seen in Figure 3. Click OK three times.
s) Click the Select Xpath button to the right of the Display name field.
t) Select the display node and click OK twice.
u) Save and close the form.

7. Verify the result

a) In Catalog view in DAT, click to Edit the TargetDocType-Scenario1.
b) Attach the updated Target-Scenario1.xsn and click Save. Click OK in the confirmation message, then click on Catalog.
c) Click on the Open icon for TargetDocType-Scenario1.
d) The form will open and the dropdown will only show the three projects selected in the ConfigDocType-Scenario1, as seen in Figure 4.

Figure 4

To modify the resource, we can use ModifyResource. Here is an example.

8. Add a second document to the ConfigDocType.

a) In DAT, click on the Open icon for ConfigDocType-Scenario1.
b) Check the boxes for projects number 2 and 4.
c) Click Submit and then close the form.
d) In DAT, click Edit for ConfigDocType-Scenario1 and then click on the Documents tab.
e) Note the DocID of the document submitted. We will use this in the next step.

9. Modify the resource

a) Design a new, blank form in InfoPath.
b) Click on Tools > Data Connection, and then click Add.
c) Select Receive Data and click Next. Then select Web Service and click Next.
d) Enter the URL of the Resource service, which in default installations is http://<servername>/QdabraWebService/DBXLResource.asmx. Click Next.
e) Select ModifyResource and click Next.
f) Enter the parameters as seen in Figure 3, but change the value for DocID to the one created in step 8.
g) To see the result of the query drag the Success node, /dfs:myFields/dfs:dataFields/tns:ModifyResourceResponse/tns:ModifyResourceResult/tns:Success, into the view.
h) Click Preview to execute the query. You will see the Success checkbox shown as checked. This means the resource has been successfully modified.

10. Check the result by clicking on the Open icon for TargetDocType-Scenario1. The form will open and the dropdown will only show the two projects selected in the new document created for ConfigDocType-Scenario1 in step 8.

Note that during server migrations, the DocID may change. This setup allows you to quickly change these references in your new environment.

To delete the resource, call DeleteResource. However, note that deleting the resource will break your form, which you will need to edit manually to fix the data connections.

Scenario 2: Resource uses xml

In this scenario, an administrator creates one DocType and sets up an Xml resource to control settings in the DocType. The settings will apply to all users; therefore, AllowUserVersions will be set to False. This avoids the use of a separate DocType, which is economical for licensing purposes. When setting up this scenario, the DocID parameter in CreateResource must be set to 0.

Before we begin, open Target-Scenario1.xsn in design mode in InfoPath and save it as Target-Scenario2.xsn, so that we don’t overwrite our previous work. For the same reasons, save Config-Scenario1.xsn as Config-Scenario2.xsn.

1. Setup the Target Document Type in DAT.

a. Open the DBXL Administration Tool.
b. Click on New Configuration.
c. Attach Target-Scenario2.xsn in the InfoPath Form Template field.
d. Name this TargetDocType-Scenario2 and click Save. Click OK in the confirmation message, then click on Catalog.
2. Create the resource for TargetDocType-Scenario2.
a. Create a new, blank form in InfoPath.
b. Click on Tools > Data Connection, and then click Add.
c. Select Receive Data and click Next. Then select Web Service and click Next.
d. Enter the URL of the Resource service and click Next.
e. Select CreateResource and click Next.
f. Enter the parameters as seen in Figure 5, noting that the value for DocID is now 0.

Figure 5

g. Click Next twice, then click on Finish and Close to exit.
h. To see the result of the query drag the Success node, /dfs:myFields/dfs:dataFields/tns:CreateResourceResponse/tns:CreateResourceResult/tns:Success, into the view.
i. Click Preview to execute the query. You will see the Success checkbox shown as checked. This means the resource has been successfully created. You can now close this form without saving.

3. Create an auxiliary XML file.

a. In a text editor, such as Notepad, create an XML file, as seen below:

<config>
<doctypename>TargetDocType-Scenario2</doctypename>
<resourcename>SelectedProjects</resourcename>
</config>

b. Save the file as configuration2.xml.

4. Add the auxiliary XML file to Config-Scenario2.xsn

a. Open Config-Scenario2.xsn in design mode in InfoPath.
b. Click on Tools > Data Connections.
c. Select Add, then Receive Data, and click Next.
d. Select XML Document and click Next.
e. Select the document created in step 3 by using the Browse button. Click Next when the file is selected.
f. Opt to Include the data as a resource file in the form template or template part, as well as Automatically retrieve data when from is opened. Finish the wizard.

5. Modify the button to submit to SaveResourceXml

a. Double click on the Submit button and click on Submit Options.
b. Click Add. This will open the Data Connection Wizard.
c. Enter the URL for DbxlResource.asmx and click Next.
d. Select SaveResourceXml and click Next.
e. You will be shown a screen with three parameters.

i. Double click on the docTypeName parameter, then select the doctypename node from the configuration (Secondary) data connection.
ii. Double click on the resourceName parameter, then select the resourcename node from the configuration (Secondary) data connection.
iii. For both fields, select to Submit data as string.
iv. Double click on the resourceXml parameter and select the group that contains the projects in the Main Data source, as seen in Figure 6.

Figure 6

v. Change the Include dropdown for the resourceXml parameter to select XML subtree, including selected element.

Figure 7

f. Click Next, then click Finish.
g. In the Choose a data connection for submit, make sure that this new connection is selected. By default, InfoPath selects this data connection you have just created. Click OK twice.

6. Generate the initial XML for your resource.

a. Click on Preview for Config-Scenario2.xsn.
b. Select the projects you wish to include and click on Submit.
c. Close the Preview, then save and close the file.

7. Modify the data connection in the form

a. Open Target-Scenario2.xsn in design mode in InfoPath.
b. Click on Tools > Data Connections.
c. Click to Modify the Data Connection called GetResourceXml.
d. Click Next until you reach the parameter-entry screen.
e. Modify the docTypeName parameter to TargetDocType-Scenario2 and finish the wizard.

8. Modify the filter for the dropdown.

a. Double click on the dropdown control.
b. Click on the Select XPath icon next to the Entries field.
c. In the Select a Field or Group dialog, click on Filter Data.
d. Click Remove to remove the existing filter (used in Scenario 1), then click Add.
e. In the first dropdown, choose Select a field or group, then select the value node.
f. In the middle dropdown, leave the selection as is equal to.
g. In the third dropdown, choose Select a field or group.
h. Change the Data Source to GetResourceXml and select the projects node. Click OK four times.
i. Save and close the form.

9. Verify the result

a. Click to Edit the TargetDocType-Scenario2 configuration in DAT.
b. Attach the updated Target-Scenario2.xsn form and click Save. Click OK on the confirmation dialog, then click Catalog.
c. Click to open TargetDocType-Scenario2 and verify that the projects selected in step 6 are shown in the dropdown, as seen in Figure 8.

Figure 8

To modify the resource, we can use SaveResourceXml to update the xml. One simple way is to open Config-Scenario.xsn, select a different set of projects, and submit the form. Alternatively, you may wish to design a form that:

1. Reads the current xml using GetResourceXml
2. Displays fields that allow the administrator to modify the values.
3. Updates the resource Xml by using SaveResourceXml.

Scenario 3: Resource uses another DocID and user versions

In this scenario, an administrator creates two DocTypes, where one will manage settings for the other. The users will be allowed to save individual versions of the XML; therefore, AllowUserVersions will be set to True. The original XML is stored in the ConfigDocType; user versions are stored in a SQL table.

Like Scenario 1, this setup is useful for server migrations, when DocIDs are likely to change. Instead of hard coding the DocID value into the InfoPath form, the user can change the resource’s reference DocID as necessary.

Before we begin, open Target-Scenario1.xsn in design mode in InfoPath and save it as Target-Scenario3.xsn, so that we don’t overwrite our previous work. For the same reasons, save Config-Scenario1.xsn as Config-Scenario3.xsn.

1. Create the configurations in DAT

a. Open the DBXL Administration Tool.
b. Click on New Configuration.
c. Attach Config-Scenario3.xsn in the InfoPath Form Template field.
d. Name this ConfigDocType-Scenario3 and click Save. Click OK in the confirmation message, then click on Catalog.
e. Click on New Configuration.
f. Attach Target-Scenario3.xsn in the InfoPath Form Template field.
g. Name this TargetDocType-Scenario3 and click Save. Click OK in the confirmation message, then click on Catalog.

2. Add a document to the ConfigDocType

a. Click on the Open icon for ConfigDocType-Scenario3.
b. Check the boxes for projects number 1, 3 and 5.
c. Click Submit and then close the form.
d. In DAT, click Edit for ConfigDocType-Scenario3 and then click on the Documents tab.
e. Note the DocID of the document submitted. We will use this in the next step.

3. Create the resource.

a. Create a new, blank form in InfoPath.
b. Click on Tools > Data Connection, and then click Add.
c. Select Receive Data and click Next. Then select Web Service and click Next.
d. Enter the URL of the Resource service and click Next.
e. Select CreateResource and click Next.
f. Enter the parameters as seen in Figure 9.

Figure 9

g. Click Next twice, then click on Finish and Close to exit.
h. To see the result of the query drag the Success node, /dfs:myFields/dfs:dataFields/tns:CreateResourceResponse/tns:CreateResourceResult/tns:Success, into the view.
i. Click Preview to execute the query. You will see the Success checkbox shown as checked. This means the resource has been successfully created. You can now close this form without saving.

4. Modify the resource data connection in TargetDocType

a. Open Target-Scenario3.xsn in design mode in InfoPath.
b. Click on File > Data Connections.
c. Select GetResourseXml and click on Modify. Click Next in the first screen.
d. Select GetUserResourceXml and click Next.
e. Enter the DocTypeName and ResourceName and click Next until the wizard is complete, then click Finish, making sure that the Automatically retrieve data when form is opened is checked. For convenience, make sure the Data Connection is named GetUserResourceXml.

5. Create an auxiliary XML file.

a. In a text editor, such as Notepad, create an XML file, as seen below:

<config>
<doctypename>TargetDocType-Scenario3</doctypename>
<resourcename>SelectedProjects</resourcename>
</config>

b. Save the file as configuration3xml.

6. Create a user configuration form.

a. Open Config-Scenario3.xsn in design mode in InfoPath.
b. Save the form as Config-Scenario3User.xsn.

7. Add the auxiliary XML file to Config-Scenario3User.xsn

a. Click on Tools > Data Connections.
b. Select Add, then Receive Data, and click Next.
c. Select XML Document and click Next.
d. Select the xml document by using the Browse button. Click Next when the file is selected.
e. Opt to Include the data as a resource file in the form template or template part, as well as Automatically retrieve data when from is opened. Finish the wizard.

8. Modify the button to submit to SaveUserResourceXml

a. Double click on the button and change its label to SaveUserResourceXml.
b. Select Submit from the Action dropdown, and then click on Submit Options.
c. Click Add. This will open the Data Connection Wizard.
d. Enter the URL for DbxlResource.asmx and click Next.
e. Select SaveUserResourceXml and click Next.
f. You will be shown a screen with three parameters.

i. Double click on the docTypeName parameter, then select the doctypename node from the configuration (Secondary) data connection.
ii. Double click on the resourceName parameter, then select the resourcename node from the configuration (Secondary) data connection.
iii. Double click on the resourceXml parameter and select the group that contains the projects node in the Main Data source. Note that this XML tree being submitted must match exactly with the XML of the document submitted in step 2.
iv. Change the Include dropdown for the resourceXml parameter to select XML subtree, including selected element.

g. Click Next, then click Finish.
h. In the Choose a data connection for submit, make sure that this new connection is selected. By default, InfoPath selects this data connection you have just created. Click OK twice.
i. Save and close the file.

9. Verify the SaveUserResourceXml button

a. Click to Edit the TargetDocType-Scenario3 configuration in DAT.
b. Attach the updated Target-Scenario3.xsn form and click Save. Click OK on the confirmation dialog, then click Catalog.
c. Click to open TargetDocType-Scenario3. It will load with the projects you, as admin, selected in step 2. Close the form.
d. Open the Config-Scenario3User.xsn form, select a different set of projects and then click the SaveUserResourceXml button. You will be notified, thru a dialog, that the form was submitted.
e. Click to open TargetDocType-Scenario3. It will load with the projects you selected, as a regular user, in step 9(d). Close the form.

10. Create a button that calls ResetUserResourceXml

a. Open Config-Scenario3User.xsn in design mode in InfoPath.
b. Insert a new button and double click on it.
c. Change its label to ResetUserResourceXml, and then change the Action dropdown to Rules and Custom code.
d. Click on Rules. Click Add. Click Add Action.
e. Select Query using a data connection from the dropdown and click Add. This will open the Data Connection Wizard.
f. In the first screen, click Next. Select Web service and click Next.
g. Enter the URL for DbxlResource.asmx and click Next.
h. Select ResetUserResourceXml and click Next.

i. You will be shown a screen with two parameters.
i. For docTypeName parameter, type in the name of the document type, which is TargetDocType-Scenario3.
ii. For the resourceName parameter, enter the name of the resource, which in this example is SelectedProjects.

j. Click Next. Make sure to uncheck the checkbox for Automatically retrieve data when from is opened. Finish the wizard.
k. In the Action screen, make sure that this new data connection is selected in the Data connection dropdown, and then click OK until all dialogs are closed.

11. Verify the ResetUserResourceXml button

a. Click Preview for the Config-Scenario3User.xsn form and click the ResetUserResourceXml button. This resets this users ResourceXml to the one originally saved by the admin in step 2.
b. Click to open TargetDocType-Scenario3. It will load with the projects you, as admin, selected in step 2. Close the form.

Note that the user is automatically detected (and stored in SQL) when we call SaveUserResourceXml and GetUserResourceXml, so there are no additional steps on our part regarding the users.

Scenario 4: Resource uses xml and stores custom user settings

In this scenario, an administrator creates one DocType and sets up an xml resource to control settings in the DocType. The individual users will be allowed to modify and save personal settings; therefore, AllowUserVersions will be set to True. This scenario, like Scenario 2, avoids the use of a separate DocType, which is economical for licensing purposes. When setting up this scenario, the DocID parameter in CreateResource must be set to 0.

Before we begin, open Target-Scenario3.xsn in design mode in InfoPath and save it as Target-Scenario4.xsn, so that we don’t overwrite our previous work. For the same reasons, save Config-Scenario3.xsn as Config-Scenario4.xsn and Config-Scenario3User.xsn as Config-Scenario4User.xsn

1. Create the configuration in DAT

a. Open the DBXL Administration Tool.
b. Click on New Configuration.
c. Attach Target-Scenario4.xsn in the InfoPath Form Template field.
d. Name this TargetDocType-Scenario4 and click Save. Click OK in the confirmation message, then click on Catalog.

2. Create the resource.

a. Create a new, blank form in InfoPath.
b. Click on Tools > Data Connection, and then click Add.
c. Select Receive Data and click Next. Then select Web Service and click Next.
d. Enter the URL of the Resource service and click Next.
e. Select CreateResource and click Next.
f. Enter the parameters as seen in Figure 10.

Figure 10

g. Click Next twice, then click on Finish and Close to exit.
h. To see the result of the query drag the Success node, /dfs:myFields/dfs:dataFields/tns:CreateResourceResponse/tns:CreateResourceResult/tns:Success, into the view.
i. Click Preview to execute the query. You will see the Success checkbox shown as checked. This means the resource has been successfully created. You can now close this form without saving.

3. Create a new auxiliary XML file.

a. In a text editor, such as Notepad, create an XML file, as seen below:

<config>
<doctypename>TargetDocType-Scenario4</doctypename>
<resourcename>SelectedProjects</resourcename>
</config>

b. Save the file as configuration4.xml.

4. Add the auxiliary XML file to Config-Scenario4.xsn

a. Click on Tools > Data Connections.
b. Select Add, then Receive Data, and click Next.
c. Select XML Document and click Next.
d. Select the xml document by using the Browse button. Click Next when the file is selected.
e. Opt to Include the data as a resource file in the form template or template part, as well as Automatically retrieve data when from is opened. Finish the wizard.

5. Modify the GetUserResourceXml connection in Target-Scenario4.xsn

a. Open Target-Scenario4.xsn in design mode in InfoPath.
b. Click on File > Data Connections.
c. Select GetUserResourseXml and click on Modify. Click Next twice.
d. Change the DocTypeName to TargetDocType-Scenario4 and click Next until the wizard is complete, then click Finish, making sure that the Automatically retrieve data when form is opened is checked. For convenience, make sure the Data Connection is named GetUserResourceXml.

6. Modify Config-Scenario4.xsn to submit to TargetDocType-Scenario4.

a. Double click on the Submit button and change its label to SaveResourceXml.
b. Click on Submit Options.
c. Click Add. This will open the Data Connection Wizard.
d. Enter the URL for DbxlResource.asmx and click Next.
e. Select SaveResourceXml and click Next.
f. You will be shown a screen with three parameters.

i. Double click on the docTypeName parameter, then select the doctypename node from the configuration (Secondary) data connection. Opt to submit this field as a string.
ii. Double click on the resourceName parameter, then select the resourcename node from the configuration (Secondary) data connection. Opt to submit this field as a string.
iii. Double click on the resourceXml parameter and select the group that contains the projects node in the Main Data source. Note that this XML tree being submitted must match exactly with the XML of the document submitted in step 2.
iv. Change the Include dropdown for the resourceXml parameter to select XML subtree, including selected element.

g. Click Next, then click Finish.
h. In the Choose a data connection for submit, make sure that this new connection is selected. By default, InfoPath selects this data connection you have just created. Click OK twice.
i. Save and close the file.

7. Modify the auxiliary XML file in Config-Scenario4User.xsn

a. Click on Tools > Data Connections.
b. Select the configuration xml and click Modify.
c. Select the updated configuration4.xml using the Browse button and click Next.
d. Opt to Include the data as a resource file in the form template or template part, as well as Automatically retrieve data when from is opened. Finish the wizard.
e. Save and close the Config-Scenario4User.xsn file.

8. Use Config-Scenario4.xsn to generate the initial xml.

a. Open Config-Scenario4.xsn, select a set of projects and click on SaveResourceXml. A dialog box will confirm the success of the operation.
b. Close the form.

9. Verify the result

a. In DAT, click to Edit the configuration for TargetDocType-Scenario4. Attach the updated form and click Save.
b. Open TargetDocType-Scenario4 and verify that it shows only the projects selected in step 7. Close the form.
c. Open Config-Scenario4User.xsn, select a different set of projects and submit.
d. Open TargetDocType-Scenario4 from DAT to see the form showing the selections made in (c).

10. Create a button that calls ResetUserResourceXml

a. Open Config-Scenario4User.xsn in design mode in InfoPath.
b. Insert a new button and double click on it.
c. Change its label to ResetUserResourceXml, and then change the Action dropdown to Rules and Custom code.
d. Click on Rules. Click Add. Click Add Action.
e. Select Query using a data connection from the dropdown and click Add. This will open the Data Connection Wizard.
f. In the first screen, click Next. Select Web service and click Next.
g. Enter the URL for DbxlResource.asmx and click Next.
h. Select ResetUserResourceXml and click Next.

i. You will be shown a screen with two parameters.
iii. For docTypeName parameter, type in the name of the document type, which is TargetDocType-Scenario4.
iv. For the resourceName parameter, enter the name of the resource, which in this example is SelectedProjects.

j. Click Next. Make sure to uncheck the checkbox for Automatically retrieve data when from is opened. Finish the wizard.
k. In the Action screen, make sure that this new data connection is selected in the Data connection dropdown, and then click OK until all dialogs are closed.

11. Verify the ResetUserResourceXml button

a. Click Preview for the Config-Scenario4User.xsn form and click the ResetUserResourceXml button. You will be notified, thru a dialog, that the form was submitted.
b. Click to open TargetDocType-Scenario4. It will load with the projects you, as admin, selected in step 8. Close the form.

Futher work

Here are two ideas for developers to consider when using the DBXLResources feature:

1. Incorporate Config xsns as a separate view in the Target document types for scenarios 3 and 4. When attempting this, remember that the individual users need to submit an xml structure that is identical to the one submitted by the admin using SaveResourceXml.

2. Improve the Config xsns from Scenarios 2, 3 and 4 to automatically display the currently-saved xml. That is, the checkboxes are right now being shown as unchecked, thus not allowing the user to see what the current version of the xml looks like. Ideally, we the developer will show the current version and submit changes to overwrite.

How To Query the Active Directory Web Service

ErnestoM — Tue, 22 Jul 2008 15:13:52 GMT

The Active Directory Service supports two methods: GetUserInfo and GetMyInfo. This document will show you how to set up a sample form that queries GetUserInfo with a user-entered username string. For a fully-formatted Word document, click the Download button above.

Setup the Active Directory Web Service

1. To confirm that the Active Directory Web Service is working on your server, open Internet Explorer from the machine where DBXL is installed and navigate to the Web Service page on http://<servername>/QdabraWebService/ADUserInfo.asmx

Figure 1

2. Click the GetMyInfo link.
3. Click the Invoke link.
4. A new Internet Explorer window will open and display the user information.

Query GetUserInfo

1. Open InfoPath and choose to Design a new blank form.
2. Create a new data connection to the Active Directory Web Service:

a. Click on Tools > Data Connections > Add. This will open the Data Connection Wizard.
b. Select Receive Data and click Next.
c. Select To a Web service and click Next.
d. Type in the URL for the Active Directory Web Service (for example http://<servername>/QdabraWebService/ADUserInfo.asmx) and click Next.
e. Select the GetUserInfo method and click Next.
f. In the parameters window, click Next.
g. Do not check Store a copy of the data in the form template and click Next.
h. Uncheck Automatically retrieve data when form is loaded and click Finish.
i. Finally, close the Data Connections window.

3. Create the form using the appropriate fields.

a. Navigate to Data Source in the task pane.
b. Change Main in the dropdown to GetUserInfo (Secondary).
c. Expand the queryFields node until you see username.
d. Drag username from the Data Source to the view.
e. Expand dataFields until you see ADProp.
f. Drag the ADProp node into the view and choose repeating table.

Figure 2

4. Add a Rules button to query the service:

a. Navigate to Controls in the task pane and click Button. This will add a Button control into the view.
b. Double-click the newly added button in the view. Change the label to Run Query.
c. Click Rules, then click Add, then click Add Action.

Figure 3

d. Select Query using a data connection from the dropdown. The data connection created previously will show up in the Data Connection dropdown. Click OK until the dialogs have been closed.

5. See the form in action.

a. Click Preview.
b. Enter the username for a user in your domain, and then click the Run Query button.

The user’s information from your Active Directory will be displayed. This will allow you to see all the Keys available, which we will use in the next section of this document.

Query GetMyInfo

The following steps will illustrate how to set the value of a field in your form to a value returned by the GetMyInfo method of the Active Directory Web.

1. Open InfoPath and choose to Design a new blank form.
2. Create a new data connection to the Active Directory Web Service:

a. Click on Tools > Data Connections > Add. This will open the Data Connection Wizard.
b. Select Receive Data and click Next.
c. Select To a Web service and click Next.
d. Type in the URL for the Active Directory Web Service (for example http://servername/qdabrawebservice/ADUserInfo.asmx) and click Next.
e. Select the GetMyInfo method and click Next.
f. Do not check Store a copy of the data in the form template and click Next.
g. Check Automatically retrieve data when form is loaded and click Finish.
h. Finally, close the Data Connections window.

3. Create the field and the rule to set its value.

a. Navigate to Controls in the task pane and click on Text Box.
b. Double-click on the textbox field in the view.
c. In the Default Value section, click on the fx button to the right.
d. Click Insert Field or Group.
e. Select the GetMyInfo (Secondary) data source from the dropdown.
f. Expand the xml tree to reach the node dataFields/tns:GetMyInfoResponse/GetMyInfoResult/ADProp
g. Select Value and click Filter Data.

Figure 4

h. In the Specify Filter Conditions dialog, click Add.
i. Select Key in the first drop down.
j. Leave the second dropdown set to is equal to.
k. In the third dropdown type the text "cn" (or whatever key you prefer) without the double quotes. Notice that InfoPath adds double quotes after you type and tab out.

Figure 5

l. Click OK to close every dialog.
m. Preview the form. The value for the key you selected is automatically loaded to the textbox.

How To Perform Bulk Edit operations on Existing Documents

ErnestoM — Fri, 11 Jul 2008 21:07:24 GMT

This document is a guide for using the BulkEditDocuments, EditAllDocuments, EditDocuments, TestAllDocuments and TestEditDocuments web methods found on the DbxlAdmin.asmx web service. These methods are intended for admin-use only and should be used with care, as one Bulk Edit operation may impact a large number of documents.

Overview

Intended Use

These methods are intended to be used by an administrator or developer who needs to make a change to a node in the XML of several documents stored within DBXL. A single call can update nodes in several documents at once without ever having to open InfoPath.

Bulk Behavior

The batch operation performs a skip-on-error iteration for the documents. If a document in the batch fails the edit for some reason (node does not exist), then an error is added to the dataFields’ return with details, and the next iteration is executed.

The Success node within dataFields only returns true if all documents were edited without error, otherwise it returns false.

The total number of changes performed is returned as an ErrorInfo node in dataFields. This count includes all changes within each document, so if you are changing a repeating node this number could be several times greater than the number of documents that are edited.

Permissions

Permissions are handled implicitly by the SetDocument method. The user who calls one of the EditDocuments methods is the user used for making comparisons for permissions purposes.

EditAllDocuments

This method performs the specified edits to all documents within the specified Document Type.

Parameters

• docTypeName – the name of the Document Type you wish to edit documents within
• xPaths – Each string passed is used to find the node(s) within each document to insert the corresponding new value into. Full freedom of axes and comparison constructs are allowed in each xPath. The number of xPath nodes must match the number of newValue nodes. An xPath may contain conditional logic so that only certain nodes are updated.
• newValues – Each string passed is inserted into the node specified by the corresponding xPath. The number of newValue nodes must match the number of xPath nodes.

FIGURE 1

Return

• Success – returns true when all documents where edited correctly, false otherwise.
• ErrorInfo – used to pass errors encountered when editing the documents, as well as returning information about the number of edits made.
• ExecutionTime – the time taken to execute the call to the web method.

TestEditAllDocuments

TestEditAllDocuments works in the same way as EditAllDocuments, but changes are not committed. The total changes are calculated and returned.

EditDocuments

This method performs the specified edits to all documents specified by the list of docIds. Generally, the documents are in the same Document Type, but this is not enforced by the web method.

Parameters

• docIds – A list of docIds to perform the edits on.
• xPaths – Each string passed is used to find the node(s) within each document to insert the corresponding new value into. Full freedom of axes and comparison constructs are allowed in each xPath. The number of xPath nodes must match the number of newValue nodes. An xPath may contain conditional logic so that only certain nodes are updated.
• newValues – Each string passed is inserted into the node specified by the corresponding xPath. The number of newValue nodes must match the number of xPath nodes.

FIGURE 2

Return

• Success – returns true when all documents where edited correctly, false otherwise.
• ErrorInfo – used to pass errors encountered when editing the documents, as well as returning information about the number of edits made.
• ExecutionTime – the time taken to execute the call to the web method.

TestEditDocuments

TestEditDocuments works in the same way as EditDocuments, but the changes are not committed. The total changes are calculated and returned.

BulkEditDocuments

This method abstracts calls to EditDocuments to allow multiple edits to be performed with one call to the web service.

Parameters

• EditDocuments – Each object passed here represents a distinct call to EditDocuments, where ID is a value simply provided for the user to distinguish between calls to the service.

FIGURE 3

Return

• Success – returns true when all documents where edited correctly, false otherwise.
• ErrorInfo – used to pass errors encountered when editing the documents, as well as returning information about the number of edits made.
• ExecutionTime – the time taken to execute the call to the web method.

Editing the DBXL MetaData Values

DBXL uses Mapping Tokens, as seen in the Documents tab of DAT. The data displayed in the catalog view of your qForm-based solution uses this same data during creation and update of a document, but the Bulk Edit Tool must perform two separate operations to maintain the two values in sync.

FIGURE 4

For example, in the default catalog view for a qForm, there is a checkbox to show the document’s descriptions. The value shown in the Documents tab of DAT and in the Catalog view of the qForm can be changed by altering the value for DBXL::Description. If you make this change, then open the document’s XML, you’ll see the value in the Description field in the form hasn’t changed. That value must be changed by modifying the value for /my:QdCatalog/my:QdForm/@my:description.

The table below shows the Xpath values for the DBXL Mapping tokens:

Note that the only comparison operation available when updating the MetaData values is testing the previous value.

Example:

xPath = DBXL::Name[ . = ‘Matt’ ]
newValue = Bob

In this example, only the name values that currently have the value of ‘Matt’ will be changed to the new value of ‘Bob’.

How To Perform a Codeless Submit to DBXL

ErnestoM — Fri, 04 Jul 2008 22:26:36 GMT

When a document is submitted to DBXL it is allocated a unique DocID which can then be used to access that document. There is a codeless solution that will return the DocID back to the form, which involves using a query-type data connection. This document takes the submit operation one step beyond that described in the document called How to Configure a Form to Submit to DBXL Webservice.

Note that this does not work with repeating values; for forms involving these, we recommend using code so that errors are properly handled.

1. In your InfoPath form, create a query-type web service connection to SubmitDocument

a. In design mode in InfoPath, click Tools > Data Connections.
b. Click Add and select a connection that receives data. Click Next.
c. Select a Web Service connection and click Next.
d. Enter the path to the DBXL Document Service, http://<servername>/QdabraWebService/DBXLDocumentService.asmx, and click Next.
e. Select SubmitDocument and click Next.
f. Enter the DocTypeName parameter, leave the other parameters blank and then click Next. This will be the name of the configuration (Document Type) in DBXL.
g. Do not check the box for Store a copy of the data in the form template.
h. Click Next, uncheck the box to Automatically retrieve data when form is opened, then click Finish to close out the wizard.
i. Click Close to exit the Data Connections dialog.
j. For demonstration purposes, create one field, a text box called customer.

2. Save an output XML file.

a. Preview your form.
b. Click on File > Save as to save the XML file.
c. Close the form preview.
d. Open the XML file in a text editor, such as Notepad, and divide the xml into segments, as seen in Figure 1.

Figure 1

e. Create a concat statement, which we will use in the next step. In the case of Figure 1, the statement will look like this: concat(‘segment1’, <customer field>, ‘segment2’). In our example, our form only contains one field. More complex forms will contain x+1 number of segments, where x is the number of fields in the form.

3. Configure a button that uses rules to call this Data Connection.

a. Insert a button into your form and double click on it.
b. Click on Rules, click on Add and then click on Add Action.
c. Select Set a field’s value.
d. For the field, select SubmitDocument (Secondary) from the Data Source dropdown, then select the node myFields/queryFields/tns:SubmitDocument/xml, as seen in Figure 2.

Figure 2

e. For the Value, insert the concat statement created in 2c.
f. Click OK twice.
g. Click on Add Action.
h. Select Query using a data connection, choosing the SubmitDocument data connection from the dropdown, and then click OK.
i. Click OK until all dialogs are closed.

4. Prepare the form to show the results:

a. Drag the node /dfs:myFields/dfs:dataFields/tns:SubmitDocumentResponse/tns:docId into the form’s view. This will allow us to see the DocID that is returned when the document is submitted.
b. Save the form and close InfoPath.

5. Create a new configuration in the DAT and test it.

a. Click on New Configuration.
b. For the configuration name, enter the same value entered in for the parameter in step 1f.
c. Attach the form and click Save.
d. Click OK in the confirmation dialog.
e. Open the form by clicking the Open link.
f. Fill out and click the button. The DocID that is allocated to the document is returned in the DocID node that was created in step 4.

Configure a Form to Submit to DBXL Webservice

ErnestoM — Wed, 02 Jul 2008 23:43:40 GMT

This exercise is intended for new users of DBXL who want to orient themselves to its features and interface. This exercise will go through configuring a form’s data connection to submit to the DBXL Web Service, verification of success, and accessing the submitted form.

Tutorial
The Qdabra DBXL Web Service stores three metadata properties for all forms: title, author, and description. When InfoPath submits the form to the database, these three fields will be stored to identify the submitted form. You can use a secondary data source or data from within your form to specify these three properties.

The following steps will illustrate how to configure a Submit button to use DBXL’s Submit Document Web Service.

1. Modify the form to incorporate a submit button

a) Click Controls in the right-hand task pane.
b) Click on Button. A button control will appear in your form’s view.
c) Double-click the button to display properties, as seen in Figure 1.

Figure 1

d) Select Submit Options from the Action drop-down.
e) Click the button labeled Submit options.
f) Check the box labeled Allow users to submit this form.
g) From the Send form data to a single destination dropdown.
h) Select Web service from the options in the dropdown, as seen in Figure 2.

Figure 2

i) Click Add to add a new data connection to your form.
j) Enter the name of the Qdabra DBXL Web Service. The URL is http://<servername>/QdabraWebService/DBXLDocumentService.asmx. Click Next. InfoPath will attempt to connect to the web service and show you a list of methods.
k) From the list of methods, select the operation named SubmitDocument and click Next.
l) Skip the first parameter.
m) Select the second parameter, named tns:xml. Select the Entire Form radio button and check the Submit as a string checkbox. The end result can be seen in Figure 3.

Figure 3

n) Select tns:name parameter. Select the radio button Field or group in the Parameters options section. Click the Modify button to the right of the textbox. In the Select a Field or Group dialog box, make sure the Main data source is selected. Select any field from your form; this will be stored as your form name. Click OK.
o) Repeat Step 13 for tns:author and tns:description, selecting fields in your form as appropriate.
p) Click Next. Click Finish. Click OK. Click OK.
q) Save the form and close InfoPath.

2. Modify the configuration in DAT

a) In the DAT, click Edit for the form’s configuration.
b) Attach the recently modified form and click Save.

3. Fill out and submit the form

a) Return to the configuration list and click on Open.
b) Fill out your form and click on your new Submit button.

4. Verify the result

a) Back in DAT, click to Edit the configuration.
b) Click on the Documents tab to verify that the data has been submitted.