InfoPath - creating sequential filenames without an extra column
/Avid InfoPath form creators will no doubt know the trick to create sequential numbers for use in filenames. The steps typically includes:
- Create a field FileName, create another field SeqNo.
- Publish the form to SharePoint Form library, and promote the column for SeqNo
- Create a view, use REST data connection or use owssrv.dll to get data from this Form library, in particular we want to know the SeqNo
- Prior to save, check that the FileName is not blank, query the data connection to find the latest SeqNo from the forms library. Using max()
- Add one to this number, store it in SeqNo
- Create FileName by concatenating some prefix with SeqNo: MyFile-004
- Submit through a data connection back to the Form library with the FileName
This blog post is NOT about using a second column
We will work out the next number completely without promoting any fields.
WHY, you may ask. Well, because it's FUN!
And we get to play with a modified double-eval trick as a bonus!
Set up
- Create a basic form, create a field filename
- Publish to SharePoint Form library
Secondary data source for querying existing filenames
Create a SharePoint connection to the Form library.
Here I'm creating a SharePoint List data connection. The old trick with owssrv.dll will work as well.
Secondary data source for submit (to save forms)
Another SharePoint connection to the Form library, this one is for submit. Allow overwrite. Also, use the filename field for saving.
Save button
Create a save button. When the user clicks save, we're going to create a new filename with a sequential number, then call the submit data connection.
Our secondary data looks like this:
I drop this onto the form to show what is the data in this data connection.
What we want to do, is to take the names, strip out all the non-numeric characters, and then perform a max() operation to find the largest number.
Stripping characters with TRANSLATE
InfoPath has a useful function translate("abc", "ABC") it replaces the first set of characters with the second set. If your second set is empty, like this: translate("abc", "") you can essentially perform a character removal.
And now, I can use my translate function like this:
Run the form in preview: we are now left with just the the numbers in the filename. Good.
Double-eval
Back to our list of existing form names.
The double-eval trick essentially gives us a way to do a for-each parse through a repeating section in InfoPath.
The basic form this is:
The inner-eval, runs "." (current), and the outer-eval, runs ".." (parent). Combined, this gives us a concatenated string of all the Title
Combine both the translate strip, and the double-eval, we get:
Preview:
Finally, replace the outer-eval with a max function, and prefix a 0 (for filenames that don't have numbers).
max(eval(Title, 'concat("0", translate(., "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz -+,_!.", ""))'))
Preview result:
So my filename function is this:
concat("form-", max(eval(Title, 'concat("0", translate(., "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz -+,_!.", ""))')) + 1 )
Here's the form running in Form Server
