Auto-Increment for InfoPath repeating tables/sections

9 בינואר 2008

24 תגובות

When creating a InfoPath forms to collect business application data, it is often required to use an auto-increment (auto indexing) behavior for auto numbering different data in the form. This is a very common and useful when implementing a repeating table and/or repeating section in order to index the rows in the repeating table or the repeating sections, correspondingly.

In the following post I'll demonstrate three available options to archive this behavior without writing a single line of code!

This options are available and support by both, InfoPath 2003 and InfoPath 2007.

This post has a sample InfoPath form template – DOWNLOAD THE SAMPLE (Auto_Increment_Sample.zip).

Option 1 – Use an Expression Box with the XPath set to position()

This option is great if all you want to do is show the row number. But if you actually want to store the sequential auto-increment numbering in a field, this is not a good option since Expression Box cannot update and save its data to fields in the form.

See option 1 in the attached Auto_Increment_Sample.xsn

image

Option 2 – Use  count() of preceding-sibling

image To store the auto-number in the forms field, i.e Index_Count field in our form, use the following XPath formula as the default value of the Index_Count field: count(../preceding-sibling::*/my:Index_Count) + 1 and uncheck the update this value when the result of this formula is recalculated.

This XPath actually counts (count() function) the number of nodes in the same level above the current node (preceding-sibling statement).

This will do the work, but if we need the generated Index_Count to assure unique across the table rows, than this will not be enough, since it in case you delete a row and then insert another row, than will have two rows with the same Row_ID value.

See option 2 in the attached Auto_Increment_Sample.xsn

Option 3 – Use InfoPath Rule to create a counter mechanism

In this option we'll use an InfoPath Rule on the index field. This rule will use as a counter mechanism to increment a counter filed as follow:

1. A field to store the item count has been added – Item_Count (/my:myFields/my:Item_Count), with a default value of 1. This field will act as a global variable to the form instance and will hold the latest generated Index_Counter_Rule.
2. The value of the above Item_Count field has been set as the default value of the Index_Counter_Rule field (again, we uncheck the update this value when the result of this formula is recalculate).

image

3. A rule named “Set_Item_Count” has been added to the Index_Counter_Rule field. This rule is will set the value of Item_Count to (Item_Count + 1). This rule is being triggered after the content of the field is being changed. When we add a new row, the content of this field is being changed due to the default value settings.
image

See option 3 in the attached Auto_Increment_Sample.xsn

Use the options above as you find best fit to your needs.

Enjoy :)

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

24 תגובות

  1. Gyan29 בינואר 2008 ב 19:58

    I used Option 3 and it works just wonderfully, with one exception. If I increment the repeating table for five rows (e.g. 1,2,3,4,5) and then I remove a row (e.g, row 3) how do I get the rows that follow (e.g. rows 4 and 5 to decrement to the correct row numbers?)

    להגיב
  2. Dean19 ביולי 2008 ב 1:47

    Thanks for the help with incrementing values in infopath tables. How might one increment version numbers like 1.0, 1.1, 1.2, etc?

    להגיב
  3. Dean19 ביולי 2008 ב 2:51

    This worked for me using example 1 I put the following in the XPath field:

    position() – (.9 * (position() – 1))

    להגיב
  4. Munazzah28 ביולי 2008 ב 2:26

    Is it possible to have an autoincrement field when ever a new instance of form is created. Actually i want an autoincrement field to store the User change request id.

    להגיב
  5. Jeremy29 ביולי 2008 ב 16:33

    This work great but what if I want to edit that location. Say now I need to change the expression in Row ID 1 but on Row ID 1?

    להגיב
  6. Rodolfo13 באוקטובר 2008 ב 12:02

    Is there a way that we can make an incremental number like a Unique Identifier or form number using these formulas?

    להגיב
  7. Jonathan Cohen31 באוקטובר 2008 ב 11:49

    Kol Hakavod
    Albany NY

    להגיב
  8. GB6 בדצמבר 2008 ב 15:57

    Excellent examples. Thank you.

    להגיב
  9. MrQuestion10 בדצמבר 2008 ב 12:59

    Very Good Example. Thank you Very Much.

    להגיב
  10. dataCore1 באפריל 2009 ב 15:48

    When you set the

    count(../preceding-sibling::my:Row) + 1

    and check (!) the "update this value when the result of this formula is recalculated". it works for me.

    You just have to refresh the content wenn the user add's a new line or deletes a line and the linenumber/fieldnumber is correct (doesn't matter if the user inserts a line somewhere in the table or just add the end – same for deletation)

    greetings und THX for the good article

    להגיב
  11. Kate Struck16 באפריל 2009 ב 19:53

    Thank you so much for teaching me how to auto-increment. I have wasted hours of searching and experimenting on how to do this. You have given me a gift. Thank you again.

    להגיב
  12. Brian22 באפריל 2009 ב 22:58

    Is it possible to make option 3 with with the web rendering InfoPath Services on SharePoint 2007?

    להגיב
  13. Arash Aghajani4 במאי 2009 ב 11:36

    Thank you so much.
    Good examples…

    להגיב
  14. Nandashri14 ביוני 2009 ב 7:56

    This is excellent and very helpful..Thanks :)

    להגיב
  15. Clayton Hampton18 ביוני 2009 ב 18:24

    I have a need for indexing of a repeating table on the fly within an existing InfoPath template. While the data appears in table form, it needs to be organized(in appearance) by cost… In other words, if the cost changes, then the highest cost should appear first with the remaining records displayed in decreasing order from that point. To this point, I haven't been able to find anything to assist me with this. My email is: clayton_hampton@hsb.com. Please respond, if you have a solution.

    להגיב
  16. aziz28 בספטמבר 2009 ב 16:45

    i tried, it works in infopath forms properly but doesn' t work on browser.1 ok, 2 ok but 3 is not ok.
    1
    2
    2
    2

    להגיב
  17. bstoyano28 בדצמבר 2009 ב 13:18

    I tried option 2 but left the Update value field checked. It works fine even with inserting and removing lines.

    Nevertheless, the article is great.

    להגיב
  18. Chris8 באפריל 2010 ב 10:18

    I had a problem when using Option 3 with InfoPath forms server. The rows were being incremented by 2 instead of 1. What I had to do was push the rule to another field and let the form do the updates through that mechanism. I'll send you the updated Option 3 that works both in the fat client and browser. It still does not address the out of sync issue you can run into but for my application, I don't want to reorder the fields just keep a unique row assignment.

    להגיב
  19. Rob Sanders23 באפריל 2010 ב 16:58

    Can I use this to specify to copy a value from another cell into a cell in the LAST position of a repeating table? (Currently, when I try to copy into the repeating table it put the new data in ALL rows of the table…) – I am a non-programmer, and an InfoPath novice.

    להגיב
  20. David30 באפריל 2010 ב 10:05

    This is garbage. Except the 1st example it's garbage

    להגיב
  21. Teja Vikram Atoory7 במאי 2012 ב 16:41

    count(../preceding-sibling::*) + 1
    surely works tested by me

    להגיב
  22. Shahid Afzal16 באוגוסט 2012 ב 9:20

    I tried option 3 when I run preview in info-path it works but after publishing on share-point it does not work

    להגיב
  23. Dave9 בינואר 2013 ב 10:43

    to answer a question raised here, about incrementing unique form id –

    'Is it possible to have an autoincrement field when ever a new instance of form is created. Actually i want an autoincrement field to store the User change request id'

    yes it is possible, just use the ID value in the form library (autogenerated by SharePoint for every form in the libraryp

    use a data connection to retrieve the ID number from the library, in descending sequence and then create a new field where the value equals ID + 1

    Hope this helps

    להגיב