Getting a Unique Value for a Field
I've been asked multiple times on how to get a guaranteed unique value for a field in a form. It normally comes up in the context of a tracking id, or a request id - something that you know will be unique and will be a way to recall that entry at some point in the future. Because of the nature of InfoPath and the fact that multiple people can be accessing the form at a time, this is not so easy to do. I posed the question to the hardcore gurus within Microsoft and got a fairly decent feel for how to do this. Both methods use a simple SQL table that has one field in it that holds a value. In one solution (the one I'm most comfortable with), two web methods are configured inside of one service. The first method indexes the current value by one, and the second method retrieves that value. I'll give you the basics of that web service, but you'll need to have some comfort level with building web services in general in order to get from point a to point b. The 2nd method uses only one web method within a web service, and that method calls into a stored procedure on SQL that basically does something similar - it indexes the current value by one and then retrieve the value. Below are the basic steps that you need to undergo.
1. Build a table in SQL, give it one field - call it MyUniqueID - of type int that is a primary key, but not an identity.
2. In Visual Studio 2003, you need to set up a new project, under Visual Basic of the type of an ASP.NET Web Service.
3. Under location, give it whatever location you want. You'll note if you are configuring this web service on the same machine that is running WSS or Portal, you'll need to exclude the path to this web service from being a managed path.
4. Drag and drop SqlDataAdapter from the toolbox into the service1.asmx.vb design space. Configure the connection to the database mentioned in step 1, and then tell it to Use SQL Statements on the page that comes next. Click on Query Builder, select the table you built and select the MyUniqueID field. When you finish the wizard, you'll have SqlDataAdapter1 and SqlConnection1 on the design space.
5. Rename SqlDataAdapter1 to DA, rename SqlConnection1 to CN.
6. Right click on DA, choose Generate Dataset, choose new and check box to Add this dataset to the designer. Rename it to DS.
7. Choose to view Code from the view menu.
8. Add at the top of the code: Imports System.Data.SqlClient
9. Paste in this code snippet, which contains two distinct web methods, below the commented out Hello World sample.
<WebMethod()>_
Public Function UpdateID (ByVal newDS As DataSet)
da.Fill(newDS)
Dim intCurVal = newDs.Tables(0).Rows(0).Item(0)
intCurVal +=1
newDs.Tables(0).Rows(0).Item(0) = intCurVal
da.Update(newDS)
End Function
<WebMethod()>_
Public Function GetNewID() As DataSet
da.Fill(ds)
Return ds
End Function
10. It's fairly self-explanatory what the web methods do. One increments the value and one retrieves that value. Piece of cake right? :-)
11. Hitting F5 will build the solution, and you should be presented with the two web methods, one called GetNewID and one called UpdateID. The GetNewID method can be called from here, the UpdateID cannot.
So assuming you've got this far, it pretty easy to build out the rest of the solution. In the form you are building, you will hook both of the services to be called on open. You'll set a conditional on open that looks at the unique reference field. If it's empty, you'll call the UpdateID service, then you'll call the GetNewID service, and then you'll set the unique reference field to the value of MyUniqueID that you pull from the GetNewID web service, which will look like a secondary datasource in your solution. It's important not to call those services when the form is opened (you should clear those check boxes), but rather only to do it on the conditional rule that fires when the form opens. One last trick that is that when you are plugging in the web services, you might need to put a sample value on the UpdateID service to make it work. I put in a zero on the Set Sample Value - I'm not sure you absolutely need to do that or not.
I will probably append this file with more information the alternative method of using a SQL Stored Procedure sometime in the near future. I need to get more comfortable with that solution first however. Kudos to Scott Heim and Joel Alley for their help in bringing me up to speed with this stuff.