Ernesto Machado

Create an InfoPath Dashboard form using QueryDocumentNodeSet

2013-03-19T15:16:00Z

In the past we have shown how to create a SharePoint data view to show the data stored in DBXL. Here are some references:

Create a SQL-based SharePoint Data View to access your DBXL Forms
Create a SharePoint 2010 Data View that uses GetListItems to access DBXL forms
Create a SharePoint Data View that uses GetListItems to access DBXL forms

These steps involve using SharePoint Designer 2007 or 2010. In SharePoint Designer 2013, the WYSIWYG editor is no longer available, which makes the process of creating a data view more difficult.

Using the QueryDocumentNodeSet web method, contained in the DBXL web service, we can create a codeless, browser compatible InfoPath form which acts as a data view or a Dashboard.

The package includes:

Step by step instructions
InfoPathDashboard-QdExpenseReport.XSN which is the Dashboard for the Expense Report doctype that comes with DBXL.
InfoPathDashboard-CustomerContact.XSN is the Dashboard after following the steps to reconfigure it to query from the CustomerContactForm.XSN (that form template is also attached).

Click here to download the package.

Create an XTP to Lock and Unlock DBXL forms

2013-01-18T19:03:00Z

This tutorial allows you to create an InfoPath template part (XTP) that will lock and unlock forms that are stored in DBXL. You can reuse this XTP in any form template where you wish to provide this functionality to your users.

Please click here to obtain the PDF tutorial.

SharePoint Designer Workflow error due to variable in email body

2012-09-12T14:12:00Z

Qdabra Software recently was faced with an error when creating a SharePoint Designer workflow. When sending an email, if you insert a variable into the body of the email, it fails to save or publish the workflow.

For example, if you add the following to the email body:

Then the following error comes up when trying to publish the workflow.

If the variable is removed from the email’s body, there is no problem publishing the workflow.

Qdabra found a workaround for this:

1) Create a string workflow variable. I called mine BlankString.

2) After entering the variables in the email body, open the Properties tab for the email step.

3) Click on the Body textbox and then click the ...

4) Place the cursor after the text </HTML>.

5) Click on Add or Change Lookup and insert the empty string variable.

6) Click OK and publish the workflow.

This simple change will allow you to publish the workflow without issues.

So you have a SharePoint form library that you want to migrate to DBXL...

2012-04-26T14:32:00Z

Let's pretend that you've been using a SharePoint Form Library and you have hundreds of documents (XMLs) already submitted. You found out about DBXL, purchased it and now want to make the full switch.

Here are some general guidelines to help you with the migration.

1. Edit the form template to add a DBXL submit. (You will find information on this in the Getting Started document.) Because you are now submitting to DBXL, you will no longer need to submit to SharePoint directly. DBXL will handle that.

2. Create a DBXL document type. (Again, you will find information on this in the Getting Started document.)

3. Add a SharePoint mapping to a new form library. (That way we keep the original library as a temporary backup in case we need to revert.) For information on creating this SharePoint mapping, see this document.

4. Use SharePoint's Explorer view to copy the XML files from the existing form library to a local folder.

5. Import the XMLs into your document type using the Import web service. This can be done in two ways.

a. Via Internet Explorer on the server where DBXL was installed. Simply navigate to https://training.formsboard.com/QdabraWebService/DbxlImportExport.asmx?op=ImportDocuments to use the Import web service. You will need to provide the name of the document type you created (in step 2) and the path where the XMLs were stored (in step 4)

b. Via the DBXL Admin Tool, in the Documents tab. However, note that this method will fail with a timeout, depending on the number of the documents to be imported.

If these steps do not satisfy your requirements, please contact support at qdabra dot com for additional options. Good luck!

Using SubmitToSharePointList in a MOSS 2007 browser form

2012-04-05T20:28:00Z

We've recently seen some questions about the SubmitToSharePointList command in qRules. One common error is:

Security Exception - please check security requirements for this command.

There are two solutions for this issue:

Set useWebServices node to true. You can do this by adding an action (before the SubmitToSharePointList action) that sets the useWebServices node to true().
Make the form Full Trust. You can change this in Form Options.

How to prevent a user from submitting duplicate data to DBXL

2012-03-06T14:14:00Z

Imagine that your InfoPath form, which submits to DBXL, needs to make sure that there are no duplicate records. Using the web method QueryDocumentNodeSet, we can quickly check whether DBXL already contains a matching record.

For the purposes of this tutorial, we will implement the solution on a very simple form, though you can extend this as desired. You can download the PDF tutorial (13 pages, 950Kb) by clicking here.

How to turn off DBXL versioning

2012-02-03T20:06:00Z

DBXL versioning is turned on by default, as it is necessary for certain scenarios. For safety purposes the checkbox in DAT is also disabled, so that it is not accidentally deactivated. (For a PDF version of this blog post, click here.)

However, should you wish to turn off versioning (i.e. uncheck Enable History), follow these steps:

Export

1.   Download and install the DBXL Migration Tool.
2.   Launch the DBXL Migration Tool (UI).
3.   Enter the DBXL Server Root and click Connect.
4.   From the Configuration dropdown, select your document type.
5.   Check the checkbox for Configuration under Items to Export.
6.   Enter an Export Path.
7.   Click Run.

Manually modify the mapping.xml file

8.   Open the mapping.xml created by the DBXL Migration Tool in the Export path. You can use Notepad or a similar text editor.
9.   Find <HistoryFlg>true</HistoryFlg>
10.   Change this to <HistoryFlg>false</HistoryFlg>
11.   Save and close mapping.xml.

Import

12.   Back in the DBXL Migration Tool UI, switch to the Import tab.
13.   Click Load next to Configuration.
14.   Select the document type from the Configuration dropdown.
15.   Under Items to import, check Configuration. Note: If you had a SQL Mapping configured, you’ll need to enter a value for Database server and Initial Catalog. These will match the Connection string in DAT.
16.   Click Run.

When you go back into the DBXL Administration Tool, you’ll see that the checkbox for Enable History is unchecked!

Reusable Contact Selectors in InfoPath

2012-01-25T18:45:00Z

During Qdabra’s free weekly webinar on November 10, 2011, we discussed three possible contact selectors, their pros and their cons. This follow up blog post will summarize that webinar and provide you with XTP and XSN samples. You can download the supporting files here.

One of the main concerns when using a contact selector is that it should be reusable. After all, you probably will need similar functionality in more than one form. Save time by creating an InfoPath Template Part (XTP) and simply injecting it into your InfoPath form template (XSN)!

People picker

Microsoft provides users with one such reusable contact selector. It’s called the People Picker and it’s available, out of the box, in InfoPath 2010.

This is definitely one advantage: it comes with InfoPath, so there’s nothing to set up, and it will connect with your SharePoint server to find users.

The disadvantage is that the schema is locked. When you add this control to your form, you’ll see that the nodes have a small padlock next to them, meaning that you cannot modify them. You also cannot add more fields under the repeating node (/my:myFields/my:group/pc:Person).

You can perform some configuration on the control, such as allowing more than one user, but it’s not highly customizable.

The control is browser compatible, and the sample form that we include in this package is as well. Simply publish the form to your SharePoint server to test it.

Qdabra’s non-Active Directory Contact Selector XTP

Going one step further we come to a contact selector that still obtains user info from SharePoint, but this one offers more flexibility. Notice that the schema is not locked, meaning that we can modify it as desired.

The XTP uses a connection to SharePoint’s UserProfileService to obtain the user’s info.

But this XTP also has its own drawbacks, namely: you’ll need to search for user's EXACT alias (there is no option for partial matches). In addition, there is no option to list all users. You search for a users alias, and select that user. In the example provided, we are also retrieving the user’s email address.

This XTP uses qRules for some actions, though it would be entirely possible to build a simpler non-Active Directory XTP, without qRules. Therefore, the XSN included in this package is injected with a trial version of qRules. If you have purchased the qRules, you can reinject the XSN with the full version.

The XTP is included in the downloadable package. To use it:
1.   Create a new XSN form template and save it.
2.   Inject your XSN with qRules.
3.   Add the XTP to your Controls, and then insert the XTP your canvas.

Once the XTP is on your canvas, you’ll see some configuration steps you’ll need to follow.

Qdabra’s ContactSelector XTP

The previous two XTPs did not query Active Directory, but Qdabra’s Contact Selector XTP does. Why Active Directory? The Active Directory web service allows us to list all users as well as search for partial matches, and the results provided by this web service contain a wealth of information about the users.

It has been discussed in a previous blog post, explained in a video demo, and it is available for sale on Qdabra’s website. If you have any questions, please contact us at support (at) Qdabra (dot) com.

Extract Attachments from your InfoPath forms

2012-01-13T17:11:00Z

Imagine that you created an InfoPath form template where users can include an attachment. You published the form template to a SharePoint form library, and then your users submitted hundreds of forms. Each of those forms contains attachments, but those attachments are "stuck" inside your xml forms!

Luckily, using the DBXL Migration Tool, you can extract those attachments from your InfoPath forms, and store them in a separate location.

Qdabra Software has put together a package you can download, which includes a PDF guide as well as sample forms. To obtain it, simply click here.

InfoPath 2010 Data Connection Wizard issue when using QueryDB or QuerySharePoint

2012-01-12T14:35:00Z

When using QueryDB or QuerySharePoint in an InfoPath 2010 form, you might encounter behavior that is different from the behavior you are used to in InfoPath 2007. When you get to the screen below, you enter values (per our previously documents for QueryDB and QuerySharePoint). If you click Back in the Data Connection Wizard, the values are removed.

In addition, if you finish the Wizard, and set the data connection to execute on load, the data connection will fail in InfoPath 2010.

This is something that changed from InfoPath 2007 to 2010. InfoPath 2010 behaves differently for some data connections, including QueryDB and QuerySharePoint.

In InfoPath 2007, you could:

Enter the Sample Value in the Data Connection Wizard.
Enter values in the following screen (labeled “Set Value”)
Set the data connection to run on load.
Preview the form and get results as expected.

If you follow the same steps in InfoPath2010, the data connection may fail on load. Therefore, in InfoPath 2010, we recommend the following:

When you create the data connection to QuerySharePoint, set it to NOT execute on load.

In your form’s On Load rules, add a rule that will:

Set the value of the parameters (e.g. listUrl and query) using “Set a field’s value”.
Query using the data connection.

Cannot Preview a form with qRules?

2011-09-15T18:58:00Z

After injecting an InfoPath form with qRules, you might be unable to open the XSN in the designer and then Preview. The error indicates:

InfoPath cannot open the selected form because of an error in the form's code.
Policy settings prevent opening Internet forms with managed code. To fix this problem, contact your system administrator.

We have seen this error:

on machines that are not joined to a domain
after performing an Internet Explorer upgrade, since these updates might reset your settings.

If you are seeing this error, there are two possible fixes to consider.

A. Modify the Internet Explorer security option causing the issue:

1.   Launch Internet Explorer.
2.   Click the Tools button, and then click Internet Options.
3.   On the Security tab of the Internet Options dialog box, select the Local Intranet zone, and then click Sites.
4.   In the Local Intranet dialog box, clear the Automatically Detect Intranet Network check.
5.   Select all other check boxes.

6. Click OK twice.
7. Preview your form.

B. If you are unable to use the solution above, specify a preview domain in the Form Options dialog box of your form template. This forces InfoPath to believe that the form is being opened in the specified domain. The form must be set to Domain Trust for this option to work.

1.   Launch Internet Explorer.
2.   Click the Tools button, and then click Internet Options.
3.   In the Security tab, select the Local Intranet zone, click Sites, and then click Advanced.
4.   Add (or ensure that you've already added) the website to the domain you will use for previewing.
5.   Click Close, and then click OK twice.
6.   Open your InfoPath form in Design mode.
7.   Choose Form Options from the Tools menu.
8.   Select the Preview category.
9.   In the Domain text box, type the website that you added to the Internet Explorer Local Intranet Websites.
10.   Click OK.
11.   Preview your form.

How to iterate a qRules command

2011-09-05T12:51:00Z

This technique comes from one of Qdabra’s own customers, John Shaw, who is a qRules user. This cool idea will iterate the SaveToSharePoint command for multiple file attachments. This tutorial builds on the example show in a previous document by making the command repeating.

1. Create a new, blank form and inject it with qRules. For information on how to do this, please refer to the qRules User Guide.

2. Create the following initial schema, where DetachFile is a Boolean attribute, which defaults to false. For information on the qRulesLink and qRulesFilename, see the qRules User Guide.

3. Set a text box (AttachmentName) in the repeating section. This field allows the user to name the attachment being uploaded.

4. On the Boolean DetachFile node, create a rule that set the command node in the QdabraRules secondary data source to the command SaveToSharePoint. This rule will only execute when DetachFile is equal to true. The command will look like this:

concat("SaveToSharePoint /url=<YOUR_LIBRARY_URL> /xpath= /my:myFields/my:ContainerGroup/my:RepeaterGroup/my:Attachment /name=", AttachmentName, " /overwrite=no")

[It makes a difference if the command has "/overwrite=no". If it is set to YES, it will only ever use the first occurrence of the /name parameter. When it is set to NO, each item in the repeating table is given its correct, corresponding name.]

5. Add a second action to reset DetachFile back to false.

6. Design the form to show the repeating section. The exact design is up to you, but the screenshot below shows an example:

7. Add a button to the form and label it “Upload attachments”. This button will use rules, and its only action is to set the Boolean attribute of the attachment field to true.

Now when a user attaches more than one attachment and provides a filename for each attachment, clicking the button will iterate thru each attachment to upload it to SharePoint.

GetEmployeesForManager in Qdabra's Active Directory Web Service

2011-09-01T23:41:00Z

Yesterday, a user of Qdabra's Active Directory Web Service reported an issue via the forum.

When calling the operation GetEmployeesForManager, the user's alias was being retrieved for both values in the dropdown.

We have quickly investigated and fixed this such that the displayName property is returned for Display and the alias for Value. In the newest version of the tool, all APIs that return a Dropdown list will return the AD “displayName” rather than “cn” for the display value. This was corrected for GetEmployeesForUser, FindUserByAlias and FindUserByName

Please refer to the product page for more information on the Active Directory web service Standalone.

How to retrieve the DocID for a submitted form

2011-08-16T14:40:00Z

Sometimes it is useful to know the DocID of a document that was just submitted to DBXL. For example, you might want to send an email immediately after submission. So how can we retrieve the DocID of the document? Follow this tutorial to find out!

These steps assume that you have an InfoPath form template (XSN) configured as a Document Type that submits to DBXL. The document type must have at least one document for the purposes of these steps (it can be a test document and it can be deleted once you are done). For more information on how to achieve this pre-requisite, refer to the Getting Started document.

Open your XSN in design mode.
Go to Data > Data Connections.
Click Add. Follow the Data Connection Wizard to create a data connection that receives data from http://<servername>/qdabrawebservice/ADUserInfo.asmx using the method GetMyInfo. Be sure to uncheck the option to “Automatically retrieve data when form is opened”.
Add a new field and call it key.
Add one more node to the schema, and call it DocID.
Click Data > Form Load.
Click New > Action. Rename the rule to “Set the value of key”.
Add a condition such that this rule will only execute when key is blank.
Add an action that queries the GetMyInfo data connection that was created in step 3.
Add a second action that that sets the value of the key to the following:

concat(xdDate:Now(), "- ", xdXDocument:GetDOM("GetMyInfo")/dfs:myFields/dfs:dataFields/tns:GetMyInfoResponse/tns:GetMyInfoResult/tns:ADProp/tns:Value[../tns:Key = "sAMAccountName"])

Note that you will need to click the fx button to enter the above formula. Your rule will look like this.

What is this rule doing? We are creating a unique key by concatenating the timestamp and the username. This creates a unique key because we can assume that one user won’t be able to submit two documents at the same, exact moment.

For information on how to use Qdabra’s Active Directory web service, refer to this document.
Next we will change the rules in the submit button. Set the button so that Action is set to Rules instead of Submit.
Go to Data > Data Connections > Add. Follow the Data Connection Wizard to add a data connection that receives data from http://<servername>/qdabrawebservice/dbxldocumentservice.asmx. This data connection will call the method QueryDocumentNodeSet and will not be set to “Automatically retrieve data when form is opened”. In the parameter screen, when asked for the value of the tns:query parameter, enter the following:

<query doctypeName="YOURDOCTYPENAME"><columns repeatingPath="/my:myFields"><column name="DocId" type="token" /></columns></query>

You can confirm that this data connection successfully obtained a schema by checking the dataFields:
For the submit button, click New > Action > Add > Submit Data. In the dialog that appears, select the data connection that submits to DBXL.
Add a second action that sets the value of the query node:

The value will be:

concat('<query doctypeName="YOURDOCTYPENAME"><columns repeatingPath="/my:myFields"><column name="DocId" type="token" /></columns><filter><eq> <column name="/my:myFields/my:key" /><value>',key,'</value></eq></filter></query>')

You will need to use the fx button when entering this value. In it, you’ll see YOUR document type name and key, which is the field we added earlier in the steps.
The third action will query data from the QueryDocumentNodeSet data connection.
The fourth action will set the value of my:DocID to the value returned by the data connection.
Save the XSN, re-attach it to your document type in DAT, and Save.

You can now test by submitting a new document and verifying that the DocID is returned to the form immediately after submitting.

This DocID can be used to generate a direct URL to your form, for example. The DocID, along with concat, can be used to set a field’s value to the URL, like this:
concat("http://YOURSERVERNAME/qdabrawebservice/documents/",DocID,"/",DocID,".xml")

Note that this method is only necessary to get the DocID for a newly submitted document. To obtain the DocID for a document that already exists in DBXL, you can use the processing instructions, as explained in this document.

Exporting documents using the web service

2010-11-02T13:43:00Z

The DBXL Administration Tool provides you controls to export the documents stored in your DBXL Document Types. However, this method may make InfoPath time out if you have a large number of documents (100+).

As a workaround for exporting a large number of documents, Qdabra provides the Migration Tool (Document Deployer), which allows you to export documents one by one, in the background.

However, this method operates in the background, exporting one document at a time. This might not be ideal for thousands of documents.
There is a third method that administrators can use to export a large number of documents. The web service used is the same as the Import/Export in DAT, but you can invoke these methods on the server by opening IE and browsing to the DbxlImportExport.asmx page.

On the machine where DBXL is installed, open Internet Explorer.
Navigate to the URL: http://<servername>/qdabrawebservice/DbxlImportExport.asmx
Click on ExportDocuments.
Enter the docTypeName and the folderPath for export.
Click Invoke.

This will export the documents to folderPath specified. A new Internet Explorer window will open showing the results. For example:

<?xml version="1.0" encoding="utf-8" ?>
<StatusInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://qdabra.com/webservices/">
<Success>true</Success>
<Errors>
<ErrorInfo>
<Code>I0001</Code>
<Description>2 documents exported.</Description>
</ErrorInfo>
</Errors>
<ExecutionTime>00:00:00.1340000</ExecutionTime>
</StatusInfo>

Note that you might need to increase the executionTimeout element. For more, read this: http://msdn.microsoft.com/en-us/library/ms824641.aspx.