Quote of the day List post to Teams

[ScottRTC]

Hi power automate community, can you tell me if its possible to post a message in Teams from an MS List when the date is equal to today? 

[ScottShearer]

@ScottRTC 

Your Flow might look something like my example below.  I assume that there is only one entry for a quote for each day.

If you need more help, please post a screen shot of your Flow and I will try to assist.

[David_MA]

I am not sure how you want to do this, but it certainly is possible. The way I do this is I have a spreadsheet with all of the quotes in it with a column that calculates the row number:

The flow, which is scheduled and runs daily, does the following:

1. List rows present in table
2. A Compose action counts how many rows were found with a length expression, which is renamed "Count Quotes". This way you can keep adding rows to the spreadsheet without adjusting the flow.
3. Next, another Compose action generates a random number based on the number of rows in the spreadsheet with the expression: rand(2,outputs('Count_Quotes'))
4. I then use a Get row action to get quote based on the random number generated
5. This is followed by another compose action to generate the message I want to post using HTML formatting.
6. And finally, it posts the message in the Teams channel

The end result (I include the day's weather forecast for each of our locations as well):

I am sure you could probably do the same with a list instead of a spreadsheet. But if you do the random thing like I am and base it off of the list item ID, you will not be able to delete any of your quotes or you will need to figure out another way to pull your quote. An example of the end result is attached.

[ScottRTC]

Hi @ScottShearer thanks for the help!  

Yes I have only 1 quote per day in the list. 

can you tell me a more about the 'Filter Query' step? 

[ScottShearer · Accepted solution]

@ScottRTC 

Your filter query should look almost exactly like the screen shot below.  This will limit the items returned to those where your date column is today and the Recurrence trigger will run the Flow once a day. 

Substitute the internal name of your date to publish column for Date.  The internal name of the column may/may not be the same as the name you see (the display name).  Internal names cannot have spaces.  One way to verify the internal name of the column is to go to the list settings and click on the column name.  The internal name will be at the end of the URL after field=

Give it a try!

[ScottRTC]

I appreciate your help @ScottShearer 🙂 

Can you help me replicate the next step in your flow?

[ScottRTC]

@ScottShearer 

My flow now runs but I don't get the post into Teams, any suggestions? 

The output from the 'Get Items' line is correct 

[ScottShearer]

@David_MA 

Please post a screen shot of your formatDateTime() expression in your condition.

If should look like this: formatDateTime(utcNow(),'yyyy-MM-dd')

Also, is Date the name of the column in your list which contains the date the item should b posted in Teams?

[ScottRTC]

@ScottShearer 

The formatDateTime expression is - formatDateTime(utcNow(),'yyyy-MM-dd') 

The list display name is 'Date', the internal name is 'field_0'

If I hover the pointer over 'date' I get - items('Apply_to_each')?['field_0'] 

If I hover the pointer over 'value' I get - outputs('Get_items')?['body/value'] 

[ScottShearer · Accepted solution]

@ScottRTC 

My apologies... I didn't think it through before my last response.

First, you should be using a query filter in you get items action.  See my Get items action above and substitute

field_0 for Date in the query.  That will make it so that only items that should be posted to Teams today are returned.

I suspect that you already have the filter because only 1 item was returned.

Because the results for SharePoint are already filtered, you don't need a condition.  So, remove the condition in your loop and just have the Teams Post a message action.

Please let me know how it goes.

[ScottRTC]

Thank you for your help @ScottShearer - the daily quotes are now posting to our Teams channel and receiving  thumbs up from colleagues! 

[JMBARNES]

I might be over 12 months late to the party; but could I see your method for "Compose Today", "Count Quotes" & "Compose Random number"?

Thanks

[David_MA]

Sure, the expressions are as follows. Compose Today, which returns the current date (e.g., Tuesday, October 24, 2023), which is used in the compose message action and could have easily been placed there instead. I like using the compose actions because when you look at the workflow history it makes it easy to see the values.

formatDateTime(utcNow(),'D')

Count quotes, which finds out how many rows are returned from the List rows in a spreadsheet action:

length(outputs('List_rows_present_in_a_table')?['body/value'])

And the random number expression is:

rand(2,outputs('Count_Quotes'))

The first number is 2 because you don't want to return the header row. The second value is the output of the prior expression to count how many rows were returned. So, if there are 366 rows in the spreadsheet, it would return 366 and would generate a random number between 2 and 366.

This has been working well since I created. I added a few more quotes a couple of weeks ago and several months ago I changed the format of the adaptive card that is posted so the quote comes before the weather.