Fixing Apply to Each with Append to String in Power Automate Microsoft Flow

and then I'm going to put the value of the cities in here and it's going to apply to each and then I'm going to put a comma in the space [Music] hey everyone my name is Andrew Hess and if you're new here I am a government Power Platform developer I first got into the Power Platform when covid came and my boss came to me he was like we have to have a form we have to have one now we have to be able to fill out this data we need it in two days and so I created a power app in probably about two days spending a good bit of time working I don't want to go into these exact specifics but it was for covid and uh that's how I got into powerapps I was studying before then uh you know how to do powerapps put my first official one really took place right at the beginning of covet and we had to have a way to enter in data now we couldn't wait and so that's when I really got into power apps and so today I kind of want to touch on power automate I've seen a lot of people struggle with this issue so I want to touch on the statements or the action in power automate append to string variable I've seen this drive people crazy I know it drives people crazy so let's kind of show you what I'm talking about and tell you how to fix this all right so imagine you have a multi-select column in SharePoint or who knows where the multi-select column is right it could be in dataverse but you have a choice field and let's say we'll call it uh cities and this is another thing so when you have a multi-select column should you name it City or cities I know this is really basic but add the plural case into that column name just so people know that you can select multiple so I'm going to put some cities in here so let's see here we'll start with uh New York um Dallas Los Angeles maybe Jacksonville um Detroit Cleveland let's make sure I spell Detroit right I did not so Detroit okay so we have a few choices in there and we're going to do check boxes allow multi-select okay so this could be in powerapps this could be in dataverse as long as you have a multi-select column I have a very basic power automate here and we are connecting to that same SharePoint site so this is the SharePoint site it's called Running comments I just kind of threw something together and we're connecting to the locations list so that's my list here has two columns uh title and cities so I'm actually going to create a new list let me create a new list and it's going to be my City's text because we're going to convert that choice field into a text field all right and then we'll just leave this title field like that now I'm in the same SharePoint site collection I'm going to connect to the my City's text there it is the title field and what's going to happen when I try and put that multi-select column in here so here's my City's cities value it puts it and apply to each and I've seen this drive people crazy I'm going to show you another example too and so when it puts in and apply to each it's actually going to create a line on each one let me let me show you what happens here so we will just save and then we'll test with a manual test and then we'll create a new line item here in the locations we'll create a new line item so a new line item but we're going to select multiple cities you know just a few in there and then we'll hit save now watch what's going to happen on our next list so if we open up my City's text you'll see it created three lines right so if we add the ID in here just so we can visualize that a little bit easier we'll add the ID in here and we'll make that number one so you can see there's three line items now now we don't want there to be three line items or maybe you do maybe do but in this instance I've seen this drive people crazy so what I'm going to do in power automate is instead of the apply to each here I'm going to take out the city's value take out the cities I'm going to pull it out probably apply to each right they apply to each drives people crazy so what I'm going to do is I'm going to initiate a variable inside power automate you know people do it different ways some people you know do the compose action a lot of times I just initiate a variable and my cities oh we'll do VAR my cities and it's going to be a string now there are multiple ways to do this I'm just showing you the way that I do it that's simple so what I'm going to type in here is append and I'm going to append to string variable and I'm going to use my variable my cities and then I'm going to put the value of the cities in here and it's going to apply to each and then I'm going to put a comma in the space so if you if we zoom in right there there's a comma and a space and now in my create item I will just use that variable now this is definitely useful if you're doing this multiple times in a power automate you know because now I have the variable up here all set and you're going to want to rename this stuff I always recommend renaming this you know rename this initialize variable VAR my cities you know do all that I'm just not going to do that during this video and then we're going to hit save and then we're going to test again or we could just automatically test with a recent trigger and now in my my City's text list we can see it's all written in one line now this does leave a comma in there and maybe we don't want that comma at the end so let's go back to Power automate now after the apply to each what I can do is I can compose compose my VAR my cities but what I'm going to do is now once again there's many ways to do this but I want to show this way because you know this is like a building block so we're going to go to expression on our compose action expression and we're going to type in substring now when you type in substring with the parenthesis it kind of gives you a hint text of what it should look like so it's saying string start index where does it start and then what's the length so in the substring we're going to say okay in the variable so we'll click back on Dynamic content in my variable where do we want to start we want to start with character Zero and then where do we want to end at and we're going to say sub and so we can look that you know number you know the acquired number of the sub trend is removed that's hard to say and we're going to say it's the length so this is the length of that variable and kind of go over here to the end and then right here in the second parenthesis we're going to say one remove the last comma okay so now we've composed we have our variable here we can pull in our compose outputs now you can rename this I would recommend recommend renaming this maybe VAR my cities um output Maybe so now when you see it you see VAR my City's output and then we'll just save tests automatic again and we'll run again and it's running it's running it's running in the background I realize I made a mistake I didn't pay attention to that space in there so let's do it one more time we're actually going to go to our substring and we're going to change it to two because I added a space in there and so now we'll test again I'm not gonna you know I'm just gonna leave it I'm gonna allow you to see my mistakes I think when you see my mistakes you realize that and you can see more things so now since that substring did not work because there was a space in there it's actually two characters now we've removed that comma so that's just one way to you know work with a multi-choice select field let me show you another example where I've seen it drive other people crazy okay let's say we're doing an approval and we're going to approve say one of the locations someone writes a new location in here and we want to approve it all right so I'm going to start a new um Power automate so when an item is created we're going to approve approval I want is right here so we're going to do start and wait for an approval and the approval type there's lots of different options I'm just going to say everyone must approve this is the approval title assigned to so we're going to assign it to me and these are the details and the item link can be the link to the item all right so now let's say we send an email with the the approval multi-choice column in there so we're going to send ourselves an email so send an email and we're going to send it to myself here is the approval email and maybe you want to make this better of course right and so we'll have the title and we'll put in the title not from the approval but from the when the item was created and then next we'll put in the city's value now watch what's going to happen it's the same thing that happened before right ah we're in that apply to each it's going to drive us crazy um we can't do that also let me show you another one that I've seen to drive people crazy on so let's say okay we'll delete that apply to each let's say we want to see the comments so from the approval there's response comments now even though I only put one person's name in there it still thinks that there could be multiple comments so it's always going to put it in that apply to each and I don't have multiple accounts to respond to this but I'm going to show you um ways around that so let's remove the the comments and take it out I've seen this happen to a lot of people is the comments people want to put it in email they want to put it in a SharePoint list they want to record the comments and they keep getting stuck and that apply to each and it drives them crazy and they send multiple emails right and you only want to send one email why would you want to send email for every comment that an approver makes so what you're going to do is you're going to initialize a variable and this will be variable comments and it's a string and then we're going to append again to a string variable the variable comments and then we're just going to say we're going to say the approver name we'll put a dash in here and then we'll say approver comments or responses comments so now for every approver name it's going to do the name a dash in their comments and instead of the comments since this is an email I'm pretty sure you can add HTML in there and it's going to pick that up so if you put a slash BR which is a break uh so like the next line in HTML it's going to pick that up in the email right because an email is going to pick up that HTML and just to double check it I'll write something right here so we can see that break um I don't have two accounts to to notice this with so I'm now in the comments we'll put in the VAR comments and I will save okay so now we're going to test manual this time and I'm going to create a new item in that list so in this list my new item and we'll pick a few in here it doesn't matter anymore um we'll hit save and then I'm going to go to my email all right so I'm in my email I did get an approval and we're gonna approve or reject and we can even write our comments in here um this does seem new compared to my governments in it so maybe this is um different but we can we can write comments in here and write a good bit and then we'll just hit submit okay so we now approved that um and we got a new email so in the new email we can see it wrote my title it wrote comments it wrote my name and what I wrote in the comments and then it did that break so now if you have multiple people commenting on an approval you'll have a list of all those comments you can put that in a SharePoint list you can store that in Excel you can write it in an email that way you can pull those comments out you have that apply to each it's not driving you crazy anymore you're not sending multiple emails you're sending one email you can see the HTML that's built into the email so there's paragraph tag BR tag maybe you don't even need the slash maybe it's just a br will work um and that's how you get around that apply to each put your variable and append a string variable and put it and apply to each and then use that later on in your power automate so I just want to say I've seen that drive people crazy the apply to each people fight with it they they they're like ah why is it putting in reply to each it drives me crazy I have to do all this well there's a multiple choices and that's how power automate does it you have to break it down and turn it into one uh data line else it's going to try and send you multiple emails it's going to write the SharePoint multiple times put your data in an append to string variable and it will automatically put it in an apply to each and then use that variable later on in your power automate thank you all for watching please like And subscribe my name is Andrew Hess I will see you next week

Channel: Andrew Hess - MySPQuestions

Views: 11,611

Keywords: append to string, power platform, power apps, powerapps, power apps tutorial, power apps help, learn power apps, learn power platform, microsoft, microsoft certifcation, low code, no code coding, app building, app development, how to make apps, workflows, ms flow, power automate, myspquestions, andrew hess, apply to each, flow, workflow, nintex, easy workflows, apply to each drives me crazy, why apply to each, power platform developer, government tenant, approval flow

Id: _PYa9XpyKlk

Length: 16min 42sec (1002 seconds)

Published: Mon Jan 09 2023