Hilary Stoupa

Fun with Sequential Form IDs, SQL and Sprocs!

2008-11-22T00:41:00Z

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

      Insert into FormIdNumbers (GUID)

      Values (@GUID)

      Commit

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
the FormIdInsertRow sproc is not balanced -- it contains the keyword 'Commit' but doesn't have 'Begin Transaction' anywhere

For the first concern, 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.

For the second issue, while the FormIdInsertRow procedure contains a commit, it lacks a 'Begin Transaction' and, if you execute it, it will run and insert a row, but will generate a message:

While you may want to 'fix' that when you add the sproc to your database, please, learn from my struggles, and leave the unbalanced 'Commit'. When we modify an InfoPath receive data connection command to execute a sproc with a parameter, it will only work if the unbalanced commit is there. Add a 'begin transaction', and you will find yourself where I was, hanging your head and weeping softly into your teacup. If anyone can explain this behavior, do comment away, please, as it makes me mildly unbalanced.

URL Input Parameters 'Gotcha'

2008-10-08T01:55:00Z

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.

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

2008-08-24T17:52:00Z

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

2008-08-22T23:43:00Z

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....

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

2008-07-25T15:59:00Z

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: