I may be exagerating when I say 'fun'. Forgive me for that.
There are a variety of ways that you can create a unique, human readable form Id. This blog post is for those of you who prefer not to have to write or buy a web service, and don't want to write more than a little code. I'm warning you -- you will have to write some stored procedures. Or, you know, copy / paste them. It sounds scarier than it is, so let's soldier on.
The Problem
You have a form that you want to give an Id field, for whatever reason. You want your Ids to start with a particular number (we are going to start with 435, just because) and for each subsequently saved form to increment by 1. Now, if your form submits to a database, you can just use an auto incrementing Id column and sleep soundly knowing your form will get the next Id when it is submitted. You don't have a problem. But if we have a form that we save in a network folder, or submit to SharePoint, or do something else with ... well, you have a problem.
The Solution
Actually, a solution. There are a lot. This one involves creating a single SQL table to create and track Id numbers, and two stored procedures to help us make sure the right form gets the right Id. While I am going to add this code to the save request in the form, you can add it to another event if you prefer. We will be adding a couple of data connections to the form, so it will need domain trust, but it shouldn't require full trust.
The Steps
The SQL
Create a new table in a SQL database called 'FormIdNumbers' with two columns: GUID, which will be a unique identifier, and ID, which will be an auto incrementing identity column.
Next, create two stored procedures. The first, called 'GenerateGUID' simply creates a new GUID we can use to uniquely identify our form while we create and retrieve its Id number:
Create Procedure GenerateGUID
As
Begin
Set NoCount On;
Select NewID() as GUID;
End
The second procedure will take a GUID for a parameter, and insert a new row in our FormIdNumbers table:
Create Procedure FormIdInsertRow
@GUID uniqueidentifier
As
Begin
Set implicit_transactions off
Insert into FormIdNumbers (GUID)
Values (@GUID)
End
The Form
Create a simple form with a field on it that you want to populate with your Id number. I'm just using a text field, so I don't have to fuss about with the xsi:nil attribute. You may be more particular and wish to use an int.
First, we will add a receive data connection that we are going to modify to call our GenerateGUID sproc. Walk through the data connection wizard, and connect to the FormIdNumbers table. When you get to the screen that shows your table and columns:
click the EditSql button.
Inside the Edit SQL field, type Execute GenerateGUID:
Select OK. You'll get a warning, you can select Yes on that:
As an aside, all that means is you won't see a view of your data structure when the dialog box closes and you are back in the Data Connection Wizard.
When you select Next in the Data Connection Wizard, you'll get another warning:
It is true. InfoPath does not know if the query is safe. We do, though. Select Yes again. No need to select the "Store a copy of the data in the form template" checkbox on the next screen. On the last screen, rename the data connection GenerateGUID and decide if you'd like to retrieve the data when the form opens. I'm not going to -- I'm going to run the query from my code. If you run the query on load, you won't need to run it in code, so keep that in mind later....
Create another receive data connection in your form to the FormIdNumbers table -- this time, leave the SQL alone, so we have a normal old connection to the table. Deselect the "Automatically retrive data when the form is opened" checkbox, as we won't want the data until we've run our FormIdInsertRow sproc and modified this data connection to include a 'where' clause. Name the connection FormIdNumbers:
The Code
You need to make an architectural decision at this point. When do you want to generate the Id number? As I mentioned earlier, I plan to generate my Id and populate the Id field in the Save Request event for my form. You may wish to add a button, or put the code in a particular field's Changed event -- it is up to you.
First I have to set my form to use custom code for its save behavior. Go to Tools > Form Options and select the "Save using custom code" checkbox:
Click the Edit button to generate the event handler. You may wish to check your programming language before clicking the Edit button to generate the event handler. The following code sample is C# for the InfoPath 2007 Object Model. Replace this line in the FormEvents_Save method:
// Write your code that will run before a Save or Save As operation here.
With this:
//get the value of the Id node. You'll need to replace "/my:myFields/my:IdNumber" with the XPath to your node
XPathNavigator idNode = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:IdNumber", this.NamespaceManager);
//if not blank, don't bother, we already have an Id
if (idNode.Value.Equals(""))
{
try
{
//set up the secondary data sources
XPathNavigator domGuid = this.DataSources["GenerateGUID"].CreateNavigator();
XPathNavigator domFormId = this.DataSources["FormIdNumbers"].CreateNavigator();
//set up the connections
AdoQueryConnection genGUID = (AdoQueryConnection)DataConnections["GenerateGUID"];
AdoQueryConnection formId = (AdoQueryConnection)DataConnections["FormIdNumbers"];
//query the GenerateGUID connection once to generate the GUID. If you are retrieving this data on form open, you can skip this.
genGUID.Execute();
//get the GUID we just retrieved. You may want to recopy this XPath from your data source task pane....
string guid = domGuid.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:row/@GUID", this.NamespaceManager).Value;
//set the command to execute our FormIdInsertRow sproc. Save the original command before modifying, so you can set it back after. We are passing in the GUID we created earlier as a param in our sproc. This way, when we create our new id, we know that we can retrieve it with our Guid and be secure that we have the right Id for the right form
string origGuidCommand = genGUID.Command;
genGUID.Command = "Execute FormIdInsertRow '" + guid + "'";
genGUID.Execute();
//get the original command for the FormIdNumbers data connection
string origFormIdCommand = formId.Command;
//add the 'where' clause
formId.Command = origFormIdCommand + " where GUID = '" + guid + "'";
//execute the query
formId.Execute();
//set the commands back to their original states
genGUID.Command = origGuidCommand;
formId.Command = origFormIdCommand;
//set the field to the ID
idNode.SetValue(domFormId.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:FormIdNumbers/@ID", this.NamespaceManager).Value);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
Of course, if you changed the names of the data connections, stored procedures, or anything else as you walked through this, you will need to modify the code to address those changes.
If you are using InfoPath 2003, here is a JScript version:
//get the value of the ID node. If blank, skip
var idNode = XDocument.DOM.selectSingleNode("/my:myFields/my:IdNumber");
if (idNode.text == "")
{
try
{
var domGuid = null;
var domFormId = null;
//set up the secondary DOMs
domGuid = XDocument.GetDOM("GenerateGUID");
domGuid.setProperty("SelectionNamespaces", 'xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');
domFormId = XDocument.GetDOM("FormIdNumbers");
domFormId.setProperty("SelectionNamespaces", 'xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');
//get the data objects
var genGUID = XDocument.DataObjects("GenerateGUID");
var formId = XDocument.DataObjects("FormIdNumbers");
//query the GenerateGUID connection once to generate the GUID. If you are retrieving this data on form open, you can skip this.
genGUID.Query();
//get the GUID we just retrieved. You may want to recopy this XPath from your data source task pane....
var guid = domGuid.selectSingleNode("/dfs:myFields/dfs:dataFields/d:row/@GUID").text;
//set the command to execute our FormIdInsertRow sproc. Save the original command before modifying, so you can set it back after. We are passing in the GUID we created earlier as a param in our sproc. This way, when we create our new id, we know that we can retrieve it with our Guid and be secure that we have the right Id for the right form
var origGuidCommand = genGUID.QueryAdapter.Command;
genGUID.QueryAdapter.Command = "Execute FormIdInsertRow '" + guid + "'";
genGUID.Query();
//get the original command
var origFormIdCommand = formId.QueryAdapter.Command;
//add the 'where' clause
formId.QueryAdapter.Command = origFormIdCommand + ' where "GUID" = ' + "'" + guid + "'";
//execute the query
formId.Query();
//set the commands back to their original states
genGUID.QueryAdapter.Command = origGuidCommand;
formId.QueryAdapter.Command = origFormIdCommand;
//set the field to the ID
idNode.text = domFormId.selectSingleNode("/dfs:myFields/dfs:dataFields/d:FormIdNumbers/@ID").text;
}
catch (err)
{
XDocument.UI.Alert(err.description);
}
}
And now....
The Results
Preview your form to test it. When I save my form, since this is the first time my form will connect to the data sources I've created, I get a warning:
If I click Yes, my Id field will be populated before the Save Location dialog opens:
Addendum
Those of you who are stored procedure savvy will recognize that if the FormIdInsertRow sproc contained a 'Select' we could return our result set with that instead of running another query.
I ran into an exciting InfoPath feature when it comes to auto incrementing identity columns. When InfoPath uses the data connection to create the schema, it rolls back the transaction, but not the identity. This means that if I used a sproc that added my new row and returned the results so I would have my new Id number for my form, I would effectively get every other Id number. This thread on a different site describes this issue a bit further.
Because of the double call issue, the solution above executes a sproc that takes a GUID as a parameter, then uses the same GUID to retrieve a row from our data table to get the ID number associated with that GUID.
Originally, this blog post had an unbalanced 'Commit' statement in the suggested sproc. That was because when I modified an InfoPath receive data connection command to execute a sproc with a parameter, it only worked if the unbalanced commit was there. Jimmy Rishe saved the day (again) and found out that if we set implicit_transactions to off in our stored procedures, everything runs like NASCAR.