This post extends the methodology described in my previous post Automatic document numbering using Microsoft Flow and demonstrates how to create a unique sequence for a specific field in the document number.

For the example I will add a new field called ‘Functional area’ and for each area I need a separate sequence of numbers. The steps are:

  • Add the code for each functional area as a item in the Parameters list
  • Add steps in Flow to lookup the appropriate code in the list
  • Get the next sequence number
  • Increment the sequence ready for future use

The result will be a unique sequence for each area. In taking this approach there is a design decision around whether you store content associated a particular functional area in a separate folder. This could be the easiest approach from a user adoption perspective as you can then set the value of the functional area as the default value for the folder.

Adding the code for each functional area

I will use Managed Metadata to define the functional areas and add a Site Column to the target Document Library.

Creation of a new Site Column to hold the functional areas using Managed Metadata to define each area. The default is set to the Set Value term
Creating a Site Column for the Functional areas

Side note – You can change the display order of the terms by defining a custom sort order for the Term Set.

Adjusting the sort order in the Term Store Management tool using the Custom Sort tab. Normally terms will be displayed in alphabetical order.
Adjusting the sort order. Normally terms will be displayed in alphabetical order

The next step is to update the parameters list to hold multiple sequence numbers with one per functional area. It is not necessary to add the name of the area as the Flow will use the index numbers (shown in square brackets for each term) to determine the correct sequence to use. An added benefit is that you can change the text describing each area without having to redefine the Flow. As the Flow will use search to lookup values the order in which they are listed is not important.

A separate list is used to hold the sequence numbers.
Index numbers are added to the Title column and for new items the Sequence value is set to 0 (or whichever value you want to start the sequence from)

Updating the Flow

Several adjustments are required to the original Flow.

  • A new logic check to ensure the Flow only runs when the Functional area is populated
  • Two additional Compose actions that get the index of the Functional area
  • A SharePoint Get Items action to determine the row ID of the correct sequence number
  • A new variable is required to hold the outcome of the Get Items action
  • The Get last sequence action is modified to use the row ID of the correct sequence number
  • Update the automatic document number string to include the Functional area

Additional logic check

A new Condition action is used to test whether the contents of the Functional area column contains [!!]. The action was added immediately after the other Condition actions at the top of the Flow.

A Condition action is used to test for the presence of [!!] in the field. If detected the Flow exits using a Terminate action
Additional condition to test whether the value of the Functional area has been set

Additional Compose actions

Two new Compose actions are used to determine the index value of the functional area. As with the original Flow, the first action determines the position of the leading square brace ‘[‘. The second uses the position and takes the next two characters. Note you will need to change the logic if square braces are present elsewhere in the field or your indexes are not always 2 characters in length.

Two Compose actions are used. The first calculates the position of the [ used in the field. The section extracts the index number
Compose actions are used to determine the index of the Functional area

The expressions used in the Compose actions are:

add(indexOf(triggerBody()?['area']?['Label'],'['),1)
substring(triggerBody()?['area']?['Label'],outputs('Get_functional_area_index'),2)

SharePoint Get Items and additional variable

A SharePoint Get Items action is used to search the parameters list for the index value for the functional areas. I have used the out of the box Title column which is provided by SharePoint by default as it is automatically indexed i.e. it is searchable by SharePoint. This saves on creating another column and ensuring it is indexed. The Get Items action issues an ODATA query to SharePoint. Flow expects this query to return a number of results and so it will wrap any subsequent action that uses its output in an Apply to each action. It will do this even if you set the number of results to be returned to be 1 in the query by using the Top Count option. This can make working with the output difficult and so I pass the results to a variable first. The variable is wrapped in an Apply to each action and subsequent steps can use the output of the variable.

Three actions are needed. The first is Get Items from SharePoint. The second initializes an integer variable. The final one sets the value of the variable. The third is wrapped in an Apply to each action
Determining the row ID for the relevant sequence using Get Items and storing it in a variable

The Filter Query field uses the Output from the Get functional area code action. Note that when you insert the Output using the Dynamic Content pane it will not be wrapped in single quotes. You will need to add the single quotes for the query to work.

Title eq 'Output'

Modifying the Get last sequence action

In the original Flow this action obtained the sequence number from the first row in the list i.e. ID = 1. The modification uses the value of the seqPosition variable determined in the previous step.

A SharePoint Get Item action is used to get the next value in the sequence using the seqPosition variable as the Id
Modified Get last sequence action

At the end of the original Flow there is an action that updates the sequence value ready for the next use. There is no need to modify this action as it uses the ID value from the Get last sequence value action.

Update the Automatic Document Number

The final step is to include the Functional area in the string used for the Automatic Document Number (ADN).

A concatenate expression is used to generate the string that describes the automatic document number
The Functional area is added to the Concatenate expression

In this example I have added the Functional area immediately before the sequence number. The updated expression is:

concat(outputs('Get_project_number'),'-',outputs('Get_location_code'),'-',variables('variableDomain'),'-',outputs('Get_document_type_code'),'-',outputs('Get_discipline_code'),'-',outputs('Get_functional_area_code'),'-',variables('variableSequence'))

Updated Flow

There you have it. A methodology for issuing separate sequence numbers for different areas. With additional logic you can include more than one automatic document number field in order to determine unique sequences for combinations of fields. The Flow could also be modified to overwrite the Title or Name of the file with the ADN value.

The modified Flow with the positions of the additional or change actions
The modified Flow

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s