Problem with Auto-incrementing ID field in InfoPath form


  • Hi Guys,

    I've got a bit of an odd problem with an auto-incrementing, customized ID field in an InfoPath form and I was hoping someone could explain why this could be happening.

    As I said, I have an InfoPath form with a read-only Project ID field.  The idea is that when the form is opened, the new ID is displayed in this field and then when the user submits the form, that value gets committed as a property for the form in a form library.  If the form is opened later for editing, the value stays the same.

    I tried a couple of different techniques for this, but ended up going with an old technique where I have a data connection to the form library and try grabbing the ID column, then I have a Form Load rule that checks to see if the Project ID field is empty and then puts in an ID in the format of SG-xxxxx using this formula:

    concat("SG-", substring("000000", 0, 6 - (string-length(string(max(ID) + 1 - 139)))), string(max(ID) + 1 - 139))

    The max(ID) is supposed to find the largest value of ID existing and then add 1 and then subtract the 139 test submissions so that when I turn this live, the first live form that gets submitted is SG-00001.

    This part of it is working correctly, the odd problem is that whenever I make a change to my InfoPath form and publish it to our Sharepoint server, the first test form I submit has the expected ID, but then the next one jumps up 9, in other words, the first one would be SG-00001, but the next would be SG-00010.  And additional test ones after this are given expected values (11, 12, 13, etc.)  I've made the ID field visible and this confirms that the formula is working correctly, it just seems that the action of editing the form and publishing it is incrementing the ID by 9 for some bizarre reason.

    Has anyone else run into something bizarre like this?  How can I prevent this from happening in the future if I need to edit the form?

    Ideally, I'd like to set the Project ID field at Submit, but how do I do this?

    • Edited by Brett242 Monday, June 25, 2012 9:03 PM
    Monday, June 25, 2012 8:57 PM

All replies

  • Hi

    2 remarks

    1.why you don't use the existing ID value, to show later the project ID as concat SG-' and {@ID}

    2.Atention!. the ID value in a list is filled after submit button done his job, this mean after the item exist in the list.

    If you realy need a saparate column, create a workflow attached to the list , which will start when a new item is added and which will fille your new column Project ID with needed value based on default ID column's value

    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Monday, June 25, 2012 9:31 PM
  • The Project ID number will be used across documents and document sets as the Project takes shape and moves forward, so I'm setting it on the submission of this form, and then in the future I'll have workflows to take that Project ID, create new documents in another library and assign the Project ID to those new documents.

    I can take a look at doing this with a workflow as well, but the real question I'm asking is why is the ID jumping up by 9 whenever I edit the InfoPath form and publish it?  The calculation I'm doing in the form is not the problem because the Sharepoint-managed ID column confirms the jump is happening, but I can't see why it's happening.

    I've redone how I'm assigning the ID in InfoPath instead of a workflow, because I really don't understand workflows.  Now, instead of the form querying for the ID at Form Load, it's waiting until the form is submitted, then if the Project ID field is blank, it queries for the max(ID), assigns the formatted ID to the Project ID field and then submits.  This way, if two people open the form, an ID won't get assigned until the form gets submitted, which should reduce the chance of duplicate Project IDs.

    However, I'm still having problems with the hidden ID column that Sharepoint maintains.  After I edit the form and publish it, the first test document I add to the library assigns the expected ID, but the next one jumps 9 places.  This is not an issue with how I'm manipulating the ID field, because if I make the ID column visible, it corresponds to what ID I'm assigning.  After the jump happens, ID's work as they should from that point on, until I modify the form and publish it again.  Is the act of publishing doing something bizarre like adding and removing a bunch of rows from Sharepoint in quick succession or something which is throwing the ID off like this?

    • Edited by Brett242 Tuesday, June 26, 2012 8:50 PM
    Monday, June 25, 2012 9:36 PM