Generate SQL Unique Identifier in Infopath

[IT PHYTOSAN]

Our database uses primary keys in the unique identifer format
{XXXX-XXXXXX-XXXXXX-XXXX-XXXX or so} The data base has a default "newid()"
entry

When trying to generate a new record in InfoPath I would like the SQL server
to automatically asign a new id. This is not possible, however, because
primary keys can not be empty for infopath to submit to the database.

Alternatively, we could have infopath calculate the unique id and I am
looking for instructions on how to accomplish this.

Thanks

IT PHYTOSAN

 

[Adam Harding]

IT PHYTOSAN

Firstly the field for the unique identifier has to be in Autonumber format
or whatever the SQL equivalent is. Second get the users to fill in the form,
submit the data to the data source then add a rule AFTER the submission event
to show a dialogue box expression stating the unique ID.

I hope this helps

Cheers Adam

 

[IT PHYTOSAN]

Thanks for your advice Adam. I am not quite sure I understand your
recommendation, but I managed to help myself in a different manner:

Create dummy table NEWID in SQL with columns ID and NEWID. The NEWID column
is a calculated column with the argument newid()

Create a secondary data connection poinint to NEWID(). Create a rule to
query this connection whenever a new item is to be added via Infopath.

In the ID field that requires the uniqueidentifier put the default value
newid from the secondary data connection. Make sure that the field update
this value every time... is unchecked.

This way InfoPath inserts a new random id through the newid() field in SQL

Thanks

IT PHYTOSAN

 

[Adam Harding]

That'll do the trick no problem, only thing is that it is RANDOM meaning that
eventually it will repete (I know it will be a long time) only by
sequentially numbering or time-date stamping can you enusre uniqueness.

What i meant was in your SQL table one of the fields numbers itself
automatically to autonumber using the Int datatype and making it the primary
key it autonumbers. so i have a button on a form that submits the data to my
database, it then reads the value of the primary key the the data has been
added under and reports it back to the user. Saying ' your unique ID is
XXXXXXXX' using a dialogue box.

I hope this is clearer as it is quite elegant.

Cheers Adam

 

[IT PHYTOSAN]

Yes that is clear and elegant, Thanks. Let me comment, that unless you are
planning on a database that registers every proton in your body, a 32 HEX
unique ID will never, ever repeat, it is just to unlikely. The probability of
2 random numbers being alike P=16^-32=3.4*10^-38. The reason I can't go with
sequences, is that we replicate this data base to a remote site with updates
in both locations. This can cause conflicts if updates occure simultaneously
in two locations ending up with the same unique id after replication.

Thanks

Again

 

[Adam Harding]

Fair play, we are talking longer than the age of the universe type time
aren't we so why bother.

Sorry i didn't mean to lecture to you just explain my idea better.


Glad you got it solved

Adam

 

[IT PHYTOSAN]

I did, not at all feel I was being lectured. On the contrary, thank you for
your effort and intelligence.

IT PHYTOSAN

 

[IT PHYTOSAN]

The way we solved our problem by the way is to introduce a new table in the
data base we called NewID. The table has a primary key and a field with
calculated value newid(). A secondary data connection queries this field.

The default value of a field that should receive a new random id points to
this data connection. The "update field every time..." check box is cleared
to make sure the value once retrieved does not change anymore. A rule on the
field queries the data connection again to make sure that the next ID is
different from the first one.

Cheers

IT PHYTOSAN