Mailing lists in Power Automate

No feedback found for this session

Aim

• build a simple mailing list:
  • based on Microsoft Lists
  • with subscribe/unsubscribe forms built in Forms
  • so that we can subscribe/unsubscribe/send out a mailshot via Power Automate
• we’ll give you some practical Power Automate experience
• and identify some common pain-points

General principles

• work incrementally
• keep names simple (form/list/flow names are all cake xxxx, variables are single-word, lowercase…)
• understand the way that Lists understands names
• test and check and test and check and test…

Setup

• make an Excel table
  • name, email, subscribed, cake
• make a new List by uploading that Excel table
• check the data types
• beware the Title column. That’s roughly an index: we’ll use Title to hold our email addresses (as we’ll check membership etc by email)

Tweak your list

• now add subscribed / not_subscribed choices to your subscribed column

Column settings

Edit column to populate values

Make two forms

• make a new Form for subscriptions
  
• make a second, unsubscribe, form with email alone
• note about form ID: the right way to identify your Forms
  

Subscription flow

• select Build an automated flow
  

Link the flow trigger to your form

• your new flow should be populated with a response block
  
• use the form ID of the subscription form as a custom value in the Form ID field
  
• that will leave Power Automate ‘watching’ your subscription form

Then retrieve the form response details

• Add a Get response details block
  
• then populate as per the previous step. This retrieves the relevant information from your subscription form

Create a new List Item

• now we’ll pass the form information to Lists
• Create a Create item block
  
• you’ll need your List address
  
• now test - it should create a blank List item when you submit something into the form

Populate specific values

• now we map the Form fields to the columns in our List
  
• the test now should be more interesting. Ensure that everything is being sent to the right column

Unsubscribe

• create a new Flow, with same two response blocks as previously
• make sure those link to your unsubscribe form
• then add additional blocks: Get Items and Update item
  

Update item

• start with your Update item block as follows:
  

Update item fails??

• I’d expect that to fail…
  

A digression: List names

• Lists stores column names in an aggravating way:
  • there’s the Name, which is the name you see in the column header
  • but there’s also the Field name, which is the proper-actual-real name of the column, as far as Lists is concerned
    • I think field names are immutable, so this bad design is probably a kludge to let people rename columns
• your email column doesn’t work because we need to use the Field name

Fixing the field name

• find the field name in Lists from Settings > List Settings
  
• then click on the column name
  
• the end of the URL should give the proper field name
  

Mail flow

• After that digression, the actual mailing flow is easy

• new instant cloud flow triggered by a button
  

• Get items from your list with a filter for subscribed - something like field_3 eq 'subscribed'
  

Compose a message

• there are several different email blocks you can try:
  

Test and check

Received email

Bonus ball: prevent redundancy

• you can tweak your subscription flow to allow people to update their details and re-subscribe via the subscription form
  

Get matching items

• on your subscription form, add a Get Items with a filter
  

Control flow via number of matching items

• next, add a Condition testing the length of the body from the matching items step
  

Create item is as before

• Create item (when the length = 0):
  

Update item

• Similar idea. The minimal version is just to change the subscribed status:
  
• You could also add name, cake fields etc if you were keen

Thanks

• many thanks to Maria Botha-Lopez (NES) who patiently taught me how to do this when I was new to Power Automate