More automatic document numbering using Microsoft Flow

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.

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

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.

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.

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.

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.

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.

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).

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.