A common task that I have found need for in SharePoint is the ability to increment an identifier field for an item in a SharePoint list. One use case that I have is the ability to send regular posts to a Team channel but continue to loop through all the items in the list no matter how many there is. In short, I want to take a list of items from SharePoint and post the contents one item per day to a Teams channel, forever in an automated fashion

I want to take this opportunity to thank Andrew from Andrew Gallagher Computing for assisting me working this out. Here is the result:

You can achieve this outcome using Power Automate in Microsoft 365, which we have done as shown in the above summary screen shot. Now let me break down all the components.

The first place to start is to create a list in SharePoint that you will use with the appropriate columns. In this case I have 3 columns:

– TitleId = a column to keep track of each entry. This will contain an integer that gets incremented.

– Title = the subject line of the post in the Microsoft Teams channel

– Description = the body of the post in the Microsoft Teams channel

The first step in Power Automate is to create a recurrence trigger, which is pretty easy. I want this Power Automate to run once a day at a set time.

Next, I initialise 3 variables:

– MaxTitleId = will hold the current maximum value found in the TitleId column in the list

– MinTitleId = will hold the current minimum value found in the TitleId column in the list

– NewTitleId = this value will be MaxTitleId + 1 and will replace the existing TitleId value in the list items when that item is used.

An interesting point here that we found was that the field type for the variables has to be Float even though the actual numbers in the SharePoint list field are integers.

Next I need to get items from the SharePoint list I have created, from the SharePoint site location I desire. You will notice here that I also want to display the Advanced fields so I can add the following fields:

– Order by = TitleId desc

– Top count = 1

In effect, I am ordering the list by the TitleID column in descending order, which places the row with the highest value in the TitleId column at the top of the list. Then I simply take the 1 item from the list in that order, i.e. the top row. In essence, I am finding the item in the list with the highest  TitleId number in the list.

I then take that List row and set the variable MaxTitleId to be the value in the TitleId field for that item in the SharePoint list I just captured. That means that I am finding the highest value in the TitleId column for the list and saving that highest value into the variable MaxTitleId for later use.

next, I perform the operation of MaxTitleId + 1 using the expression:

add(variables(‘MaxTitleId’),1)

and store it into the variable NewTitleId. This will be the new value of the field TitleId in the list item after I have used that item. In effect, it will move the item that I use to end of my list.

I now again, get the same list, but this time sort by the field TitleId in ascending order using the command:

TitleId asc

After doing this, I again only take the top row of the list. In essence, I am now finding the row in the list where the field TitleId is the lowest. This will be the item that I want to post out to my Teams channel.

Before I do that, I save this minimum TitleId value to the variable MinTitleId as shown above.

I now post the information in the row item with the lowest value of TitleId to my preferred Microsoft Teams channel as shown above.

and above, is what it appears like in my Teams channel.

Finally, I update the item that I just used to post information to my Teams channel and replace the existing Titleid with the one in the variable NewTitleId. In effect, I am now making this used item in the list with the lowest TitleId field, the one with the highest value + 1 of TitleId in the list. This means that this item will fall to the bottom of the list and won’t be used again until down the track it becomes the item with the lowest TitleId field once again. That won’t happen until all other items in the list have been published and completed a similar process.

So there you have it. Yes, technically we don’t use the variable MinTitleId but we have included here to help anyone who may be looking for that using Power Automate. I speak more about the business reason for creating this type of automation in an upcoming article, but for now, consider it as a way to continually broadcast common messaging to people using Microsoft Teams.