{"id":33086,"date":"2020-12-18T17:33:49","date_gmt":"2020-12-19T00:33:49","guid":{"rendered":"https:\/\/www.padtinc.com\/2020\/12\/18\/subscription-with-power-automate-flows\/"},"modified":"2022-04-01T15:59:18","modified_gmt":"2022-04-01T22:59:18","slug":"subscription-with-power-automate-flows","status":"publish","type":"post","link":"https:\/\/www.padtinc.com\/2020\/12\/18\/subscription-with-power-automate-flows\/","title":{"rendered":"Managing a Subscription List with a Flow in Microsoft Power Automate"},"content":{"rendered":"\n

This is an unusual HOW-TO post for our blog. Most of the time, we post useful technical content about Ansys, Flownex, 3D Printing, Scanning, and product development. But I’ve been on a no-code kick using Microsoft Power Automate and the flows you can create there. But as I’ve learned the tool, I’ve found a lack of good resources that are similar to the type of content we like to do for our Ansys users, so I thought I’d break the mold and post about a simple flow I did that shows how to add and modify data in Microsoft Excel from the results from a Microsoft form. <\/p>\n\n\n\n

\"\" <\/picture><\/figure><\/div>\n\n\n\n

It all started with a virtual happy hour I started back at the beginning of the pandemic. I invited a handful of people that I’m used to seeing at Arizona tech community events. Over time, I invited more people, and the regulars invited their friends. The invite list got long. Also, I found that no-one was being asked to be taken off the list, but many people have never shown up.<\/p>\n\n\n\n

I needed a subscribe and unsubscribe form that updated my list. <\/p>\n\n\n\n

Rather than using a perfectly good and free online tool to manage the list, I decided to use this need as a reason to learn more about flows in MS Power Automate. <\/p>\n\n\n\n

Here is what I wanted:<\/p>\n\n\n\n

  • Subscribe
    • Person goes to form, enters their email<\/li>
    • Email is checked against list<\/li>
    • If the email was on the list:
      • If the email is flagged as unsubscribed
        • Flip flag to subscribed<\/li>
        • Send a success email<\/li><\/ul><\/li>
        • If the email was flagged as subscribed
          • Send an email to the person letting them know they are already subscribed<\/li><\/ul><\/li><\/ul><\/li>
          • If the email was not on the list:
            • Add them to the bottom of the list<\/li>
            • Send an email letting them know they were added<\/li><\/ul><\/li><\/ul><\/li>
            • Unsubscribe
              • Person goes to form, enters their email<\/li>
              • Email is located in list<\/li>
              • If the email was on the list
                • The email is flagged as Unsubscribed (TRUE in second column) <\/li><\/ul><\/li>
                • If it was not on the list
                  • Send an email letting them know that their email was not found. <\/li><\/ul><\/li><\/ul><\/li><\/ul>\n\n\n\n
                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    So how do we do this in MS Flow? It is actually pretty simple. The basic concept behind flows is that you have some sort of an event on a document or a form that you set a watch on. Then you take the information from that event and do something with other Microsoft tools, and some 3rd party tools. All with no writing of code! You set up a flow chart and fill in forms. <\/p>\n\n\n\n

                    The other thing I like about this example is it shows how to deal with errors and branch when something doesn’t go right. <\/p>\n\n\n\n

                    I’m going to assume if you are reading this, you have a basic familiarity with the tool. If not, run through some basic tutorials and come back. <\/p>\n\n\n\n

                    Before you start doing the flow, you need to create a subscribe form, an unsubscribe form, and an Excel spreadsheet. The forms just ask for an email. <\/p>\n\n\n\n

                    \"\" <\/picture>
                    The Subscribe Microsoft Form<\/figcaption><\/figure><\/div>\n\n\n\n
                    \"\" <\/picture>
                    The Unsubscribe Microsoft Form<\/figcaption><\/figure><\/div>\n\n\n\n

                    Because flows work on tables, create a table with two columns. The first is for emails, and the second is for a flag on if they have asked to unsubscribe. You can have other fields on your forms and other columns in your table if you want more information, like company or names. For my happy hour, I just want emails. You can start with dummy emails or just your own. Save the file to a SharePoint site that you are part of. <\/p>\n\n\n\n

                    \"\" <\/picture>
                    The Table in Microsoft Excel<\/figcaption><\/figure><\/div>\n\n\n\n

                    Unsubscribe<\/h2>\n\n\n\n

                    The unsubscribe is simpler, so let’s start there. My flow looks like this:<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Let’s look at each block to understand how things work:<\/p>\n\n\n\n

                    I start the flow when my Unsubscribe form is submitted. (If you have Office365 and you are using a different form tool, stop and check out MS Forms. We have been very happy with it. ) All you need to do is pick the form you want. Note, I changed to the title with … > Rename so when I come back in 6 months, I can remember what is going on. <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Each block creates output that can go to the next block. All that the form trigger does is return the ID for the response. So we need to now get the information that was submitted with a “Get response details” block:<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Notice that you have to re-identify the form. It does not assume that the previous block is where the information is. So select the form again. <\/p>\n\n\n\n

                    For the Response ID value, we will use the results from the trigger block. Any time you fill in a field that is not a dropdown, you get a popup that shows you information passed down from previous blocks. At this point, all we have is the response ID. Click on that to fill the form out. These chunks of information are called Dynamic Content and will have an icon next to their label that reflects the application the information came from. <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Now that we have the email address to add, we need to try to add it to a table in Excel. We use an Update a row block for that. Our goal is to set the value to TRUE for the unsubscribe flag. <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Flows use files stored in SharePoint. So you need to find first specify the site you stored your Excel file on. Then the folder, then the file. All of these self-populate as you go. <\/p>\n\n\n\n

                    Now, pick the table you want to update. The way this works is you specify a “key column” and a value to look for. The first row that has the supplied value in it gets updated. So we need to specify our “email” column and then the submitted email from the dynamic content. <\/p>\n\n\n\n

                    It auto-populates with the columns in the table, so we can see our two columns that can be updated. We will leave email alone and set Unsubscribe to TRUE.<\/p>\n\n\n\n

                    Now, if that all works just fine, we want to send a confirmation email. If it doesn’t, because the flow could not find the email given, we want to send an email letting the person know if it didn’t work. <\/p>\n\n\n\n

                    We use the failure of the “update a row” block as a way to decide which way to branch. First, we need to make the branch. Add the success email:<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Put the submitted email in the To: box and put in a descriptive subject. I then explain what is going on in the body and include the email so they can see what they submitted. I also put a link to the subscribe form if they want to get back at some point. <\/p>\n\n\n\n

                    So that is great; if all goes well, they are marked as unsubscribed and get an email. But if their email was not on the list already, we need to let them know. To do this, you create a parallel branch and set “Configure Run After” to branch for an error. <\/p>\n\n\n\n

                    Click on the + and chose add a parallel branch:<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Do another email for that second option. I add in the body that they should use the email address that was in the last invite they got. <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Now is the branching part. If you leave it like it is, the flow will send both emails if the update is successful and nothing if it fails. We need to tell the “fail email” to only send on a failure. <\/p>\n\n\n\n

                    Do this by clicking on the … then chose “Configure run after.” <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure>\n\n\n\n

                    That brings up a form that lets you specify when the block should be run based on the exit status of the previous block. Check only “has failed” and Done.<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Notice how the down arrow leading to the block is reddish. This tells you that it only runs if the previous block did not run successfully. <\/p>\n\n\n\n

                    And that is a simple unsubscribe flow! I tried it out by unsubscribing myself and then using an email that is not in the list. <\/p>\n\n\n\n

                    Subscribe: More logic and branching <\/h2>\n\n\n\n

                    For subscribing, we are going to add a row to our table, and we also need to check and make sure that the email was not already on the list, which lets us use some “has failed” branching, but we also want to change them from Unsubscribed = TRUE if they are already in the list but want to re-subscribe.<\/p>\n\n\n\n

                    Here is the flow: <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure>\n\n\n\n

                    The first two blocks are the same. But the third block is a get a row block. It grabs the contents of the first row that matches the supplied Key Value for the Key Column. Some input, but the output is a list of the row values rather than letting them update the row. So we supply the Email column and the email address given. <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure>\n\n\n\n

                    For the case where it finds the row (we will come back and branch on the failure), we need to first check to see if the Unsubscribe flag is TRUE. So we insert a Condition Block. We put the returned value for Unsubscribe in the first field, set the condition to “is equal to,” and set the third field to true. See in the Dynamic Content dialog how the row results show up?<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure>\n\n\n\n

                    Note: Excel returns all lower case “true” or “false.” That tripped me up. So use all lower case. <\/p>\n\n\n\n

                    That block generates an If yes and an If no branch. <\/p>\n\n\n\n

                    For the If yes branch, we need to change the value of the row to FALSE and then send an email saying that the person has been resubscribed. So in the If yes block, we first add an Update Row block:<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    We do everything just like the unsubscribe changing of the row, except the value is now FALSE.<\/p>\n\n\n\n

                    Then we add a new email, letting them know they were turned back on:<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Now, if someone tried to subscribe and was already on the list and was subscribed, we should let them know with an email. So we add another email block into the If no Block<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Next, we need to go back to handle the case when looking for the row of data showed that they were not already in the list. <\/p>\n\n\n\n

                    We add a parallel Branch that points to an “Add a Row into a Table” block. <\/p>\n\n\n\n

                    The block looks a lot like the other two blocks we have used for excel, except there is no Key Column or Value. You point to the table, then supply the value you want added. For our flow, the email and FALSE for Unsubscribe. <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Remember, we add the row when it was not already there, the “get the row” block failed. So use “… > configure run after” and set it to “has failed” only. <\/p>\n\n\n\n

                    Then add a success email after that block:<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    I have also added an email to me if the attempt to add a row failed. That is not necessary; if a flow fails, you get an email. But I thought it was the right thing to do. So I added one more email block parallel to the success email: <\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n\n\n\n

                    Remember to set its “configure after run” to only execute on a failure. <\/p>\n\n\n\n

                    And it all works! Or seems to so far. And not one line of code. <\/p>\n\n\n\n

                    Final Thoughts<\/h2>\n\n\n\n

                    One thing I didn’t do was BCC or CC myself on the emails. If you click “Show advanced options” at the bottom of the email blocks, they let you do a lot more, including BCC and CC addresses. <\/p>\n\n\n\n

                    I could have also created a single form and had a check box for subscribing or unsubscribing. Then added a Condition block to branch based on that value. <\/p>\n\n\n\n

                    As mentioned above, I could have done this with a dozen different free or paid tools. But this was a great way to up my Flow skills for something more serious, like the tool we are building to manage NDA agreements or our project numbers. Powerful stuff.<\/p>\n\n\n\n

                    Or you can build your own list as an excuse to start your own Happy Hour. <\/p>\n\n\n\n

                    PADT has developed expertise in many areas since our founding in 1994, and automating processes and integrating different tools are two areas demonstrated in this example. Please reach out if you need to make your workflows more efficient or need simulation, design, or 3D Printing tools, training, consulting, or services. <\/p>\n\n\n\n

                    Cheers!<\/p>\n\n\n\n

                    \"\" <\/picture><\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"

                    This is an unusual HOW-TO post for our blog. Most of the time, we post useful technical content about Ansys, […]<\/p>\n","protected":false},"author":33,"featured_media":33087,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2323],"tags":[2469,2470,1355,2471,2472,2473,2474,2475],"post-podcast":[],"_links":{"self":[{"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/posts\/33086"}],"collection":[{"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/users\/33"}],"replies":[{"embeddable":true,"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/comments?post=33086"}],"version-history":[{"count":0,"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/posts\/33086\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/media\/33087"}],"wp:attachment":[{"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/media?parent=33086"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/categories?post=33086"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/tags?post=33086"},{"taxonomy":"post-podcast","embeddable":true,"href":"https:\/\/www.padtinc.com\/wp-json\/wp\/v2\/post-podcast?post=33086"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}