InfoPath Dev

I am using info path as a form solution for one of my clients. The
data source is MS SQL and has a primary field for document number. The script defaulted to jscript, so I am stuck using jscript for the
scripting language.

I am looking for a solution to increment to the next highest document number on form submit.


I do have a stored procedure that selects the next highest number, but have been unable to figure out how to execute the stored procedure and pass the value to the document number field at submission.

However, the solution need not be based on the stored procedure if there is another way to accomplish this.

Any help would be greatly appreciated.

Thanks,

Boris Jocoy

Hello Boris, and welcome to our forum. Ah yes. We have to solve this exact same problem in advanced implementations of our Database Accelerator product. You have to do this server side to ensure you get the correct ID and you don't want it to happen if the submit fails for some reason.

Using a web service will allow you to more closely control the submit process. What you would want to do in the web service is after the document is submitted to the DB and assigned a number, then you return in the SOAP response back to InfoPath the doc id and write it into your document number node. This of course requires code.

Thanks for the reply, but being a complete newbie to Infopath I am unsure where to start.

Also, numbering gaps are not a horrible thing in this case, and if the process can be done at submission, then it would be safe to say that if the form does not submit, then the highest number in the table is still not used.

Still searching for a solution.

Another option would be to add an additional data connection to query your DB just for all the document ID numbers or the last ID number via your SPROC. You could use rules to setup a multiple actions when clicking your submit button. The first action would query the DB for the latest DOC ID. The second action would then copy that value from the secondary data source to the doc number field. Then your third action would be the actual submit to the DB. One issue with this method is that it is technically possible that if two people try to submit to the DB simultaneously then they could get duplicate IDs. That's why I recommended the first method, but this is a simple short term option.

I suppose it is possible for two people to submit simultaneously, but wouldn't it be entirely unlikely to have the transactions at the same microsecond? Or am I being naive?

Either case I don't know how to code the execution of the SPROC, then retrieve the value, then place into document number field at submit in jscript, so that when the record is created the number is incremented.

Any veterans out there able to help a newbie out?

Oh ok. Let's try another method. Instead of getting the highest number from the SPROC create a new Data Connection to the DB to just query all the document numbers. Then in the rule to set the form document number field value you will just set it to "max()" of the document number node in the secondary data source. That is the easiest way to do this without writing any code.

I follow you, but then how do I run that query at submit time and insert the field value into my form document number?

To do that you set your submit to use Rules. Then in the rules you will have 3 actions:
1) Query using a data connection on your all Doc IDs DC
2) Set a field's value with Field being the target Doc ID node and Value being the max(secondary data source field) function.
3) Submit using a data connection to finally submit to your database