InfoPath Dev

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

 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:

1. 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
2. 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
3. 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
4. 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
5. Use the number function to get rid of preceding zeros & then add one
6. 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?

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

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.

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