Lookups in InfoPath when you don't want the dropdown value

Sometimes you want to get a value based on a dropdown, but not actually the value in the ID column. For example, I have a list of project roles as below

RoleId	Name	Value
1	Consultant	15
2	Senior Consultant	20

Here I want a dropdown listing "Consultant" and "Senior Consultant". I need the. I then want to retrieve the corresponding value for use in a calculation. If you try this straight off, you will find that you always get the value first item from the list. This is because the context of which node you want is confused. To provide the "correct" context, you probably want an XPath query like below

../../../my:Roles/my:Role/my:RoleSaleValue[../my:RoleID = current()/../my:TaskRole]

More information can be found in this rather nice article. http://blogs.msdn.com/b/infopath/archive/2004/09/13/228881.aspx

Code Signing for Click Once - The Free Way

When writing click once applications deployed over the web you are going to need to sign your manifest files with a certificate. In development you can quite happily test this by using the "create test certificate" option and use a certificate signed by yourself.

It's not ideal though - particularly if more than one person is developing and deploying the product - as each person ends up with their own certificate. Deploying to test becomes a chore as you have to uninstall from the test device each time to avoid manifest errors.

This issue can be compounded when you have components in the "partially trusted caller" category, such as infopath forms running inside the windows InfoPath form control. Each of these forms can also be signed with test certificates - the end result being anyone who makes a change to the form will be told that the certificate is invalid, resign it with their own, and have further fun deploying to test.

The answer is to make a common certificate for code signing - using the makecert.exe tool. For example:

C:\Program Files\Microsoft Visual Studio 8\VC>makecert -r -pe -n "CN=Temp Code Signing" -b 01/01/2005 -e 01/01/2100 -sky exchange -ss my

creates a pub/priv key signed certificate in the "my" section of your certificate store. You can export this using the cerificate store mmc snap-in, and use the "select from file" option to add the certificate into your project.

Now it is a project file, the certificate isn't called "P_Devenney" or some rubbish, and you can sign your infopath forms from it too. Each developer can sign with the same certificate, saving a load of test deployment hassle, and simulating a live scenario far better.

You can actually use this in a live environment too - if you accept it's disadvantage of being highlighted to the user as an unverifiable certificate. It does have an advantage over commercial code signing certifcates too - you'll see my cert was set to expire in 2100! Unfortunately most providers resign each renewed code signing certificate with a different private key - meaning that the end user has no choice but to uninstall and reinstall - having received scary warnings that the manifest is not from the same publisher! We actually use the makecert certificate in some intranet enviroments, as trusting the certificate once on each device is no real hassle.

InfoPath 2007, SQL05 and ZQuery

As part of the latest project we're looking at an uber Sharepoint 2007 solution. One area we are looking at are storing completed forms data directly to SQL05, and what querying we can do directly.
So, to try this out I created a table with 2 columns as below.

CREATE TABLE [dbo].[tblInfoPathForms]([FormGuid]
[uniqueidentifier] NOT NULL CONSTRAINT [DF_tblInfoPathForms_FormGuid] DEFAULT
(newid()),[FormData] [xml] NOT NULL,CONSTRAINT [PK_tblInfoPathForms] PRIMARY KEY
CLUSTERED ( [FormGuid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON
[PRIMARY]) ON [PRIMARY]

FormGuid is obvious, the second column however uses the new datatype in SQL05 - the xml datatype

How is this different to storing the xml in a text column? Glad you asked. The XML datatype has the following advantages. It's stored as a binary stream, so access is quicker. It also supports the XQuery language directly through SQL statements, allowing you to find node data and manipulate it directly.

So - Having created a test infopath form, published it and completed it. I have my XML. Infopath XML is not too bad really. Inserting the XML is just like any other insert statement.

XQuery can use either XPath structure or the more complex FLWDR syntax - which acts more like SQL. The query below uses $s as an alias for the Xpath root /statusReport and returns the text inside the reportDate node.

SELECT FormData.query('for $s in
/statusReportreturn $s/reportDate/text()')
FROM tblInfoPathForms
WHERE
FormGuid='FE66CE45-891E-453D-8225-E5504D26608B'

Easy! Well....not quite - if you run this query you will get an empty cell back. Microsoft disregards the XQuery specification here and does not return an error message, instead returning an empty result. So what is the problem? Namespaces. Looking at the original XML

xmlns:xhtml="http://www.w3.org/1999/xhtml"
xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-09-22T20:42:56"
xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"
xml:lang="en-us">

we can see that the namespace "my" is used throughout the document. By adding a default namespace for the document in the query prolog we get the correct results

SELECT FormData.query('declare default element namespace
"http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-09-22T20:42:56";
for
$s in /statusReport
return $s/reportDate/text()')
FROM
tblInfoPathForms
WHERE FormGuid='FE66CE45-891E-453D-8225-E5504D26608B'

Now that we can get at the expected result it's just a case of playing about with the query to get other results. XQuery is quite powerful and allows count and sum operations, along with methods to "shred" the XML back into result sets.