Managing a Subscription List with a Flow in Microsoft Power Automate

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.

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.

I needed a subscribe and unsubscribe form that updated my list.

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.

Here is what I wanted:

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

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.

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

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.

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.

The Subscribe Microsoft Form
The Unsubscribe Microsoft Form

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.

The Table in Microsoft Excel

Unsubscribe

The unsubscribe is simpler, so let’s start there. My flow looks like this:

Let’s look at each block to understand how things work:

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.

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:

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.

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.

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.

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.

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.

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.

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.

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:

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.

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.

Click on the + and chose add a parallel branch:

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.

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.

Do this by clicking on the … then chose “Configure run after.”

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.

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.

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.

Subscribe: More logic and branching

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.

Here is the flow:

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.

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?

Note: Excel returns all lower case “true” or “false.” That tripped me up. So use all lower case.

That block generates an If yes and an If no branch.

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:

We do everything just like the unsubscribe changing of the row, except the value is now FALSE.

Then we add a new email, letting them know they were turned back on:

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

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.

We add a parallel Branch that points to an “Add a Row into a Table” block.

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.

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.

Then add a success email after that block:

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:

Remember to set its “configure after run” to only execute on a failure.

And it all works! Or seems to so far. And not one line of code.

Final Thoughts

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.

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.

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.

Or you can build your own list as an excuse to start your own Happy Hour.

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.

Cheers!

Five Basic Windows 10 Computer Skills Every Engineer Should Know

WINDOWS-ANSYS-1
At PADT we provide help to many of our customers who have trouble with their ANSYS simulations. At the top level, though, there are some computer skills for Windows that we consider basics that every engineer should know. If these are skills you already have in your tool belt, fantastic! If not, hopefully this information will help you be more effective in your simulation tasks.
Also, since most of us have been or are currently being updated to Windows 10, I’m providing the instructions for Windows 10. Windows 7 is similar, though.

Second-hand computers or second-hand laptops? Second-hand computers and second-hand laptops are an excellent solution for various computer tasks. At Computers R Us, you can find high quality Used Laptop Shop.

1. Run as Administrator

This allows us to run programs, a.k.a. “apps” with administrator privilege, even if our login credentials don’t allow this level of usage. This is the case for most users of engineering software. Certain components of ANSYS, including the CAD Configuration Manager and the Client ANSLIC_ADMIN Utility require changes to your computer that non-admin rights won’t allow. By running as administrator, we allow the program to make the needed changes.

To do this, click the Start Menu, then find the program (app) you need to run in the resulting list, such as the Client ANSLIC_ADMIN Utility, but one important thing to keep is mind is to use a privacy filter, it is important to maintain your privacy. Next, right click on that program, select More with the left mouse button, then select Run as Administrator with the left mouse button. If you are prompted to allow changes to your system, click Yes. Here is what it will look like:

ansys-windows-10-f01
2. View File Extensions

When using Windows Explorer, now known as File Explorer in Windows 10, by default you probably won’t see file extensions. Instead, you’ll see the prefix of files, but won’t see the endings of the file names. This will be the case when browsing for files to open or save as well. Sometimes you can rely on the icons associated with a file to know which program it’s associated with or the Type field in the list view, but sometimes there are conflicts. For example, an ANSYS Mechanical APDL macro file will have the extension .mac. You can probably guess that there is at least one other major company that can have software that uses that extension. By viewing the file extensions, even if the icons are wrong, we can more easily identify the files we need. Here is how it’s done:
Click Start, then File Explorer:

ansys-windows-10-f02

The default view using “Details” in File Explorer will look something like this (file names don’t include extensions):

ansys-windows-10-f03

To view the extensions, we click on the View menu in File Explorer, then Options, then Change Folder and Search Options.

ansys-windows-10-f04

The way I set this option for all folder on my computer is to then click on the View tab in the resulting small window, then uncheck the box for Hide extensions for known file types, then click Apply to Folders, then click OK.

ansys-windows-10-f05

Now the list view (using Details under the View menu) in File Explorer looks like this, with each file showing its extension in the list:

ansys-windows-10-f06

3. Define and Edit Environment Variables

Environment Variables are values that are used by certain programs to define settings. For example, an environment variable can be used to specify the license server for certain programs. It’s good to know how to define and edit these if needed. To do this, we bring up the control panel. In Windows 10, click on the Start button, then Settings:

ansys-windows-10-f07

A quick way to get there is to type “environment” in the search window in the resulting Settings window:

ansys-windows-10-f08

The search should find Edit the System Environment Variables. Click on that:

ansys-windows-10-f09

In the resulting System Properties window, click on the Environment Variables button in the Advanced tab:

ansys-windows-10-f10

A new window will open with a list of currently defined User variables (just for your login) and System variables (for anyone who is logged in), like this:

ansys-windows-10-f11

You can click on an environment variable to edit it using the Edit… button, or you can click on the New… button to create a new one. One ANSYS-related environment variable that occasionally needs to be set is ANSYSLMD_LICENSE_FILE. This is only needed if the default license server specifications aren’t working for some reason. You won’t need to set this under normal circumstances. Just in case, here is how to define it, using the New… button under System variables. We type in the Variable Name, in this case ANSYSLMD_LICENSE_FILE and then the Variable Value, which in this example is 1055@myserver.

ansys-windows-10-f12
When done defining and editing environment variables, we click on the OK button to complete the action and get out of that environment variable-related windows.

4. Check Usage of Your Computer Resources

As simulation experts, we are often pushing the limits of our computer resources. It’s good to know how to check those. First is disk space. An easy way to check disk space is to bring up File Explorer again. Click on This PC on the left side. This will give you a snapshot of the available space on each hard drive that is accessible on this computer:

ansys-windows-10-f13

Next, we may want to check CPU or memory utilization. Perhaps we want to make sure that our solution is running on multiple cores as we have requested.
To do this, hold down the Alt, Control, and Delete keys on the keyboard, all at the same time. Then click on Task Manager in the resulting window (it will look for a second like your computer is going to restart – it won’t actually do that).
In the resulting Task Manager window, click on More details:

ansys-windows-10-f14

In the resulting window, we can click on the Performance tab and view, for example, the current memory utilization, or we can click on Open Resource Monitor and get even more details, including utilization on each CPU:

ansys-windows-10-f15
5. Search for Large Files

It’s very common in the simulation world to end up filling up your disk drives. Therefore, it’s good to be able to find large files so we can delete them if they are no longer needed. For a simple way to do this, we’ll start with File Explorer again. This time, we’ll click in the search window at upper right, but won’t actually type in anything. We just want the search tools menu to appear:

ansys-windows-10-f16

Next, click on Search under Search Tools, followed by Size, then Gigantic (I will argue that 128 MB isn’t all that gigantic in the simulation world, but Microsoft hasn’t caught up with us yet):

ansys-windows-10-f17

Windows will now perform a search for files larger than 128 GB. If any of these are no longer needed, you can right click and delete them. Just make sure you don’t delete any files that are truly needed!

That completes our discussion on 5 computer skills every engineer should know. In conclusion, these basic skills should help you be more productive over time as you perform your simulation tasks. We hope you find this information useful, if it is is not enough, than visit this website for more infromation.

Also read: Windows SQL Server by SaveOnIT.Com.