Auto-Numbering InfoPath Forms
Posted by Clayton Cobb on June 15, 2009
How do I avoid getting NaN when using the max() function? There is already a blog post referencing this, but people seem to be having trouble with it, so I am going to explain the steps in detail with screenshots. Here are the steps that SYM Wong-A-Ton gave:
- Change the RetrieveIDs data connection to use the autonumber field instead of max(@ID) + 1.
-
Add a Button to the form with the following 4 rules:
- Query the RetrieveIDs data connection
- Set the autonumber field equal to 1 when count(@ID) = 0
- Set the autonumber field equal to max(@ID) + 1 when count(@ID) > 0
- Submit the form to the form library
- Disable the submitting of the form via the Submit button on the toolbar (the Button you added will submit the form instead)
These steps make sense, but you have to know how to actually configure your button settings to match the words. I think some more steps are needed, so here is my outline:
- Create RetrieveIDs data connection
- Create hidden fields for storing the ID and Filename
- Create a Submit data connection for your that uses Filename field for submit filename
- Disable toolbar options
- Create custom button with 5 rules
Create RetrieveIDs data connection Create a RECEIVE data connection in your form template that connects to the Form Library where the form resides. Unless you want other metadata from this library, only select the ID field when configuring the DC. On the last page, make sure to deselect the checkbox for “Automatically retrieve data when form is opened.” We want to retrieve the data at the exact moment before submitting the form just in case someone else has submitted a form while our form was open (Fig 1).
Fig 1 – Data connection to retrieve IDs from the SharePoint List
Create hidden fields for storing the ID and Filename Create a numID field (Whole Number) and strFilename field (Text). Do not set any conditional formatting, rules, or data validation on these. Add them to the canvas for now and make them read-only (Fig 2). These will be visible for testing purposes only.
Fig 2 – Create hidden fields for ID and Filename
Create a Submit data connection that uses the strFilename field for dynamically creating the filename Create a SUBMIT data connection in your form template that connects to the Form Library where the form resides. Put in the URL of your Form Library for the “Document Library” field, and choose the strFilename data element for “File name” field by using the fx button. Check the box for “Allow overwrite if file exists” (Fig 3).
Fig 3 – Creating the Submit data connection for the Form Library
Disable toolbar options Click Tools > Form Options > Browser. Uncheck Save, and Save As (Fig 4). Submit should be grayed since it has not been configured. If it has been configured, be sure it is unchecked. Update should be unchecked by default, so leave it. I personally uncheck “Views,” since I use views to dynamically route people to certain information based off their identity or the form’s workflow status, but it’s up to you. If your form is not browser-enabled, use the Open and Save menu in Form Options to uncheck Save and Save As.
Fig 4 – Disabling toolbar functions for Browser-Enabled Forms
Create custom button with 5 rules
Fig 5 – The full view of the 5 Rules
- 1) Query the RetrieveIDs data connection (Fig 6)
Fig 6 – Querying the RetrieveIDs data connection
-
2) Set numID to the the next incremental ID only with two conditions – there is at least one form already existing in the library and only if the current form has not already been submitted (Fig 7)
-
Set a condition rule to have two conditions
- For the first, click “Select a field or group,” select the RetrieveIDsdata source from the top pulldown, drill down to the IDdata element, and choose “Number of occurrences of ID” in the bottom pulldown. Select the operand “is greater than” and type the number 0 in the last box.
- For the second condition, simply choose strFilename in the first pulldown and set the operand to “is blank.”
-
Fig 7 – Setting the incremental ID with conditions
- Create one action that sets numID to the next number higher than the highest ID in the form library (Fig 8). For the Action, choose “Set a field’s value.” For the Field, choose numID. For Value, click the fx button and put in the formula max(@ID) + 1(do not copy and paste this – you must use the Insert Function and Insert Field buttons to make sure it resolves properly. You can also copy this Xpath string: xdMath:Max(xdXDocument:GetDOM(“RetrieveIDs”)/dfs:myFields/dfs:dataFields/dfs:AutoNumbering/@ID) + 1
Fig 8 – Setting the ID to the next incremental value
What we are accomplishing here is that we are giving our current form the next ID in line before submitting the form. We are making sure this rule only runs when the library is not empty, because it would cause an error when there is no ID to use for the max(ID) function. Also, we are only setting this value when the form is brand new, which means it has no strFilename yet. We have to do this so that if the form is edited, there will be no change to the numID field. We only want this rule to run when the library is not empty and when the form is brand new.
-
3) Set numID to 1 upon the single condition that the form library is empty (Fig 9).
- Set a condition rule to have one condition. Click “Select a field or group,” select the RetrieveIDsdata source from the top pulldown, drill down to the IDdata element, and choose “Number of occurrences of ID” in the bottom pulldown. Select the operand “is equal to” and type the number 0 in the last box.
Fig 9 – Setting the Initial ID with a condition
-
- Create one action that sets numID to 1 (Fig 10)
Fig 10 – Setting the ID to 1 for the first form in the library
What we are accomplishing here is that we are giving our form the first ID of “1” due to the fact that it is the first form to be submitted to the library. This is only possible when the form is brand new, so there is no need to add a condition related to the strFilename. All we need to do is check to see if the form library is empty. This step circumvents the NaN error when using max(ID) and getting a null value back. Also, notice that this rule is put in line AFTER the Set Next ID step above. The reason for this is because both rules would run if they were in the opposite order, and you’d be skipping IDs.
-
4) Edit the existing form without changing numID or strFilename, then close it (Fig 11).
- Set a condition rule to have one condition stating that strFilename is not blank.
-
Add two actions in this order:
- Submit to your SharePoint Library Submit data connection
- Close
Fig 11 – Editing the form with the submit data connection
Here, we are simply submitting the form back to the library in a manner that will not change any metadata and will overwrite the existing file of the same name. This is the whole reason for the strFilename field. We do not want the strFilename to be recreated each time the form is edited. This rule is placed before the Submit rule for the same reason as stated above. We need to check first to see if the form has already been submitted. If so, then we submit using the current strFilename. If not, then we skip this rule.
-
5) Submit the current form only if it is brand new and after dynamically creating the strFilename, then close it.
- Set a condition rule to have one condition stating that strFilename is blank.
-
Add three actions in this order:
- Set the Value of strFilename (Fig 12) to the concatenated string combining userName() with the numID field. The formula is concat(userName(), numID). The Xpath is concat(xdUser:get-UserName(), my:numID).
- Submit to your SharePoint Library Submit data connection
- Close
Fig 12 – Setting the dynamic filename prior to submission
Here, we are submitting the form to the form library for the first time. This is why we first dynamically create the strFilename, because the Submit data connection uses this field to create the filename in SharePoint. We only want to do this step upon first submission, so that is why this rule only runs if the strFilename is blank. Doing this rule last keeps us from double-submitting, because the Edit rule would run right after the Submit rule due to its condition being met (strFilename is not blank). You can of course choose any concatenation formula you want, or you can use no formula and simply use the numID value – this part is up to you. After publishing the form template, go to your library, which should be empty with no files ever having been submitted (Fig 13), otherwise the ID of the actual files in the library won’t start with 1, and the InfoPath numID won’t match the SharePoint ID.
Fig 13 – In your new Form Library with no history of records, click New
The new, blank form should look like this (Fig 14):
Fig 14 – Blank version of the form
Click Submit, and you should be returned to the library showing just one file (Fig 15), and it should have the Name of username1 (i.e. ccobb1).
Fig 15 – First list item is correctly given the ID of 1
Click on that file to re-open it, and it should show the populated fields with numID = 1 and strFilename = ccobb1 (Fig 16).
Fig 16 – Form data fields show proper ID and Filename after submission
Click Submit again, and notice that you go back to the form library, but nothing has changed on the form except the Modified date/time stamp. The ID did not increment, and a new file was not created, because we were just editing the same file. Now, click New again, and submit. You should see a new file with a Name that is equivalent to ccobb2. Do this several times and edit some of those forms several times to ensure they all behave properly. There is one big problem with all of this, though, and I’ll demonstrate it for you. This entire concept works well UNLESS all the forms get deleted so that there are no forms remaining. If you do that, then the next form you create will be given the numID of 1 and the strFilename of ccobb1. However, in SharePoint, the ID will be the next highest ID in succession after the highest ID that had been created and subsequently deleted. Deleting files in a SharePoint list does not re-start the ID increment, so keep that in mind. If you delete all but one of your forms, then the numID solution we have here continues to work. Here is an pic showing what happens if you delete all but one of the forms and then create a new one (Fig 17).
Fig 17 – AutoNumbering continues even after records have been deleted as long as there is at least one record in the library
Three other major concerns are the scalability issue, the View settings issue, and the heavy use issue.
- Scalability – The data connection is pulling down the ID of every form in the main view of your form library. What if you have hundreds or thousands of forms? You will be pulling down tons of info that will slow the form load when all you need is a single piece of info – the highest ID in the list. A potential workaround for this is to create a custom view that is set to Sort by Created in Descending Order with a finite Item Limit of 1 (Fig 18). This always show only ONE item, and that will be the item with the highest ID in the entire list, because IDs are assigned upon creation of the file, so the most recently created file will have the highest ID. The problem then becomes that you need to create a data connection directly to this view instead of the library itself (DCs to SP lists use the default view). To do this, you must create an XML data connection pointing to the XML view of that SP view by using this write-up: Populating Form Data from SharePoint List Views. Believe me, this works, and I use it all the time, but I can imagine if it seems daunting to people who are just trying to figure out the entire solution above. To be clear, this DC would replace your RetrieveIDs DC from step 1.
Fig 18 – View Settings for showing only the list item with the highest ID#
- View Settings – Similar to the above issue, the Max(ID) function is only going to return the highest ID that InfoPath sees in the default view. The default view settings for a form library are to limit the items to 100. Also, any filtering on the default view would potentially cause InfoPath not to see every ID. You either have to make sure your default view has EVERY list item visible with no Item limit, or you need to use the above alternative view that shows only the highest ID form.
- Heavy Use – By heavy use, I mean that new forms are being created very frequently – enough that there is the potential for multiple people to submit new forms within the same moments (the window of time depends on the speed of the system), because this method relies on being able to read the items in the library to get the current Max(ID). If both get submitted close enough together, there is a chance that they assign themselves the same ID or that the submit fails altogether. It’s best to submit the doc, retrieve the ID after submission, then submit again (all in one button push), but this only works if you don’t need the ID itself in the filename.
Clayton's SharePoint Madness 
sameel said
Thanks a million.
Works perfectly.
Clayton Cobb said
You’re welcome. Thanks for my blog’s first comment.
maleesha said
Clay,
This is the best Sharepoint blog out there, and I have read them all. I will definitely be back often!
Stella Goranova said
Hello Guys,
Please tell me:
1. Do I definatelly need an XML schema doc in order to connect the Infopath form with the Sharepoint Library?
2. Can you say/describe something more about using the Submit button. I got Error messages all the time and honestly do not know where the problem can be.
Thank you!
Stella
Clayton Cobb said
Stella,
1) You do not need to make an XML schema. That is one method for creating a template – you can have a pre-defined XML schema that you set as your main data source when creating the form. However, none of my examples on this blog mention that. Were you talking about something else maybe? When initially creating your template, you can simply choose “blank,” which has no schema, but when you start adding data elements to it, the XML schema gets built for you. So, in essence, your InfoPath template (XSN) is really an XML schema that is built to work with an associated XML file that gets created after you submit a form based on that template. You don’t have to worry about all of that really, because you can just use the GUI to build it.
2) There is a built-in submit button that I am not using in my example. I just drag a button over to the canvas, double-click to get its properties, then click Rules to tell it what to do. I do not change the button type to Submit, and I don’t use the built-in Submit button. Even so, any method of using a submit button should work, so I’d need to see your errors to help. You can email me your template at warrtalon@gmail.com if you’d like.
Stella said
Hello,
thanks for the answer.
Indeed, I used/tried both approaches: created an XML Schema and then I decided I need to play around and did it from scratch without the schema.The problem is that I used the drag and drop button for the SUBMIT. And when I call/open the already connected Form to the SharePoint is gives me an error message. I checked the folders, the names.. and the last message was that there is a missing folder NaN.xml in the Form library where the results to be stored. Here comes my confusion.. Can you help me? Probably I guess I miss smth in the process or need to create a folder?!Hm..
thanks in advance!
Clayton Cobb said
Stella, This should not be difficult, so go to my Contact page and send me an email with your form template (XSN).
Duncan said
Thank you, it works!
Clayton Cobb said
Duncan, glad to help. I guess when people just say thanks and don’t have a lot of confusion, then it must be easy to follow. Any comments along those lines would be very helpful…
Patrick Halstead said
You rock Clay! Keep up the nitro-powered MVP acceleration.
Your biggest fan,
Patrick
Joyce Hughes said
I have searched high and low for the answer to this problem. Thanks to Patrick at InfoPathDev.com for pointing me in the right direction. This works amazing. The only thing is that I have an electronic signature on the form and when they sign it, they can no longer insert the ID upon submission. Any other thoughts?
Clayton Cobb said
Joyce, are you using the Qdabra Electronic Signature solution? How exactly does the user “insert the ID” upon submission? this may require some back-and-forth, so just email me the details, and we can get Qdabra dev help if it’s related to their solution.
Also, I thought you were referring to the Auto-Generating Filenames solution, so I sent the wrong pics. I will send you the pics from this blog (18) now.
Joyce Hughes said
Hi Clay,
Sorry it took so long to respond Clay. I am in a class this week. Thanks for the pics, I received the correct ones.
I am using the signature control that is available in the Infopath Form Application. I don’t know if it is Qdabra. I send the form by email to the manager who clicks on the link. The browser form displays and he/she is able to electronically sign with their CAC card. Then he/she clicks on the submit button. Because the form is already signed, it becomes read only so the ID cannot be inserted into the form. The problem is that the final receiving end of this form needs the ID to be assigned to the form. I think you are right about the back and forth, but I don’t know how to do it.
Thanks for your excellent and prompt help Clay.
Clayton Cobb said
Ah, I see exactly what you mean:
1) You said electronic signature, which confused me into thinking it was the Qdabra solution. It’s just semantics really, but what you are using is a true DIGITAL signature. This is backed with a real security token, which is a CAC card for you. An electronic signature is a step below and doesn’t use a true secure solution but is still electronic. Check out the electronic solution on Qdabra’s site or InfoPathDev (same company) and see if that may meet your needs. If not, and you require the true digital sig, then we’ll go that route.
2) You say it makes the whole form read-only? Doesn’t the digital signature only lock the section(s) that you define? Can’t you put your whole form editing area in a section and only lock that section so that the ID concept I have in my blog post can still be used with hidden fields? Whichever way you go, the digital signature should not lock the entire form unless you require it to be so…
Joyce Hughes said
Maybe I am doing it wrong (setting up the digital signature) to sign only a part of the section. I thought that is what I did, but maybe that is the problem. This is what I have set up:
The group that includes all of the data is in Group 1.
In the section properties of the digital signature, I have the following checked:
Allow users to digitally sign this section
Sign the following data in the form when this section is signed (Group 1)
Show signatures in the section
Also, I have the control ID in its own section that includes your instructions. If I submit the form without the signatue, it works slick.
If I sign it then submit, I get an alert that says “Some rules were not applied.” I click on Show Details an get the following message “This value has been digitally signed and cannot be changed.
Am I setting up the digital signature correctly?
Joyce Hughes said
Joyce <<<======== Jumping for joy!
I figured it out. The problem was that I was setting the params for the digital signature to sign Group 1, and it still didn't work.
So…I was playing around and set the digital signature to sign its own section(in this case group 7) I was then able to sign, then submit and the ID number then was populated into my form.
Yea!!!! Thanks for your help Clay.
Clayton Cobb said
Sweet! Glad you figured it out before I even had time to dig into the form. Saves me some time for sure! =P Also glad all of my input has helped move you along – that’s the whole point of what I do. =D
Just make sure that only signing group 7 is ok, because you don’t want your “signed” data to be modifiable after signing.
SuperScott said
Hey Clay thanks for your work on this.
Is there any way you can repost the pictures on your wordpress site, because at work photobucket is blocked.
I’m pretty sure i’m doing this right, but i think i’m losing something in the translation.
=D
Thanks,
-Scott
Clayton Cobb said
Scott,
I created this one before I learned how to use Word to publish blog posts. I no longer need to host pics elsewhere, so I will download this blog post with Word and then re-publish from Word, which saves the pics directly into my WordPress account. I’ll do that later from home, since it’s not working from the office.
-Clay
Scott said
awesome thanks!
Lalith said
When i trying to click submit button following error come
what is the reson?
InfoPath cannot submit the form.
An error occurred while the form was being submitted.
One of the required values for submitting the form is missing. The value is created using the following XPath expression: my:strFilename
If you know the value in the form that specifies this value, revise it and try again.
Clayton Cobb said
Lalith, strFilename must have a value. The error is telling you that strFilename is a required field but has no value in it. You need to populate strFilename dynamically using my technique in Figure 12. If you did that step, then it must be incorrect.
Dave said
Your Rules are out of order. You probably have the submit rule before the rule(s) to set the filename
Wesley said
Hi guys!!!
I need to store the number generated to continue, because when I close the form the ID start from begin (0)!
When I close the form I need that he star from the last number generated.
Someone to help me???
Clayton Cobb said
Wesley, could you explain your problem in more detail? I do not understand what you are saying. Are you closing the form or submitting it? Just closing the form does nothing. If you follow my instructions, then when you submit the form, it will always use the next ID in sequence.
Wesley said
Hi Clayton,
So, I submitted my form, but when I open to submit a new form, is showed a new ID, but is 0 again! lool
He don’t search the higher number to increase it, it always start from 0.
Clayton Cobb said
It should never be 0 to begin with. In my write-up here, the ID is never 0. If there are no items in the list, then it automatically sets the first ID to 1. After the first one is submitted, it always uses the next ID in line. Why are you getting 0? At what point in my write-up does it cause the ID to be 0?
Wesley said
Clayton,
I will explain all my code to you!!!
“Id” is a fild that the SharePoint add a number automatically when I create a new form.
“controlnumber” is a fild that I need the user see when she fill the form, so I created a button to generate a new control number, and imputed the rules.
I have in my rules:
Query ID (conform you explain);
SetNext ID (ID > 0)
ControlNumber = max(@ID) + 1
Set Initial ID to 1 (ID = 0)
@ID = “1”
Wesley said
I’ll be back tomorrow.
Tks a lot for your help today!!!
Clayton Cobb said
Are you actually doing this with code? I don’t follow your logic. Is there a reason you didn’t follow my steps? My steps work for sure, and there is no code involved.
Wesley Augusto said
This that I wrote is this code that you write on the button.
◦Query the RetrieveIDs data connection
Query ID (conform you explain);
◦Set the autonumber field equal to 1 when count(@ID) = 0
My code:
Set Initial ID to 1 if (ID = 0)
@ID = “1″
◦Set the autonumber field equal to max(@ID) + 1 when count(@ID) > 0
SetNext ID if (ID > 0)
ControlNumber = max(@ID) + 1
◦Submit the form to the form library
I created a other button to submit the form, because I need the user view the number generated before to submit de form, because this number will be used to fill out the an e-mail.
When I submit the form all filds were sent to the sharepoint, the button submit is ok.
I think this is the connection that isn’t reading the ID from the Infopath field or the search the max number to increase +1 is bad.
When I said generate number, it generate starting from the number 1, and when I click on to button generate again it show number 2, the problem is that isn’t save the number and when I open the form again it don’t start from the last one generated.
I hope have cleaned the explain….
Tks so much for you help!!!
Clayton Cobb said
Your logic just isn’t correct. You don’t need to write code for any of this, and if you follow my steps, it works. That’s all i can say. I don’t know what you’ve done wrong. I don’t see why you need code nor why you need to show the number for an email. I would just automatically generate the email with the number – no reason to display it to the user for manual use. If you do that, you run the risk of another form getting submitted while you’re waiting, which throws off the IDs. You also have to make sure your default view in the library shows all the forms so that you can see all the IDs. That is in one of my warning.
Wesley Augusto said
These isn’t a code is the steps from the figures 6, 7, 8 and 9.
Do you have MSN… Can you add me?? wesleyaugusto@gmail.com
Wesley said
Clayton!!!
I could do it, but following other way, but with your considerations!!!
Tks and regards for your help!!!
Your forum is the best!!!
Mark said
Any idea why i get this error when i try and submit the form?
The query cannot be run for the following DataObject: RetrieveIDs
InfoPath cannot run the specified query.
You are working offline. InfoPath will use offline data instead of connecting to external data sources. If no offline data is available, some form elements, such as drop-down lists, may be blank.
Mark said
ok i think i found reason.. i did not include document library name in the url for the submit…
however on submitting im not seeing any forms in the document library.. the form closes and to all appearances has submitted, but nothing is listed in the library so cant tell if the id/filename is working ok
Clayton Cobb said
Are you sure you put the right URL in for your submit data connection? Did you accidentally put the whole URL instead of just the URL to the library itself. Check to see if you are submitting your files to the hidden Forms folder.
Good URL: http://site.domain.com/sites/formlibrary/
Bad URL: http://site.domain.com/sites/formlibrary/forms/allitems.aspx
Bil Simser said
@Clayton: Nice job on the post and well documented. There is a problem with this approach however. Add 10 forms to a library so the next ID would be 11. Delete 5 of them. This solution will return 6, not 11 but when it actually saves it the name would be cobb5 but the ID field would be 11.
I’m currently trying to retrieve the values of created, author, and ID when a user clicks on Save in a browser form (which just does an auto-filename then submits the form to the library). If I open the form up the values are there and can be used in an email I send out however if I try to send that email out on the first try the values from the form library are not there. Still scratching my head on this one.
Thanks!
Clayton Cobb said
Bil,
Why do you think my solution will return something other than the actual ID? I pointed to this exact situation and showed how my method deals with it. It should always go with the ID – I even gave a screenshot of it in Fig 17. My solution does continue to fall in line with the proper ID unless you delete every record from the library, because then there is no way to know what ID is next. None of this matters, though, if you don’t need the ID in the filename. If you just need the ID in the form, then you don’t have the same limitation and can always do a double-submit. It’s the fact that some people need the ID in the filename that requires us to perform the logic PRE-SUBMIT, because once you submit once, the filename is set – it can’t be changed after that.
How are you clicking Save and doing an auto-filename? Do you mean you are clicking Submit on a custom Submit button that you named Save? The built-in Save can’t do what you describe. Anyway, you aren’t seeing values when trying to send an email initially, because the form has to submit to the library then be retrieved back into the form before you do the Submit to Email piece.
Bil Simser said
@Clayton: Maybe I’m missing something (just built it on my system to confirm). If you create a 5 items and delete the last 4, the next one you create will have numID set to 2 but the ID number is 6. In my situation I had to a couple of rules to adhere to. 1) don’t reuse ID numbers and 2) send a notification out with the ID# of the form submitted.
Your solution works for autonumbering if you want the next highest number based on the last item submitted (if the last item had numID = 20 but the ID was 100 the next numID and name would be 21). If you need the real ID you need to do a retrieve after the submit which seems to always set the numID to match the real ID (at least from my testing).
I did it through something similar but used a query filtered based on the filename (which I set in a similar fashion based on whether it’s set or not). I have custom Submit (i.e. save and email a notification) vs. Save (just save the form as a work in progress). Mine works using the filter but I might switch it to what you have here as it might be a little cleaner.
In any case, I think we’re talking about the same thing but thanks for the post. It’s very informative and great to have a resource like this for this sceanario.
Clayton Cobb said
Yeah, I know exactly what you’re talking about, but I had tested that scenario, and it still worked. What I hadn’t done is delete the most recent file, which then does screw it up. You can delete a whole bunch of records as long as the most recent one doesn’t get deleted, so that is an issue.
The thing you mentioned where you retreive the ID after submission does not work for my scenario, because this article was for people who MUST have the ID in the filename. You can’t submit the form without a filename, so if you only retrieve the ID after it’s submitted, then it’s too late to put the ID in the filename. If you only need the ID in the form’s XML for logging purposes or email purposes, then it’s very easy, and none of this matters. The article is mainly for folks who need to get the ID in the filename, and doing so without code requires that the form developer be aware of certain limitations.
Kevin Bao said
Hi Clayton,
This is a great article. How would you handle the rules when you are using the view insteads of entire sharepoint list (the Number of Recurrence ID is not available for view).
Thanks for your help!
Kevin
Clayton Cobb said
Kevin, did you see my View Settings comment at the bottom?
– View Settings – Similar to the above issue, the Max(ID) function is only going to return the highest ID that InfoPath sees in the default view. The default view settings for a form library are to limit the items to 100. Also, any filtering on the default view would potentially cause InfoPath not to see every ID. You either have to make sure your default view has EVERY list item visible with no Item limit, or you need to use the above alternative view that shows only the highest ID form.
Does that help? I give an example in the blog on how to create a view that only shows the highest ID, and you connect to that view specifically in order to do the calculations in the form. However, this is not foolproof, because if the record with the highest current ID gets deleted, then there is nothing we can do
John Reinmann said
In your post, you reference the “close” action.
Edit the existing form without changing numID or strFilename, then close it (Fig 11).
Set a condition rule to have one condition stating that strFilename is not blank.
Add two actions in this order:
Submit to your SharePoint Library Submit data connection
Close
I’m not finding any “close the form -no prompt” action in my drop down list of available actions. How do I select “close”?
John
Clayton Cobb said
John,
“Close the form” is always an action you can add when setting rules. I don’t know of any scenario where it doesn’t show up. You are adding this action to a button, right?
-Clay
John Reinmann said
I found my problem. My button properties “Action” setting, was set to submit. So I was trying to build my rules in “submit options” When I switched the button properties action to “Rules and Custom Code” by action choices included a “close” option.
Great thread! Thanks for the help.
Mary Ann D said
This works well, but is there any way to reset the autonum to zero without creating an entirely new library? I am new at this and would appreciate any assistance.
Clayton Cobb said
You can configure the formula so that it re-starts at 1, but you can’t reset the library itself to 0. It will always increment upwards to ensure unique values. You would have to take the next number, subtract by whatever number is 1 less, and then go with that for your formula moving forward, assuming you deleted all existing files first.
Mary Ann D said
Thank you for the prompt response.
So if the last form in my library is 88, I should delete all forms from the library and replace all occurences of (max@ID)+1 with 88-87?
Clayton Cobb said
The next would be ID #89, so the formula would be (max@ID)-88.
Mary Ann D said
Worked like a charm. Thanks again.
Mika said
Awesome tutorial, though I’m having one issue.
After I click the button, I can see the ID data being extracted from SharePoint, the form closes, BUT when I go to the actual SharePoint to see any itesm loaded I see nothing. Hope you can help me pinpoit why this is happening
Clayton Cobb said
Do you have your submit data connection pointing to the right place? It should not point to the /Forms/ folder of your Forms Library. Paste the exact text here if you can’t figure it out. If it’s submitting, and you don’t see it, then it must be submitting to the wrong place.
Mika said
My RetrieveIDs Data connection is pointing to:
http://teams4.sharepoint.hp.com/teams/TMO-PTT/
My SharePoint Library Submit is pointing to:
http://teams4.sharepoint.hp.com/teams/TMO-PTT/IT%20PTT%20Details/Forms/
I’m assuming either one of these is causing the error, would that be the second one?
Mika said
I tried redoing the Data Connections
I copy the full path
http://teams4.sharepoint.hp.com/teams/TMO-PTT/IT%20PTT%20Details/Forms/AllItems.aspx
But when I publish the form, the data is being sent to
http://teams4.sharepoint.hp.com/teams/TMO-PTT/Name.xml
Clayton Cobb said
Your submit data connection should point here exactly:
http://teams4.sharepoint.hp.com/teams/TMO-PTT/IT%20PTT%20Details
Mika said
Thanks!!! your instructions worked perfectly, though I have one final question. What happens if I want to add a custom save button to the form? I noticed that the “submit” button works fine when entering a new project, BUT when editing an existing set of data the “submit” button doesn’t work…hence the need for a “save” button. Hope you can help me out
Clayton Cobb said
Why does the submit button not work? My submit buttons are made for submitting and updating (saving). If you follow the instructions, then it should work. The “edit” rule in the button is the one used for re-submission. It overwrites the existing form using the pre-existing filename. That is exactly why I put it in there so that you can continue to edit the form, otherwise it wouldn’t be very useful. Let me know what you mean by “doesn’t work,” so I can help troubleshoot. What exactly happens?
Mika said
When I go into the SharePoint i have a view of the data so that user can go in and select their project if they need to edit data at all. When they click on the file the form opens and displays all the data, but when I hit the submit button the following error appears
“InfoPath cannot submit the form.
An error occurred while the form was being submitted.
A value in the form may be used to specify the file name. If you know the value in the form that specifies the file name, revise it and try again. Otherwise, contact the author of the form template.
“
Clayton Cobb said
My guess is you didn’t set it up exactly how it’s written. The submit button should have the Edit rule from my screenshots, and that submit action occurs should be set to leverage the strFilename that contains the filename generated previously by the Submit rule. All it does is use that same value and overwrite the previous form. Did you perhaps not check the box for “allow overwrite if file exists” on the Submit Data Connection settings wizard?
Mika said
Awesome…it works now. I had not checked the “allow overwrite option” so now it works fine
Brett said
I take it there is no way to take advantage of the autonumbering if we are not using MOSS.
Clayton Cobb said
If you mean MOSS as opposed to WSS 3.0, then you can still take advantage of it, because there is nothing in the write-up that is related to MOSS except the browser-form aspect, but you don’t need to do this as a browser form. Unless I’m just forgetting something, then there’s no reliance on MOSS here. Was there a piece that you thought relied on MOSS, or are you asking if SharePoint itself is needed? If so, then yeah, you need SharePoint to do exactly what I’m doing.
Jeffrey D Lee said
Hi Im having that issue with saving the file.
My Submit Data Connection and Receive Data connections are http://greenemerald/HiveNote/Forms/AllItems.aspx
It does not reall like that. Can you advise?
Thanks
Clayton Cobb said
Change the submit to http://greenemerald/HiveNote/
-Clay
Tracy said
Thanks for the helpful post. I was able to setup form and necessary rules as you detailed very well. I too was faced with accounting for multiple users submitting the form at the same time.
I added to the Submit rule – Submit (Filename is blank and ID is not equal to max(@ID)). This does cause the duplicate form to fail for submit. However, it does not recalculate the ID and the erorr is not helpful to the user. Would you have any hints for this? I am going to continue to work on this but if you have a tip for this approach that would be great!
Thanks
Clayton Cobb said
Put a conditional in the button where if it sees an existing ID that matches, then instead of following through with the submit, stop the rules and show a red italicized error message inside a small section. Hide that section with conditional formatting until you push that button and see an ID match.
Tracy said
Thanks for the tip! I will give this a try.
Anita said
I need help with submitting data from InfoPath Form to a SharePoint Document Library. The form values has to be submitted via a web service to SharePoint.I’m looking for good examples.
Clayton Cobb said
Which service must you use? Lists.asmx? Why do you have to submit data to a doc library with web services instead of just submitting the form directly to a form library?
Ruth McDunn said
Is there a way to set the QueryID retrieve so it filters based on another field in the form? We want to auto number our policies with a combination of the Governing Prinicpal (GP form 1-9) that it belongs to and then a sequential number. We have a dumb numbering system in place that works okay as long as the governing principal of the policy is not changed after initial submission. What we want it the form to do is look for the highest number that exists in, for example, the 600 series and then assign the next sequential number in that series. Same, if the series changes. My quick and dirty solution would be to separate out the policies into document libraries based on the GP and the form would have to move if the GP changes. Not very elegant. Any suggestions would be appreciated!
Clayton Cobb said
Ruth, I don’t know if this is the same topic/solution as the one I helped with in another group (I didn’t work with you on it but was trying to help Nate), but that one had a numbering system that I had to figure out and cause it to be dynamic. The issue with that one was that resulting code wasn’t necessarily a number, so it wouldn’t be usable for finding a max value. If the values being used for your series numbering are actual numbers, then you should be able to specify your conditional filter based off of that and should be able to determine the max value. Maybe something like max(@GP[series=@series]). I can’t tell based off the info given, and it’s hard to picture, but I think it can be done. However, I know you are keen on this type of logic, so you may have already ruled out this approach.
Yeah, you’d definitely want to avoid submitting to separate doc libs if you can. If we need to discuss it on the phone, I can do that on my drive home tonight.
Alex said
Clayton, I’m having an issue on step 12. (The same error Lileth posted earlier) I get the following error:
____
InfoPath cannot submit the form.
An error occurred while the form was being submitted.
One of the required values for submitting the form is missing. The value is created using the following XPath expression: my:strFileName
If you know the value in the form that specifies this value, revise it and try again.
____
I’m guessing that perhaps in regards to the concatenate function, what you stated for an earlier section: “do not copy and paste this – you must use the Insert Function and Insert Field buttons to make sure it resolves properly.” might be an issue. I’ve tried typing concat(userName(), ***) (where for *** I insert numID with the buttons), I’ve tried concat(“Form ID: “, ***) (where for *** I use numID from the buttons). I’ve even just tried concat(numID) and even just numID by itself (using the buttons in both instances). The auto-populate for the strfilename field just doesn’t appear to be working. Any suggestions?
Clayton Cobb said
You need to build the function using the buttons without copying anything. You can test everything you’re trying by removing the submit action and just show the strFilename field on the canvas to see what happens when you push the button.
jenn said
Hi Clayton,
i have used your tutorial about autonumbering. it’s a great one.
although when i submit a form to the library it gives me this error:
One of the required values for submitting the form is missing. The value is created using the following XPath expression: my:HiddenFieldAutoNum/my:strFilename
If you know the value in the form that specifies this value, revise it and try again.
and when i check my library the form is submitted. if i edit the form it works fine. only when i add a new form it gives me this error.
Clayton Cobb said
If the form still gets submitted in spite of that error, then it tells me there are multiple actions occurring in that same button click. The first submit is working, and the 2nd one is not. It sounds like a logic error or ordering error.
JRIESEN said
Based on glancing through this article this looks like something that I would like to try. My issue is that I want the incrementing to start, at say, 0092 because I will have some existing legacy records in my SharePoint list. I have the legacy issue that some of the list rows have already been deleted. I am planning to use this for an Employee List proof-of-concept. Is what I am describing doable using your solution? Would I be able to use a field other than the ID of the SharePoint list to increment my form number? Your recommendations would be greatly appreciated.
Clayton Cobb said
Are we talking about 2010 list forms here? I ask, because you can only use InfoPath with lists in 2010.
My recommendation is to not use a numbering convention like 0092, because that is not actually a number – it’s a word, or rather, it’s a set of alphanumeric characters. Regardless of what we call it, it’s not a number from a database perspective, so although you could build some logic to use that type of scheme, it would be much more difficult than utilizing an actual number. You can account for the pre-existing records by doing a calculation so that whatever the next number should be, you figure out what the next ID of that list is and subtract the necessary number to achieve your desired increment. From that point forward, every record will be numbered properly. However, if you really care about deleted records, then this goes out the window, but ignoring this type of TRUE unique numbering is dangerous. I recommend storing your custom ID in a custom field, submitting the form, retrieving that form’s ID, doing your calculation, setting the custom field to the custom ID, then re-submitting the form – all in one button click. In this scenario, you cannot put the custom ID in the filename, but it’s best if you don’t have to do that anyway.
If you truly must put this custom ID in the filename, then it’s going to be risky and unreliable, because you’ll never know 100% that it’s going to assign the proper ID. However, if you still choose to do this, then simply perform a calculation (if using a number) or concatenation to “add” an increment to the highest known custom ID.
Roostermiester said
Thanks for this post. I’d been wondering for some time how I could automate the generation of the next PO number (no, we don’t have this in an accounting system). I did have to tweak our numbering since the legacy numbers roled over every month and had leading zeros. I built the final action to set the file name to: concat(substring(today(), 3, 6), numID), so it looks like: 10-10-1.
Thanks again.
Chanakz said
Immense thankx for this post. This helps me a lot to complete my task. Thank u once again..
Phil Jones said
Excellent post Clayton, many thanks.
I had a lot of fun trying to achieve a filename of yy-nnn were yy is the financial year and nnn is a sequential number with leading zeroes that re-starts at 1 each financial year… but you’ll be glad to hear that I worked out how to adapt your solution with very few problems!
I do have a couple of questions for you though…
1. Do the submit & close steps really need to be in both the Edit and Submit rules? I replaced the Edit rule with a SetFileName rule which sets the file name if it is blank, then I removed the file name condition from the Submit rule so it always submits. Seems to work OK, just wondering if I’ve missed something?
2. I tried to use your solution of using a view in SharePoint to show only the highest value, but I couldn’t point my connection to the new view – any thoughts on what I’ve missed?
Thanks again.
Clayton Cobb said
1) It’s fine to do it that way if you’d like. I typically have more actions in each rule and don’t want to run both rules.
2) You have to use the XML data connection method that utilizes owssvr.dll: http://blogs.msdn.com/b/infopath/archive/2007/01/15/populating-form-data-from-sharepoint-list-views.aspx
Steve said
Clayton,
I’ve used this script on other forms and it has worked great. I am currently using it on two other forms and I am getting The form has been closed error. I am getting this on one form when I submit it and the other when I open it to edit and submit. I’ve striped my form down to pretty much the submit button. What could be causing my error. I am using infopath 2007. Logs are not showing anything obvious.
Thanks in advance
Steve
Steve said
I figured out my problem (almost 8hrs later). The view on the form library defaulted the first column to Name(linked to document with edit menu). I changed this to name(linked to document) and it got rid of error “The form has been closed”. The type(icon linked to document) also worked. Time for a beer.
Clayton Cobb said
But that’s the default column that is always used, and it works fine. It should not have any affect on the form operation unless you have something going on inside the form that retrieves values from that field maybe.
Khaled said
HI,
please guide me how I can put the infopath attchment in deferent library than which I already saving the items, the reason is to speed the performance of opening form,
because if the attachemnt is havy then the form will take alot of time to open or if there is a workaround for this issue will be better.
many thanks for you
Clayton Cobb said
Khaled, it requires to do that, but it can be done. Qdabra’s qRules package has a qRule that does this for you, or you can write the code yourself. I don’t write any code, but I know people have done it. You may find a how-to on BizSupportOnline.net.
Dan said
Thanks, works well!
totie said
Thanks you so much. This is the best blog the I have ever read. Well explained and very detailed.
Sharma said
Clayton,
is it possible cani use only numID inspite of conctenate user name and numID. My client want only numID. So please help me how can i do with only numID.
Clayton Cobb said
Sharma, what’s stopping you from just using numID? You can use whatever you want.
Joanne said
Clayton,
This blog has helped me a great deal while learning InfoPath 2007, particularly this Auto-Numbering. I have used it in several forms with wonderful results! There’s one thing I’m having problems with and I’m not sure if it has to do with how this is setup, or something else in my form, but I’ll start by eliminating this potential problem first. 🙂
(Using InfoPath 2007 and MOSS 2007)
I have one form (PO) that auto-numbers and saves the file name (ex PO1840) to a SharePoint site. In a hidden field, I populate the filename to pull from later for a list in another form (CR). So, when I open the CR form, and click a field’s drop-down, it queries SharePoint and pulls in the list from the hidden field in the PO form. Using the selected item from the list, the form populates with all associated information from the PO form. When saving the CR form, the naming convention concats the words “Check Request_” and the PO file name (Check Request_PO1840). Once I start saving CR forms in the same library, my pre-populated list that had only POxxxx names in it, now has blank items in the list. Usually, it adds the blank items to the bottom of the list, but sometimes it adds it in between the items. So, basically, everytime I add a Check Request, it’s adding a blank line to my pre-populated list. How do I get that to stop?
Thanks!
Joanne
Clayton Cobb said
Joanne, imo, the real problem here is that you are submitting these separate forms to the same form library. What is the reason for that? These should really be in separate form libraries for reasons other than the issue you’re seeing here. Please reconsider this design.
Anyway, not sure if you’re using browser forms, but I’m going to assume you are based off the behavior you referenced. In browser forms, data from SharePoint lists/libraries always displays in order by ID, and IDs are automatically assigned in chronological order upon creation of new items. So, your CR forms don’t have any data in that hidden filename field for your POs, and they are getting interspersed with the other forms in the library due to the auto-incrementing unique ID that SharePoint gives it. Since you cannot filter SharePoint data connections in browser forms, you cannot hide these blanks. If you were to add data to the PO field of your CRs, you’d then have bogus options in your dropdown. If it’s a client form, then you CAN filter the dropdown to only show POs, but I figure you would have done that if you could. All in all, the CRs should be in a separate form library, and this would not be an issue at all.
Joanne said
Thank you Clayton. Yes, I have considered the Design and did try to set those up in Separate SharePoint Libraries. But alas, I was overruled farther up above me. 🙂 I had a feeling that was the issue, and you’re right, I could not find a way fo filter the drop-down when I selected the data source value/display in the List Box Entries area of the drop-down Properties.
I will have to work harder at convincing the higher ups that the info be in two separate libraries in order to make it work correctly and more efficiently. :))
Thanks again!
Joanne
Clayton Cobb said
Is it a browser form or client form? I was guessing in my reply. If it’s a client form, then you CAN filter out the CRs in that dropdown by adding a filter to the Source box of the dropdown control properties. You could make the condition say, “Where POFilename is not blank” – referring to the hidden PO field that stores the PO filenames. You could also choose some other field that specifies PO or CR.
Joanne said
Sorry, meant to indicate that it is a browser form.
Tom Londe said
Hi Clayton-
I’m running into a bit of an odd issue with the autonumbering that I’m hoping you might know something about. I had been using your autonumbering system, and for a while it was working well. Recently though, I’ve run into an issue where my Retrieve Data data connection keeps “turning off”. In the data connection wizard, I’ll go to the screen where I’m supposed to select the fields that I want queried (the third screen if you’re accessing the wizard through an existing connection) and find that all of the check boxes have been cleared. This was effecting both the copy of the form that is saved on my computer and the copy that’s published to SharePoint. I was finally able to figure out that the problem occurs when I publish the form…. the fields I want selected will be checked prior to me publishing the form, but as soon as I publish it, my check marks go away in both the saved and published copies of the form. Because of this, all of my new forms are being numbered as 1 and I’m having to go in afterwards and manually correct the numbers. It would seem that the issue is with the specific library I’m using as well because I was able to publish the form to a different library without the problem occuring, but as soon as I tried again on the actual library, the data connection cleared its check boxes again. Are you familiar with anything that might be causing this?
Thanks!
Tom
Clayton Cobb said
Tom, that is definitely very strange. I have not seen that happen before with any of my forms. If you’ve isolated it to the one form library, then you can target your troubleshooting efforts only to that form library, or you can potentially do a migration (save as template/re-create, export/import, 3rd-party migration tool) to basically replace the busted form library with a new version of it to see if it goes away. I would be concerned, though, that if it happened once, it could happen again. Logic tells me that it’s not some deep database corruption issue but rather a specific setting or nuance of this form library that is causing the issue. Unfortunately, though, I’ve never seen it and don’t have any places to point you off the top of my head.
Marcy said
Hi Clayton,
Thanks for posting such great steps to setting the autonumber.
I’m very new to this and am learning a lot by trial and error, but I wasted enough time trying to figure out what I’m doing wrong so I thought I’d just ask you directly. I’ve read through and saw that someone had asked a similar question but the answer you provided did not help me/I couldn’t figure it out.
I’ve followed your steps to the letter and can make the autonumber work no problem to start at 1. But I want to start at 1136 and continue to count from this number. How do I change the rules on the button to make this happen?
Thanks in advance!
Clayton Cobb said
Marcy, it’s really just a math problem. My solution doesn’t necessarily start at 1. It starts at whatever your next ID is, so for you to start the next one at 1136 depends on what the next ID number in your form library is. If the next number is 1136, then you wouldn’t change anything. If the next number is 500, then you need to retrieve the most recent ID, which would be 500, and then add the difference between 1136 and 500 (636). Instead of doing max(@ID) + 1, it would be max(@ID) + 636.
Chris said
Hey Clayton,
Browser enabled Form SP 2007 / InfoPath 2007
I tried doing the view data connection to a sharepoint list using the MaxID view, in a web browser form, but keep getting. “An error occurred accessing a data source – and the lovely Event ID 5566
.” This is only happening using the “Populating Form Data from SharePoint List Views” , you suggested using. I have Full Trust Administrator Approved and uploaded to the server – using as a content type in the sharepoint list.
1. I do not get an error when directly using the data connection to the sharepoint list only when using the view data connection which I trigger using a button on the form.
2. The error does not show in Preview Mode in InfoPath.
3. Design Checker shows no errors.
4. I can copy and paste the view XML into my browser’s Address bar and it shows values – see below.
5. Is this one of these times where this data connection needs to be converted?
6. Frustrating, as you say this works? Is what I am doing the same thing you are doing?
7. Why can’t this XML data connection work?
http://changesmadetothisvaluetoprotectserver/_vti_bin/owssvr.dll?Cmd=Display&List=%7BD82D6DA4%2D8781%2D4477%2D88BE%2D9B19445DCDA0%7D&View=%7B01338A7F%2D3F5E%2D4C73%2D9377%2D630EF2ED551B%7D&Source=%252Fcollaboration%252Fpr%252FPRTestSite%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D%25257BD82D6DA4%25252D8781%25252D4477%25252D88BE%25252D9B19445DCDA0%25257D&XMLDATA=TRUE&noredirect=true
(NOTE: I added &Source at the time to see if it made a difference. I noticed in the Infopath client it came up faster with &Source in the data connection. Tried removing it or adding it, but did not make a difference.)
Any suggestions? Thanks,
Chris
Clayton Cobb said
Chris, it is likely the LoopBackCheck issue. Do not use a Source parameter – that does nothing for you.http://www.harbar.net/archive/2009/07/02/disableloopbackcheck-amp-sharepoint-what-every-admin-and-developer-should-know.aspx
Chris said
Here an update to my post above, when open querying for the view. Not sure what this means, but it looks like a web.config trust issue?
Type: DataAdapterException, Exception Message: The form cannot run the specified query.
The remote server returned an error: (403) Forbidden.)
Chris
Benjamin said
I’d like to say thanks even thanks is not enough, thanks for sharing the “Auto-Numbering InfoPath Forms”
I came across your blog, its awesome!
Kev Houston said
Hi Clayton,
Thanks for your work.
I’ve used this walkthrough previously without error. however today, Ive replicated. Checked, double and triple checked, but I cannot get the initial submission to occur.
I get the following error:
InfoPath cannot submit the form.
An error occurred while the form was being submitted.
The form cannot be submitted to the following location: http://URL document name – 1.xml
The folder does not exist
Mohammad Ahmed said
Kev… check the url you have given in submit data connections…
We sometime include… Forms/Allitems.aspx along with the URL..which cause problems… ensure URL you have entered there doesnt have Forms/Allitems.aspx
Kivuos said
Hi Clayton,
Need one help regarding this Auto Numbering feature. I have a InfoPath Form Library to submit Technical Support Requests and this library has a very complex approval process. This approach works fine in Development and we have done a lot of testing on the same. Now the latest value in the ID field for this library has crossed 500. Now when I move the same library will all its workflow and customization to Production (Backup the library from development and restore it in production). We cleaned up the library in Production, but still when a form is submitted, the first ID used is 1 (since there are no entries) and then the next entry used is 500…Is there any way in which I can reset the ID field of the list to 0 in production while deploying into production?
Mohammad Ahmed said
Wonderful illustration Clayton!! Just one doubt here.. what is there are 2 users submitting the form at once.. will 2 different IDs be picked?
Clayton Cobb said
Mohammad, they would have to click the submit button at the exact same instant for it to be a problem.
Juli Reid said
Thanks so much for the Auto-numbering information. Only one slight problem. I have created the xml file to use the alternate view, but it isn’t working. I noticed that article references SharePoint 2010. We are still using 2007 and need it to work there until we are migrated to 2010.
Is there something different I need to be doing to capture the correct information for the xml file in 2007?
Clayton Cobb said
Juli, the referenced article is for 2007 and was written long before 2010 came out (Jan 15, 2007), so it definitely works for 2007. I also wrote my blog post well before 2010 came out. =)
Juli Reid said
Sorry for the confusion, I was referring to the article on how to setup the xml file. At the top of the article it indicates SharePoint 2010 and when I try to use the xml file I created I keep getting an error indicating “…semicolon expected at position xxx’. I wasn’t sure if I was doing something wrong since we are still using SharePoint 2007.
Juli said
Ok, I finally figured out how to create the xml connection… Sorry to be so dense but not having worked with xml or infopath before it was not immediately clear from the other blog that you weren’t saving the notepad file as an xml and using it but rather actually putting it directly into the wizard to create a dynamic file.
So now that I am beyond that and trying to follow your steps to create the submit button using this new data connection, but I have run into a difference that I am not sure how to handle. In your steps for setting the next ID and the initial ID we are setting the occurrences to either 0 or >zero.
When using this new data connection that is not an option.
How do I handle these conditions using this new xml data connection?
Thanks
Clayton Cobb said
Juli, you should not experience different behavior. I don’t know what to tell you without seeing it myself. Any repeating data sources – like this XML data connection – should have the options for “All/Any Occurrences…”
Juli said
I would be more than happy to show or send a screenshot………………….
Juli said
When I select my newly created data source for the Highest ID view I created, it looks very different from the selection I see when I choose the Retrieve ID’s data connection.
This is what I see when I use the new data connection. If I select :ID, my drop down contains ‘id’ and nothing else. The choices under rs:data are nothing but a list of the fields in my form.
xml
– s:Schema
:id
– ElementType
:name
:content
rs:CommandTimeOut
– AttributeType
:name
rs:name
rs:number
– datatype
dt:type
dt:maxlength
dt:lookup
+ rs:data
Juli Reid said
I sort of figured this out. No matter what I did I wasn’t seeing the ‘ID” element under the rsdata header. It was only AFTER a record had been created and saved into the library that the rsdata fields populated and I could then select the ‘id’ field and choose ‘number of occurences of……’
As long as there was no initial form in the library I couldn’t do it.
Tracy said
Clayton
I am trying to work through this post, but with the added complication that I am using another post, as I need my forms to be numbered starting with a pre-determined number i,e 2000 and whatever the next ID number is currently in the libary, the post I am refferring to was on sharepointryan.com (add an auto-numbering field to an infopath form).
I cant seem to get the form to pick the latest ID number, I am nit sure at what point to switch from your post to his and get it to work?.
Scott said
Hi Clayton,
Your article is fantastic, and I have used variations of it on some of my forms, thank you! I do have a question about doing something and I have it pretty much worked out, but wanted to get your opinion on one part.
I am creating a Travel request form and what I plan on doing is using is concating the first initial with the last name and last 4 as the string filename, but would like to add a counter to it so that it increments the file names as that user submits new requests. So for example it would look like this “SPOWERS5096” then I would like it to query the library and check if there are any previous file names and then increment the number to as the new filename “SPOWERS5096-001”, SPOWERS5096-002… What I am not sure about is querying the librry to check for matches and having it use a 3 digit number. Any suggestions?
Thank you,
Scott
Clayton Cobb said
Scott, thank you for the kind words.
Your idea makes sense, but I do really prefer not to put auto-incrementing IDs or numbers into filenames (because the filename has to be determined the moment the file is submitted, and it’s better to determine the number AFTER the form is submitted). Is it not possible to keep your filenames clean and clear and then put the incrementing number in a separate field for reporting purposes? This is a much better way of tracking and storing the data in case it’s an option. Even if it’s not, we’re going to do this in order to generate the special filename anyway. So, here goes, and this is assuming you’re using 2010 for everything:
Juli Reid said
I have been using this to create a Mobile Device request form. It’s great. I had to recreate it as a web-only form and I am having some difficulty with the submit. SharePoint 2010 and it won’t name the form based on the submit unless I put the concat string in the SharePoint library submit data connection. However now that I do that it wants to create a new form when you access a submitted request to approve it.
Almost as though it’s not recognizing the edit rule. I have verified that all my rules are correct.
Juli Reid said
And I am having a brain-dead day. I just figured it out. Set the data connection to use the ‘strfilename’ field and put the concat in the rules for the submit button.
I really appreciate your SharePoint columns for forms. They have been a great help to me.
Juli Reid said
Have you ever seen where you setup your custom view data connection for ‘RetrieveIDs’ and you are trying to set the number of occurrences for ows_id and you select ows_id but it doesn’t give you any options? I have recreated my form and form library on my test server and I can’t get this rule for the submit button working because when I select the ows_id, the number of occurrence options in the dropdown are missing.
Clayton Cobb said
Yes, it does that if there are no items in the form lib unfortunately.
Clayton Cobb 202-413-3036 http://claytoncobb.com
Juli Reid said
I put an item in the form library deliberately before trying to create this particular submit setup. It is still not finding it. Do you think a second item might do the trick?
Clayton Cobb said
1 item should have made the nodes available.
Clayton Cobb 202-413-3036 http://claytoncobb.com
Juli Reid said
Any suggestions as to what I might look at or do to rectify this?
Juli Reid said
I have already recreated the data connection a couple of times and that didn’t do it.
Juli Reid said
So here is a sort of related question. Using the setup for the submit button here, would there by any way to have it submitted to the library and then sent out in an email for the approval? I don’t know if a Workflow could actually send the form in an email out to the approvers.
Clayton Cobb said
You don’t typically want to send the form. It’s best to send a workflow email that includes a subset of info and a link directly to the form. Approvers would click the link and open the form to see it directly and approve it within the form itself. Sending the actual form via email is not recommended, because that copies the data all through the email system and doesn’t leave the data in a single, central location. That’s my opinion anyway.
However, yes, you can have multiple submits, including a combo of submitting to SharePoint and submitting to email. You can add whatever combination of submits you want.
Clayton Cobb 202-413-3036 http://claytoncobb.com
sooboth said
Clayton,
This works like a charm. Thank you very much!
Juli Reid said
This has to be the feature that I love to hate! It works great except that I have found it impossible to setup the submit button with the ‘Number of occurences of ows_id’ until AFTER I have 2 saved forms in the library. With no saved forms I don’t get anything to select under the rs:data. With one form I can navigate down and select the :ows_ID but ‘Number of Occurences of’ does not exist in the ‘select’ drop down. AND these are only available if I create the data connection to the view after the 2 documents are saved in the library. Is there some trick to having the ability to create the submit button rules for auto-numbering without having to go through all of this?
Nemo said
Thanks Clayton for the wonderful post. I had problems implementing SYM Won A Ton’s instructions to achieve the same result. Your post explained it more clearly. However I have a question. I want to start the ID from 5000. So I set up the third rule as Set Initial ID to 5000 instead of Set Initial ID to 1. Since its a brand new form library, the very first form submitted got the right filename which was myUserName5000. That was expected. However for second form submitted, it gave a name myUserName2, then for third form, myUserName3 and so forth. I want them to start as myUserName5001, myUserName5002. Is this a possibility? Please let me know. Thanks in adv!
Clayton Cobb said
That’s because this solution is based off the ID field. If you want to base it off the custom ID that you’re creating, then you need to store that fabricated ID# (i.e. 5001, 5002, 5003) into a separate CustomID field and then change the rules to read from that number instead of the built-in ID field.