InfoPath Dev

I want to make a read-only autonumber field in my form.  I knwo there's a lot of infomation out there covering autonumbers in Infopath, but none of it seems to fit my problem.  Please help I've been looking for days.

I need a read-only field that autonumbers on Submit.  I thought I could do it by querying the SQL DB, and adding 1 to the result, but I have no idea how to modify the query's results.  I also know I can set up a custom workflow for an autonumbering sequence, but there's no way IT would let me get ahold of Sharepoint Designer.  This field makes up part of my file name, and it also needs to write back to form's SQL table.  Hilary Stoupa's article (here) would have been perfect I think, but the code for the sprocs doesn't work - Toad spits back an error saying "Invalid syntax near 'Procedure'" on the first sproc.  Any ideas would help greatly, it's the last thing I need to do on this form!

Just to clarify -- the create statements for the sprocs in my blog post *do* work in SQL Server Management Studio.... but TOAD doesn't seem to like them. When you say 'I have no idea how to modify the query's results' -- couldn't you just use the field in a formula? Say, in the default value for the form field that will hold the ID? The other thing you can do is edit the SQL in a regular InfoPath receive data connection... something like 'SELECT MAX(columnname) + 1 as ID FROM tablename'.

Of course, that has the risk of two forms with the same ID -- there is always the chance that User A and User B will open a new form and the query will run before either of them has submitted....

I never doubted they didn't work, the complicated formula you banged out for me awhile back made sure to that.  The same ID issue could be fixed if the query is run on click of the submit button, right?

Well, you will greatly reduce the chance of a multiple IDs if the query is run on submit, yes... Or, you know... Are you submitting to a DB? Can you add an auto-id column to the table you submit to? Then you don't have to try to grab a max and add one or anything like that.... the form would get its ID on submit....

But I need the filename to contain the ID because multiple people will be going in and out of this form as it makes it's way through it's approval workflow.  The ID is set up in the DB as an autonumber.

Where all does your form submit? Do you submit via rules? You could submit just to the DB, get the ID back, set the form's name and sumbit to SharePoint, for example. Just a thought.

The form submits to Sharepoint only until the very last section is filled out.  At that point, there's a button that submits to both Sharepoint with a read only version (overwriting the R/W version) and sends the data to the SQL DB.  I have the information being submitted via rules.

So up until the last section is filled out, the form info doesn't go to the DB. Yet you need a unique ID that the form can use in its name for the entire life of the form.... But since the form isn't submitted to SQL until the end of its activity, querying the SQL table that you store them in could surely lead to duplicate IDs. After all, it could be weeks between the initial query for the max ID and the submittal of the form -- with no way of knowing how many other forms had been started between those two things. Is there anything in SharePoint that you could use for this, I wonder?

You nailed it on the head.  I know there's a way to retrieve the Sharepoint ID for the file and use a rule to set the value of the field equal to that plus 1.  I'm guessing it's max(@ID) + 1, however I haven't been able to get it to work quite yet.

Sorry I was slow getting back to you on this. What happens when you try the formula above?

Sorry I was so slow getting back to you!  I tried it, but for whatever reason, it never populated.  I can set it up to query the DB ID field, and then add 1 to the number, but that gives us the problem of before - if it doesn't write immeditately, then there could be 10 number 1 forms before one ever gets written to the DB.  Which brings up another question, say I do set it up to write when submitted the first time (it's set to begin a workflow now), can you overwrite a record, or will SQL Server yell at your about duplicate ID fields?