Photo Credit Nick Hillier

Article Updated: June 11, 2019

As a consultant working in SharePoint, I have been asked one question more times than I can remember. That question is some variation of: “Can we have an automatically generated, unique, formatted identifier in the Title field?”. And, because history repeats itself, I recently got this question again. So, I sharpened up my MS Flow skills and created a template.

Scenario

Sometimes SharePoint is used to replace an existing system or needs to supply information to an existing system. In these instances, many organizations have requirements to use a unique identifier and have requirements on how that identifier is formatted.

Generally, something like one of these:

• ABC-00123-XYZ
• ABC-2019-00123-XYZ
• ABC-00123
• ABC-2019-00123
• 00123-XYZ
• 2019-00123-XYZ
• 00123
• 2019-00123

To put it generically: Prefix-Year-PaddedNumber-Suffix

Each section (except for the number) is optional.

This solution will cover all of the options above.

Note: When you create a new item in a SharePoint list, SharePoint gives that item an internal ID. This ID starts at 1 and increases by 1 for each item. So, if all you need is a unique ID, SharePoint already provides that.

IDC – Just give me the template

Here is the link to the template in my GitHub repo.

Overall Setup

The setup for this project was reasonably simple. I used a new team site, a “single item” SharePoint list, and a Flow for the heavy lifting. The list had a few columns that were used for the configurable section values. I also used a second list to trigger the Flow. More on that later…

SharePoint Setup

• Site
  • Type: Team Site
  • Name: Autonumber
  • URL: /sites/autonumber/
• List
  • Name: AutoID
  • Columns:
• Title: Default. This was renamed to “Prefix” and set to “not required”
• Suffix: Single Line of text.
• CurrentYear: Single Line of text.
• CurrentIndex: Single Line of text.

Create a single list item as follows:

• List
  • Name: TriggerList
  • Columns:
• Title: Default.

Flow

The high-level flow will be:

The “Create Formatted ID” portion will be:

Trigger

For the template, I used a Flow button. Most likely you would need to replace this trigger with a “SharePoint – When an item is created” trigger.

Please note that for this solution to work properly, the concurrency for the Flow MUST BE set to 1. If the flow runs multiple times concurrently you will get locking issues with the identifier or the same index supplied twice.

To configure the concurrency on a trigger, click the triple dots in the top right corner, click Settings, enable the concurrency control, and set the slider to 1.

Variables Used

The following variables were used in the Flow. They are initialized at the beginning of the flow.

Stage 1: Get AutoID

In this stage, I read the values from the SharePoint AutoID list item.

I used a “SharePoint – Get Items” action with a Top Count = 1 to get the list item. Then I looped through the “items” (there is only 1) and saved the ID to the IDFromAutoIdItem variable. Lastly, I used a “SharePoint – Get Item” action with IDFromAutoIdItem variable to get the single item. I could have simplified this whole stage by using a single “SP Get Item” action with a hard-coded ID, but I didn’t like the idea of hard-coding an ID.

Stage 2: Create Formatted ID

In this stage, I used the info from the AutoID list item to construct the formatted identifier.  This stage utilizes the “Set Variable”, “Append to String Variable”, and “Condition” actions.

Initially, I set the CurrentYear variable to the current year. The Flow uses:

formatDateTime(utcNow(),'yyyy')

This could be replaced with the “created date” of the actual trigger.

Next, I checked 2 things:

• Is the ResetIndexEveryYear variable set to true?
• Is the CurrentYear variable is different from the CurrentYear value from the AutoID item?

If both of those are true, I set the IndexValue to 1, otherwise, I set IndexValue to the AutoID CurrentIndex value +1.

Next, I append the IndexValue to the TempPaddedNumber. Since this number was initially “00000” it will now be something like “00000123”.

I created the final PaddedNumber buy using the skip function as follows:

skip(variables('TempPaddedNumber'),sub(length(variables('TempPaddedNumber')),length(variables('ZeroPadding'))))

That takes the length of “00000123” (8), subtracts the length of “00000” (5), and removes the first 3 (8 minus 5) characters from “00000123″ to get “00123”.

Lastly, I set the FullIdentifer based on the IncludeDateAfterPrefix setting.  Options:

• [AutoID-Prefix][Variable-CurrentYear]-[Variable-PaddedNumber][AutoID-Suffix]
• [AutoID-Prefix][Variable-PaddedNumber][AutoID-Suffix]

Stage 3: Update AutoID

In this stage, I updated the values from the SharePoint AutoID list item.

To do this I used a “SharePoint – Update Item” action. I supplied the ID from the initial “Get AutoID Item”, set the item’s CurrentYear value to the CurrentYear variable, and set the item’s CurrentIndex value to the IndexValue variable.

Stage 4: Apply Formatted ID

This stage is empty. It is where you would use your formatted ID.

Adding the Template to your Environment

Here is the link to the template in my GitHub repo.

After downloading the Zip file you can add it to your environment using the following steps.

From Flow, click Import

Click the Upload button and select the Zip file

The file will upload

On the Import Package screen, you will need to select an account to use for the SharePoint Connection, and then click Import

The Flow will import

Updating the Template to Use a New Item as a Trigger

If you want to configure the flow with a “SharePoint – When a new item is added” trigger you would follow these steps.

Remove the Button trigger by clicking the triple dots and selecting delete

Select the “SharePoint – When an item is created” trigger.

Alternately you could use “When a new file is created” above.

Select the site address and the list name to trigger the action

Note: Go into settings, turn on Concurrency Control and set it to 1 or you will get save errors.

Update the “Set CurruentYear” action to use the “Created” value passed from the trigger item.

Add a “SharePoint – Update Item” action to the Create Formatted ID stage. Use the ID from “When an item is created.

Add an item to the SharePoint List. It should trigger your flow and update the Title with the identifier.

What could be better?

1. Standard Separator: It might be a little more elegant if the separator was included in the SP list item instead of making sure it’s added to the prefix and suffix. Then it could be used in the code. That said, the current way allows for different (or no) separator characters in each part.
2. YYYY-MM-DD: In a couple of instances, I have had a requirement to add the month and day added to the Unique ID. If this is the case, I think adding an extra initialize variable step at the top, and an extra set variable after the Set CurrentYear action would be needed. Then just replace the CurrentYear part in with the new variable when setting the FullIdentifier
3. All options are user configurable: I did think about this. Moving the IncludeDate, ResetIndex, and ZeroPadding out to SharePoint would make the solution a bit more dynamic. In the end, I thought that I didn’t necessarily want to offer that to the end users. You could probably make the case either way.
4. Status: I have done some flows that report status as they move through the Flow. I didn’t bother with this for now.
5. Only update if successful: Something that may be useful is to flip stage 4 and 3 and have the update of the item be dependant on the successful execution of applying the generated ID. That way you don’t have gaps in the numbering.
6. Error Checking: There is currently none. This could lead to failures.
7. Checking on Item Modified: Until Flow doesn’t fire infinitely on item modifications from Flow (natively), I’m tempted to use “new item” only.
8. Calling from multiple list/libraries: Because of the nature of incrementing and writing back the current value, the concurrency must be 1. That means you can’t just duplicate the flow and use the same source list because you make have 2 flows fire at the same time from different lists. To overcome this, you could make a single list to fire this flow containing links back to the original lists and list items. There are probably several other solutions that would work for this as well.

I’m sure there are some more items that I could add to this section, but it’s a start. Best of all, I don’t have to create this solution over and over again.