Newbie Questions

Re: Reference Number

Jen-hsb — Fri, 19 Aug 2016 01:54:30 GMT

The form will be used several times everyday by different people in different locations. Has you understood I need each form to have a unique number for business reason and having a user number won't unfortunately solve my problem but I will have a look at the list of database idea. Thanks again Hilary

Re: Reference Number

Hilary Stoupa — Thu, 18 Aug 2016 22:03:52 GMT

In general, the approach I suggested may work if you have low traffic on the form, but if the form is in frequent use, there is a chance two users could be submitting at the same time for the same site and end up with dupes. Personally? I wouldn't do this at all. Is there some business reason you have to use this numbering scheme? If you have to use something like this, best to figure a way for users to reserve numbers - using another list or a database to do so.

Re: Reference Number

Jen-hsb — Thu, 18 Aug 2016 03:06:06 GMT

Hi Hilary, thank you for this solution. I was hoping you'd reply. I don't want at all that two forms get the same number. What other options would you recommend? What I really need is for each form to have a unique and consecutive number starting from the first form (example: 0001). And if possible that the field Reference number in the form is automatically populated with that number when a new form is opened. Thank you

Re: Reference Number

Hilary Stoupa — Wed, 17 Aug 2016 14:55:49 GMT

I really dislike this type of numbering for a variety of reasons.

However, if you must do this, you should do it as part of the submit rules, so as to minimize the likelihood of two forms getting the same number. You'll have to query the form library to get back all the existing numbers in use, then use some substring functions to parse things out.

If I had to do this, I would probably:

Promote a field to the library that contains the first three letters of the Site field to make it easier to query for just the forms I need
Add a data connection to query the library, including just the Reference number and field from step 1, sorted by Reference number desc - don't run the query on form load
In my submit rules, set the query field in the data connection from step 2 to the three letters of the Site field in this form, and query the data connection - this way I get back JUST the Reference numbers that apply to this site
Since I sorted by Reference number descending, the first returned row will have the number I need to increment. I know the first three letters of the site, so can use substring-after to get just the number portion
Use the number function to get rid of preceding zeros & then add one
Use a concat formula with substring to pad with zeros (search this site or the web in general for zero padding in InfoPath)

Make sense?

Reference Number

Jen-hsb — Tue, 09 Aug 2016 07:26:04 GMT

Hi, I have created a form that is published on Sharepoint Online 2013. In that form I have a Reference Number and a Site field, what I'm trying to achieve here is when a user click new to fill up a new form that my Reference Number field is automatically populated with the first 3 letter of the Site field +001 and so forth. For example: MIA001 for the first form, BRI002 for the 2nd, PER003 for the 3rd... Any help will be greatly appreciated due to the fact that I'm still learning SharePoint. Thanks