The Document ID service in SharePoint is great for adding a unique, trackable reference to content. The format of the ID is constrained to:
{Prefix}-{Sequence 1}-{Sequence 2}
- Prefix :: Identifies the Site Collection
- Sequence 1 :: Identifies the library (list) where the document was first stored
- Sequence 2 :: Identifies the row in the library (list) where the document was first stored
Engineers and architects love metadata and various engineering standards e.g. BS 1192 Collaborative production of architectural, engineering and construction information. Code of practice insist upon metadata being used in the ID of a document. A typical format could be:
{Project number}-{Location}-{Originator}-{Discipline}-{Document type}-{Sequential number}
This kind of ID cannot be readily accommodated for using the Document ID service. This is where Microsoft Flow comes in. In this example I have created a Flow that combines user generated metadata with system determined values. The aim is not to replace the native SharePoint ID but compliment it with an automatic document number that engineers can use to refer to a specific item of content.
The solution comprises:
- A Team Flow that gathers the requisite metadata
- A SharePoint list which is used to hold the index value for the sequential number
- A SharePoint document library and associated content type
The Team Flow is triggered by any content that is added or modified in the library. The content type includes a choice field that allows users to rerun the automatic document numbering (ADN) if they need to replace an ADN value (say in the instance their initial metadata choice was wrong). The choice field is also used to prevent the Flow going into a continuous loop whereby it keeps checking the content for changes and burning through your Flow allocation. This constant loop is side effect of the ‘SharePoint – When a file is created or modified (properties only)‘ trigger.
The first step is to configure SharePoint. I’ve reproduced my configuration notes below:
The steps are:
- Create Term Sets and seed with at least the default value
- Create Site Columns
- Create Content Type
- Attach Content Type to target library
- Create list to hold sequence number
- Create sequence column in list and set to the desired start value minus 1
(For the brevity I have skipped the good practice steps of short internal names for lists and columns, enabling versioning, creating views etc.). The most terms are formatted as a string that contains a corresponding abbreviation. It is the abbreviation that is used in the automatic document number.
The second step is to create the Flow. I created the Flow in my ‘My Flows’ and shared it as a Team Flow once I was sure it ran as expected. The building blocks for the Flow are:
- Trigger :: When a file is created or modified (properties only)
- Conditions and Switch actions :: Used for error handling and decisions
- Compose actions :: Used to gather and manipulate metadata
- Variables :: Used for passing in a variable to a Switch action
I’ve reproduced my configuration notes below:
You’ll notice that my Flows uses a number of Compose actions. In creating the Flow I noticed that Flow struggles if the expression formula is too detailed. It also makes triage easier when you keep it simple.
I have also included a PDF file of my configuration notes . (In the notes, can you spot the user created column 😉 ) Reproduced below are several of the key actions:
Flow control
This action uses a Control – Terminate action to exit the Flow if the flowcontrol is set to Off. The terminated Flows are listed as cancellations. It may be possible to customise the control but the control is not well documented.
If the condition is met the Flow continues to the next action. The Discipline and Document checks use the same approach but the exit for these actions is on their ‘If yes’ branch.
Get discipline index
Several actions use expressions to locate a symbol in a text field e.g. a left square brace [
add(indexOf(triggerBody()?['discipline']?['Label'],'['),1)
It is necessary to increase the resulting index value by 1 in order to include the symbol in subsequent operations.
Get discipline code
Once an index value is determined for a particular symbol I then create a substring for the abbreviation.
substring(triggerBody()?['discipline']?['Label'],outputs('Get_discipline_index'),2)
In this case the abbreviations are always 2 characters long.
Get originator
The originator code is based on the person who first added the file to SharePoint. I know that this might not be the case every time and so if you do use this step you might wish to ask the user to pick the code rather than using this process. (mutliple branches will get messy). The process is:
1. Get index of @ symbol in creators email address
add(indexOf(triggerBody()?['Author']?['Email'],'@'),1)
2. Get overall length of creators email address
length(triggerBody()?['Author']?['Email'])
3. Get length of domain portion of email address using @ index
sub(outputs('Get_originator_email_length'),outputs('Get_originator_domain_index'))
4. Get domain portion of email
substring(triggerBody()?['Author']?['Email'],outputs('Get_originator_domain_index'),outputs('Get_domain_length'))
I then use a condition based on the output of the domain portion of the email that sets the variable for the originator code. The variable is initialized earlier in the Flow and populated with dummy data.
Sequence number
The sequence number is held in a separate SharePoint list and incremented by the Flow. A requirement of the automatic numbering is that the sequence number be padded to 5 digits e.g. 00123. A switch condition is used to determine the length of the sequence with the requisite number of leading zero’s added by the matching case.
1. Initialize sequence variable (output)
outputs('Increase_sequence_by_one')
2. Switch
length(variables('variableSequence'))
3. Pad with 2
In the case the sequence is 3 digits long so only 2 leading zeros are required
concat('00',variables('variableSequence'))
4. Set variable 3 (output)
outputs('Pad_with_2')
Automatic document number
This expression is a simple concatenation:
concat(outputs('Get_project_number'),'-',outputs('Get_location_code'),'-',variables('variableDomain'),'-',outputs('Get_document_type_code'),'-',outputs('Get_discipline_code'),'-',variables('variableSequence'))
Flow outputs
The Flow concludes with two actions that update the file properties and the sequence. It is important to note that you only need to update the metadata that you need to update. The rest can be left blank (except for required fields that have to be set e.g. the ID field).
This service works well and is highly customisable. Worth remembering that it may be constrained to 2000 Flow runs per month. I am now looking at the industrialisation of the process using Azure Logic Apps. The beauty of starting with Flow is that you can hone the process before adding more complexity by using Azure Logic Apps.