InfoPath Dev

Each form needs to have a ref no when it is submitted to the library. The department currently do this by hand on a printed bit of paper- at the moment they are on about ref no 150 and when we switch over to the InfoPath form which submits to a form library I need the ref number field to automatically update. So the first person that submits a form will have ref no 151 and the next person to submit automatically has 152 when they submit. Is there a way this can be done?

 

Thanks  

 

Hi Luke and welcome,

This is a very common request and one that has been answered many times in this forum.

You could import all of the previously entered forms into ShP and rely on the ShP library ID. Just add a double submit to your form. First submit saves form to library (with a field that has a unique ID promoted as a field - see File->Properties->Promoted). Then you have a rule that queries the library filtering on that unique field ID with an action to set the Ref No of the form equal to the List-Library ID from the ShP form library and submit again to have the form XML updated with this Ref No. This works well until you have to migrate data to another ShP site.

The problem with migration is that the IDs on the new site won't match. Since you are storing the Ref No in the form, maybe this isn't a big deal. New forms on the new site should get IDs beyond the numbers already assigned and you just have to promote the Ref No column and use that as your primary key for the forms.

If you aren't migrating, this is a perfectly fine solution. Since you have a unique ID in the form, you can always use that as your primary key. To create a unique ID, you can use today's date time stamp with the username - use the out-of-box Translate xpath function to remove special formatting form the datetime fields. Use the concat to prepend the username to the datetime string. For additional options, take a look at our blog here: http://www.infopathdev.com/blogs/kaoruo/archive/2011/08/26/use-qrules-to-create-unique-form-names.aspx

Best of luck,

Hello. Thanks for your answer.

I found this quite confusing, but what i did was propogate the ref no field into sharepoint then put a formula on the field that found out the max number in this column and added 1.

Thanks again

Luke, that may not be enough in cases, where forms could be deleted or multiple people submit simultaneously (editing sometimes takes long enough, so many people start same process)

Then the total number of documents will decrease and will cause a number duplicate. Now if your column is also defined as unique/index, then your form will crash.

The first recommendation is better and helps with potential concurrency issues.

Thanks for your answer

I found a way to do this a few weeks ago..... I set up a data connection to query the Ref Number column in the document library and the form adds 1 to the highest value - in my case this does work because the forms should never be deleted from the library and the ref number is not set until the submit button is clicked and the form is submitted. I have tried this way and you can take as long as you want to fill out a form and still never get duplicate numbers :)

Thanks again

Hi Luke,

I am glad you found a solution, which works for your case.

Curious, if you do this ," I set up a data connection to query the Ref Number column in the document library and the form adds 1 to the highest value" on Form Load?

If so  and several people open and take their time (let say an hour to complete) and more people open/start same data entry on another instance of the form, wont they all get the same new ID (Ref #)?

If you do the Ref#/Max-value lookup on clicking Submit, then most likely you will be fine in 99% of the cases, otherwise see below:

Maybe I am missing something, but unless you can somehow "reserve" the IDs or never have more than one person working at a time with this form (in a network /Sharepoint environment) it is likely to get duplicate IDs. Only once you save the particular form you can get a unique ID (numerical, not date-conversion based) from system.

Just to clarify, I found that it works best if I do a double-save (fast/hidden submit, then use the secondary connection to pull the Form ID from Form Library and assign to whatever form field you need, then do a final submit/close). This way my forms always have and know their unique IDs (numerical). I even rename the Form Name in Library to use that numerical ID, so my Forms show something like ABC-12-1247 on the form pages and the the filename, for cases when downloaded for offline use.

Hi i have a rule on the submit button which querys the data connection and then sets the ref number and submits. I know what you mean but after testing the form, i have submitted two at the same time, left one form open while filling out another etc and it works perfectly. Thanks for your other suggestion though will remember that for the next time :)