InfoPath Dev

XPath's confusing not() function - inverting a boolean field

Jimmy — Sat, 25 Oct 2008 03:19:00 GMT

If you've ever tried to toggle a boolean field back and forth in an InfoPath form, or perform some behavior based on the inverse of a field's value, you may have encountered a problem I found. The not() function does not behave the way one would intuitively expect. The online InfoPath function reference describes the function as follows:

not

Returns true if the argument is false or null. Returns false if the argument is true or not null.

So what happens when a field is false and not null?

In actuality, any time the not() function contains a reference to a field, it returns true if the field exists, and false if it does not exist, regardless of its value.

To demonstrate this, I've created an InfoPath form with a boolean field in an optional section, and set up a rule and expression box like this:

When I preview the form, it exhibits the following behavior:

not(field1) is only true when field1 is non-existent, and when field1 has a value, not(field1) is always false.
What's more, the button changes the value of field1 from true to false, but not from false to true. It's only half of a toggle button!

The solution:

Despite all this, we can obtain the inverse of a boolean field's value with a little finagling.

The XPath expression field1 = "true" will have the value "false" when field1 is false, and "true" when field1 is true; in other words, it will always follow field1's value whenever field1 exists. We can conclude that not(field1 = "true") will be the inverse of field1's value, and that is certainly the case. But if we are going to use an equality expression to determine field1's value, we may as well ditch the not() function entirely, and just use the expression field1 = "false", which will be "true" when field1 is false, and "false" when field1 is true. Here are the new settings for the expression box and the button's rule:

And when I preview the form, I see this behavior:

Both expressions have the inverse of the value of field1 when it exists, and the button correctly toggles field1's value. One final note is that the two expressions have different values when field1 does not exist.

URL Input Parameters 'Gotcha'

Hilary Stoupa — Wed, 08 Oct 2008 01:55:00 GMT

Ah, input parameters. It is so nice to be able to open a form with some values already filled in, isn't it? Except, of course, when they don't work. And to say they are a bear to debug would be an enormous understatement.

I was recently helping a forum user with a form using input parameters passed in from a URL. Every thing looked exactly right. But try as we might, we kept getting an error:

And the field we wanted filled from the parameter remained blank:

For purposes of demonstration, I'll mopdify this code posted on MSDN for the LoadingEventArgs.InputParameters property.

My form code looks like this:

public void FormEvents_Loading(object sender, LoadingEventArgs e)

{

try

{

string vDept = e.InputParameters["Dept"];

XPathNavigator myNav = CreateNavigator();

myNav.SelectSingleNode("/my:TestForm/my:Department", NamespaceManager).SetValue(vDept);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

Now, you'll notice that unlike the MSDN sample, I've added a little bit of error handling. Without it, the input parameters fail silently. While that may be the preferred end result, while trying to figure out why your parameter isn't populating a form field -- well, without a try/catch, you'll end up weeping and smacking your forehead against your desk. We don't want that.

Basically, all this code does is get the value of the input parameter, and set a form field to that value. To use the parameter, you'd add it to the URL for the template, like this:
http://SharePointSite/FormLibrary/Forms/template.xsn?Dept=Accounting

Sadly, follow this exactly and you will get the "Given Key not Present" error. Want to know why? Because on its way to your form, that parameter key got dropped to lower case. So did the value. So we think we are sending in 'Dept' and 'Accounting' but in fact we are sending 'dept' and 'accounting'. Modify the code to:

string vDept = e.InputParameters["dept"];

Guess what? No more error. Republish the form, and you can use the same input parameters from the URL above to open the form with the Department field filled:

Do you notice the case of the word 'accounting'? The value passed was 'Accounting' -- and that is what lead me to guess that the parameter key was being changed to lower case as well.

One last thing to keep in mind with URL input parameters -- be aware you are going to have to do some clean up if you are passing in strings like this. First off, you are going to lose any capitalization. Secondly, if you are passing in a string with spaces, be prepared to clean out all the '%20's.

Creating a Web Link or href for SharePoint New Button on InfoPath Forms Library

NG_Nibbler — Tue, 23 Sep 2008 00:49:00 GMT

Using the New button on a SharePoint InfoPath Document List is not intuitive for new SharePoint users. It would be nice if users could simply click a web link to launch a new InfoPath Form. Here is how to do this in a single line URL ...

Edit a Web Page and add a web link using this format:

<a href="BLOCKED SCRIPTcreateNewDocumentWithProgID(escapeProperlyCore('http://{SERVER}/{SITE}/{LIBRARY}/Forms/template.xsn', true), 'http://{SERVER}/{SITE}/{LIBRARY}/', 'SharePoint.OpenXMLDocuments', true)">Create a New Document</a>

Note: if you see the words BLOCKED SCRIPT in the line above, replace it with 'j a v a s c r i p t :' without the single quotes as shown here and without any spaces. This blog automatically replaces the key word 'java script' (when run together) and the colon with the phrase BLOCKED SCRIPT.

You must replace {SERVER}, {SITE}, and {LIBRARY} with your path to the forms library. Also, you can change the phrase "Create a New Document" to whatever you want.

I have not done this yet, but I also believe that if you have more than one form published to a library, you will need to determine what the form's name is. The default name of "template.xsn" is used in the example above. So, if you have more than one published form, you may need to change "template.xsn" to whatever it is called. You can view and manage all InfoPath templates by going to the Forms Library where the form is published, then select: Settings-->Form Library Settings-->Advanced Settings.

There are posts similar to this on the web; however, none of them are as concise as this single line launcher. Credit for this goes to my colleague, Phil Chuang. Thanks Phil.

Workaround for non-descriptive errors in IPFS

ErnestoM — Fri, 19 Sep 2008 21:41:00 GMT

Sometimes InfoPath Forms Server (IPFS) gives you a non-descriptive error when you try to admin-publish a form using the "Manage Form Templates" UI in SharePoint Central Administration. These steps could provide a more informative error message.

Deploy the solution via stsadm. First time installations should use uploadformtemplate; otherwise use upgradeformtemplate:

stsadm -o uploadformtemplate -filename ...
stsadm -o upgradeformtemplate -filename ... -upgradetype overwrite

This has to be done from the SharePoint machine. Typically stsadm lives in this folder: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN

For example, if the dropped solution is located on a machine from which the admin is term-served from, this can be used (modify the path as needed):

stsadm -o upgradeformtemplate -filename "\\tsclient\C\SScore.xsn" -upgradetype overwrite

So you want to send an email.....

Hilary Stoupa — Sun, 24 Aug 2008 17:52:00 GMT

There are two very common email scenarios with InfoPath. One -- you want to use an email submit connection but modify some things before the email goes out (common reasons: you'd like to use form fields in the Information section which is not currently supported in the user interface, you need more flexibility to determine who the email should be sent to, you have other logic you need to perform prior to sending the form) and two -- you don't want to submit the form via email, you just need to send an email to someone with some form information in it.

Let's address both of those scenarios. A few caveats first --for scenario 1, I haven't included error handling. If your user cancels the submit when they see the preview of the email to be submitted, they are going to get some ugly messages. I'm sorry. It is Sunday afternoon, and I'm being a slacker. Also, for scenario 2, your form will have to be full trust to do cool stuff like send an email that isn't based on a submit connection -- at least, mine had to be.

Here is the data source I'll be using for all the samples, just so you know what the xpath is pointing to:

Okay, without further ado, scenario 1:

Modify email submit properties before submitting:

In this scenario, I have added an email submit connection to my form. My form submit options are set to use my email connection:

Also, I've added a button to my form that I'm *calling* a submit button, but in reality, it is just an ordinary old button I've labeled Submit:

C# -- 2007 Object Model

        public void btnSubmit_Clicked(object sender, ClickedEventArgs e)

        {

            //get the value from the text field

            string interestingText = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:textField", NamespaceManager).Value;

            //get the value from the date field

            string date = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:dateField", NamespaceManager).Value;

            //get that email submit connection

            EmailSubmitConnection emailSubmit = (EmailSubmitConnection)this.DataConnections["Email Submit"];

            //set the introduction with a value from the form

            emailSubmit.Introduction = "This is my shiny new introduction with a date from my form: " + date + "\nAnd an interesting value from a text field as well: " + interestingText;

            //submit the form

            this.Submit();

      }

VB.Net -- 2007 Object Model

        Public Sub btnSubmit_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

            'get the value from the text field

            Dim interestingText As String = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:textField", NamespaceManager).Value

            'get the value from the date field

            Dim dateField As String = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:dateField", NamespaceManager).Value

            'get that email submit connection

            Dim emailSubmit As EmailSubmitConnection = Me.DataConnections("Email Submit")

            'set the introduction with a value from the form

            emailSubmit.Introduction = "This is my shiny new introduction with a date from my form: " + dateField + System.Environment.NewLine + "And an interesting value from a text field as well: " + interestingText

            'submit the form

            Me.Submit()

        End Sub

And, scenario 2:

Send an email with some form information in it:

We are going to use a button click event (I named my button btnEmail -- nothing against the oh-so-intuitive IP default names like 'CTRL15_23' but I am fond of slightly more meaningful names) and send an email to someone with information from the textField and the dateField in our form.

C# -- 2007 Object Model

        public void btnEmail_Clicked(object sender, ClickedEventArgs e)

        {

            //get the value from the text field

            string subject = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:textField", NamespaceManager).Value;

            //get the value from the date field

            string date = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:dateField", NamespaceManager).Value;



            //set the information for the message

            MailAddress from = new MailAddress("someone@someplace.com");

            MailAddress to = new MailAddress("someoneElse@someplace.com");

            MailMessage msgmail = new MailMessage(from, to);

            //the body has the date variable we created in it, the subject uses the value from our text box -- mind you, this is all made up for a demo, so it isn't all that interesting

            msgmail.Body = "A new form was created on " + date + ". Please review it.";

            msgmail.Subject = subject;

            //this stuff is going to vary depending on your setup

            SmtpClient client = new SmtpClient("smtp.someServer.com");

            client.Credentials = new System.Net.NetworkCredential("someUserName", "somePassword");

            //send that baby out

            client.Send(msgmail);

        }

At the top of your code window, with the other "using" statements, be sure to add this:

using System.Net.Mail;

VB.Net -- 2007 Object Model

        Public Sub btnEmail_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

            'get the value from the text field

            Dim subject As String = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:textField", NamespaceManager).Value

            'get the value from the date field

            Dim dateField as String = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:dateField", NamespaceManager).Value

            'set the information for the message

            Dim fromAddress As MailAddress = New MailAddress("someone@someplace.com")

            Dim toAddress As MailAddress = New MailAddress("someoneElse@someplace.com")

            Dim msgmail As MailMessage = New MailMessage(fromAddress, toAddress)

            'the body has the date variable we created in it, the subject uses the value from our text box -- mind you, this is all made up for a demo, so it isn't all that interesting

            msgmail.Body = "A new form was created on " + dateField + ". Please review it."

            msgmail.Subject = subject

            'this stuff is going to vary depending on your setup

            Dim client As SmtpClient = New SmtpClient("smtp.someServer.com")

            client.Credentials = New System.Net.NetworkCredential("someUserName", "somePassword")

            'send that baby out

            client.Send(msgmail)

        End Sub

At the top of your code window, with the other "Imports" statements, be sure to add this:

Imports System.Net.Mail

Now promise to only use your newfound powers for good, and have fun!

InfoPath 2007 Object Model Map

Hilary Stoupa — Fri, 22 Aug 2008 23:43:00 GMT

Okay, I know I'm always a few days late to the picnic, but I found this great poster while online looking for something else, and thought this would be a great place to share it. Now if I just had a humongous color printer, I'd finally be able to gussy up my office properly....

More new DBXL v2.2 Documentation published this week

ErnestoM — Fri, 22 Aug 2008 21:33:00 GMT

The following new documents have been published this week.

qForm:

Permissions:

An Overview of DBXL Permissions
Three ways to configure provider permissions in web.config
How To Enable QdFlow Permissions for a Document Type - note that this requires qForm

Document Deployer:

Document Deployer User Guide
Deleting all documents for a document type using Document Deployer - note that this download comes with the necessary scenario file
Reshredding all documents for a document type using Document Deployer - note that this download comes with the necessary scenario file

Other subjects:

How to use Full Trust form templates in DBXL
How To Setup Server-Side E-Mail Notifications
Bulk Assignment Tool - note that this scenario comes with the tool itself

Check them out if you are using or interested in using DBXL v2.2!

How To Add Documents to Another Document Type Inline

ErnestoM — Wed, 20 Aug 2008 21:39:00 GMT

A solution that uses multiple Document Types that feed data to one another often requires one form to be filled out before another. However, the user might not realize this requirement until he or she has already started filling out the second form.

One example is when you have two Document Types: Customer and Timecard. The Customer Document Type is a qForm that is only opened whenever customers need to be edited or added. The Timecard form pulls from the Customer Document Type to populate the drop-down for assigning hours to a Customer’s Accounts Payable registry. If a user begins to fill out the Timecard form and the customer is not in the drop-down, it is much easier to add the new customer directly from within the Timecard form than having to open the separate Customer form.

Figure 1. A catalog form populating the drop-down for another form.

In this task we will look at the steps necessary to solve this problem.

Add a Connection to SubmitDocument

1. In InfoPath Design mode, open the Data Connections dialog, and then click Add.
2. Add a connection that will Receive data. In the following screen, select Web Service and click Next.
3. Type the URL to the DbxlDocumentService.asmx web service that corresponds to your DBXL installation.
4. Select the SubmitDocument web method, and then click Next.
5. For the docTypeName parameter, type the name of the Document Type that you will be adding documents to, but leave the rest blank. Click Next.
6. Make sure the option to Store a copy of the data in the form template is unchecked, and then click Next.
7. Name the data connection something descriptive, deselect the option to Automatically retrieve data when the form is opened, and then click Finish.

Figure 2. The data source for the SubmitDocument web method.

Add Controls to Input Necessary Data

In the example of adding new Customers from the Timecard form, you will need a control on the view to collect all of the information you need to create a new Customer. You can bind these controls to nodes on the Main DOM, but it is best practice to create a secondary data connection based off an XML document to collect the necessary data.

Get a sample XML document to be added as a secondary data connection

1. Open the target form (in this example, the Customer form) as if you were ready to input a new Customer.

a. If the form is a Catalog form with qForm, click the Add Document button.

2. From the InfoPath File menu, select Save As.
3. Save the XML document to your desktop.

Now you have an empty XML document that embodies what DBXL is going to receive when adding a new document to the Document Type. In the next step we’ll add that XML Document to the Timecard Document Type to allow input of data.

Add the sample XML document to the host form template

1. In the InfoPath Designer, open the Data Connections dialog, and click to Add a new Data Connection.
2. Select to add a new connection to Receive data.
3. Select to retrieve data from an XML Document.
4. Browse to and select the XML document saved to your desktop that was created in the previous section.
5. Select to Include the data as a resource file, and then click Next.
6. Name the data connection something significant, deselect the option to Automatically retrieve data when the form is opened, and click Finish.

Now that the data connection has been added, create the controls to allow entry of the data on the view. Your design might look something like the Figure 3.

Figure 3. Adding controls to collect document information.

Codeless versus Coded Implementation Techniques

This task may be accomplished by using rules to construct the necessary XML structure and then submit that XML to the web service. Using rules instead of code can be a quick and easy way to implement this feature for simple forms, but will quickly become too cumbersome for forms that have numerous fields. In the case of adding more complicated documents, you should use the coded implementation.

Adding Rules to Submit the New Document

After all controls have been added, you are ready to add the rules to the Upload button that will upload a new document. To construct the XML needed to submit to DBXL with Rules you must piece it together from one long string using the values that are stored in the secondary data source.

Breaking the XML Document into manageable pieces

1. Open the XML Document saved to your desktop in a text editor, such as Notepad.
2. Separate the document into pieces that you will concatenate together with the values of the secondary data connection. This process is shown in Figure 4.
3. Eliminate the Qdabra DBXL PI node, which will look like this: <?QdabraDBXL docid="" doctype="Customer" name="" author="" description="" ?>

Figure 4. Breaking up the XML.

Add the rules to the Upload button

1. Add a rule to the Upload button that will set the value of the /myFields/dataFields/SubmitDocument/xml node, as seen in Figure 5.
2. For the value, click the fx button to add a new formula.
3. Select the option to edit the XPath directly, and type the following:

concat(‘XML Fragment 1’, XPath_to_FullName, ‘XML Fragment 2’, XPath_to_PhoneNum, ‘XML Fragment 3’, XPath_to_Email, ‘XML Fragment 4’)

4. Be sure to replace the XML Fragment strings with the appropriate XML that you segmented in the previous step.
5. For the XPaths to the various nodes, you can click on the Insert Field or Value button in the expression builder to graphically find the node.
6. Click Ok until you are back to the Rules dialog box for the Upload button.

At this point, you will have a button that creates the necessary XML on the secondary DOM, so now you must add the rules to query the appropriate data connections.

Add rules to query the data connections

1. Click to add a new rule, select the Query Web Service action, and specify the SubmitNewDocument data connection. Make sure this rule executes after the rule that creates and sets the XML string.
2. Add another rule to query the data connection that pulls the Customer list from the database, and then close all dialog boxes.

Figure 5. The completed rules.

Coded Implementation

Implementing this feature using managed code is much simpler and allows for easier manipulation of the XML fragments. Below is an example of creating the necessary XML, loading it into the xml parameter, querying the web service, and then updating the view to reflect the new data.

[InfoPathEventHandler(MatchPath="btnUploadNewCustomer", EventType=InfoPathEventType.OnClick)]
public void btnUploadNewCustomer_OnClick(DocActionEvent e)
{
    IXMLDOMNode nNewCustomerNode = builtInLogic.GetNode(domNewCustomer, "/my:QdCatalog");

    // Set the query DOM with everything needed to query
    string name = builtInLogic.GetNodeValue(domNewCustomer, "/my:QdCatalog/my:QdForm/@my:name");
    string author = builtInLogic.GetNodeValue(domNewCustomer, "/my:QdCatalog/my:QdForm/@my:author");
    string description = builtInLogic.GetNodeValue(domNewCustomer, "/my:QdCatalog/my:QdForm/@my:description");

    builtInLogic.SetNodeValue(domSubmitNewCustomer, "/dfs:myFields/dfs:queryFields/tns:SubmitDocument/tns:name", name);
    builtInLogic.SetNodeValue(domSubmitNewCustomer, "/dfs:myFields/dfs:queryFields/tns:SubmitDocument/tns:author", author);
    builtInLogic.SetNodeValue(domSubmitNewCustomer, "/dfs:myFields/dfs:queryFields/tns:SubmitDocument/tns:description", description);

    builtInLogic.SetNodeValue(domSubmitNewCustomer, "/dfs:myFields/dfs:queryFields/tns:SubmitDocument/tns:xml", nNewCustomerNode.xml);

    // Upload the new customer
    ((WebServiceAdapter2)thisXDocument.DataAdapters["SubmitNewCustomer"]).Query();

    // Enumerate from the DB to pick up the latest customer
    ((WebServiceAdapter2)thisXDocument.DataAdapters["EnumCustomer"]).Query();

    // Determine the last one added in the list, assume sort by DocId is default
    string sLastCustomerDocId = builtInLogic.GetNodeValue(domCustomer, "/dfs:myFields/dfs:dataFields/ns1:GetColumnsForRows2bResponse/ns1:GetColumnsForRows2bResult/Rows/row[last()]/DocId");

    // Set the value of the drop-down to the most recently added customer
    builtInLogic.SetNodeValue(String.Format("/my:QdCatalog/my:QdForm[ @my:docId = '{0}' ]/my:Estimate/my:Customer/my:CustomerDocId", builtInLogic.DocIdToView), sLastCustomerDocId);
}

Reshredding all documents for a document type using the Qdabra DBXL Migration Tool v2

Greg Collins — Tue, 19 Aug 2008 19:28:00 GMT

If you have to reshred a lot of documents, there is an easier, automated way of doing this rather than using Qdabra DBXL Administration Tool (aka DAT).

Download and install the Qdabra DBXL Migration Tool.
Download the Scenario File for documents reshredding.
Open the Migration Tool UI.
Make sure DBXL Server Root is set to your DBXL (normally http://myserver/QdabraWebService).
Select the "custom" tab and load the scenario you've downloaded in step 2.
Scroll down in the scenario variables list and set "docType" to the document type you want to clear.
Scroll down the Scenario Variables list and change sourceDBXL/targetDBXL.
Select "ReshredDocuments" in the scenario runs and click "Run". This should reshred all the documents from the specified document type:

More new DBXL v2.2 Documentation

ErnestoM — Fri, 15 Aug 2008 22:22:00 GMT

This week we have published three new documents related to DBXL v2.2.

If you are interested in setting up server-side notifications for your forms, check out the document called How To Setup Server-Side E-Mail Notifications.
If you are developing a solution based on our QdCatalogBase.xsn form and are interested in promoting properties other than those promoted by default, check out How to Use Promoted Properties in qForm.
If you have an existing solution based on the QdCatalogBase.xsn form, you might be interested in deleting documents for a variety of reasons. However, special care must be taken when doing this, as indicated in the DBXL v2.2 Release Notes. For information, please read Deleting Documents and Flow for a qForm.

For a full catalog of the available documentation for DBXL v2.2, please see the DBXL v2.2 Documentation node in the Browse Folders menu table.

Two new documents related to DBXL v2.2

ErnestoM — Thu, 31 Jul 2008 18:08:00 GMT

While this week sees the publication of only two documents, they cover two very important topics.

The Web Service Developer's Reference contains development tips and web method descriptions, useful for anyone intending to develop a solution using DBXL.
The QueryDB User Manual explains the use of QueryDB, a web method that allows your DBXL instance to gather data from SQL, as well as Query Builder, a form tool that makes working with QueryDB an easy task.

For the list of all available documents covering DBXL v2.2, click on the DBXL v2.2 Documentation node on the right, as well as all its subnodes. Stay tuned for future updates!

New documentation available for DBXL v2.2!

ErnestoM — Fri, 25 Jul 2008 19:38:00 GMT

Now that DBXL v2.2 Beta is available on our website, we have begun to publish documents that relate to this new product. This week (June 21 to 25) we have published the following documents:

How to Create Nodes in Taxonomy: DBXL allows you to tag your qForm-based solution with a set of category filters. This document will explain how to use the qForm feature.
Using DBXL Resources: The DBXLResource API allows users to use custom XML in their DBXL Document Type Configurations. This document explains various scenarios that merit the use of XML resources.
How To Use the Bulk Edit Tool: Qdabra’s Bulk Edit Tool allows users to quickly perform bulk editing operations. This document will explain the options available in the Bulk Edit Tool, and it comes with the BulkEditForm.xsn file.
Installing and using QuickSearch: QuickSearch is a standalone web service that runs against DBXL v2.1 and v2.2. This document explains the steps required to setup and use QuickSearch in your forms.
How To Query the Active Directory Web Service: The Active Directory Service supports two methods: GetUserInfo and GetMyInfo. This document will show you how to set up a sample form that queries GetUserInfo with a user-entered username string.
Mapping and shredding in DBXL is a hot topic in the forums. You can now find the following related documents on InfoPathDev.com:

For a full list of documents available for DBXL v2.2, see the DBXL v2.2 Documentation node.

Take Two -- Mapping Data from Nested Repeating Tables to a SQL Database - DBXL v2.2

Hilary Stoupa — Fri, 25 Jul 2008 15:59:00 GMT

In an earlier post, we looked at mapping data from nested repeating tables using DBXL v2.2. In that post, the SQL table associated with the main repeating form group used an identity column that was set to auto-increment for the key. DBXL, however, currently follows a delete and reshred model (see this post by Ernesto for more information) which would mean that our identity would change every time the document was updated.

While this may not be an issue for some scenarios, there could be cases where you would prefer your identity to remain a bit more stable. One way to work around this is to use the DocId (generated by DBXL) for your key -- after all, that number remains the same for the life of the document. However, in our initial example, we have mapped a repeating group for the node path for our main table (Projects). We could have one to umpteen projects for a given DocId!

I am sure there are many creative solutions to this problem, but only one occurred to me, and that is what I'd like to walk through in this post. What if we modify our Projects table to use a composite key consisting of the DocId and a ProjectId that identifies each project group in the document? We could do away with auto-incrementing completely. The foreign key on the Issues table would relate back to the DocId and ProjectId, and then, upon re-shredding, none of our identites will change.

First, let's modify our form schema:

We've added a ProjectId field. The datatype is integer, and it has a default value of count(../preceding-sibling::my:Project/my:ProjectId) + 1 which will give us a running count of our groups -- the field's value will be 1 for the first group inserted, 2 for the second, and so forth.

Now we need to modify our database. Add a column called ReportId (we'll map the DocId to it) to both the Projects and the Issues tables. In the Projects table, remove the Identity specification from the ProjectId column, as we'll now be mapping our new ProjectId field to that column.

We need to set ReportId and ProjectId as a composite key for the Projects table. While your table is open in design view, right click anywhere on the canvas and select Indexes/Keys:

When the Indexes/Keys window opens, you can select the button next to the Columns field in the General section and select the columns you wish to use for your composite key:

Don't forget to modify the foreign key relationship with the issues table:

To our mapping! We need to make a few changes -- we are going to map ResourceId to the DocId and ProjectId to ProjectId. When you select your columns from the Database Tree, your keys will be marked as such.

Don't forget -- special DBXL fields are available to you for mapping in the Schema Tree:

Next, we adjust our Issues mapping to reflect our changes, first setting up our keys:

And, after we add the rest of the Issues columns, our full map looks like this:

Let's cut to the fun part. We save our new mapping, and open our form, fill it out and submit it:

When you look at the tables, you can see that our DocId is being used for ReportId and the new ProjectId field is providing a ProjectId:

And, in Issues, we are relating all of a project's issues back to it, as well as relating the issues to the report:

For greater database integrity, you could use the same approach as we used for Projects to provide the Issues group in the form an identity, and create an IssuesId column in your table. Your primary key for your Issues table could be a composite key consisting of ReportId, ProjectId and IssueId.

We can now reopen our form that we just submitted and make some changes, as well as adding additional rows:

Once again, we race to the database, happily clapping our hands, to make sure that all our identities remain unchanged and our new identities are what we would expect:

Codeless way of getting DBXL document properties (docid, doctype and DBXL URL)

davidair — Tue, 22 Jul 2008 03:08:00 GMT

A DBXL solution that is designed to be generic needs to be able to access several DBXL properties. For example, a solution that wants to display a hyperlink in an e-mail needs to be able to determine its docid and the DBXL URL.

While it's easy to write code to retrieve this data, it is often not desirable to do so. For instance, browser-based solutions are much harder to deploy when they contain code.

This post describes a way of getting this information using only rules. Here is what you need to do:

To get the DBXL document ID, add a docId field to your data source and set it on load via this rule:
substring-before(substring-after(processing-instruction()[local-name(.) = "QdabraDBXL"], 'docid="'), '"')

Note that this value will not be set for a new document

To get the DBXL URL, add a url field to your data source and set it on load via this rule:
substring-before(substring-after(processing-instruction()[local-name(.) = "mso-infoPathSolution"], 'href="'), "/Forms")

Finally, to get the DBXL document type, add a doctype field to your data source and set it on load via this rule:
substring-before(substring-after(processing-instruction()[local-name(.) = "mso-infoPathSolution"], concat(url, "/Forms/")), "/template.xsn")

Note how the last rule builds on the url value we've gotten previously.

It is important to understand that these rules will only work after the InfoPath solution has been published to DBXL.

You can download a full sample here: http://www.infopathdev.com/files/folders/community_uploads/entry30638.aspx