none
Grouped Auto Increment by Region in a SharePoint List Column.

    Question

  • Hi All,

    I have requirement to have an auto increment list in SharePoint 2013. But the requirement is a bit odd.

    I have regions like APAC, EMEA, US, AMERICAS. I want to add an incremented value for each region like the below.

    APAC0001
    APAC0002
    APAC0003
    EMEA0001
    EMEA0002
    EMEA0003
    US0001
    US0002
    US0003

    I would like to have this without any programming. Is this possible?

    Thanks

    Monday, December 19, 2016 7:11 AM

All replies

  • Hi Chiranthaka J,

    According to your description, you want to create an auto-incrementing filed.

    An idea for this requirement is that using JavaScript. In script, use CAML query to group items by the specific region and get the number of the last item, then concatenate region with number+1.

    A demo about auto-populate a column with the increment number using JavaScript:

    http://sharepoint.stackexchange.com/questions/88340/how-to-auto-populate-a-list-column-with-a-sequential-number

    Best regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, December 20, 2016 6:23 AM
    Moderator
  • Hi Chiranthaka- about the only way you could accomplish that without "programming" is to use a calculated column that takes the value from another column (APA, EMEA, etc.) + the ID. Although that wouldn't make them sequential.


    cameron rautmann

    Tuesday, December 20, 2016 3:36 PM
  • Hi Chiranthaka,

    I had done this for a previous requirement of mine.

    I had used a SharePoint 2013 workflow. In the workflow I had used REST calls to fetch the items matching my condition, in your example the filter condition will be something like IDColumn contains APAC or EMEA or US.

    The result returned will give you the count of the items already existing with those initials. You need to create your new ID with the initials of your query example APAC or EMEA or US and the count +1.

    However, the important thing to note here is that you should not allow user to delete items from this column.

    In case you have to give delete permissions then you need to order by IDColumn in descending and pick the first value from the result returned. Add 1 to the value and create a new ID.

    In any case it is not a simple solution. But that is the best code-free solution possible.


    Regards, Huzefa Mala, MCPD, MCT Please mark the post that answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.

    Thursday, December 22, 2016 9:49 AM
  • Hi Huzefa,

    Thanks for your comments.

    Can explain me how can I do this using an example. Because I have now generated an auto-number without any grouping.

    Thanks

    Wednesday, December 28, 2016 8:24 AM
  • How did you generate the auto number? Did you use workflows.

    My solution will require you to create an SPD 2013 workflow.

    You can refer this example to understand how to query a SharePoint list using a REST service in a workflow: 

    SPD Workflow to query list

    In your query you will need to filter the items based on the constant part of the code i.e. if you are querying for APAC you will need to query CustomID contains APAC in your rest query.

    Get the count from the dictionary object and then create the new ID based on the count.


    Regards, Huzefa Mala, MCPD, MCT Please mark the post that answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.

    Wednesday, December 28, 2016 10:58 AM
  • Hi Huzefa,

    Yes I used a workflow. It's a SharePoint 2010 workflow in my SharePoint 2013 site. I have used the below tutorial.

    Create Auto-Increment number column in SharePoint List

    Can you help me to how to modify this workflow to achieve my target please?

    Thanks

    Thursday, December 29, 2016 2:18 AM
  • Unfortunately this will not be possible in a SharePoint 2010 workflow.

    You need to install Workflow Manager 1.0 and then use SharePoint 2013 workflow.

    SharePoint 2013 workflow allows you to call web services, which can allow you to query your SharePoint lists.

    Alternatively, if you will be using only SharePoint 2010 workflows then the solution is a bit tedious.

    You will need to create separate lists for each of your regions example - APAC, EMEA etc.

    On adding new item in your main list you will need to also make a new entry in the appropriate region list. The purpose of the region list is to only calculate the new ID that will be generated.

    Example - if the first entry made in the main list is related to region APAC then make a default entry in the region list  - something like APACx. This will return you the ID of the region list.

    Create your APAC ID by concatenating the value of the region (in this case APAC) and the ID given from the region list.


    Regards, Huzefa Mala, MCPD, MCT Please mark the post that answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.

    Thursday, December 29, 2016 4:02 AM