I so appreciate this tutorial, thank you! Been struggling with this concept for a while, and all the other training videos I've watched have lost me halfway because the presenters don't explain why they are doing each step and how everything hangs together. Finally I've got this flow set up and working. 🥳So useful.
16:22, If you want to put spaces in your HTML table, you can hold down the ALT key, and press "32" on the number pad, then release the ALT key. This is the ASCII input for Space, and works with these Automate HTML videos. Great video! Super helpful, I went from having no idea how to do this to feeling knowledgeable and confident.
Thank you so much for the video. It answered my questions on how to create something for myself... your explanation and step by step helped me extremely.
this is working great, also liked the suggestion by someone of using multiple single people columns and running the same flow in parallel, but I'd like to see how you recommend processing the multi-select people column with the same outcome. If not a full video, just that portion...
I will like to say a big thank you for this amazing piece of work. It It's proved to be the solution to a task that has bugged me down for a week. God bless you real good.
I need to do exactly same but for multi users. I have to read multiple email addresses from single field of type Person or Group. It would be great if we can tweak the flow a bit and have that result. Please provide the approach , Sincerely appreciate your help.
This was great. I had two single person columns. I did a parallel branchand same operation in both. I'm sure a cleaner way might be possible, but works nonetheless. Cheers!
Thanks for sharing! I do have an issue that I get a summary email multiple times. Like when I have 2 task, I receive the email twice. When I have 3 tasks, I receive the summary email 3 times... I did follow your exact instructions.
Can we create a approval flow in this Task Summary. When a user closed the task an email will be sent to HOD and after approval of HOD that particular task close.
Hi there - in your odata filter for Get Items you will want to add in something like myemailcolumnname ne Null so you only pull items where there is a value for email OR you could return all items and then use a condition so that if myemailcolumnname is Null send the task reminder to someone else like an admin, but if there is value in this field to send to that person instead. I'll try to put a clip up to show how to do this.
Hi - do you mean emails with attachments - I have a couple recordings around attaching files - please check out ruclips.net/video/-E5AzqM1_R8/видео.html and ruclips.net/video/IhIZ1qmcYII/видео.html and see how you go!
Hi there - if your person column is a single select type you should be able to call the column name.email from the dynamic content pop up box in Power Automate? If its a multi select person field - check out my video on this one - ruclips.net/video/QYCnODQtlBY/видео.html Let me know how you get on.
Hi Richard! After doing the filter, there are no open items due but the powerautomate still sends the email with blank table. How can we avoid sending blank table like this? If there's no open items then email should not be sent. thank you.
Hi there - a method I use for this is to initialize a variable - integer set its initial value to zero. Then in your apply to each that looks for items - use an increment a variable action and increment by 1. Then put your email action in a condition - if variable greater than zero send email. This way if there are no items for a user - no email is sent. Hope this helps!
update: I managed to solve the problem by putting a filter query in the first "get items". In my case, it is an expiry alert, so it only obtains the items that are expiring (as a consequence, it only obtains the emails of those responsible for the expiring items), and the flow works perfectly without sending blank tables to those who do not have activities expiring. :)
Hi @Richard Burdes Great Video :-) Unfortunately I can't get data with 2nd Get Items. I am working with Task "Assigned To" field that is an array (User Id, Claims, Display Name, Email, ...). Select with Assigned To /EMail returns error message :-( Any idea to replace it?
Excellent tutorial, covers exactly what I wanted to do. Two small issues (for me at least). 1) The compose step using union() is case sensitive. I need to either make the union statement case insensitive or convert the resulting array to lowercase using the ToLower function. 2) I'm in the gov cloud so the Create HTML Table feature is not available. How can I accomplish this without creating an HTML table? Thanks for your help!
Hi Chris - thanks for the feedback :) - appreciate it! I would use the ToLower function in your expression - or pop it into a compose statement and call that so its easier to see what you are pulling into the Union. For the html table - how is your html? You can manually create a table by using an array create the initial array with your html table elements eg column namecolumn name 2, then use a for each action looking at the results of your union and use the append to array action adding in the item plus table html eg your dynamic item nameyour dynamic item value Then after your apply to each loop ends close the table with a final append to array action and simply pop in I appreciate this might not be what you need - but it will make a table in the array value you can add into your email out by calling the variable in the email body. Hope this makes sense! Helps! Rich
@@Richie1902 Hi Richard, Thanks much for the quick reply! So I guess where I'm stuck on the toLower function is whether I use a compose or try to put into the expression somewhere I get this error: The template language function 'toLower' expects its parameter to be a string. The provided value is of type 'Array'. Would I have to run a for each on the array converting each input to a string, lower casing it, and then putting it back into the array? I might have to read that a few times before I can be sure I understand it haha but yeah I can bust out an html table. I'll have to give this a shot and see what I can come up with. I'm brand new to power automate and am a bit out of my depth trying to convert some old Nintex workflows into PA Flows.
@@chrishaberman5813 - great stuff - for your union question - relooking at the workflow.. (its been a while) I would be putting the ToLower actually in my Select action in the value part of the map segment - hope that makes sense! so Map has 'TaskEmail' as the Key then in the value column your expression would be ToLower(item()?['SinglePerson/Email']) - switch out the 'SinglePerson' to match your column
Hi Richard, great video. Would you be able to do a similar video but for Excel? I've got an Excel workbook with a table of tasks for people and I would like to send out a summary email showing the tasks per person. Many thanks.
Hi Paul - excel should work similar to the sharepoint option - same process get items with a filter and then apply to each. Main issue with excel is that the odata filter only looks to support 1 odata filter so you cant string a few conditions together - but you can use a data operation 'Filter' action to help refine things more, then of course if the date values are excel dates then you need to convert current date - or date you are checking against into an excel type number date. Overall though the principals are similar, but out of all the data sources you can work with excel is possibly the least well supported - moving up to SharePoint. SQL or Dataverse would be a good move if the data you are working with is essential to the business. Hope that helps!
I've got it working (I think!) - the only trouble I had was with the Excel Column Names initially having spaces in them - I removed them and tried again and it worked. In the video you mention about putting some CSS around the HTML Table? Could you point to an example of that? Thanks again
Sorry - a couple of other formatting issues - 1/ is there a way to include spaces in the Create HTML table column headings? and 2/ some of the numbers that come through show multiple decimal points - could I re-format that from say 0.12578 to 12%?
@@paultrower4302 great stuff! For your number to a percentage - the best option I use is to use the 'format number' action - just type that into the search and you'll get a lilac? colored option to add in. You'll have to convert the number before adding into the email - then in the email just add the formatted number result from your dynamic content. Using this action put your number to be formatted into the 'number' field in the connector - make sure it is in a number format - if its a string you will need to use an expression - eg float(variables(mynumber) Then in the 'format' field put in a custom formula of 0.00% Then Locale - you pick - assume English (en) That do it. Good luck!
Hi Richard - thank you SO much for this video. It's exactly what I was looking for but even after following your steps, the CreatedBy users are getting SP list items they didn't create, any idea as to what I could have done wrong?
Hi there! Best steps to try to work it out are to look at a flow that has run and and click on each of the get items actions to see what your filter is asking for then compare that with the list details. If that checks out - have a look at where in your flow you have the email action - it may be sitting outside of an apply to each and so is spamming all users based on the full result of the flow? Another action would be to check if you are clearing your variable for users - could be that its building up an array of emails as the workflow progresses... Tricky without seeing it! I'm at Rich@brighterdays.co.nz if you want to send any screen shots over to check - hard to tell with out seeing it! Best of luck!
Hi Kirsten - in the o365 get users action in Power automate there is 'Given Name' which is generally your first name.. but depends if your IT have populated it. On a sharepoint person column you really only have display name which is your first and last name. So you could use an apply to each email found and get user profile from the o365 users action to achieve this.. Hope this helps - just let me know if you'd like a walk through clip on doing this.
Thanks Richard. I’m actually using a SQL table to get my data. I’ve used your flow and it works, but as soon as I use the ‘Forename’ from my SQL table in my email, it’s adds another ‘Apply to each’ step in 😕
@@KirstenChinnock - is the Forename column in the same row/table as the item you are running the check on to get the items? So you will have a list items, then an an apply to each item. Can you put a compose into the apply to each action where you are getting the record and use an expression maybe to show the forename? If you wanted to send a screen shot of how you have the actions configured I might be able to help more rich@brighterdays.co.nz Cheers
This is exactly what I needed. I got this to run the 1st time but now the table is continuously repeating in the email, even at the end of the email where there is nothing being called. Also, the dynamic expressions disappear from the body of the email when I go back into edit mode. Any suggestions on how to fix this? (Note: The list showing when I view the run is correct, just repeats in the email itself.) Thanks.
Hi Bria - it would be great if you could check if you email action isnt inside an existing loop that is making it repeat? Happy for you to send me screens of your workflow to richard@brighterdays.co.nz so I can see if I can spot it!
Hi @Richard Burdes, Thanks very much for such an informative video. My data is exactly as yours, but one of my email has apostrophe so the action get failed at second get items, can you help please. I have tried different approaches but none of them is working.
Tried the following expression in compose step before the second get items. replace(items('Apply_to_each')['EmailtoCheck'],'','''') But getting error: Unable to process template language expressions in action 'ComposeEmailsTo' inputs at line '0' and column '0': 'String cannot be of zero length. Parameter name: oldValue'.
@@muskjee thanks for your message - I think you actually need to string 4 apostrophes in order to find the one you want to replace - check this article which might assist - tomriha.com/how-to-use-string-with-apostrophe-in-power-automate-filter-query/ hope it does? You will also want to check that the email will actually send if you remove the apostraphe as well I guess? so maybe look at finding the unicode equivalent to replace with vs blank? i think it is ' but could be wrong there!
By deriving the parse JSON schema from the previous step result, does it mean every time the flow runs you need to re-copy the JSON sample code as the data refreshes?
Hi Daisy - if your json schema matches what may come in via the workflow, there is no need to adjust it as the workflow will be able to check against the known schema. If your schema changes - or you start to have null values come in - you may need to adjust - but generally its a create once type operation. Hope that helps.
ERROR IN SEND EMAIL: Saving the flow failed with error code 'OpenApiOperationParameterValidationFailed' and message 'Validation of input parameter' emailMessage 'failed in workflow operation' Send_a_message_of_mail_ (v2) ': The parameter with the value' "@ outputs ('Componi_2mail_TO') "'in path' emailMessage / To 'with type / format' Array 'cannot be converted to type / format' String / email '.
Davide - is your person field mutli select or single select - that error looks like one where you have a multi select or have pushed the whole record of the person into the To field vs just their email address. cheers
@@Richie1902 i also varied in single selection, but i get the same error. I notice in my flow the OUTPUT of the JSON schema leaves the TaskMail unchanged (incudendo Claims, JobTitle, Deparment, etc.) and not only the email as I see in your video. thank you
Thanks , You did great work. Just one kind suggestion, lots of RUclipsr make one mistake to understand their target audience. Experts don't need to watch your tutorials. Beginners can't understand the way you are explaining stuff. Keep great work up. 💚
Thank you for the great Video. I am new to this and first time trying to create one . I am using Assign to field in my list. In your viedo , you are mapping to Taskemail to single person email . But i didn't find the Assigned to Email . Can i know how i map to email ?
Hi there - best thing to do here is to check your source list or library in sharepoint - open the list settings and check the column names of all the person type fields - in my demo I just made a column called taskemail but if you wanted to use Assigned To you could - you just need to check it exists and then use the filter in the get items to go against that column. Hope this helps - just reply if you have more questions - happy to help!
@@Richie1902 Thank you so much for your quick response. Much appreciated. Basically I do not have email field in my task list. I only have assigned to field and it contained name of the person only. But what i noticed is once i get open items from the list in the first step i have all values including email in the outcome 'Value'. But not sure how to assign this in the second step :(
Hi - so in Power Automate if you click to add dynamic content and search in the dynamic content area for assign - do you see a control called Assigned to Email? or Assigned to Claims?
Assigned to in a list like Issue Tracker is still just a person type column so you can still get the email address of the person - it just takes a bit more of a search to find the right column - hope that helps
@@Richie1902 Thank you for your quick response. Unfortunately no field . I can get created by claims and Modified claims but not Assigned to . Can i know your email ?
Hi Daniel - if you navigate to the SharePoint list/library then go to its settings page, click on the column you'll see in the page url the system name of your Assigned To column - probably as Assigned_x0020_To copy this value and try it with the email selection - eg Assigned_x0020_To/Email Good luck!
Hi Richard, I managed to get the flow to work BUT when I received the email the columns are full of data that has the whole html syntax in it?! hope that you could tell me what's wrong as we really want to use this flow
Hi KD - are you able to let me know the column types you are putting in the email table? if they are people / choice / lookup types you will need to select the value of the column in the dynamic content picker in Power Automate - eg for a person column for showing the user name it would be ColumnName DisplayName vs just ColumnName. If the text you are pulling in is multi line rich text you could use the Html to Text connection to convert things to read better in the email - but I'm picking its people fields or lookups?
Hi Richard - Thanks for your quick responce, guess the smell of desperation travels far?! 😁. The columns that I am using in the email are Choice, multiple lines of text & date & Time. I think I have an idea what you mean (no idea where to find or where to insert the 'Html to text' connection, though?!). Going to give it a try
@@kdduroshola8685 Hi - hope you sort it - to find html to text just type that into the power automate add an action search box and it should show up.. date and time you can use the date time action of convert timezone - you can use the same zone you are in, but select a more friendly looking date format if that helps. Hope you sorted the choice fields - in the dynamic data box where you select the choice field make sure you select the choice value item - should be right under the main choice item.. if you get totally stuck - ping me at richard@brighterdays.co.nz happy to try to tweak things on a teams call - I'm in the New Zealand timezone
@@Richie1902 Hi Richard - found the Html to text action, put it after my create html table & ..... nope, didn't work. Thank you so much for your offer of help - going to take you up on it so, as soon as I start work I'll ping you
Awesome - followed your vid to a T and it worked beautifully. Any way to add stipulations based on date? If I wanted to only send out emails to users about entries where their 'Created' date is greater than 14 days ago at the time the flow runs, is that possible? If so, how can I include that into your flow?
Hi there - yes you could use the odata filter of lt for less than rather than the eq for equals and that will find anything with the date older than the one you specify - so if you use the same set up for get past date of 14 days then filter your sharepoint list with a lt rather than an eq you should be good to go. Hope that helps!
@@Richie1902 Hi. Thanks for getting back to me! So, if I already have a filter in my Filter Query within the "Get Items Filtered" action that says Status eq 'Pending', in order to only get SP List entries that were made greater than 14 days ago (because I don't want anyone to be notified on entries they made less than 14 days ago) would I then write: Status eq 'Pending' and Created lt getPastTime(14,'day')
@@mariamel-agha3114 Hi - looks ok - to be honest I like to use the action 'get past time' and toggle that to 14 days, then use a Text action - substring (0,10) that cleanly pulls in a date that sharepoint likes such as 2023-07-19 and put that into your odata filter. But you can also write an expression for get past time as well. Hope this helps!
@@Richie1902 Thanks! My expression *seems* to work - hopefully it stays that way. But if anything goes sideways, I'll definitely come back to your suggestion. One more thing...do you know, or have a video on how (or if) I could play out the following scenario in Filter Query as well: My filter query (loosely) looks like this: Status eq 'Pending' and Created lt getPastTime(14,'day') or Shipment eq 'Scheduled' and Created lt getPastTime (14,'day') That said, is it possible to then add one more filter for my Shipment column to where it does NOT bring in SP list records for items where the Shipment eq 'Scheduled' but the date they have it scheduled for still has not passed at the time the flow runs (the date they scheduled the shipment for is in another column titled 'Shipment Scheduled Date, but I'm not calling that out in my FIlter Query...however maybe it might be necessary to do so?). BUT, I would want it to bring in records where Shipment eq 'Scheduled' and Created lt getPastTime(14,'day') where their Shipment Scheduled Date HAS already passed at the time the flow runs That was probably a lot....but does that make sense / is it possible?
@@mariamel-agha3114 - Hi - I think if you wrap your odata filter up in to section using brackets you should be able to get the correct syntax working - so (filtera eq 'this' or filterb eq 'that') and filter c not 'test' or similar.. best bet is to check out odata syntax - but I think you are ok if you can break it down.
Hello Richard, amazing video I need your expertise, I have a Status column 'complete' 'In progress' 'Overdue' , I do not want to include the 'Complete' in my HTML table... I am struggling to add these conditions without breaking up everything, my filter query in GetItem already include the DueDate in7 days , can I filter with two condition?
Hi Gotur in your getItems you can filter this condition out so your odata filter just needs to use an 'and' like Status ne 'Completed' and DueDate eq 'myDynamicDateValue' hope that helps - but yes you can have lots of conditions in your odata filter
@@Richie1902 Hi Richard, Trying to use this flow with an Excel table and it appears that on the ODATA filter query Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.
@@gotursiks8071 nice one! the excel connector struggles with more than 1 odata filter I think - forcing the use of a Select data operation as well sometimes! Glad you have sorted things.
Great Video. If I wanted to filter by 2 line items *in the sharepoint list how would I go about doing that? For example I only want to send out email reminders when Reminder date = Today and if the status is Open. Thanks!
Hi Craig, Thanks! you can add extra filters in with the 'and' action in your odata filter - so ReminderDate eq '01/01/2022' and Status eq 'Open' just remember for text and date values you need a single speech mark either side of the filter value and for numbers, no speech marks eg Number eq 10. Hope that helps!
Hi - skip ahead to 21.09 mins and you'll see it - very basic, with no Css injected to make it pretty - there are few walk throughs on formatting emails in power automate if you need some help there. Cheers
Oh my god thank you!!! I've spent days trying to figure this out. You explain everything so clearly and well, thank you :)
Awesome! Thanks for the feedback - glad it helped you!
I so appreciate this tutorial, thank you! Been struggling with this concept for a while, and all the other training videos I've watched have lost me halfway because the presenters don't explain why they are doing each step and how everything hangs together. Finally I've got this flow set up and working. 🥳So useful.
Hi Wendy - that's great to hear! Thanks for the feedback!
@@Richie1902 any chance to show us how to do the multi person flow pls
16:22, If you want to put spaces in your HTML table, you can hold down the ALT key, and press "32" on the number pad, then release the ALT key. This is the ASCII input for Space, and works with these Automate HTML videos.
Great video! Super helpful, I went from having no idea how to do this to feeling knowledgeable and confident.
sorry but i didnt work for me, please assist
This video is a lifesaver, thank you, literally the only video i have found showing how to do exactly what i want in this situation, thanks again!
Awesome! Thanks for the feedback!
Thank you so much for the video. It answered my questions on how to create something for myself... your explanation and step by step helped me extremely.
Thank you so much.. This is so straightforward and very helpful.
this is working great, also liked the suggestion by someone of using multiple single people columns and running the same flow in parallel, but I'd like to see how you recommend processing the multi-select people column with the same outcome. If not a full video, just that portion...
reading up on some old comments! here is the clip on how I approach multi select people columns.
ruclips.net/video/QYCnODQtlBY/видео.html
I will like to say a big thank you for this amazing piece of work. It It's proved to be the solution to a task that has bugged me down for a week. God bless you real good.
That's perfect and thank you Richard. when are you going to do the same flow for the "Multi-Person" column? I would be glad if you do it soon.
ruclips.net/video/P1KW0JjaFJM/видео.html&ab_channel=RichardBurdes
Thank you so much. This is what I was looking for
Audio volume is way too low. I have all volumes tomaximum and cannot hear you clearly. I have to sit in quiet room.
I need to do exactly same but for multi users. I have to read multiple email addresses from single field of type Person or Group. It would be great if we can tweak the flow a bit and have that result. Please provide the approach , Sincerely appreciate your help.
That's perfect. Thank you, Richard.
Excellent and easy to follow video
Amazing tutorial, simple and to the point.
This was great. I had two single person columns. I did a parallel branchand same operation in both. I'm sure a cleaner way might be possible, but works nonetheless. Cheers!
ruclips.net/video/P1KW0JjaFJM/видео.html&ab_channel=RichardBurdes
Thanks for sharing! I do have an issue that I get a summary email multiple times. Like when I have 2 task, I receive the email twice. When I have 3 tasks, I receive the summary email 3 times... I did follow your exact instructions.
Hi! I need help. At time 6:49 you are selecting single person emails. Why don’t I have the option to get single persons emails? :(
Can we create a approval flow in this Task Summary. When a user closed the task an email will be sent to HOD and after approval of HOD that particular task close.
Very helpful. Thank you. How do we handle tasks with null email?
Hi there - in your odata filter for Get Items you will want to add in something like myemailcolumnname ne Null so you only pull items where there is a value for email OR you could return all items and then use a condition so that if myemailcolumnname is Null send the task reminder to someone else like an admin, but if there is value in this field to send to that person instead.
I'll try to put a clip up to show how to do this.
Great video. Can you please list the steps I would need if I wanted to send the email via an attachment instead. Thank you.
Hi - do you mean emails with attachments - I have a couple recordings around attaching files - please check out ruclips.net/video/-E5AzqM1_R8/видео.html and ruclips.net/video/IhIZ1qmcYII/видео.html and see how you go!
Has the multi-person workaround been figured out?
very helpful! thank you!
I really appreciate your tutorial on this! I know you're planning to post the Multi-User option-- any idea when that video will be ready?
Hi @Richard Burdes. Great video. Is there a similar way to do this with multiple emails per task?
Hi Mahit - yep - but i havent released the video yet! hope to very soon
How can i do that using the tesks of Microsoft Task?? Somebody hepl.
Hello, I have the "Email" field is part of a complex object. How can I get the value of just the email address from this object?
Hi there - if your person column is a single select type you should be able to call the column name.email from the dynamic content pop up box in Power Automate?
If its a multi select person field - check out my video on this one - ruclips.net/video/QYCnODQtlBY/видео.html
Let me know how you get on.
Hi Richard! After doing the filter, there are no open items due but the powerautomate still sends the email with blank table. How can we avoid sending blank table like this? If there's no open items then email should not be sent. thank you.
Hi there - a method I use for this is to initialize a variable - integer set its initial value to zero.
Then in your apply to each that looks for items - use an increment a variable action and increment by 1.
Then put your email action in a condition - if variable greater than zero send email.
This way if there are no items for a user - no email is sent.
Hope this helps!
I have the same problem, but this solution didn't solve it, it keeps sending the blank table. Is there another solution? Thanks@@Richie1902
update: I managed to solve the problem by putting a filter query in the first "get items". In my case, it is an expiry alert, so it only obtains the items that are expiring (as a consequence, it only obtains the emails of those responsible for the expiring items), and the flow works perfectly without sending blank tables to those who do not have activities expiring. :)
Very good Richard. I stopped at the expression. can you help me?
Hi @Richard Burdes
Great Video :-)
Unfortunately I can't get data with 2nd Get Items.
I am working with Task "Assigned To" field that is an array (User Id, Claims, Display Name, Email, ...).
Select with Assigned To /EMail returns error message :-(
Any idea to replace it?
I don't think we need to query sharepoint each time for each unique email, we can use the output of first getitems and apply filter to it
did you do the multi select video? I can't find it?
Hi there - yep - here you go ruclips.net/video/QYCnODQtlBY/видео.html
Excellent tutorial, covers exactly what I wanted to do. Two small issues (for me at least).
1) The compose step using union() is case sensitive. I need to either make the union statement case insensitive or convert the resulting array to lowercase using the ToLower function.
2) I'm in the gov cloud so the Create HTML Table feature is not available. How can I accomplish this without creating an HTML table?
Thanks for your help!
Hi Chris - thanks for the feedback :) - appreciate it!
I would use the ToLower function in your expression - or pop it into a compose statement and call that so its easier to see what you are pulling into the Union.
For the html table - how is your html? You can manually create a table by using an array create the initial array with your html table elements eg column namecolumn name 2, then use a for each action looking at the results of your union and use the append to array action adding in the item plus table html eg
your dynamic item nameyour dynamic item value
Then after your apply to each loop ends close the table with a final append to array action and simply pop in
I appreciate this might not be what you need - but it will make a table in the array value you can add into your email out by calling the variable in the email body.
Hope this makes sense! Helps!
Rich
@@Richie1902 Hi Richard,
Thanks much for the quick reply!
So I guess where I'm stuck on the toLower function is whether I use a compose or try to put into the expression somewhere I get this error:
The template language function 'toLower' expects its parameter to be a string. The provided value is of type 'Array'.
Would I have to run a for each on the array converting each input to a string, lower casing it, and then putting it back into the array?
I might have to read that a few times before I can be sure I understand it haha but yeah I can bust out an html table. I'll have to give this a shot and see what I can come up with.
I'm brand new to power automate and am a bit out of my depth trying to convert some old Nintex workflows into PA Flows.
@@chrishaberman5813 - great stuff - for your union question - relooking at the workflow.. (its been a while) I would be putting the ToLower actually in my Select action in the value part of the map segment - hope that makes sense!
so Map has 'TaskEmail' as the Key then in the value column your expression would be ToLower(item()?['SinglePerson/Email']) - switch out the 'SinglePerson' to match your column
Hi i have created a flow but instead of sending 1 email it is sending email number of row present in excel
table
Hi Asif - sounds like you may have your apply to each set up incorrectly? or possibly the filter so its picking up all items for each email?
Hi Richard, great video. Would you be able to do a similar video but for Excel? I've got an Excel workbook with a table of tasks for people and I would like to send out a summary email showing the tasks per person. Many thanks.
Hi Paul - excel should work similar to the sharepoint option - same process get items with a filter and then apply to each.
Main issue with excel is that the odata filter only looks to support 1 odata filter so you cant string a few conditions together - but you can use a data operation 'Filter' action to help refine things more, then of course if the date values are excel dates then you need to convert current date - or date you are checking against into an excel type number date.
Overall though the principals are similar, but out of all the data sources you can work with excel is possibly the least well supported - moving up to SharePoint. SQL or Dataverse would be a good move if the data you are working with is essential to the business.
Hope that helps!
@@Richie1902 Thanks - I will try and get it working and then let you know how I get on.
I've got it working (I think!) - the only trouble I had was with the Excel Column Names initially having spaces in them - I removed them and tried again and it worked.
In the video you mention about putting some CSS around the HTML Table? Could you point to an example of that?
Thanks again
Sorry - a couple of other formatting issues - 1/ is there a way to include spaces in the Create HTML table column headings? and 2/ some of the numbers that come through show multiple decimal points - could I re-format that from say 0.12578 to 12%?
@@paultrower4302 great stuff! For your number to a percentage - the best option I use is to use the 'format number' action - just type that into the search and you'll get a lilac? colored option to add in.
You'll have to convert the number before adding into the email - then in the email just add the formatted number result from your dynamic content.
Using this action put your number to be formatted into the 'number' field in the connector - make sure it is in a number format - if its a string you will need to use an expression - eg float(variables(mynumber)
Then in the 'format' field put in a custom formula of 0.00%
Then Locale - you pick - assume English (en)
That do it. Good luck!
hello, do you have the video where you show sending this to multi person field?
Hi Monique - I actually demo'd this at a recent meet up here in Christchurch - I'll try to get that up on RUclips asap!
@@Richie1902 thank you, so much!!
Hi Richard - thank you SO much for this video. It's exactly what I was looking for but even after following your steps, the CreatedBy users are getting SP list items they didn't create, any idea as to what I could have done wrong?
Hi there! Best steps to try to work it out are to look at a flow that has run and and click on each of the get items actions to see what your filter is asking for then compare that with the list details.
If that checks out - have a look at where in your flow you have the email action - it may be sitting outside of an apply to each and so is spamming all users based on the full result of the flow?
Another action would be to check if you are clearing your variable for users - could be that its building up an array of emails as the workflow progresses...
Tricky without seeing it!
I'm at Rich@brighterdays.co.nz if you want to send any screen shots over to check - hard to tell with out seeing it!
Best of luck!
Wow great thanks
Great video, thank you so much! Is there a way to use the forename and compose the email with 'Dear Forename' for each person? Thank you :)
Hi Kirsten - in the o365 get users action in Power automate there is 'Given Name' which is generally your first name.. but depends if your IT have populated it.
On a sharepoint person column you really only have display name which is your first and last name.
So you could use an apply to each email found and get user profile from the o365 users action to achieve this..
Hope this helps - just let me know if you'd like a walk through clip on doing this.
Thanks Richard. I’m actually using a SQL table to get my data. I’ve used your flow and it works, but as soon as I use the ‘Forename’ from my SQL table in my email, it’s adds another ‘Apply to each’ step in 😕
@@KirstenChinnock - is the Forename column in the same row/table as the item you are running the check on to get the items?
So you will have a list items, then an an apply to each item. Can you put a compose into the apply to each action where you are getting the record and use an expression maybe to show the forename?
If you wanted to send a screen shot of how you have the actions configured I might be able to help more rich@brighterdays.co.nz
Cheers
@@Richie1902 thank you so much for the reply, I will take a look and see if I can work it out. I'll drop you an email if not, thanks again! :)
@@KirstenChinnock - new tutorial to show how I tackled this is here - ruclips.net/video/BkMuz3yONmE/видео.html enjoy!
This is exactly what I needed. I got this to run the 1st time but now the table is continuously repeating in the email, even at the end of the email where there is nothing being called. Also, the dynamic expressions disappear from the body of the email when I go back into edit mode. Any suggestions on how to fix this? (Note: The list showing when I view the run is correct, just repeats in the email itself.) Thanks.
Hi Bria - it would be great if you could check if you email action isnt inside an existing loop that is making it repeat? Happy for you to send me screens of your workflow to richard@brighterdays.co.nz so I can see if I can spot it!
Hi @Richard Burdes, Thanks very much for such an informative video.
My data is exactly as yours, but one of my email has apostrophe so the action get failed at second get items, can you help please.
I have tried different approaches but none of them is working.
Tried the following expression in compose step before the second get items.
replace(items('Apply_to_each')['EmailtoCheck'],'','''')
But getting error: Unable to process template language expressions in action 'ComposeEmailsTo' inputs at line '0' and column '0': 'String cannot be of zero length.
Parameter name: oldValue'.
@@muskjee thanks for your message - I think you actually need to string 4 apostrophes in order to find the one you want to replace - check this article which might assist - tomriha.com/how-to-use-string-with-apostrophe-in-power-automate-filter-query/ hope it does? You will also want to check that the email will actually send if you remove the apostraphe as well I guess? so maybe look at finding the unicode equivalent to replace with vs blank? i think it is ' but could be wrong there!
@@Richie1902 thanks for a quick reply I will give it a go and let you know how it goes.
Thanks
@@Richie1902 It all worked Thanks very much for your help :)
By deriving the parse JSON schema from the previous step result, does it mean every time the flow runs you need to re-copy the JSON sample code as the data refreshes?
Hi Daisy - if your json schema matches what may come in via the workflow, there is no need to adjust it as the workflow will be able to check against the known schema.
If your schema changes - or you start to have null values come in - you may need to adjust - but generally its a create once type operation.
Hope that helps.
@@Richie1902 thank you for the response. I have understood the logic . It is just the schema
ERROR IN SEND EMAIL: Saving the flow failed with error code 'OpenApiOperationParameterValidationFailed' and message 'Validation of input parameter' emailMessage 'failed in workflow operation' Send_a_message_of_mail_ (v2) ': The parameter with the value' "@ outputs ('Componi_2mail_TO') "'in path' emailMessage / To 'with type / format' Array 'cannot be converted to type / format' String / email '.
Davide - is your person field mutli select or single select - that error looks like one where you have a multi select or have pushed the whole record of the person into the To field vs just their email address. cheers
@@Richie1902 Hi, exactly this field is multi select, how can i modify to make it work?
@@Richie1902 i also varied in single selection, but i get the same error.
I notice in my flow the OUTPUT of the JSON schema leaves the TaskMail unchanged (incudendo Claims, JobTitle, Deparment, etc.) and not only the email as I see in your video.
thank you
Thanks , You did great work. Just one kind suggestion, lots of RUclipsr make one mistake to understand their target audience. Experts don't need to watch your tutorials. Beginners can't understand the way you are explaining stuff. Keep great work up. 💚
Thank you for the great Video. I am new to this and first time trying to create one . I am using Assign to field in my list. In your viedo , you are mapping to Taskemail to single person email . But i didn't find the Assigned to Email . Can i know how i map to email ?
Hi there - best thing to do here is to check your source list or library in sharepoint - open the list settings and check the column names of all the person type fields - in my demo I just made a column called taskemail but if you wanted to use Assigned To you could - you just need to check it exists and then use the filter in the get items to go against that column. Hope this helps - just reply if you have more questions - happy to help!
@@Richie1902 Thank you so much for your quick response. Much appreciated. Basically I do not have email field in my task list. I only have assigned to field and it contained name of the person only. But what i noticed is once i get open items from the list in the first step i have all values including email in the outcome 'Value'. But not sure how to assign this in the second step :(
Hi - so in Power Automate if you click to add dynamic content and search in the dynamic content area for assign - do you see a control called Assigned to Email? or Assigned to Claims?
Assigned to in a list like Issue Tracker is still just a person type column so you can still get the email address of the person - it just takes a bit more of a search to find the right column - hope that helps
@@Richie1902 Thank you for your quick response. Unfortunately no field . I can get created by claims and Modified claims but not Assigned to . Can i know your email ?
Found the error - My field name was Assigned To (2 words) and multiple word field/EMail doesn't work !!
Hi Daniel - if you navigate to the SharePoint list/library then go to its settings page, click on the column you'll see in the page url the system name of your Assigned To column - probably as Assigned_x0020_To copy this value and try it with the email selection - eg Assigned_x0020_To/Email
Good luck!
Hi Richard,
I managed to get the flow to work BUT when I received the email the columns are full of data that has the whole html syntax in it?! hope that you could tell me what's wrong as we really want to use this flow
Hi KD - are you able to let me know the column types you are putting in the email table? if they are people / choice / lookup types you will need to select the value of the column in the dynamic content picker in Power Automate - eg for a person column for showing the user name it would be ColumnName DisplayName vs just ColumnName.
If the text you are pulling in is multi line rich text you could use the Html to Text connection to convert things to read better in the email - but I'm picking its people fields or lookups?
Hi Richard - Thanks for your quick responce, guess the smell of desperation travels far?! 😁. The columns that I am using in the email are Choice, multiple lines of text & date & Time. I think I have an idea what you mean (no idea where to find or where to insert the 'Html to text' connection, though?!). Going to give it a try
@@kdduroshola8685 Hi - hope you sort it - to find html to text just type that into the power automate add an action search box and it should show up.. date and time you can use the date time action of convert timezone - you can use the same zone you are in, but select a more friendly looking date format if that helps. Hope you sorted the choice fields - in the dynamic data box where you select the choice field make sure you select the choice value item - should be right under the main choice item.. if you get totally stuck - ping me at richard@brighterdays.co.nz happy to try to tweak things on a teams call - I'm in the New Zealand timezone
@@Richie1902 Hi Richard - found the Html to text action, put it after my create html table & ..... nope, didn't work.
Thank you so much for your offer of help - going to take you up on it so, as soon as I start work I'll ping you
Awesome - followed your vid to a T and it worked beautifully. Any way to add stipulations based on date? If I wanted to only send out emails to users about entries where their 'Created' date is greater than 14 days ago at the time the flow runs, is that possible? If so, how can I include that into your flow?
Hi there - yes you could use the odata filter of lt for less than rather than the eq for equals and that will find anything with the date older than the one you specify - so if you use the same set up for get past date of 14 days then filter your sharepoint list with a lt rather than an eq you should be good to go. Hope that helps!
@@Richie1902 Hi. Thanks for getting back to me! So, if I already have a filter in my Filter Query within the "Get Items Filtered" action that says Status eq 'Pending', in order to only get SP List entries that were made greater than 14 days ago (because I don't want anyone to be notified on entries they made less than 14 days ago) would I then write: Status eq 'Pending' and Created lt getPastTime(14,'day')
@@mariamel-agha3114 Hi - looks ok - to be honest I like to use the action 'get past time' and toggle that to 14 days, then use a Text action - substring (0,10) that cleanly pulls in a date that sharepoint likes such as 2023-07-19 and put that into your odata filter.
But you can also write an expression for get past time as well. Hope this helps!
@@Richie1902 Thanks! My expression *seems* to work - hopefully it stays that way. But if anything goes sideways, I'll definitely come back to your suggestion. One more thing...do you know, or have a video on how (or if) I could play out the following scenario in Filter Query as well:
My filter query (loosely) looks like this: Status eq 'Pending' and Created lt getPastTime(14,'day') or Shipment eq 'Scheduled' and Created lt getPastTime (14,'day')
That said, is it possible to then add one more filter for my Shipment column to where it does NOT bring in SP list records for items where the Shipment eq 'Scheduled' but the date they have it scheduled for still has not passed at the time the flow runs (the date they scheduled the shipment for is in another column titled 'Shipment Scheduled Date, but I'm not calling that out in my FIlter Query...however maybe it might be necessary to do so?).
BUT, I would want it to bring in records where Shipment eq 'Scheduled' and Created lt getPastTime(14,'day') where their Shipment Scheduled Date HAS already passed at the time the flow runs
That was probably a lot....but does that make sense / is it possible?
@@mariamel-agha3114 - Hi - I think if you wrap your odata filter up in to section using brackets you should be able to get the correct syntax working - so (filtera eq 'this' or filterb eq 'that') and filter c not 'test' or similar.. best bet is to check out odata syntax - but I think you are ok if you can break it down.
Hello Richard, amazing video
I need your expertise, I have a Status column 'complete' 'In progress' 'Overdue' , I do not want to include the 'Complete' in my HTML table... I am struggling to add these conditions without breaking up everything, my filter query in GetItem already include the DueDate in7 days , can I filter with two condition?
Hi Gotur in your getItems you can filter this condition out so your odata filter just needs to use an 'and' like Status ne 'Completed' and DueDate eq 'myDynamicDateValue' hope that helps - but yes you can have lots of conditions in your odata filter
@@Richie1902 Hi Richard,
Trying to use this flow with an Excel table and it appears that on the ODATA filter query Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.
I did use Parse Json > Filter array > create html table and it works!
@@gotursiks8071 nice one! the excel connector struggles with more than 1 odata filter I think - forcing the use of a Select data operation as well sometimes! Glad you have sorted things.
Error message came ". An action failed. No dependent actions succeeded."
Great
Great Video. If I wanted to filter by 2 line items *in the sharepoint list how would I go about doing that? For example I only want to send out email reminders when Reminder date = Today and if the status is Open. Thanks!
Hi Craig, Thanks! you can add extra filters in with the 'and' action in your odata filter - so ReminderDate eq '01/01/2022' and Status eq 'Open' just remember for text and date values you need a single speech mark either side of the filter value and for numbers, no speech marks eg Number eq 10.
Hope that helps!
can u show the email screenshot?
Hi - skip ahead to 21.09 mins and you'll see it - very basic, with no Css injected to make it pretty - there are few walk throughs on formatting emails in power automate if you need some help there. Cheers
audio is so low I can't hear it, even on max headphone volume.
Thanks for the feedback John - this is a pretty old clip now and my world has improved in terms of having a better mic. Cheers
union()?