InfoPath Dev

A space in the URL, encoded as %20, causes issues for QuerySharePoint

ErnestoM — Fri, 05 Mar 2010 15:10:00 GMT

When using QueryBuilder and QuerySharePoint, you need to enter the URL to your SharePoint list. Internet Explorer encodes spaces as %20, but DBXL produces an error if you use a URL containing a %20.

The workaround is simply to replace %20 in the URL with an actual space. This works in QueryBuilder and in the InfoPath data connection wizard.

Form Detailing: Spring Cleaning for Your InfoPath Forms

Jennifer Walts — Tue, 23 Feb 2010 03:15:00 GMT

For some of us, spring is in the air. We can’t help you clean the winter grime off your car, but we can help make your InfoPath form shine. Qdabra offers a detailing service–you send us your InfoPath form templates and we analyze them and send you a detailed report.

Check us out on Twitter – @InfoPathDev or @QdabraSoftware – and see how you can get your form detailed for free! We will randomly select a person who is following us and re-Tweets the offer. You get our compact form service, normally $95, where we review one form (one to three views and less than 40 fields) and send you a detailed report.

Our reports contain:

Best Practices - we list any best practices your form is missing. For example, is your data source well-structured?
Performance - can your form's performance be improved? We give you a quick summary of ways to make your form faster.
Cost of Ownership - how can you simplify your form to reduce maintenance costs down the road? We list several ways to simplify your form while not changing its functionality.
Security - does your form have security issues? We perform a quick threat analysis and send you results.
Visual Design - can you improve the look and usability of your form? Our UI gurus review your form’s look and feel and recommend improvements.

See an example report (PDF).

Even if you are not selected to get the free detail service, you can still have your forms detailed. See more information and pricing here.

Removing qRules from a Form

Hilary Stoupa — Fri, 05 Feb 2010 16:48:00 GMT

Adding qRules to your form is simple -- just browse to your form and click Inject and you are done! But what if you want to remove qRules for some reason?

Currently, that is a manual process, but it can be done.

First, use the Logic Inspector to find any rules that are using the Command node from the QdabraRules data connection:

That will help you better understand what will need to change after you remove qRules. Next, you will need to remove one or two data connections, depending on your version of qRules. If you have the trial version, you'll need to remove both the QdabraRules and the QdabraVerification data connections. If you are using a purchased version, you will only need to remove the QdabraRules data connection:

Next, navigate to Tools / Resource Files:

Remove the QdabraRules.xml file:

Finally, we need to remove the .dll that contains the qRules logic. The easiest way to do this is to add code to your form. For example, add a loading event:

Close the Microsoft Visual Studio Tools for Applications window that opens after adding the event, then navigate to Tools / Form Options:

Go to the Programming category, select the Remove Code buttton to remove all code from your form:

Qdabra’s AdUserInfo XTP

Mel Balsamo — Thu, 04 Feb 2010 09:11:00 GMT

Qdabra’s AdUserInfo template part is useful for solutions that require sending of forms via email. Populate your ‘From’ and ‘To’ fields with valid email addresses that are retrieved from the Active Directory.

HOW IT WORKS

This is how the template part looks in InfoPath Preview mode:

Clicking the ‘Click to Get Your Info’ button retrieves the current user’s AD information and stores the name, alias and email address in the XML.

In the text box on the right, enter a valid alias for the user you wish to send email to. After tabbing out, that user’s AD info is retrieved using Qdabra’s Active Directory Web Service. This way, we are assured that the form will be sent to a valid email address.

HOW TO ADD THE XTP IN INFOPATH

Launch InfoPath and select Design a Form Template based on a new blank one.
In the Controls taskpane, click Add or Remove Custom Controls…

In the Custom Controls dialog box, click Add > Template Part, and browse to the location where you placed your AdUserInfo XTP.

Verify that the XTP has been added to your Custom Controls taskpane.

Drag the XTP from taskpane to your canvas. You will see the following section:

Your Main Data Source should look similar to this:

Go to Tools > Data Connections. You will see that there are two data connections that got added as well.

These data connections retrieve data using Qdabra’s Active Directory Web Service. If you already have DBXL installed, you can use the AD web service that comes with it.

Modify the data connections and replace the URLs to point to the location of your web service installation, i.e. http://<YourServerName>/QdabraWebService/AdUserInfo.asmx. If you are using Qdabra’s AD Standalone Tool, replace the web service URL in the format: http://<YourServerName>/QdabraAD/AdUserInfo.asmx.

As you modify your data connections, you might want to remove ‘_AdUserInfo’ in the data connection names. Those got appended when you injected the XTPs.

When done with the above steps, you can start testing your form template in Preview mode.

Download Qdabra's AD User Info XTP for FREE here.

InfoPath gaps in SharePoint and SQL

Patrick Halstead — Fri, 29 Jan 2010 08:31:00 GMT

Here’s a brief summary of InfoPath gaps in SharePoint and SQL integration for out-of-box solutions (assumes no code added to InfoPath form nor SharePoint):

SharePoint limitations
- SharePoint views cannot be configured to show repeating data from an XML form.
- Document level permissions – you can manually set permissions in SharePoint for documents, but controlling permissions based on document level fields cannot be done.
- SharePoint list view performance degrades after ~3000 forms. This is a known limitation in SharePoint. Not a limitation if the data is in SQL. Workarounds include:
  - Archive forms so you have <3000 active. Not a solution if you need to see all of them.
  - Restrict views to show very few forms and enable paging. Once again, can’t see all forms.
  - Submit forms to a separate database via a Web Service and use BDC Web part with filtering and paging to quickly view data
- Submit performance is slower (~4x slower) going to SharePoint than to a Web Service. Click here for more details.
- No support to search for users in Active Directory or SharePoint user list. Call GetUserProfile to set user, but can’t check for group membership or get other Active Directory properties.
- No dynamic filtering for SharePoint lists – populating dropdowns pull all data down from the SharePoint list leading to slow form load times. Filtering via OWSSVR is hardcoded (not dynamic).
- Number of columns limited to fixed numbers. Using Web Service with SQL and BDC solves this scenario.
- SharePoint out-of-box search does not support wildcard searches for forms (details). You can’t search for App* and find both Apple and Application..

SQL Database integration issues
- No database support for forms created first using XML. Requires total rewrite.
- Adding fields to data source don’t show up in new SQL columns. You’d have to add to SQL first and then convert your main data source. Previous XML data would not migrate forward.
- Changes to DB schema will invalidate previously saved XML files in SharePoint.
- Images, rich text, and other non-SQL data types cannot be saved to SQL. Must be saved in XML but then you have migration issues per previous two bullets.
- No server-side dynamic filtering when populating dropdown values with SQL queries. If the SQL table is large, it could take a very long time and time out.
- No locking of data submitted to SQL – multiple writes could happen if two people open the same form. Last writer wins.
- No dynamic role-based permissions – you have to add users and/or groups to SQL manually and specify READER or WRITER. Can’t dynamically determine reader and writer based on field value.

Without SQL, you have Reporting Issues
- Reporting would be flat – repeating data cannot be aggregated in a report. You would need to move the data to a database or create an XML query tool on top of your XML files which would likely be a solution specific to one XSN, i.e. schema-dependent not general.
- Export to Excel not supported for more than one repeating section and not feasible for more than 10-20 XMLs at a time.

Qdabra’s DBXL Web Service fills all of the above gaps.

DBXL Sharepoint Mapping: When Object Model should work but doesn’t

ErnestoM — Fri, 22 Jan 2010 19:55:00 GMT

When creating a Sharepoint mapping from the DBXL Administration Tool (DAT) you have two options: Object Model and Web Service. Web Service is required when the Sharepoint instance lies on a different server. However, we have seen instances where Object Model produces an error, even though DBXL and Sharepoint are on the same machine.

How do you know if you’re in this situation?

Add a Sharepoint mapping. Select Web service. Save the document type. Add documents and verify that everything works as expected.
Edit the same doctype to use Object Model. When you click Save in DAT you get this:

Simply using Web Service is an acceptable workaround for some people. But if you wish to use IPFS (browser enabled forms) you’ll need to use Object Model. So how do we fix this?

When faced with this scenario please check the Applications eventlog. It is our experience that we find a SharePoint error with Network Service not having permission to access the Sharepoint database. Evidently, the object model does direct database access. To fix this, switch DBXL to use the same identity as the SharePoint app pool.

Qdabra's Image Picker Template Part

Mel Balsamo — Mon, 11 Jan 2010 08:34:00 GMT

SCENARIO

You want to add images to your InfoPath form, but:

Image attachments, even those that are embedded in the form, get stored in the XML itself, thus making its file size huge.
Your images are stored in a SharePoint list and cannot be retrieved via filters without using code.

SOLUTION

Install Qdabra’s Image Picker template part!

Based on Qdabra’s qRules library – extend your InfoPath forms without writing code
Easy-to-add XML Template Part – just add custom control via the InfoPath 2007 Designer
Comes with pre-configured layout which saves you time – just drag and drop from the taskpane
Less file size since no images are attached nor embedded in the XML – the images are loaded from the external SharePoint file.
Able to query images from a SharePoint list – filter using image tags for easy searching, without code!

Note that the QdImagePicker XTP uses a picture control to hold the images. Since picture controls are not supported in InfoPath Forms Services (IPFS), the template part will not work in the browser.

QUICK PREVIEW

Let’s take a quick look: here is a screenshot of Qdabra’s ImagePicker XTP:

In the screenshot above, the user searched for images that were tagged with ‘Logo’ as the Subject and returned two images. The user then clicked on the ‘View’ link for the first entry to display the actual image as well as its properties, as shown below:

Clicking on the ‘Use Selected Image’ button leaves the image on the form and a button that lets the user pick a different image.

The ‘Add’ tab becomes handy if the user wants an image that is not in the SharePoint list, but is on his network or local machine. Simply add the image to the picture control and click ‘Use This Image’.

The newly-added image is then uploaded to your SharePoint library/list.

This blog post details the steps needed before you begin using the XTP in your InfoPath forms; just a few modifications and you’re ready to go!

REQUIREMENTS

QdImagePickerXTP – Image Picker template part. Download it FREE here.
InfoPath 2007 – template parts (XTPs) are not supported in InfoPath 2003.
Qdabra qRules v2.0 – add the most commonly requested InfoPath features without the need of writing code. Download a FREE trial here.
A SharePoint list to retrieve and submit images to.

SET UP A SHAREPOINT LIST WITH IMAGES

1. In your SharePoint site, create a list. For the purposes of this tutorial, name your list ImagePickerList.

2. Add the following columns:

3. In your main ImagePickerList view, i.e. http://<SharePoinSite>/Lists/ImagePickerList/AllItems.aspx, click on the ‘All Items’ dropdown and select ‘Modify this View’.

4. In the ‘Edit View’ page, ‘Columns’ section, enable only the following columns and (optional) sort them as shown below:

5. Click OK when done.

6. Back in your ImagePickerList view, click New > New Item.

7. In the ‘New Item’ page, fill out all the fields, like such:

Hints:

Subject – this is the main tag used in the QdImagePicker XTP when searching for images. Since in your XTP, the Subject dropdown list box uses the following entries: Animals, Business, Family, Landscape, Logo, People, Travel, and Other; make sure to tag your images using only one of these values.
IsPrivate – can either be ‘true’ or ‘false’.
Image – the URL to the image
- If you have an existing image that’s already uploaded on the web, simply enter the full image URL (e.g. http://images.google.com/intl/en_ALL/images/logos/images_logo_lg.gif) in the Image field:

- If you wish to upload an image from your network or local machine:

a. Leave the Image field blank for now.

b. Click on the ‘Attach File’ link in the New Item page

c. Browse to the image and click OK.

The ‘Attachments’ field will now appear in your ‘New Item’ page:

Note that the Image field will remain blank.

d. Click OK.

e. Back in your list main view, click on the Title dropdown of your newly-added image and select ‘Edit Item’.

f. You will be taken to the ‘Edit Item’ page where you’ll see the image attachment filename which now shows as a hyperlink. Right-click on the link and select ‘Copy Shortcut’ to place the image URL in your clipboard.

g. Paste the image URL in the Image field:

h. Click OK.

You now have your first Image item in your SharePoint list:

You would want to add more images to this list so that when you begin using your QdImagePicker XTP in your forms, you will have a couple of images to pick from. We recommend adding the images that you use most of the time, such as company logos, banners, signatures, etc.

For the purposes of this tutorial, we will use the following entries in our SharePoint list:

ADD THE TEMPLATE PART IN INFOPATH CUSTOM CONTROLS

1. Launch InfoPath and select Design a Form Template based on a new blank one.

2. In the Controls taskpane, click Add or Remove Custom Controls…

3. In the Custom Controls dialog box, click Add > Template Part, and browse to the location where you placed your QdImagePicker XTP.

4. Verify that the XTP has been added to your Custom Controls taskpane.

5. Save your blank form template (XSN). For the purposes of this blog post, we will name it as ImagePickerXSN.

6. Close InfoPath.

INJECT QRULES AND THE XTP IN YOUR FORM TEMPLATE

1. Launch the qRules Injector from Start > All Programs > Qdabra > Tools > qRules Injector.

2. Browse to the location where you saved your ImagePickerXSN.

3. Click Inject, then OK in the confirmation dialog, and then close the qRules Injector.

4. Open ImagePickerXSN in InfoPath Design mode.

5. Scroll down to the very bottom of the Controls taskpane to get to the Custom section, and then click on the QdImagePicker control.

You should now have the template part on your canvas. The main and the secondary data sources will be injected in your XSN as well.

SOURCE FILES CLEAN-UP

If you go to Tools > Data Connections, you will notice that you have two instances of QdabraRules, one for the XSN, and one for the XTP:

When you first injected qRules in your XSN, it added the QdabraRules XML as a secondary data source. In this same XSN, you injected the QdImagePicker XTP which also uses qRules. This added another QdabraRules XML secondary data source to the XSN (with a different name). So now, you have two qRules XMLs, which is confusing and might cause issues when calling the qRules commands. Unfortunately, there is no workaround at the moment, hence the need to do some clean-up.

1. Save ImagePickerXSN as source files and close InfoPath.

2. Modify the manifest.xsf file:

a. In the directory where you saved your source files, locate manifest.xsf and open it in any text editor.

b. Replace all eight instances of “QdabraRules_QdImagePicker” with “QdabraRules”.

c. Delete the associated blocks where you find “QdabraRules1”:

i. xsf:file (for .xsd)

ii. xsf:file (for .xml)

iii. xsf:dataObject

d. Save and close manifest.xsf.

3. Modify the view1.xsl file.

a. Open view1.xsl in a text editor.

b. Replace the two instances of “QdabraRules_QdImagePicker” with “QdabraRules”.

c. Save and close view1.xsl.

4. Modify the sampledata.xml file

a. Open sampledata.xml in a text editor.

b. Delete the xd:DataConnection block where you find “QdabraRules_QdImagePicker”.

c. Save and close sampledata.xml.

5. Delete the extraneous QdabraRules1 files in the source files directory:

a. QdabraRules1.xml

b. QdabraRules1.xsd

6. Verify changes.

a. Open manifest.xsf in InfoPath Design mode.

b. Go to Tools > Data Connections and verify that you now have only one instance of QdabraRules.

7. Modify the qtpFormLogic data connection and remove the _QdImagePicker suffix in its name.

ADD A DATA CONNECTION THAT FILTERS DATA FROM YOUR SHAREPOINT LIST

The owssvr dll file comes with SharePoint. Clever people have been creating filtered SharePoint data queries by using this DLL. The Qdabra qRules command called FilterOwssvr attempts to make it easier to filter data from a SharePoint list and use it in your InfoPath form.

Before we start we need to locate a few values that we’ll need during this tutorial:

A. Owssvr.dll - On MOSS 2007, this file is located at the following URL: http://<SharePointSite>/_vti_bin/owssvr.dll.

B. List ID – Open the list in Internet Explorer, select Settings and List Settings. You’ll see a URL that looks like this: http://<SharePointSite>/_layouts/listedit.aspx?List=%7B51964A2F%2D1C71%2D4C1C%2D969E%2DF5C9764B312B%7D. The List ID is in red, but we need to decode it to obtain the Guid. You can use an online tool such as http://meyerweb.com/eric/tools/dencoder/ to obtain the Guid, which in this case is {51964A2F-1C71-4C1C-969E-F5C9764B312B}.

Now that we have these values, we can set up our form to receive data from this list.

1. Confirm your values visually in Internet Explorer by navigating to http://<SharePointSite>/_vti_bin/owssvr.dll?Cmd=Display&List={51964A2F-1C71-4C1C-969E-F5C9764B312B}&XMLDATA=TRUE. You’ll see something like this:

2. In your ImagePickerXSN form template, go to Tools > Data Connections.

3. Click Add, and follow the wizard to create a data connection that receives data from an XML document, and enter the full URL we accessed in Step 1, i.e. http://<SharePointSite>/_vti_bin/owssvr.dll?Cmd=Display&List={51964A2F-1C71-4C1C-969E-F5C9764B312B}&XMLDATA=TRUE.

4. Continue the wizard, making sure to select ‘Access the data from the specified location’.

5. Click Next. We’ll name this data connection ‘FilterImages’ and uncheck the box ‘Automatically retrieve data when form is opened’.

6. Click Finish, and then Close to exit out of the wizard.

RE-BIND SECTIONS TO THE FILTERIMAGES DATA SOURCE

These sections of the QdImagePicker XTP are bound to the qtpFormLogic secondary data connection.

These sections will hold our SharePoint list images and details. We then need to re-bind them, including the fields, to the ‘FilterImages’ secondary data source.

1. Start from the outermost section; right-click on it and select ‘Change Binding’.

2. Select ‘FilterImages (Secondary)’ from the ‘Data source’ dropdown and click on the rs:data node, then click OK.

3. Next, re-bind the repeating section; right-click on it and select ‘Change Binding’, then select the z:row repeating node.

4. Do the same for the innermost section; right-click and select ‘Change Binding’, then bind it to the z:row repeating node.

You should now have all the three sections bound to the ‘FilterImages’ data source:

5. Next, we will modify the expression boxes to point to the nodes in our ‘FilterImages’ data source since these nodes are actually the columns in our SharePoint list. Do this by double-clicking on each expression box, clicking on the fx button under the ‘General’ tab and entering the formula as follows (Note that the underlined fields point to the ‘FilterImages’ secondary data source nodes):

a. Date – substring-before(@ows_Date_x0020_Taken, " 00:00:00")

b. Title – @ows_LinkTitle

c. Subject – @ows_Subject

d. Location – @ows_Location

e. Creator – @ows_Creator

f. Copyright – @ows_Copyright

g. Private – select ‘Text’ under the ‘General’ tab and on your keyboard, hold Alt + 129 (numeric keypad) to get ü and display a check mark. If you are using a non-English OS keyboard, simply copy ü and paste it in the ‘Text’ field.

Go to the Display tab > Conditional Formatting > Add and add the condition ows_IsPrivate ‘is not equal to’ “true” (entered as text), to hide the control.

h. ID – @ows_ID

i. Image (picture control) – @ows_Image

MODIFY THE FORM LOGIC

First, we need to change the rules that call the FilterOwssvr qRules command that are present in the ‘Search’ button.

1. Double-click on the ‘Search’ button and click ‘Rules’. The last two rules in this button are the ones that use the ‘FilterOwssvr’ command.

2. Double-click on the second rule (No Date Taken), modify the action and click on the fx button.

3. We already have the correct command syntax in there. We just need to insert the correct data connection adapter name, which in this case is ‘FilterImages’.

4. Click OK three times to get back to the ‘Rules’ dialog box.

5. Do the same actions for the third rule (Date Taken specified); change the data connection adapter name to ‘FilterImages’.

Notice that we’re using ‘Date_x0020_Taken’ in the /filter parameter instead of ‘Date Taken’. This is because, in InfoPath, if our SharePoint List columns have spaces in their names, they are encoded with 'x0020' when retrieved from the data source.

Also note that the values in this parameter are case-sensitive. If you accidentally give this command an invalid value for the /filter parameter, no error is returned. It is up to the form designer to ensure that the /filter parameter is correct.

To make sure that we are using the correct values in our /filter parameter, check for the exact node names in your data source (without the :ows_ prefix):

For the same purpose that we need to point the sections and the fields to the newly-added ‘FilterImages’ secondary data source, we also need to change the rules and the conditional formatting in our controls that have any instances of the ‘qtpFormLogic’ Images node to point to the ‘FilterImages’ rs:data node instead. We will do this by looking for logic that uses any of the following nodes:

and replacing them accordingly with these:

6. View button:

a. Double-click on the button to display its properties.

b. In the ‘Button Properties’ window, click ‘Rules’ and modify the action in that rule. You will see that it sets the value of ImageDocId to ID that comes from the ‘qtpFormLogic’ secondary data source.

c. Change ID into :ows_ID of the ‘FilterImages’ secondary data source.

You should now have this action in your ‘View’ button rule:

7. FilterImages sections:

Go to each section’s properties and click ‘Conditional Formatting’ in the ‘Display tab’.

a. data (FilterImages) section (Both conditions hide the control):

From:

To:

b. row (FilterImages) repeating section:

From:

To:

c. row (FilterImages) section:

From:

To:

8. Use Selected Image button:

a. Modify only the first rule (Copy Image URL) in the button properties:

Click OK four times to go back to the ‘Insert Formula’ dialog. Your value for the @qRulesLink should look similar to this:

Where the XPath formula for the value is: xdXDocument:GetDOM("FilterImages")/xml/rs:data/z:row/@ows_Image[../@ows_ID = xdXDocument:GetDOM("qtpFormLogic")/FormLogic/View/ImageDocId]

This formula allows us to retrieve the value of the Image column in our SharePoint list, filtered such that its ID column’s value is equal to the value in the ImageDocID field of the qtpFormLogic secondary data source.

b. Back in the ‘Button Properties’ Display tab, click on the ‘Conditional Formatting’ button and change the condition from:

To:

c. Click OK to close all dialogs.

<!--[if gte mso 10]>

1. Use Selected Image button:

a. Modify only the first rule (Copy Image URL) in the button properties:

From:

To:

Where the XPath formula for the value is: xdXDocument:GetDOM("FilterImages")/xml/rs:data/z:row/@ows_Image[../@ows_ID = xdXDocument:GetDOM("qtpFormLogic_ImagePicker")/FormLogic/View/ImageDocId]

This formula allows us to retrieve the value of the Image column in our SharePoint list, filtered such that its ID column’s value is equal to the value in the ImageDocID.

b. Back in the ‘Button Properties’ Display tab, click on the ‘Conditional Formatting’ button and change the condition from:

To:

c. Click OK to close all dialogs.

9. The error expression “Nothing found. Please try a different search criteria.”; change the first conditional formatting from:

to:

We should now have the following conditional formatting in our error expression box (each hides the control):

Lastly, we should also modify the ‘Use this Image’ button which allows us to upload our own images from our network or local machine. This is where we use the SaveToSharePoint qRules command which saves a file, a set of files, an image or a set of images to a SharePoint Document Library or List.

10. Double-click on it to open its properties, and then click ‘Rules’.

11. You’ll see three rules in this button. We need to modify the action in the first rule only since it calls the ‘SaveToSharePoint’ qRules command. In the ‘Action’ dialog box, you’ll see that the Command field is being set to the value: SaveToSharePoint /url=<SharePoint List URL> /xpath=/my:QdImagePicker/my:Image

a. For the /url parameter, replace <SharePoint List URL> with the list URL that we’ve set up earlier in this tutorial, e.g. http://<SharePointSite>/Lists/ImagePickerList, making sure to replace <SharePointSite> with yours.

b. For the /xpath parameter, replace /my:QdImagePicker/my:Image with the correct XPath for your Image node; in this case, we have: /my:myFields/my:QdImagePicker/my:Image

Your command syntax should then be:

SaveToSharePoint /url=http://<SharePoinSite>/Lists/ImagePickerList xpath=/my:myFields/my:QdImagePicker/my:Image

(Again, making sure to replace <SharePointSite> with yours).

To make our scenario more realistic, let’s add a few fields and make our form template a bit fancy. Here’s what I have in Preview mode:

That’s it! Our form template is all set up and is ready to be published to our SharePoint Site.

PUBLISH TO A SHAREPOINT SITE

1. Save all the changes you’ve made in your ImagePickerXSN form template.

2. In InfoPath’s menu, select File > Publish.

3. In the ‘Publishing Wizard’, select:

4. Enter the location of your SharePoint site in the next screen.

5. We will publish this form template in a ‘Document Library’, so select that option and click Next.

6. You can create a new document library with a name of your choosing and promote/display columns as you wish.

7. When done, click Publish, and then Close to exit out of the wizard.

You can also add a submit data connection as you normally would if you wish to submit your forms to your SharePoint site. We’ll skip those steps for now since the ImagePicker XTP will function simply by just opening a new form where we published our form template to.

Open your SharePoint document library and create a new document/form. Fill out your fields, click on that ‘Pick Image’ button and start searching for images! Don’t miss out on the ‘Add’ tab and select any image from your network and local machine!

Once you’ve started using Qdabra’s ImagePicker template part in your InfoPath solutions, you’ll see how easy and neat it is to search and add images in just a few clicks.

Download the QdImagePicker XTP for FREE here.
Download Qdabra qRules v2.0 for a FREE trial here.

Adventures in Troubleshooting

Hilary Stoupa — Sat, 02 Jan 2010 13:37:00 GMT

This is going to be short and sweet. The other day I ran into an issue that made me aware of a little ... inconsistency .... in Visual Studio that I'd like to make sure everyone else is aware of to.

I have a table that looks like this:

I have a form template with a data connection to that table.

I have a bit of code that looks like this:

What you can't see is that I have a trailing space in my Orders table for PO Number def-456. And if you look in the XML visualizer for the data source, you won't see it there, either....

Opening the Text visualizer for the OuterXml for the nav variable tells a different story:

So, if you are troubleshooting some code, trying to figure out why you aren't getting back a node that you are just sure should be in your data source... take a minute to look at both visualizers. It may save you some time and at least a bit of your sanity.

Qdabra's Header-Footer Template Part

Mel Balsamo — Wed, 23 Dec 2009 10:32:00 GMT

When creating InfoPath solutions, one of the most important aspects is the visual design. This includes making sure that the layout, fonts, colors, white spaces, and most especially, the branding, are all uniform. We can always decide how our forms look like, but for a company to have its unique identity in its InfoPath forms, it should maintain proper branding by using its own company logo.

Company logos can either be placed in the InfoPath form’s header or footer, or both. Qdabra created a template part that has header and footer sections built-in. We can just change the logo, form title, company details, etc. and start using them in our forms.

Here’s how the Header-Footer XTP looks:

This blog post details the steps needed to modify the Header-Footer XTP and begin using it in your InfoPath forms.

REQUIREMENTS

Header-Footer XTP – Download it FREE here and save it your local machine.
InfoPath 2007 – template parts (XTPs) are not supported in InfoPath 2003.

MODIFY THE TEMPLATE PART

1. Launch InfoPath.

2. In the Getting Started dialog, under the ‘Design a form section’, click Design a Form Template…

3. In the ‘Open a form template’ section, click On My Computer…

4. In the ‘Open in Design Mode’ dialog box, select InfoPath Template Parts (*.xtp) from the file types dropdown.

5. Browse to the location where you saved the Header-Footer XTP and open it.

6. The template part will open in InfoPath Design mode.

7. Make all the necessary changes:

a. Header: replace the company logo with your own.

b. Footer: replace the small logo/icon as well as the company details.

Note: The Document ID in the footer is applicable only if you have Qdabra DBXL installed. You can delete this whole row if you do not intend to use the form with DBXL.

8. Save and close the template part.

ADD THE TEMPLATE PART IN INFOPATH CUSTOM CONTROLS

1. Launch InfoPath and select Design a Form Template based on a new blank one.

2. In the Controls taskpane, click Add or Remove Custom Controls…

3. In the Custom Controls dialog box, click Add > Template Part, and browse to the location where you saved your HeaderFooter XTP.

4. Verify that the XTP has been added to your Custom Controls taskpane.

ADD THE TEMPLATE PART TO YOUR INFOPATH FORM

1. In your blank InfoPath form template, click or drag the the HeaderFooter XTP from the Custom Controls taskpane. This will add the XTP onto your view.

2. Highlight or select the entire contents of the HeaderFooter XTP section.

3. Drag the selection outside the section.

4. Delete the empty HeaderFooterXTP section, leaving only the its dragged contents in the view.

5. Delete the HeaderFooterXTP node from the main data source.

6. Modify the two expression boxes in for the Document ID found in the footer.

Note: You may skip these steps and proceed to Step 7 if you do not intend to use the form with Qdabra DBXL.

a. Change the conditional formatting for the expression box labeled New.

i. Right-click on the expression box and select Conditional Formatting…

ii. In the ‘Conditional Formatting’ window, click Modify.

iii. Delete my:HeaderFooterXTP in the formula.

iv. Click OK twice to close the dialogs.

b. Change the formula for the Document ID expression box.

i. Right-click on the expression box and select Expression Box Properties…

i. In the ‘Expression Box Properties’ window, under the ‘General’ tab, click on the fx button.

ii. Check ‘Edit XPath’ in the Formula dialog and delete my:HeaderFooterXTP

iii. Click OK twice to close the dialog boxes.

iv. Repeat all the steps in 6a above but this time, changing the conditional formatting for the blank Document ID expression box.

7. Change the formula for the Form Version expression box in the footer.

a. Right-click on the expression box and select Expression Box Properties…

b. In the ‘Expression Box Properties’ window, under the ‘General’ tab, click on the fx button.

c. Check ‘Edit XPath’ in the Formula dialog and delete my:HeaderFooterXTP

d. Click OK twice to close the dialog boxes.

That’s it! You can now design your form to suit your company needs. By having all your forms designed this way, ensure that you are maintaining uniformity and proper branding in your InfoPath solutions.

Qdabra's Zero-ActiveX Contact Selector XTP

Mel Balsamo — Mon, 14 Dec 2009 14:27:00 GMT

PROBLEM

You want to use the InfoPath Contact Selector to add multiple contacts to your form, but:

Selecting multiple users with Contact Selector requires code
Contact Selector is an ActiveX control and requires installation on client machines
Contact Selector doesn’t work for browser-based forms when the user is not using Internet Explorer

SOLUTION

Install Qdabra’s Zero-ActiveX Contact Selector

Based on Qdabra’s qRules library – extend your InfoPath forms without writing code
Easy-to-add XML Template Part – just add custom control via the InfoPath 2007 Designer
Comes with pre-configured layout which saves you time – just drag and drop from the taskpane
No ActiveX – works with browser based forms

QUICK PREVIEW

Let’s take a quick look: here is a screenshot of Qdabra’s Zero-ActiveX Contact Selector XTP

In the screenshot, the user searched for all users with “admin” in their Active Directory records and then clicked on the arrows to add Jim Cantwell and Patrick Halstead to the form.

Click here to watch a short video demo that shows how the template part works in InfoPath and in the browser.

This blog post details the steps needed before you begin using the XTP in your InfoPath forms; just a few modifications and tweaks and you’re ready to go!

REQUIREMENTS

QdContactSelectorXTP – Contact Selector template part, no ActiveX required, no code. Download it FREE here.
InfoPath 2007 – template parts (XTPs) are not supported in InfoPath 2003.
Qdabra Database Accelerator – includes the Active Directory Web Service. Download a FREE trial here. Alternatively, you may purchase Qdabra’s Active Directory Standalone tool here.
Qdabra qRules v2.0 – add the most commonly requested InfoPath features without the need of writing code. Download a FREE trial here.
Admin-deploy to SharePoint – qRules is a library that contains code, so when you publish your form to SharePoint you need to publish it as an administrator approved form. This requirement goes away for SharePoint 2010.

ADD THE TEMPLATE PART IN INFOPATH CUSTOM CONTROLS

1. Launch InfoPath and select Design a Form Template based on a new blank one.

2. In the Controls taskpane, click Add or Remove Custom Controls…

3. In the Custom Controls dialog box, click Add > Template Part, and browse to the location where you placed your QdContactSelector XTP.

4. Verify that the XTP has been added to your Custom Controls taskpane.

5. Save your form template (XSN). For the purposes of this blog post, we will name it as ContactSelectorXSN.

6. Close InfoPath.

INJECT QRULES AND THE XTP

1. Launch the qRules Injector from Start > All Programs > Qdabra > Tools > qRules Injector.

2. Browse to the location where you saved your ContactSelectorXSN.

3. Click Inject, then OK in the confirmation dialog, and then close the qRules Injector.

4. Open ContactSelectorXSN in InfoPath Design mode.

5. Scroll down to the very bottom of the Controls taskpane to get to the Custom section, and then click on the QdContactSelector control.

You should now have the template part on your canvas. The main and the secondary data sources will be injected in your XSN as well.

SOURCE FILES CLEAN-UP

If you go to Tools > Data Connections, you will notice that you have two instances of QdabraRules, one for the XSN, and one for the XTP:

When you first injected qRules in your XSN, it added the QdabraRules XML as a secondary data source. In this same XSN, you injected the QdContactSelector XTP which also uses qRules. This added another QdabraRules XML secondary data source to the XSN (with a different name). So now, you have two qRules XMLs, which is confusing and might cause issues when calling the qRules commands, hence the need to do some clean-up.

1. Save ContactSelectorXSN as source files and close InfoPath.

2. Modify the manifest.xsf file:

a. In the directory where you saved your source files, locate manifest.xsf and open it in any text editor.

b. Replace all seven instances of “QdabraRules_QdContactSelector” with “QdabraRules”.

c. Delete the associated blocks where you find “QdabraRules1”:

i. xsf:file (for .xsd)

ii. xsf:file (for .xml)

iii. xsf:dataObject

d. Save and close manifest.xsf.

3. Modify the sampledata.xml file

a. Open sampledata.xml in a text editor.

b. Delete the xd:DataConnection block where you find “QdabraRules_QdContactSelector”.

c. Save and close sampledata.xml.

4. Delete the extraneous QdabraRules1 files in the source files directory:

a. QdabraRules1.xml

b. QdabraRules1.xsd

5. Verify changes.

a. Open manifest.xsf in InfoPath Design mode.

b. Go to Tools > Data Connections and verify that you now have only one instance of QdabraRules.

MODIFY THE ACTIVE DIRECTORY DATA CONNECTION

1. In Tools > Data Connections, modify the FindUsersByName_QdContactSelector data connection.

2. Replace servername (in http://servername/qdabrawebservice/ADUserInfo.asmx?WSDL) with the correct location where you installed DBXL.

Note: If you are using Qdabra’s AD Standalone Tool, the AD Web Service URL should be in the format: http://servername/QdabraAD/ADUserInfo.asmx?WSDL.

3. Click Next and make sure that the FindUsersByName operation is selected. Click Next again.

4. Leave the tns:name parameter blank, and for the tns:searchType*, select Contains.

5. Click Next twice more, then Finish to exit out of the wizard.

MODIFY THE QRULES COMMANDS

Since you’ve added the QdContactSelector XTP into an XSN, you need to make sure that the qRules commands point to the new fields’ XPaths.

1. Double-click on the button to open its properties.

2. Click Rules and modify all the three actions in the Insert User rule:

a. Change the first action that calls the Insert command to:

Insert /parent=/my:myFields/my:UserList /child=/my:Alias /count=1

b. Change the second action that calls the SetValue command to:

concat("SetValue /xpath=/my:myFields/my:UserList/my:Alias[last()] /value=", @value)

Note: @value refers to a field in the form. If in doubt, tick the checkbox labeled Edit XPath to see the full xpath of the @value node.

c. Do the same for the third action for the SetValue command:

concat("SetValue /xpath=/my:myFields/my:UserList/my:Alias[last()]/@my:name /value=", @display)

3. Click OK as many times needed to close all the dialog boxes.

4. Double-click on the button to open its properties and modify the Remove User rule.

In the Insert Formula dialog, check on the Edit XPath (advanced) box and change the Delete command to:

concat("Delete /xpath=/my:myFields/my:UserList/my:Alias[", count(preceding-sibling::my:Alias) + 1, "]")

5. Click OK until all dialog boxes are closed.

6. Add conditional formatting so that, when users have already selected a contact, they can no longer add the same contact.

a. Double-click on the button to open its properties.

b. Go to the Display tab > Conditional Formatting > Add.

c. Add a condition such that if Alias (from the main data source):

is equal to the value node (from the FindUsersByName_QdContactSelector secondary data source):

then hide the control:

d. Click OK until all the dialog boxes are closed.

e. Do the same for the @display field. Double-click on the textbox to open its properties.

f. Go to the Display tab > Conditional Formatting > Add.

g. Add a condition such that if Alias (from the main data source) is equal to the value node (from the FindUsersByName_QdContactSelector secondary data source), then change the font color to something lighter:

h. Click OK until all the dialog boxes are closed.

That’s it! You’re all set to use Qdabra’s Zero-ActiveX Contact Selector! Test your XSN in Preview mode and search for users, select contacts and even delete them. Finish designing your form template, save it, and you’re ready to go!

PUBLISH TO SHAREPOINT

Here’s a link to an article describing how to publish your new form to SharePoint so you can use it in the browser.

You can also refer to the User Guide that comes with your qRules v2.0 installation for instructions on how to use your qRules solutions in InfoPath Forms Services.

Copy SharePoint List Data to Main Data Source

Mel Balsamo — Thu, 03 Dec 2009 17:32:00 GMT

This post was originally written by Clay Cobb and is republished here with permission. To see the original article, please click here.

One thing that you can't easily do in InfoPath is to copy repeating data from a secondary data source - like a SharePoint list - to the main data source. This is an important necessity, because the data from secondary data connections does not get saved in the resulting XML data file of a submitted/saved InfoPath form. So, when you look at the form initially, you see the nice pretty data from your SharePoint list enumerated within the repeating table you placed on the canvas, but if you were to open the raw XML file, none of that data would be there. This is because an InfoPath form only keeps whatever data is saved to its main data source. A data connection to a SharePoint list is just a secondary data connection, which in essence becomes a window into the data as it currently exists. Yes, you can re-open your XML file in InfoPath and see the data from your SharePoint list, but it's the current instance of that data, not the data that existed at the time you previously submitted the form. Sometimes, this is ok, but what if you needed to get that SharePoint data into your form and keep it there? To do this, you must copy the data into the main data source. How can you do that? Well, you can custom code something, or you can use the great set of special commands known as qRules that is provided to all of us by Qdabra. qRules allows you to do many things, but the one command we will focus on is named CopyTable.

Caveats:

qRules introduces code to your form, so if your forms are browser-enabled, then they will require Full Trust and Administrative-Approval publishing type. I have not tested any in browser forms yet
The CopyTable command will only work if the nodes match, which also means each node has to have data in it (no blank fields). The reason your fields can’t be blank is because when there is a blank field, no placeholder for that field is brought down in the XML, so it’s as if that node doesn’t exist.

The example I will use for this blog is a Weekly Status form template (based off the Meeting Agenda sample) within a Meeting Workspace where you pull in Active Tasks from a SharePoint Task List on demand so that a snapshot of the tasks is saved within the form. The purpose of this is so that at a later time, the Weekly Status for that week's meeting can be opened, and you will see the status of the team's tasks when the meeting occurred rather than the current point in time. In essence, it becomes an historical record of the team's tasks just like meeting minutes are intended to be. For the purposes of this blog, I will not go into great detail about meeting workspaces and how they work, but I will briefly explain how InfoPath forms need to be used in order to get the recurring meeting functionality. Here are the steps to accomplish the goal:

Create Tasks List (covered in minimal detail)
Create Meeting Workspace with a Form Library (covered in minimal detail)
Create Form Template
Install qRules and Inject Form
Apply CopyTable Command Rule
Publish the Form and Verify Success

Create Tasks List

Create a simple tasks list.
Add some tasks with a variety of statuses, priorities, and due dates (Fig 1).

Fig 1 - Basic tasks list with several tasks added

Take note that the Active Tasks built-in view only shows tasks that are not completed (Fig 2).

Fig 2 - Active Tasks view only shows incomplete tasks

Set Active Tasks view as Default View.

Create Meeting Workspace

Create a Basic Meeting Workspace with Recurrence (I prefer to use Outlook 2007 to create my recurring meeting, and then I use the integrated Meeting Workspace feature button to provision my site in SharePoint - Fig 3).

Fig 3 - Creation of Meeting Workspace in Outlook 2007

Edit the default page of your new Meeting Workspace and close the default web parts (unless you prefer to use one or many later).
Create Form Library and be sure to NOT click Yes for "Change items into series items" (Fig 4). If we do that, then all forms show on every meeting page (each recurring date). If we leave this as No, then only the relevant form for that meeting will display each week.

Fig 4 - Form library within a Meeting Workspace - not a series item

This will put your new Weekly Status form library on the default page for each recurring meeting within this workspace (Fig 5).

Fig 5 - View of default Meeting Workspace page showing the current week

Create Form Template

Create new template based on built-in sample Meeting Agenda template and modify to your preference (Fig 6).

Fig 6 - Weekly Status form template

Create a Receive data connection to the Team Tasks SharePoint list, but set it NOT to "Automatically retrieve data when form is opened" (Fig 7). Be sure to choose only the fields you want to ultimately see in the form, because this will play a big part in our CopyTable command.

Fig 7 - Tasks data connection with automatic retrieval check box de-selected

Create data structure in form template that exactly matches the data structure of the SharePoint data connection. Your SharePoint Tasks list data connection will include whatever fields you selected during the creation of the data connection from step 2. Simply go to the Data Source pain in InfoPath and choose your "Tasks (Secondary)" data source. Drill down until you get to the nodes. You will notice that the fields within the Tasks repeating group are attributes and not elements (this is important). *Note: You can also see the raw data structure by clicking Save As Source Files and opening your Tasks.xsd file in notepad. Now, go back to your main data source and add a new non-repeating group (aka Table) that includes the Tasks repeating group (aka Row) and all the attribute fields (Columns). Be sure that the field names and data types match exactly for the attribute fields (Fig 8 ).

Fig 8 - Tasks node structure in main data source matching SharePoint secondary data source

Drag repeating Tasks group from Main data source onto the canvas as a repeating table and configure the columns/fields (Fig 9).
- Stretch and shrink the columns so that the data will display properly
- Change the Complete column header to %
- Change the % field control format to show Percentage with ndecimal places
- Change the Due Date field control to a Date Picker (optional)
- You'll also probably want all of these to be read-only fields. *Note: To make a Date Picker control Read-Only, you set conditional formatting on it that says, “If Due_Date is present, then make control Read-Only."

Fig 9 - Tasks Main data source repeating table with formatting

Install qRules and Inject Form

After installing qRules, you will see that it does far more than CopyTable, but those other commands will be the subject of future blogs. You can also get previously-written explanations and discussions related to qRules here.
After a successful install, Inject qRules into the form template (Fig 10)

Fig 10 - Injecting form template with qRules

Apply CopyTable Command Rule

Create button for retrieving Active Tasks on demand and for performing the CopyTable qRules (Fig 11)
- Simply drag a Button to the canvas and change the label to something like Show Active Tasks
- Click the Rules button and Add a rule named something like Query Tasks and Copy
- Add an Action that Queries using the Tasks data connection
- Add an Action that Set's a Field's Value, choose the Command node from the QdabraRules (Secondary) data source, and set the value to this command string:
  CopyTable /dsnamesrc=Tasks /tablesrc=/dfs:myFields/dfs:dataFields /rowsrc=dfs:Tasks
  /tabledest=my:meetingAgenda/my:Task /rowdest=my:Tasks /empty=yes
  - KEY NOTE!! Do not try to paste your command string into the function builder (fx button). Just paste it directly into the Value field.
  - dsnamesrc: This is the name of your source data connection, which is named Tasks in our example. Remember that this could be different if you apply this elsewhere, so be sure to use the proper name for this attribute
  - tablesrc: This is the table within your data source that provides the data. You need to properly type in the hierarchy from the Tasks secondary data connection using the information you found in Figure 8. Notice that the namespace for a SharePoint list is DFS and not MY.
  - rowsrc: This is the repeating group that includes the nodes from your SharePoint data connection. Mine is Team_Tasks, but if you use the default Tasks list from a Team Site, this would just be dfs:Tasks.
  - dsnamedest: This is not shown, because it defaults to the main data source, but if you needed to copy your data to another secondary data source, you would provide the name of that data connection here.
  - tabledest: Like tablesrc, this is the table that will receive the data. This uses the structure we created in our main data source in Figure 8. Notice the default namespace for an InfoPath form is MY, and then my data source root is meetingAgenda due to using the Meeting Agenda sample template.
  - rowdest: This is the repeating group where we want to send the data
  - empty: This is a field that accepts a yes/no flag based on whether you want to first erase all existing data first from the destination table or not.

Fig 11 - Custom button that queries the SharePoint list and performs the CopyTable command

Preview the form and click the button to verify that there are no errors and that your repeating table populates with the Active Tasks only (Fig 12).

Fig 12 - Repeating table in main data source showing Active Tasks

Publish the Form and Verify Success

Publish the form, go to your Meeting Workspace, and click New in the form library
Fill out the form as much as you want
Click the Show Active Tasks button, ensure it populates the table, and then click Save to save the form back to the library
When you see the XML form in the library, click on it and verify that you still see the data
Close the form, then go change an active task to mark it as completed so that it doesn't show up in our Active Tasks view (Fig 13/14)

Fig 13/14 – CopyTable task complete and no longer active

Re-open your existing form (Fig 15) and notice that you still see the previous data (3 tasks, not 2). This is the desired behavior, because we want to know the status of our Active Tasks at the time of the meeting, not later after the meeting when we re-open the form. If we were only showing the secondary data source, then we would always see the current Active Tasks, which is not desired. If you were to click the button again, then it would update with the new tasks, but that is not the intent here (feel free to apply conditional formatting to hide the button after it is saved). You would only hit the button on new forms when conducting future meetings.

Fig 15 – Final view of completed form

Another point of note is that we aren't using the Auto-Generating Filenames for InfoPath Forms concept, because if you use submit in a Meeting Workspace, then it saves the XML form to the root of the form library, which then makes it not visible to any of the recurring meeting dates. Using save allows it to save to its respective meeting (Fig 16) date and thus only see one form per meeting and ensuring that you only see the snapshot of active tasks at that given date.

Fig 16 – Saved form shows up in proper meeting date

Add a License to DBXL without using DAT

Hilary Stoupa — Fri, 20 Nov 2009 23:45:00 GMT

Licenses can be added for DBXL using the DBXL Admin Tool (DAT) or using the DBXL Licensing web service.

To add a license via web service, you must open a web browser on the server where DBXL is installed. Copy your license file to that server, and make a note of the file path.

In your web browser, navigate to:
http://<servername>/QdabraWebService/default.htm
with <servername> replaced with the correct address for your install.

Select DBXL License Management from the Web Service descriptions:

Select AddLicenseFromFile from the DBXL Licensing page:

The method page opens:

Enter the contact email for your license. Enter the file path to the local copy of the license:

Click the Invoke button:

The XML response will show Success as true or false. If there are any errors configuring the license, those will be returned as well. For example, if you add a license that is alread installed:

Be sure to check out the other methods for licensing as well. You can use GetLicenseInfo2, for example, to return the information about installed licenses, and then use the serial number returned for the RemoveLicense method.

Sloooooooooooowwwwww Form

Hilary Stoupa — Tue, 17 Nov 2009 21:18:00 GMT

If you've ever tried to speed up a slow form, you've probably come across this link at MSDN. It is great and has some useful advice and troubleshooting.

However, I have found one single culprit that has caused an unbelievable performance hit in some of my forms, and recently talked to another designer who was able to fix a slow form by de-selecting a single checkbox in a dropdown control:

In both cases, de-selecting the checkbox to show only entries with unique display names quickly resolved performance issues. So, if you have a slow form, by all means, check out the suggestions on MSDN, but first, try changing your drop down controls and see if it makes a difference for you as well.

Submit Items to a SharePoint List with qRules v2.0!

Hilary Stoupa — Mon, 16 Nov 2009 22:41:00 GMT

qRules 2.0 is here, and it has a whole host of cool new commands. One of the real superstars is SubmitToSharePointList. While this command takes a bit more set up than the standard qRules commands, I assure you it is worth the trouble. Once you've walked through this demo with me, I think you'll agree.

Pre-requisites

You will need to have installed qRules v2.0 on your machine. You'll also need to install the InfoPath to SharePoint List Tool that ships with qRules v2.0, as we will use that tool to create the form to library mapping. And, of course, you'll need InfoPath 2007.

The Form

Let's make life easy and start with the Sample Expense Report that shipped with InfoPath 2007. Open InfoPath and in the Design a Form Template dialog, select Customize a Sample:

From the Getting Started dialog, select Sample - Expense Report:

When the template opens in design mode, save a copy some place where you'll be able to find it again. Then close the template. We'll want to inject it with qRules before we go any further, but first, let's create ....

The List

Create a list on a SharePoint site for your Expense Items from your Expense Report form. I've created a custom list that has columns for the Item, the Item Date, the Category and the Amount:

Note the data types for the list items! Amount is currency, Item Date is Date and Time.

Injecting

We need to inject our form template with qRules in order to be able to use it. Make sure your form template is closed, and then open qRules:

Browse to your form template, or drag and drop the template file onto the injector window and select the Inject! button. Logging information will be written to the injector's output window, and you'll get a message that the injection was successful:

Select Ok in the success message dialog, and close the qRules Injector.

Mapping

Next we need to create the mapping.xml file that qRules will use to know what form field should be submitted to what list column. For this activity, we will use the InfoPath To SharePoint List Tool that was included with your qRules v 2.0 purchase (fun side note -- you can also use this tool without qRules for bulk list data loading operations!)

You can open the tool from the install location (the default is C:\Program Files\Qdabra Software\Qdabra InfoPath to SharePoint List Tool) or from InfoPath. The form will open to the Import Forms tab. That tab is for bulk upload operations -- for simple mapping, select the Define Mapping tab:

Attach the Expense Report template that we just injected with qRules to the Source XSN field:

Fill in the site URL for the SharePoint site where you created your list for your Expense Report items, and select the Get SharePoint Lists button:

Select the list you created, and select the Extract Schemas button:

Our Item data in our Expense Report form is repeating, so select the Data is from Repeating Group check box in the Mapping Definition section:

Select the ... button next to the Repeating Group field:

A custom task pane will open, displaying the schema of our attached template:

Navigate to the repeating item node:

And double-click to select and populate the Repeating Group field with the correct XPath:

We can now use the task pane to map the form fields from the item group to the correct columns in our list. Select the ... button next to the Form Field column field:

And double-click the date node inside the item group:

From the drop down list control in the SharePoint Column column, select the column you wish to save the date information to -- in my sample list, that would be the Item Date column:

Add another row to the table and continue mapping in this manner until all your fields and columns are mapped:

Select the Save As qRules Mapping button to save the mapping.xml somewhere you can easily find it in the next steps. You can name the file whatever you choose, but mapping.xml is the default:

Add Data Connections to the Form

Open your injected Expense Report in design mode. We need to add two data connections to our form. First, we need to add a receive data connection to the mapping.xml file we just created.

Navigate to Tools > Data Connections to open the Data Connections dialog:

Select Add to add a new data connection:

Select Create a new connection to Receive Data:

Select Next, and then leave the source as XML Document:

Select Next, then browse to the mapping file we just created:

Select Next, leaving the default as is, to add the XML file as a resource file to the form:

Select Next for the final time, and either leave the default name or give the data connection a new name (make a note of it! You'll need it in a few short minutes!). Be sure to leave the default selected to automatically retrieve the data when the form is opened and select Finish to complete this data connection.

The Data Connection dialog should still be open, so save yourself a click or two, and select the Add button again. This time we are going to add a submit data connection to the SharePoint List we want to submit our data to:

Select (you guessed it, you clever thing) Next, and leave the default of To a Web service:

Enter the URL to the lists web service for the site where you created your Expenses list:

Select Next and select the UpdateListItems operation (no, the operations are not in alphabetical order. Yes, that does bother me, too):

Select Next. We are going to use values from our mapping data connection for our operation parameters. For the tns:ListName parameter, select the ListCollection node from our mapping data connection:

The Data Connection Wizard dialog will look like this:

For the tns:updates parameter, we'll select the entire Batch node from our mapping data connection:

The Include drop down needs to be changed to Entire XML subtree, including selected element:

Before selecting Next, take a minute to verify that your Data Connection Wizard looks like mine:

Select Next. You can leave the default data connection name, or enter one of your own choosing before clicking Finish. Make sure you name it something you can remember, because we'll need that information for our qRules command!

Close the Data Connection dialog -- we have all our data connections in place.

Add a Field for the List Item ID

This bit is optional. But if we want to connect our form items to our SharePoint list, so that we can update existing items rather than always creating new items, we need an ID field in our repeating group to hold the value.

Open the Data Source Task Pane in the form and navigate to the repeating item node. Add a field to hold the ID -- I named mine 'id', but you are welcome to get all creative. I also moved mine to the top of the group -- if you are less neurotic than I (likely), feel free to leave the field at the bottom of the group:

Add the qRules Command

After all that setup, adding the actual command may be a bit anti-climatic, and I apologize for that. However, now that all the pieces are in place, we have it pretty easy.

Navigate to Tools > Submit Options:

This form is already set up to submit via email to the email entered in the managerEmailAddress, so we are going to modify the submit options to use rules. We'll submit to our SharePoint List using a qRules command, then send the form to the manager in email.

Select Perform custom action using Rules in the Submit Options dialog:

Select the Rules button:

The Rules for Submitting Forms dialog opens. Select the Add button to open the Rule dialog:

Give your rule a meaningful name (trust me, you do NOT want to get me started on why 'Rule1' is a miserable naming convention), and select the Add Action button to open the Action dialog:

The action will be Set a field's value, and the field to set will be the QdabraRules Command node:

And finally, the value will be:
SubmitToSharePointList /submit=ShPListSubmit /mapping=mapping /id=my:id

The /submit parameter takes as a value the name of the data connection we set up to the lists webservice for our SharePoint site. The /mapping parameter takes the name of the data connection we set up to the mapping file we created with the InfoPath To SharePoint List Tool. The /id parameter takes the relative XPath to our Id node (if we weren't working with repeating data, it would take the full path).

Set up one more rule with the action of submitting using a data connection to include the email submit the sample came with.You'll have two actions now in your rule:

Select OK until all the dialogs are closed.

And now, to test!

Previewing the Form

Preview your expense report. Fill in an email for the 'manager' (I always use my own when testing) and then add some items to the report:

Check your SharePoint list after submitting:

Ta-da! Items!

But wait, there's more. Perhaps we didn't really go to Disneyland, we went someplace a little less...exotic. Change the value in the form:

And submit again. Refresh (or re-open) your SharePoint list:

Who's got your back, baby? Why, Qdabra, that's who. qRules populated our id node, and now those expense items are connected to the list items.

Sure, sure, you can do this with a CAML query as outlined here. Of course, if you do that, you've got to go off and decode the list GUID. You've got to know what SharePoint is calling your columns. For example, in my list, the Item column started out as the Title column, but I renamed it. SharePoint still thinks of it as Title. The Item Date column -- I initially called that Item, so SharePoint will call it Item from now till eternity. And, if you want to take the CAML approach and you have repeating data, you are going to have to write some code... or have some little button on every single row for single items submits.

For simpler set up, for no code, and for the nifty update feature, I'd take the qRules route. I hope you will, too!

Errors

What? Something went wrong? If you followed the tutorial but when you use the SubmitToSharePointList command in qRules you don't have any items auto-magically appearing in your list, be sure you check the QdabraRules data source error node. We return errors to a node to ensure that you can use qRules in browser forms, but it does mean that you need to take responsibility for error handling. For testing purposes, you can just drag the error node onto your form canvas, click the submit button and go from there. For production, you can return the error in an expression box or a dialog box expression (depending if your form needs to be browser compatible). For additional information on this and other great qRules commands, check out the documentation or the sample form that you'll find in your install location (generally C:\Program Files\Qdabra Software\Qdabra Rules Library).

Also, both the SubmitToSharePointList command and the InfoPath to SharePoint List Tool will not work with attributes. The fields you are trying to submit to SharePoint need to be elements.

QuerySharepoint cannot derive a schema with blank fields

ErnestoM — Mon, 26 Oct 2009 21:53:00 GMT

The QuerySharepoint web service allows you to retrieve data from a SharePoint list. It requires a <query> parameter, which can be built using the QueryBuilder form that comes with DBXL. QueryBuilder allows you to specify the columns you wish to retrieve.

In the screenshot above, you can see that I wish to retrieve three columns. I generate my query and proceed to create the data connections in my form. In InfoPath, I go to Tools > Data connections and create a receive-type connection. For the <query> parameter, I enter:

<query maxrows='2'><columns><column name='Category'/><column name='Unit_x0020_of_x0020_Measure'/><column name='ProductCode'/></columns></query>

Notice that the data source taskpane will show me the xml structure that was returned by this new data connection:

Where are my other two columns?! This issue was recently encountered by one of the users of DBXL (read it here).

If we look at the source SharePoint list, we’ll notice that the first two items in my list contain blank values in those columns.

Because of these blank values, the web service was unable to derive the correct schema.

The workaround is simple: increase the value of the maxrows parameter. Then the data connection “sees” that third row, and returns the columns correctly:

That’s a bit tedious, especially if you have a large list with many blank values. So the workaround becomes even simpler: remove the maxrows parameter!

Use DBXL Submit with Existing SQL Data!

Hilary Stoupa — Tue, 13 Oct 2009 15:01:00 GMT

So, you've invested in DBXL, which is great news for your organization -- all of our web services can speed your form development, help you create more performant forms, give you a great deal of flexibility for saving the data from your forms to different SQL tables and generally make you look like an InfoPath super-hero.

There's just one problem. You have a legacy form, built the old-fashioned way, based off a database. You have one (or more) tables that this form has been submitting to. You want to be able to update your existing data, like you can with your legacy form, but you want to start using DBXL for submitting your data to SQL. Patrick Halstead describes the current DBXL architecture here, and since DBXL takes a delete then insert approach to SQL, you don't have a way to update your existing rows..... Now what?

Never fear. A little SQL magic and you'll be back in your cape, saving the day yet again.

At the highest level, we'll create a new form, starting with a blank form. We'll use Qdabra's QueryDB web service to return existing data to the new form and add a trigger to the main table we shred to that will delete the loaded data when we submit via DBXL. Other Qdabra tools like qRules can assist for special cases, like forms with repeating data sections.

The Scenario

For our sample case, I've created a simple database bound expense report form:

The tables have auto incrementing IDs and a foreign key relationship:

I've already submitted to these tables using my SQL bound form:

ExpenseReports (Parent Table)

ExpenseItems (Child Table)

The New Form

In order to make use of this existing data with a form that has been created to submit to DBXL, we will first want to recreate the form, starting from a blank template:

My new main data source collects the same data that my old SQL bound form did:

Using Qdabra's QueryDB web service, I've added data connections to first return data from the ExpenseReports table, so that I can select from an existing record:

My drop down is bound to the ID field in my form, and while the display value is the name, the value is the record's original ID.

I've added rules to my ID field to populate the Name and Purpose when an ID is selected. I've also added a rule to run another QueryDB query to return the items for the selected expense report. Since InfoPath doesn't currently include a way to copy data from one repeating table to another, you will need to either use code or qRules to copy the returned data if you have a repeating section. I've chosen to use qRules:

I've added a web service Submit connection to my form, and I'm using the Submit method of the DBXLDocumentService to sumit my form. At this point, then, my new form can load data previously submitted by my SQL bound form. If I create a new document type in DBXL and create a SQL mapping for my form and database as they currently exist:

I'll be in for a surprise when I submit my form after having loaded it with existing data and modified some information. I'll end up with new SQL rows for the same data!

The Workaround

First, we need to make some database changes. Some of you will not be overy excited about what I'm going to suggest here, so please bear with me -- I have another solution that may sit more comfortably with you, but does involve code, and I'll talk about that at the end of this post. For those of you who don't want the overhead of adding, writing and maintaining code, this workaround may be acceptable.

We need to set our ID on our main table to not auto increment, remove the foreign key relationship between our two tables, and change the ID column on our main table to not be the primary key. In fact, our main table will be without a primary key all together.

I'm not crazy about removing keys and relationships from my tables. However, since I’m only going to allow data to be entered into my tables from DBXL, and I know that I’m setting up my mapping with the DocId (an int unique to my DBXL install) as the primary key for my parent table and the foreign key to my child table, I feel like I am minimizing my risks. If there comes a day that all of my pre-existing data has been loaded into XML and resaved to the database, I’ll be able to set the DocId as my key in my SQL tables.

Disclaimers now in place, we'll forge ahead. We need to add a column to both our parent and child tables for the DBXL DocId and remove the key and auto increment on the ID field in our parent table. We also need to remove our foreign key relationship:

I'll modify our database mapping to incorporate the DocId. I'm going to manually select the DocId as the key for the parent table. I'll use the DocId field to create a relationship between my tables. I also want to be sure I'm still shredding the original ID from forms that had been previously submitted, because I'll need that information from my inserted data for my trigger:

Finally, I'm adding a trigger to the parent table. This trigger checks to see if there is a row in our parent table that has the same ID as the data being inserted, but a null value in the DocId field. If such a row exists, we know we are dealing with data that has not been inserted by DBXL before, so we need to delete the original rows:

USE [QdabraSamples]

GO

/****** Object: Trigger [dbo].[DemoDelete]    Script Date: 10/12/2009 15:12:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:                                Hilary Stoupa

-- Create date: 2009-10-02

-- Description:         Demo Delete Trigger

-- =============================================

Create TRIGGER [dbo].[DemoDelete]

   ON [dbo].[ExpenseReports]

   AFTER INSERT

AS

BEGIN

                -- SET NOCOUNT ON added to prevent extra result sets from

                -- interfering with SELECT statements.

                SET NOCOUNT ON;



                Declare @id int;

                Set @id = (Select ID from Inserted);



                Delete from ExpenseItems

                Where ReportID = @id

                AND DocId is null;

                Delete from ExpenseReports

                Where ID = @id

                AND DocId is null;

END

GO

Now, if we use our new form, and populate with existing data, we don't end up with duplicate rows:

We also now have an XML file in the DBXL Administrative Tool that corresponds with our database row.

Tidying Up

Remember that drop down in the new form that allows us to select and load an existing row? That should be modified to only display rows where the DocId field is null in our parent table. Once we have an XML file for a row, we want to open that file to make updates to our data, and allow DBXL to handle the SQL tables as usual.

An Alternate Route

For the programmer, or if you don't mind the maintenance costs of adding code to your forms, there are other alternatives. For example, you can use custom code for your submit that will run a stored procedure or SQL statement to delete any rows that have the same ID as the data loaded into your form prior to the web service submit. This works well for data that has been using unique identifiers for keys.

Swap the AD Alias and Name Fields in Place

Greg Collins — Fri, 09 Oct 2009 20:53:00 GMT

In this tutorial you will learn how to use perform an in-place swap of an Active Directory alias and display name so as to conserve space in the view. This sample uses a text field and a button along with conditional formatting, rules and data validation to keep things running smoothly.

While this solution is not for everyone, it will come in very handy for some of you with tight space requirements.

Start a new form:

1. Create a new blank form.

2. Add a Receive data connection to the GetUserInfo method of the Qdabra AD Web Service.

a. Set it to NOT automatically retrieve data when the form is opened.

Create the schema:

1. In the Data Source task pane, add the following text fields: Title, Subtitle, Author, Coauthor, Year, and Description.

2. Add the following text and Boolean attributes to Author and Coauthor schema nodes: displayName, isEditing (default to TRUE).

Design the view:

1. Drag myFields into the view and select Controls In Layout Table.

2. Edit the properties for both Author and Coauthor fields to:

a. Add data validation to show the ScreenTip “Invalid Alias” when isEditing is FALSE and displayName is blank and respective Author and Coauthor is not blank.

b. Add a rule to set the GetUserInfo username query field with the value from Author and Coauthor respectively.

c. Add a rule to query GetUserInfo.

d. Add a rule to set displayName with Value (from the GetUserInfo data connection) filtered on Key=“displayName”.

e. Add a rule to set isEditing to “false”.

f. Add conditional formatting to hide the field when the isEditing is FALSE and displayName is not blank.

3. Add buttons below the Author and Coauthor fields in the table and edit their properties to:

a. Set the respective label with the values of the Author/displayName and Coauthor/displayName.

b. Name the respective ID value to btnEditAuthor and btnEditCoauthor.

c. Add a rule to clear the respective Author and Coauthor value.

d. Add a rule to set the respective isEditing to “true”.

e. Add conditional formatting to hide the button when the respective isEditing is TRUE.

f. Add conditional formatting to hide the button when the respective isEditing is FALSE and displayName is blank.

g. Set the width to 100%.

Preview the form and test. You can apply other formatting to the button as you like, such as removing the border and background shade, and manually left-justifying the text in the expanded view .XSL file.

You can download the sample form I created for this article from here: http://www.infopathdev.com/files/folders/examples/entry47771.aspx

Debug Managed Code in VSTA -- For the Accidental Programmer

Hilary Stoupa — Sun, 27 Sep 2009 13:49:00 GMT

Perhaps you are an accidental programmer. You are the person at your organization who was in charge of some laborious business process and you thought "Gee, we have this InfoPath thing. Maybe I can build a little form for this?". You build the form...and suddenly everyone wants to know if you can do this or if you can do that. You run into a couple of the things that InfoPath doesn't do out of the box, and you see some code samples hanging around....

Next thing you know, you're writing managed code. Or at least copying and pasting managed code and trying to get it to work for your form.

With InfoPath 2007 came VSTA (Visual Studio Tools for Applications). This can be an incredible boon to the accidental programmer, because you can leverage the rich debugging experience of Visual Studio to find your code issues and errors. If you are trying to adapt someone else's code sample for your form, or follow a tutuorial that just isn't working for you, one of the first things you can do is run the project in debug mode.

For the purposes of this simple sample, I've added a loading event to my form.

VSTA opens with the event added:

I've written a little code to set a field's value:

Now, I'm going to add a breakpoint. Click in the margin of the code editor window by the line you want to stop at, and you'll see a big burgundy dot in the margin, as well as a highlight on the line:

We need to run the form from VSTA, so click the toolbar button that looks like an old school tape player's Play button (also under the Debug menu, Start Debugging):

When the form hits that line of code, you'll see it hightlighted:

And now we can have a little fun. Use the Step Over button to go to the next line:

And hover your mouse over the variable that you just set to see what value it has. You can also right click the variable and either Add Watch or Quick Watch to see its values:

Let's see what happens if I have the XPath wrong in my code (a frequent cause of errors -- I'd really have thought that the IP 2007 Copy XPath feature would have prevented those, but we all love our copy / paste too much when it comes to code....):

Not too surprisingly, my XPathNavigator is null, and look what happens when I try to set its value:

Everyone's favorite, the NullReferenceException! But now I at least know the source of my troubles -- that the XPathNavigator I'd created was null. I can focus on the line that is the problem, make adjustments and run my code again.

Setting breakpoints and walking through your code while it runs will not only help you find and understand errors, it will help you understand just what the code is doing in the first place. If you are an accidental programmer, watching your code run can help you learn the syntax and logic of the code sample you've found someplace online, and help you see what you need to do to get that sample to work with your unique form. Happy Debugging!

How To Deploy A Form To Multiple SharePoint Libraries

Jo-Anne Alfafara — Fri, 11 Sep 2009 17:07:00 GMT

There are times when you want to deploy a template to multiple SharePoint form libraries. For example, multiple teams may use the same template but need to keep their documents separate. The typical way to do this is to deploy the form twice, once for each library. However, a few problems arise when you do this:

If a user tries to open a form from the other library, they will get the form conflict dialog even though the form is supposed to be the same.
A user who has cached a form from one library cannot view forms from the other library offline. InfoPath thinks they're different.
To deploy form updates you have to republish to each library separately.
There is a little known feature whereby you can publish the XSN template to one location and then reference it in multiple SharePoint form libraries. The steps to do this are as follows.

DEPLOY A TEMPLATE.XML FILE

1. Deploy the XSN to your shared location. It can be on the same SharePoint server in a document library (using the deploy to file share publish wizard option) or any file share or web site.
2. Launch the form from that location and immediately save a blank form instance and call it "template.xml"

3. Next, browse to the first form library where you want to publish the template. In the top right View dropdown, click Explorer View. In SharePoint 2003, you can find this link displayed in the left column.

4. You will see a Forms folder. Double-click that to go there.

NOTE: Make sure Show hidden files and folders option is selected in your Folder Options. Otherwise, the folder will not be displayed.

5. Once Forms folder is opened, you will see a file called template.xsn.

6. Right-click the template.xsn and delete it.
7. Copy the template.xml file you created earlier into this folder.

8. Click Form Library Settings in the library toolbar. If you’re using SharePoint 2003, click Modify Settings and Columns in the left column.

9. Under General Settings, click Advanced settings.
10. In the Document Template field, rename the Template URL from template.xsn to template.xml. Note you might think you could point directly to the other XSN from here but you can't because this link can only be a file in this library.

11. Click OK and you're done!
12. Now when you click New button in the SharePoint library toolbar it will launch the form that you deployed in the central location.
13. Repeat for other libraries where you want to use the same template.

DEFAULT CONTENT IN THE TEMPLATE.XML FILE

An advanced use of this can also be to have the template.xml file have some prefilled content that you want users to have when launching a new file. At step 2, fill in the default form data and save that locally. You can make a different copy of the template.xml with different default data for each library (only 1 per library). Then upload that file and when the user clicks in the library to create a new form, that default data will be pre-populated.

Qdabra Vertical Tabs Template Part

Mel Balsamo — Sun, 06 Sep 2009 11:53:00 GMT

This blog post provides the steps on how to create an InfoPath form that switches views through vertical tabs. When you’ve followed all those steps and have successfully created your own InfoPath form with vertical tabs, you’ll probably use this technique when designing your forms going forward. It’ll be more cool to have a template part that’s readily available from your InfoPath Custom Controls so you can easily add vertical tabs in your forms.

Qdabra has packaged this template part for you so you can add vertical tabs in your InfoPath solutions anytime, in as many form templates as you want, in a single click!

And not only that!

We’re aware that template parts don’t inherit the color scheme of its destination form. Instead, your template part section will remain to have the default, not-so-exciting Blue theme, when everything else in your form is gray, or burgundy, purple, or what have you. If you wish to change the vertical tabs’ color scheme to match your entire form’s, you will have to edit the styles in each of your layout tables and controls, or worse, tinker with the XSL files and hand-edit them.

Qdabra’s Vertical Tabs XTP is designed to acquire color schemes. We’ve done it all for you!

So if I want the Gray theme:

or Burgundy:

or the Purple Sage theme:

I can pick whichever in a single click.

Cool, eh?

The Vertical Tabs package includes the template part (XTP), along with a user guide and a sample InfoPath form template (XSN) to get you started. Click here to purchase the package.

Create an InfoPath form that switches views

Mel Balsamo — Sun, 06 Sep 2009 08:27:00 GMT

We’ve seen InfoPath forms that have tabs on top, lined up horizontally. These tabs are actually buttons that are meant for switching to different views within the form.

This blog post describes how this is done:

For a change, why don’t we have these tabs lined up vertically instead, like this:

Nice?

Follow the steps below to find out how this is done.

1. Design a new, blank InfoPath form template.

2. Insert a table with at least 6 columns and 1 row.

3. Merge the first 5 columns:

The left column will hold your form template’s fields and controls, while the right is where your vertical tabs will go.

4. Insert at least 4 buttons in the right column, labelling each as Tab 1, Tab 2, Tab 3 and Tab 4, and with IDs tabOne, tabTwo, tabThree and tabFour, respectively.

5. Right-click on the cell where the buttons are > Table Properties > Cell and make all paddings 0px:

6. Add three more views in your form template named View 2, View 3 and View 4:

7. Save your form template as source files:

8. Close InfoPath.

9. Modify the .xsl files

a. In the directory where you saved your source files, open all the .xsl files in any text editor such as Notepad.

b. In view1.xsl, search for “<body style”

The section above represents the entire content of your first view which we will copy onto our other views so that they’d all look similar.

c. In view1.xsl, copy the entire section shown above and paste it onto the other three views replacing this part:

d. This section represents your vertical tabs/buttons:

Note that for each view, we will have a different style for the button corresponding to that view, i.e. the “active” view. For example, the first view (View 1) will have the first button (Tab 1) as the active view button, while the rest will have non-active view buttons.

For your active view, insert the following for the active button:

style="BORDER-RIGHT: #000000 1pt solid; PADDING-RIGHT: 10px; BORDER-TOP: #000000 1pt solid; FONT-WEIGHT: bold; PADDING-BOTTOM: 7px; MARGIN: 4px 0px 0px; BORDER-LEFT: #1e3c7b 2.25pt solid; WIDTH: 100%; COLOR: #ffffff; PADDING-TOP: 7px; BORDER-BOTTOM: #000000 1pt solid; BACKGROUND-COLOR: #517dbf; TEXT-ALIGN: right"

And for the non-active ones, insert the following:

style="BORDER-RIGHT: #000000 1pt solid; BORDER-TOP: #000000 1pt solid; PADDING-LEFT: 7px; PADDING-BOTTOM: 3px; MARGIN: 4px 10px 0px 0px; BORDER-LEFT: #c0c0c0 2.25pt solid; WIDTH: 100%; COLOR: #517dbf; PADDING-TOP: 3px; BORDER-BOTTOM: #000000 1pt solid; BACKGROUND-COLOR: #ebf0f9; TEXT-ALIGN: left"

You will then have the following for view1.xsl:

e. Repeat the same process for the remaining views (.xsl files), inserting the active style tags for the corresponding view, and the non-active ones for the others.

f. When done, save all your .xsl files.

10. Right-click on manifest.xsf and click Design to see the results in InfoPath design mode.

11. In all the views, add rules to all the buttons (except for the button corresponding to the current view) that switches to the other views:

a. View 1:

i. Double-click on the Tab 2 button to open its properties, click Rules > Add > Add Action > Switch View > View 2.

ii. Double-click on the Tab 3 button to open its properties, click Rules > Add > Add Action > Switch View > View 3.

iii. Double-click on the Tab 4 button to open its properties, click Rules > Add > Add Action > Switch View > View 4.

b. View 2:

i. Double-click on the Tab 1 button to open its properties, click Rules > Add > Add Action > Switch View > View 1.

ii. Double-click on the Tab 3 button to open its properties, click Rules > Add > Add Action > Switch View > View 3.

iii. Double-click on the Tab 4 button to open its properties, click Rules > Add > Add Action > Switch View > View 4.

c. View 3:

i. Double-click on the Tab 1 button to open its properties, click Rules > Add > Add Action > Switch View > View 1.

ii. Double-click on the Tab 2 button to open its properties, click Rules > Add > Add Action > Switch View > View 2.

iii. Double-click on the Tab 4 button to open its properties, click Rules > Add > Add Action > Switch View > View 4.

d. View 4:

i. Double-click on the Tab 1 button to open its properties, click Rules > Add > Add Action > Switch View > View 1.

ii. Double-click on the Tab 2 button to open its properties, click Rules > Add > Add Action > Switch View > View 2.

iii. Double-click on the Tab 3 button to open its properties, click Rules > Add > Add Action > Switch View > View 3.

You can now design your views according to your needs. Add your titles, fields/controls, borders and shading, etc…

There’s your vertical tabbing!

When you’ve done all the steps in this blog and have successfully created your own InfoPath form with vertical tabs, you’ll probably use this technique when designing your forms going forward. It’ll be more cool to have a template part that’s readily available from your InfoPath Custom Controls so you can add vertical tabs in your forms in one click! Click here to learn more.

SQL: A workaround for “Cannot generate SSPI context” when using DBXL

dan.balsamo — Mon, 24 Aug 2009 19:59:00 GMT

When working with DBXL and other applications, you need SQL. Some organizations deal with multiple SQL instances and this can create a problem if some SQL instances are on different domains. When we attempt to connect an SQL instance using SQL Server Management Studio, we might encounter an error that reads: ‘Cannot generate SSPI context’.Here’s a workaround if you find yourself in a similar situation:

1. Launch command prompt (cmd).

2. Use “runas” to launch SQL Server Management Studio. Specifying the userdomain\useralias of the account user.

For SQL Server 2008 Management Studio, the command will be similar to this:

runas /netonly /user:userdomain\useralias "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

For Microsoft SQL Server Management Studio 2005, you’ll use this:

runas /netonly /user:userdomain\useralias "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Note: This should work on Vista, Windows 7 and Windows Server 2008, but has not been tested with 2003 or XP.

Identify InfoPath Version Build for Support

dan.balsamo — Mon, 10 Aug 2009 20:56:00 GMT

When helping users with problems we have found it dangerous to make assumptions about the version of InfoPath that is installed.

The user must first identify the version build number of his/her InfoPath:

1. Click Start, and then click Control Panel.

2. In Control Panel, click Add or Remove Programs.

3. In Add or Remove Programs, locate and then click the appropriate Office edition or individual product.

4. Click the resulting Click here for support information link. A message box displays the correct version information for the product as it is recorded by the Microsoft Windows Installer.

Here are the Version Builds by year released:

InfoPath 2003 - 11.0.5531.5606
InfoPath 2003 SP1 - 11.0.6357.6360
InfoPath 2003 SP2 - 11.6565.6568
InfoPath 2003 SP3 - 11.0.8173.0

InfoPath 2007 - 12.0.6028.5000
InfoPath 2007 SP1 - 12.0.6214.1000
InfoPath 2007 SP2 - 12.0.6425.1000

InfoPath 2010 - 14.0.4006.1010

A mutually exclusive radio button in a repeating section

Jimmy — Wed, 05 Aug 2009 01:51:00 GMT

Here's a nifty trick you can use when you want to add a radio button or checkbox to a repeating section or table that can only be checked in one row of the section or table at any given time.

The Scenario
You are creating a form for a team roster for teams in a sports tournament. As a rule, each team may only designate one team captain and one vice-captain. You could enforce this using custom validation, but let's see if we can't do something a bit fancier.

Creating the form
We begin by dragging an empty repeating section into the form:

By default, this section will be created as my:group2.
We then add the fields that we want below my:group2. We create a my:name field to store each team member's name (this will not serve a real purpose in this demo, but let's include it for good measure), a my:captain field and a my:vice-captain field (let's create both of these fields as Boolean (true/false) fields.

Then we drag the fields into the repeating section from the taskpane. First we drag the name field in and create it as a text box. Then we drag the captain field with the right mouse button and create it as a checkbox, and drag the vice-captain field with the right mouse button and create it as a radio (option) button. (Ordinarily you would probably just use one or the other, but for the sake of demonstration we'll use one of each this time.
Creating the radio button should create a checked and unchecked radio button with the words Yes and No next to them. Delete the No radio button and change the "Yes" text to "Vice-captain."

The final result should look like this:

Adding rules
Now that everything's laid out, it's time to add rules to make the fields mutually exclusive. Right-click the checkbox (the captain field) and select Rules... top open up the Rules dialog box, and then click Add... to add a new rule, and name it "Clear other captains".
Click Set condition... to create a condition and set the condition to be captain is equal to TRUE. That is, whenever a user clicks this checkbox to designate a team member as the captain, we want the rule to clear all of the other captain fields.

Now add the rule action. Make the action "Set a field's value." Select the captain field itself as the field to set, and for the value, just type false.

Please repeat this process for the vice-captain field, replacing captain with vice-captain in the instructions above.

Not done yet
If you preview the form at this point, you will find that you are unable to check any of the checkboxes or radio buttons. This is to be expected. As soon as you try to set a value to TRUE, the rule is setting all of the nodes of that field to false, including the field you just checked. To get around this, we'll need to employ a little trick that Hilary Stoupa blogged here.

Editing the manifest
Save the form as its source files, close InfoPath and locate the place where you saved the files. Open the manifest.xsf file in a text editor of your choice.

In the xsf:ruleSets section, you should find the definitions for your rules:

For the first rule, edit the targetField attribute to have the value:

(../preceding-sibling::my:group2 | ../following-sibling::my:group2)/my:captain

This will tell InfoPath to set the "false" value to the my:captain field in all preceding and following repeating nodes.

Now do the same for the vice-captain rule, replacing my:captain with my:vice-captain.

Save the manifest.xsf file, right-click it in Internet Explorer and click Design to open it up in Design mode again.

Now preview the form. If you've done everything right up to this point, you should find that any time you click a captain checkbox, all of the other captain checkboxes become cleared, and the same happens for the vice-captain radio buttons.

Repeating fields: Copy individual nodes using qRules

ErnestoM — Tue, 04 Aug 2009 11:01:00 GMT

Using qRules you can use the commands CopyTable and MoveTable to copy or move an entire table.

But what about copying individual repeating fields? We can do this by using Insert and a couple of InfoPath tricks. The steps below illustrate a simple example from which you can extrapolate others.

First we need to design our form.

1. Create a new, blank InfoPath form and inject it with qRules v1.7.

2. Create a data source as seen in the screenshot below.

3. Go to Tools > Data Connections and create a new data connection that retrieves data from an xml file every time the form is opened. Here is a screenshot of my xml file.

Your secondary data source will look like this:

4. Right-click on the repeating node called name and select Repeating table to insert it into the view.

5. Next to each the text field, insert a button. Double-click on it and change its Label to “Copy over”.

6. Right-click on the repeating node in the main data source and select Repeating table to insert it into the view.

By now, your view looks like this:

We don’t want the destination table to have any rows when the form loads.

7. Click on Tools > Default Values, uncheck the repeating field in the main data source, and click OK.

Now we need to configure our rules.

8. Double-click on the button and click Rules.

9. Click Add, then click Add Action.

10. Select Set a field’s value from the Actions dropdown.

11. Click on the icon next to Field and select the Command node in the QdabraRules secondary data connection.

12. For Value, enter the command we will execute: Insert /parent=/my:myFields /child=/my:field1 /count=1. Click OK.

13. Click Add Action.

14. Select Set a field’s value from the Actions dropdown.

15. Click on the icon next to Field and select the repeating field in your main data source.

16. Click on the icon next to Value, click on Insert field or group and select the repeating node in the secondary data connection.

17. Click OK to close all dialogs.

Finally, we need to manually modify the xpath of the destination to make sure we are only copying into the node we just inserted.

18. In InfoPath, click File > Save As Source Files and choose a destination folder in your local drive.

19. Navigate to that folder and open manifest.xsf in a text editor.

20. Locate the node <xsf:ruleSets> towards the end of the file. This node contains your form’s rules.

21. Find the second rule we created. In the case of our sample form, it looks like this <xsf:assignmentAction targetField="xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:field1" expression="."></xsf:assignmentAction>

22. Change the rule to add [last()], as seen here: <xsf:assignmentAction targetField="xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:field1[last()]" expression="."></xsf:assignmentAction>

Resave the form and test:

23. Save manifest.xsf.

24. Right-click on the manifest and select Design.

Double-click on the button and then click Rules to verify your rules. This is what your rules will look like once you are done:

Optionally, select the second action and click Modify. Verify that the xpath to the target field is underlined; this visual cue indicates that InfoPath has recognized our manual modification.

Now you can preview the form and test it! Click on copy over for John Doe and that value gets inserted into the destination table.

Click on copy over for Bruce Wayne and that value gets inserted into the destination table.