Posting random person's name to Teams

[Lumenia]

Hello,

I have a SharePoint list and want to build a flow that would randomly pick a person from the list and post it to Teams channel. Something like "Hello @ ABC you have been selected!".

The person can be picked only once.

I try to replace the person's name with outputs('Compose') but I get blank messages on Teams instead of the name.

I'd be grateful for help!

[abm]

Hi @Lumenia 

I can see you have you have got spaces between Name and Surname. You can't use spaces here. So to find out the actual schema name go to SharePoint list > Settings > Click that field name called 'Name and Surname'. This will open a new browser window. Check the browser URL at the end it says field=xxxxx

Copy the field name and use that in your browser.

Next you mentioned about the person can be picked only once. So for that you may need to create a boolean field in SharePoint list called 'Picked' (True or False). Then at the end of your flow. Do an update operation to set the flag as true. Also in your List Rows action step add a filter to check whether Picked is false. This way it won't pick again.

Thanks

[creativeopinion · Accepted solution]

 Since you've indicated that you want to only post the name once, you will need a way to filter out the names that have already been posted. Add a column to your SharePoint list to store a value. For my SP list, I'll use a Date and Time Column. 

Get Items

In the Get Items action, you'll need to specify a filter query. You will need to use the internal column name of your column. Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren’t sure how to get this, please refer to this section of one of my YT tutorials.

Use the eq (equals to) operator and enter null. This filter query will return items where the Posted on Teams (in my case) Column doesn't contain a value.

Specify a Top Count (optional)

Depending on how many items you have in your SP List, I would recommend limiting the Top Count to a smaller number than the total number of items in your list. This will increase the speed of your flow runs. Instead of returning all items in your list—it'll limit the Get Items action to the number entered into the Top Count field.

Return Count of Items

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow. Tip: Remember to rename your actions to keep things organized. It also makes it easy to identify which dynamic content belongs to which action. 

Insert a Compose action. Ad

d an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.Run a test. Review the output of the Compose action. Because I've specified a top count of 5, the number returned is 5. 

Get Details of Random Item

The Get Items action returns an array of items. To return a single item from an array, you need to specify an index. Array index numbers start at 0. The first item index would be 0, then second item would be 1, the third item would be 2 ... and so on.

Add a Compose action.

Insert the dynamic content that is storing the name. In my case it's the Profile column.

Because the Profile column is a person column, I'm using the Profile DisplayName dynamic content. When you insert the dynamic content into your Compose action. Power Automate will automatically nest the Compose action inside an Apply to Each action. This is because the Get Items action returns an array of items.

Click on the three dots of the Compose action and select Peek code.

Highlight the text after the question mark and before the closing double quotes. Make sure to include the square brackets. Copy this to your clipboard.

Remove the dynamic content label and pull the Compose action OUTSIDE of the apply to each action—delete the Apply to Each action. 

In the Compose action, insert an expression. Start with this expression:

?[0]

For this first test we'll use 0 as the index number to return the first item. Later on we'll replace this with a random number.

After the index key paste in the content on your clipboard.

Place your cursor at the start of the expression by pressing the up arrow key. Click on the Dynamic content tab and insert the value dynamic content from the Get Items action. 

Run a test. Review the output of the Compose action. It should return a name. Keep in mind, this will always return the first item.

Note: You could leave your flow as is if you'd like, since you are filtering the SP list by a column—a name would never be posted more than once anyway. The first item would always be a different name.

Get a Random Number

If you'd like to return a random item from the array, instead of always returning the first item—you'll need to edit your expression so the index number is a random number.

First, let's compose a random number. Add a Compose action to your flow between the Compose action storing the Item Count and the other one storing the Random Name. Insert the rand() function.

The rand() function takes two parameters:

rand([min integer],[max integer])

You'll need to specify a number to start at and to end at. The rand() function will output a random number between your start number and end number.

For the min integer, enter a 0 and add a comma.

Since the array index starts at 0 for the first item, we'll need to subtract 1 from the total number of items returned. This will give us the index of the last item from the Get Items array. Insert the sub() function.

Click on the dynamic content tab and insert the output from the Compose action storing the item count. This is why it's important to name your actions. Otherwise it makes it hard to tell which dynamic content belongs to which action.

Add a comma and a 1. 

This expression will return a random number between 0 and the number of items returned from the Get Items action. In my case, between 0 and 5. 

Run a test. Run a few tests.. each time you run a test the number should be different. 

Randomize the Name

Now that you have a random number, you can insert the output from the Compose action above into the expression returning the name.

To do this click on the dynamic content label for the expression. Highlight the index number and insert the output from the Compose action above.

Run a test.

Post Message

Add the Post a message in a chat or channel action. Use the output of the dynamic content from above in your message.

While you are testing your flow. Always set yourself as the recipient. Once you've confirmed the flow is working the way you've intended it to work, you can adjust where the message is posted and/or the recipient.

Update Item

Add an Update Item action. Don't insert the ID dynamic content from the Get Items action. Otherwise the Update Item will nest itself inside an Apply to Each action. You want to avoid this since you are only updating a single item. 

Start with this expression:

?[]

Between the square brackets you need to specify the index number. Insert the output of the random number here. Pay attention to the dynamic content you are selecting. Ensure you are selecting the output of the correct Compose action.

Add another set of square brackets and insert ID between single quotes.

Place your cursor at the start of the expression by pressing the UP arrow key. Insert the value dynamic content from the Get Items action. 

Insert any dynamic content that is required into the Update Items action. Remember that you will need to use an expression to do that so you can avoid the apply to each action. You can refer to the tutorial I've linked below. 

Sine I've used a Date and Time column to mark those items that have already been posted, I'll insert the Timestamp dynamic content.

Run a test.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

 

Take a look a this YT Tutorial I recently uploaded: 3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow

In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:

1️⃣ Looping through a Single Item

2️⃣ Creating Unnecessary Nested Loops

3️⃣ Looping through an Unfiltered Array

At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.

I'll also cover:

✅ How to avoid the Apply to Each action with a single item array

✅ How to use the item() function to access dynamic content in an array

✅ How to prevent unnecessary nested Apply to Each action loops

✅ How to use the Select action

✅ How to convert an array to a string with the Select action

✅How to use the Filter Query field

✅ How to count the number of items in an array

✅ How to use a condition control

✅ How to use the concurrency control

✅ How to set a top count

✅ How to use Compose actions for troubleshooting

@Lumenia

[Lumenia · Accepted solution]

Thank you so much!!