Use Power Automate to auto-increment your SharePoint lists and libraries for the next file or request you need recorded!
SharePoint lists and libraries are excellent resources for managing and aggregating an abundance of resources within the same container. In libraries, your files can take the form of policies and procedures, whereas lists are commonplace for dynamic things like request submissions or projects.
While SharePoint maintains great retention of data via version history, you may feel the need to add additional metadata on your items like specific date occurrences, delegates of responsibility, or in particular: a simple but unique reference number to give your content a searchable “barcode” string.
In this piece, we cover how to integrate Power Automation with your SharePoint lists and libraries to not only apply a unique reference number but also auto-increment that number in anticipation of the next file or request that needs it recorded.
Let’s first answer why this could be a good implementation for users:
The Gut Check: When a client asks how to implement a reference numbering system in SharePoint, I don’t automatically assume that they need one especially if they haven’t managed much-existing data in that environment. I prefer to challenge the client (or have the client challenge their users) to populate content in the library they manage their content and conduct various searches to try and get that data to surface to their users.
SharePoint has a historically refined search functionality and it’s only getting much better in modern SP. Previously a SharePoint search would compare a keyword string in the search box with string characters in any SharePoint column in the library. The most commonly checked columns were the default Title, Filename, Author, Created Date, and virtually any other metadata added to the content in SharePoint.
Now SharePoint scrubs the HTML within your file and can roll up balanced search results based on the number of hits, or even other ranking factors depending on how you choose and customize the search index in your SharePoint tenant. All this is to say that many users think they need a reference number column to help with the search, but, likely, their users are not using that number to access the file.
* It’s important to consider the process of users updating and accessing these files or requests and determine if the cosmetic benefit is worth the effort to configure the Power Automation.
Adding a dynamic number property to your files can be as simple as adding a column to the library. A few considerations below help frame why you may want to employ more than just a single-line number column:
It can seem simple to “stamp” a number onto a file property column in a SharePoint library, but it’s a little more complex to consider how SharePoint can remember what number comes next in line. As strange as it sounds, we need to manage that uniquely incrementing number in its SharePoint List separate from those library files we’re stamping.
Creating an additional SharePoint list seems like extra work, but we need a repository for the Power Automation to continuously look at what number to stamp on a recently uploaded file, increment the number in the list that the number exists on, and then be ready to repeat on the next event.
Creating this “Number List” also enables us to monitor the incrementation in real-time via the automation flow and potentially allows us to add more than one type of reference number. Technically a number list could manage multiple number incrementation for several departments or service areas at a company.
After logging into your Office 365 environment. You can get to the Power Automation app from anywhere by selecting the waffle in the top left and choosing it. When you navigate to My Flows from the left-hand menu, you can create an automated cloud flow from the `+New Flow` dropdown in the top toolbar above the canvas.
Depending on how many flows you intend on creating with your O365 account, a more descriptive title is useful. In this case, I searched for a flow trigger with `file created` as the keyword so I could pick when a file is created or modified (properties only).
After you’ve filled out the `Site Address` and `Library Name` the flow will trigger when an item is added to the library, whether it is created or uploaded:
The next step `+New Step` below the trigger, is to get the data from the number list that holds the current numbers. In this case, the first trigger in the flow already pulled the data from the file that was added, so the flow knows the service area defined on the document. We search for a `Get Items` action and pick the SharePoint operation. After picking the `Number List` as the list name, we can dynamically filter the number to lookup in the list by the service area that came from the document:
Now we take data that we have in Power Automate and edit the content with the `Update File Properties` step, another SharePoint operation. Be careful to choose the document `ID` property from the trigger step and the `Number` property from the `Get Items` step. This will ensure that the automation updates the document number column on the document with the number property from the number list:
You may notice that more than one property can be updated on the document. This is also not the only step that can be used to update files in SharePoint. Sending HTTP requests is another common way to post updates to data in Office365 environments in a similar fashion:
The final step is to increment the source number in the number list so that it is updated for the next document that triggers the automation. This is accomplished using a Compose data operation step and inputting the `syntax: add(outputs('Number'),1)`. The outcome of his composition becomes dynamic content that can be put back into another `Update Items` or `Send an HTTP Request` to update the actual number itself in the number list.
You’ll want to consider what it means to manage a continuously incrementing reference number in its list that has relevance to historically static numbers on your SharePoint list…
Play through these scenarios yourself and with colleagues to determine what your plan of action will be based on your preferences. What’s important is that you are in touch with the process and anticipate what your users will expect to happen. One outcome of these would be:
Using Power Automation to dynamically assign metadata to content in SharePoint is a great challenge toward honing your Office 365 skills as a power user. It requires you to integrate between two applications within the MS suite and pull numerical data from a SharePoint list into Power Automate to copy it into content back in a list or library back in that environment.
Although I alluded to many other bells and whistles in this blog, I recommend setting up and populating the content in SharePoint first, both the number list and document library or request. Once you have the containers set up, creating the most basic form of automation and proof-testing will provide a great foundation for adding more components to it if you prefer.
Your management team will thank you for creating a sustainable system on-site that you’ll be familiar with and be poised to help troubleshoot if anything goes awry. Having the data in your O365 environments may encourage other users to utilize more and more of their suite applications and become power users themselves for other-like scenarios.