locked
Auto Increment Row number in repeating table? RRS feed

  • Question

  • Hi, Everyone

    I have a dropdown list to choose project No, and a repeating table for user to insert all sub projects under the existing projects. All sub projects Under each unique projects should get an Identification No auto incresed from "01" to 02, 03, 04...

    e.g:

    In Project NO dropdown, I have 3 project NO to choose: 

    2010Jan1
    2010Jan2
    2010Jan3

    If I chose "2010Jan1" from project No drop down list, Records in Repeating Table to list sub projects should be based on every insersion to be:

    (ID NO in repeating table)

    2010Jan1_01
    2010Jan1_02
    2010Jan1_03

    If I chose "2010Jan2" from project No drop down list, Records in Repeating Table to list sub projects should be based on every insersion to be:

    (ID NO in repeating table)

    2010Jan2_01
    2010Jan2_02
    2010Jan2_03

    How can I do this?

    Thank you so much.

    Monday, January 4, 2010 12:00 PM

Answers

  • You could assign concatennation of the project id and count() function as default value of your repeating id field.


    Edit: Formula that works even when rows deleted (set as default value) :

    count(../preceding-sibling::*[local-name() = "YourRepeatingGroupName"])

    Note: That formula might error out when you deleting rows with position higher than 15 from the end.


    http://alecpojidaev.wordpress.com
    • Proposed as answer by Ashwin Bhagwat. _ Tuesday, January 5, 2010 4:52 AM
    • Marked as answer by GuYuming Friday, January 15, 2010 5:11 AM
    Monday, January 4, 2010 5:32 PM
  • As Alec said, You could assign concatennation of the project id and count() function.

    however if you delete a row from the repeating table that will mess up your IDs.
    This is why i prefer to use Guid to identify rows.

    To do this, do the following:

    -Right click the Repeating node in the Data Source tab.
    -Choose (Programming > Changed Event...)
    -Insert the following code in the event:
                if (e.Operation == XmlOperation.Insert)
                {
                    e.Site.SelectSingleNode("./my:ID", NamespaceManager).SetValue(Guid.NewGuid().ToString());
                }
    

    -Remeber to replace the "my:ID" in the code with the name of your Identifier in your form.
    -Remeber to go to form options and choose Full Trust, and assign a certificate for the form.
    -There is a draw back with this code that it will not generate a guid for the first column. If this is an issue then you will have to tune the code by adding another element as a child to the repeating table called (HasGuid).This new element will act as a flag and tune the code to be like this:
                if (e.Operation == XmlOperation.Insert && !e.Site.SelectSingleNode("./my:HasGuid", NamespaceManager).ValueAsBoolean)
                {
                    e.Site.SelectSingleNode("./my:HasGuid", NamespaceManager).SetValue("true");
                    e.Site.SelectSingleNode("./my:ID", NamespaceManager).SetValue(Guid.NewGuid().ToString());
                }
                else if (e.Operation == XmlOperation.ValueChange && !e.Site.SelectSingleNode("./my:HasGuid", NamespaceManager).ValueAsBoolean)
                {
                    e.Site.SelectSingleNode("./my:HasGuid", NamespaceManager).SetValue("true");
                    e.Site.SelectSingleNode("./my:ID", NamespaceManager).SetValue(Guid.NewGuid().ToString());
                }
    This way a Guid will be created for any row at the first use.

    Goodluck
    Hazem Elshabini OMS (Online Modern Solutions) Software Developer http://infopointblog.com
    • Marked as answer by GuYuming Friday, January 15, 2010 5:23 AM
    Monday, January 4, 2010 7:37 PM
  • You right about count() function problems with deleting. But it easily could be overcome with simple rule that picks up new IDs from hidden field which is increments itself each time the new row is added.
    http://alecpojidaev.wordpress.com
    • Marked as answer by GuYuming Friday, January 15, 2010 5:21 AM
    Monday, January 4, 2010 8:35 PM

All replies

  • You could assign concatennation of the project id and count() function as default value of your repeating id field.


    Edit: Formula that works even when rows deleted (set as default value) :

    count(../preceding-sibling::*[local-name() = "YourRepeatingGroupName"])

    Note: That formula might error out when you deleting rows with position higher than 15 from the end.


    http://alecpojidaev.wordpress.com
    • Proposed as answer by Ashwin Bhagwat. _ Tuesday, January 5, 2010 4:52 AM
    • Marked as answer by GuYuming Friday, January 15, 2010 5:11 AM
    Monday, January 4, 2010 5:32 PM
  • As Alec said, You could assign concatennation of the project id and count() function.

    however if you delete a row from the repeating table that will mess up your IDs.
    This is why i prefer to use Guid to identify rows.

    To do this, do the following:

    -Right click the Repeating node in the Data Source tab.
    -Choose (Programming > Changed Event...)
    -Insert the following code in the event:
                if (e.Operation == XmlOperation.Insert)
                {
                    e.Site.SelectSingleNode("./my:ID", NamespaceManager).SetValue(Guid.NewGuid().ToString());
                }
    

    -Remeber to replace the "my:ID" in the code with the name of your Identifier in your form.
    -Remeber to go to form options and choose Full Trust, and assign a certificate for the form.
    -There is a draw back with this code that it will not generate a guid for the first column. If this is an issue then you will have to tune the code by adding another element as a child to the repeating table called (HasGuid).This new element will act as a flag and tune the code to be like this:
                if (e.Operation == XmlOperation.Insert && !e.Site.SelectSingleNode("./my:HasGuid", NamespaceManager).ValueAsBoolean)
                {
                    e.Site.SelectSingleNode("./my:HasGuid", NamespaceManager).SetValue("true");
                    e.Site.SelectSingleNode("./my:ID", NamespaceManager).SetValue(Guid.NewGuid().ToString());
                }
                else if (e.Operation == XmlOperation.ValueChange && !e.Site.SelectSingleNode("./my:HasGuid", NamespaceManager).ValueAsBoolean)
                {
                    e.Site.SelectSingleNode("./my:HasGuid", NamespaceManager).SetValue("true");
                    e.Site.SelectSingleNode("./my:ID", NamespaceManager).SetValue(Guid.NewGuid().ToString());
                }
    This way a Guid will be created for any row at the first use.

    Goodluck
    Hazem Elshabini OMS (Online Modern Solutions) Software Developer http://infopointblog.com
    • Marked as answer by GuYuming Friday, January 15, 2010 5:23 AM
    Monday, January 4, 2010 7:37 PM
  • You right about count() function problems with deleting. But it easily could be overcome with simple rule that picks up new IDs from hidden field which is increments itself each time the new row is added.
    http://alecpojidaev.wordpress.com
    • Marked as answer by GuYuming Friday, January 15, 2010 5:21 AM
    Monday, January 4, 2010 8:35 PM
  • Using event receiver you can also get the auto increment column.

    Please mark as answer if it work for you.

    Use your column name instead of "PONumber".

    Public override void ItemAdding(SPItemEventProperties properties)    {

               try
               {
                   this.EventFiringEnabled = false;
                   string columnName = "PONumber";
                   SPSecurity.RunWithElevatedPrivileges(delegate()
                   {
                   using (SPWeb web = properties.OpenWeb())
                   {
                       web.AllowUnsafeUpdates = true;
                       /*get the current list*/
                       SPList list = web.Lists[properties.ListId];
                       int highestValue = 0;
                       foreach (SPListItem item in list.Items)
                       {
                           if (item[columnName] != null && item[columnName].ToString().Trim() != "")
                           {
                               string value = item[columnName].ToString();
                               try
                               {
                                   int currValue = int.Parse(value);
                                   if (currValue > highestValue)
                                       highestValue = currValue;
                               }
                               catch (Exception ex)
                               {
                                   Trace.WriteLine(ex.Message);
                               }
                           }
                       }
                       SPListItem currItem = list.Items.GetItemById(properties.ListItem.ID);
                       currItem[columnName] = (highestValue + 1).ToString();
                       currItem.SystemUpdate(false);
                       web.AllowUnsafeUpdates = false;
                   }
                   });
                   this.EventFiringEnabled = true;
               }
               catch (Exception ex)
               {
                  Trace.WriteLine(ex.Message);
               }
           }

    Thursday, June 25, 2015 8:58 AM