Incrementing a SharePoint List Column using Power Automate and Flow

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:

image

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.

image

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

image

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.

image

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 time for the variables has to be Float even though the actual numbers in the SharePoint list field are integers.

image

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.

image

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.

image

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.

image

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.

image

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

image

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

image

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

image

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.

CIAOPS Need to Know Microsoft 365 Webinar–September

laptop-eyes-technology-computer

Time to look at the newest Microsoft 365 service – Lists. In this webinar I’ll show what Microsoft Lists is all about and give you some suggestions on how you can put it to work in your business. I’ll also have the  the latest Microsoft Cloud updates plus open Q and A as well.


You can register for the regular monthly webinar here:


September Webinar Registrations


The details are:


CIAOPS Need to Know Webinar – September 2020
Thursday 24th of September 2020
11.00am – 12.00am Sydney Time


All sessions are recorded and posted to the CIAOPS Academy.


The CIAOPS Need to Know Webinars are free to attend but if you want to receive the recording of the session you need to sign up as a CIAOPS patron which you can do here:


http://www.ciaopspatron.com


or purchase them individually at:


http://www.ciaopsacademy.com/


Also feel free at any stage to email me directly via director@ciaops.com with your webinar topic suggestions.


I’d also appreciate you sharing information about this webinar with anyone you feel may benefit from the session and I look forward to seeing you there.

Updated CIAOPS PowerShell course

I am pleased to announce that I have updated my online PowerShell course for Microsoft 365. A lot has changed in recent times so I’ve been through the content and updated it as well as add lots more lessons and resources. The only thing that hasn’t changed is the price. That remains at US$39!

You can view the course content and sign up here:

https://www.ciaopsacademy.com/p/powershell-for-microsoft-365

Of course, if you are eligible CIAOPS Patron, you’ll also get immediate access to the course for free as part of your benefits.

The idea with the course is not to give you a deep dive into all the workings of PowerShell. It is designed to get you up and running using PowerShell with Microsoft 365 as quickly as possible. No pre-existing knowledge is assumed. I’m aiming to bring out more advanced courses, but this course is the foundation on which courses will be built.

As I have said many times here, PowerShell is a KEY skill for any IT Pro going forward in the Microsoft Cloud space. It allows you to do things faster and more consistently, just to name two benefits. If you haven’t taken the step to learning PowerShell then invest in yourself and you future and do so!

September poll

ask-blackboard-chalk-board-chalkboard-356079

For September I’m asking people:

Have you ever paid, or helped someone else pay, a ransom after a ransomware attack?

which I greatly appreciate you thoughts here:

http://bit.ly/ciasurvey202009

You can view the results during the month here:

http://bit.ly/ciaresults202009

and I’ll post a summary at the end of the month here on the blog.

Please feel free to share this survey with as many people as you can so we can get better idea of how much ransom is being paid out there. I’ll bet is more than most think. Let’s see.

August poll results

August’s question was :

Are you considering or using Microsoft Defender ATP in place of other third party anti virus and end point security solutions?

and the results are:

image

A much stronger result for Defender ATP that I thought! 93% of respondents are looking at using Defender ATP! Wow, shows you that Defender ATP is really starting to kick some goals out there and make waves.

The anonymous September question for you is:

Have you ever paid, or helped someone else pay, a ransom after a ransomware attack?

which can be found at:

http://bit.ly/ciasurvey202009

appreciate if you could take a moment and let me know your experiences.

Microsoft Whiteboard works on the web

image

Microsoft Whiteboard is an app that allows you to mimic what a physical whiteboard does, however being digital it has a number of advantages. One of these advantages is that it works on the web. To see this just visit:

https://whiteboard.microsoft.com/

and sign in with a Microsoft account. You can use your commercial Microsoft 365 account and have all the standard capabilities included.

In our current world of remote work I am finding Microsoft Whiteboard very handy. I really enjoy the ‘infinite’ canvas and the ability to export the output to a file, I typically use Whiteboard on a device like a Surface PC or iPad and share that back to the desktop on which I’m conducting my remote meeting. However, you can also easily share it with others in the company so you can all collaborate together.

The web version isn’t as full featured as the device app as yet but it is more than adequate for most I feel. if you work remotely and want to get your message across, make sure you take a look at Microsoft Whiteboard and now with a web version, nothing could be simpler!

Need to Know podcast–Episode 250

I’m joined in this episode by MVP Lisa Crosbie to talk about what’s new in the Power Platform, especially Project Oakdale or as it was known when we recorded it, Microsoft Dataflex. Lisa shares with us what this technology is all about, how it integrates and the benefits it can provide businesses.

There is also cloud news and updates from Microsoft at the top of the show, as usual to keep you up to date.

This episode was recorded using Microsoft Teams and produced with Camtasia 2020

Take a listen and let us know what you think – feedback@needtoknow.cloud

You can listen directly to this episode at:

https://ciaops.podbean.com/e/episode-250-lisa-crosbie/

Subscribe via iTunes at:

https://itunes.apple.com/au/podcast/ciaops-need-to-know-podcasts/id406891445?mt=2

The podcast is also available on Stitcher at:

http://www.stitcher.com/podcast/ciaops/need-to-know-podcast?refid=stpr

Don’t forget to give the show a rating as well as send us any feedback or suggestions you may have for the show.

Resources

@lisamcrosbie

@diirectorcia

Lisa Crosbie on YouTube

Lisa Crosbie on Linkedin

The UP podcast

Power App in a day

Dataflex in now Project Oakdale

Available for preorder today, Surface Duo is purpose-built for mobile productivity

Microsoft Surface Duo Press Briefing

Microsoft Office 365—Do you have a false sense of cloud security?

Introducing EDR in block mode

End users can now report “This wasn’t me” for unusual sign-in activity

What’s new: Azure Sentinel and Microsoft Defender ATP improved alert integration

CIAOPS Getting Started with Azure Sentinel online course

Microsoft Whiteboard in Teams Adds Sticky Notes and Text, Improves Performance

Maximize cost control with new auto-shutdown setting

Allow administrators to delete any chat message in Teams

By default, in Microsoft Teams, you have the ability to delete your own messages but not other people’s. That’s a good thing unless you are an administrator or owner of the Team. There are plenty of cases where a chat message from another member of the Team could be considered inappropriate and needs to be removed by an administrator. Problem is that, generally, even an administrator cannot delete another person’s chat message.

This behaviour can be changed in the Teams administration portal by making changes to the appropriate Messaging policy.

image

You’ll firstly need to navigate to the Teams administration center which you can access via the Microsoft 365 admin center. You can also get there directly by navigating to:

https://admin.teams.microsoft.com/

You then need to select Messaging policies from the items on the left as shown above.

Then, on the right, you will see the option to Manage policies, which you should select.

Most tenants should only have a single policy as shown above, but if not you’ll need to select the appropriate policy you wish to change. Simply click on the policy name you wish to change. Here, I’ve selected the only one available Global (Org-wide default).

image

Ensure the option Owners can delete sent messages is set on On as shown above.

You’ll now need to wait for that policy to be updated to your Team. It will take a little while so don’t expect the changes to be seen immediately. Not that I have tried to see whether using PowerShell to force the changes works any quick but if you find it does, let me know. The command you want if you plan to user PowerShell to make this change is:

set-csteamsmessagingpolicy –tenant <GUID> –allowownerdeletemesage $true

Once the changes in the policy have been pushed out to the Team, any owner of that Team will now be able to delete any chat message from any user as simply as they can their own messages.

image

This ability will hopefully make life a little easier for Team owners who need to ensure compliance and appropriate behaviour in their Teams.