InfoPath storing identity (autonumber) value in the form

[JimJ]

I have my Infopath form working where it will submit data to SQL Server and
the SQL server automatically provides the next sequential number (identity
column), but I then want to store that number in the form (xml) on Sharepoint
so that the number on the form is identical to the unique key value of the
purchase requisition in the SQL server database. That way when the requestor
(or someone) opens that form/document up it will then query the data out of
the SQL server based on that unique key identifier that is stored in the
form. I am also tying this to Sharepoint workflow so that I need that
special unique identifier for the document to tie it to the database data.
I wonder if anyone has some tips and/or best practices with this type of
functionality. It would seem like it should be very common for any type of
documents that need to be tracked with some solid process and be able to rely
on unique identifier, digital signatures, workflow, and a document (that may
also have attachments to it).

Thanks, Jim

 

[BobCh]

Some options:
1) generate a guid in managed code as the pkey, pass this to SQL
2) Get the next key from a table in SQL and apply this to the record.
This is how i manage the issue. One web service that serves up next
numbers to all forms based on a form type. This is a common approach.

/hth

 

[JimJ]

BobCh,
Thanks. I need to get used to how to add code (SQL) to a form when the form
is saved or submitted. We now have 2 versions of the PR form, one where we
just save the form to Sharepoint and the other one where we submit it.
The research I have done so far points me to look at setting up a secondary
data connection (or maybe I could just do that in the Jscript of VB code,
insert a row into an table that has an autonumber field, and then immediately
query/select that row out of the table (so that I know what number it
assigned as the next number).
Then I could take that number and assign it to the variable in the form
before it gets saved (or submitted).
Jim

 

[Moojjoo]

Jim how did you get the InfoPath form to work with SQL Server with Identity
set to YES...???

My form keeps giving me a validation error on my ID field.

Any help would be great.