Newbie Questions

Re: InfoPath autonumber field

punkn00dlez — Fri, 19 Dec 2008 17:56:52 GMT

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?

Re: InfoPath autonumber field

Hilary Stoupa — Thu, 18 Dec 2008 23:21:56 GMT

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

Re: InfoPath autonumber field

punkn00dlez — Wed, 17 Dec 2008 14:33:25 GMT

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.

Re: InfoPath autonumber field

Hilary Stoupa — Tue, 16 Dec 2008 21:43:36 GMT

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?

Re: InfoPath autonumber field

punkn00dlez — Tue, 16 Dec 2008 18:41:31 GMT

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.

Re: InfoPath autonumber field

Hilary Stoupa — Tue, 16 Dec 2008 18:20:00 GMT

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.

Re: InfoPath autonumber field

punkn00dlez — Tue, 16 Dec 2008 18:04:38 GMT

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.

Re: InfoPath autonumber field

Hilary Stoupa — Tue, 16 Dec 2008 17:52:41 GMT

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

Re: InfoPath autonumber field

punkn00dlez — Tue, 16 Dec 2008 17:46:45 GMT

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?

Re: InfoPath autonumber field

Hilary Stoupa — Tue, 16 Dec 2008 15:37:20 GMT

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

InfoPath autonumber field

punkn00dlez — Tue, 16 Dec 2008 15:22:07 GMT

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!