Creating a Check-in Reminder App

This post was inspired by a recent tweet from Taylor Desseyn.

app idea here….

a networking tool where instead of social media/linkedin you plug in a persons info

and the app reminds you to check in with folks every 30 days

— taylor desseyn (@tdesseyn) December 14, 2022

So what we’re going to do is create a pair of Power Platform tools to help us do exactly that. First, we’re going to need a Power App that we can use to maintain a list of people we want to keep in touch with. The second piece will create a scheduled flow in Power Automate that will run daily, determine who we need to catch up with, and then send us that list of people, along with quick links.

_ Note: As noted in previous posts, you’ll need a paid license to use many features of Power Apps and Power Automate. _

Creating Our Data Source

The first thing we need is a place to store our data. For this app, we’re going to use an Excel spreadsheet as our backing store, but you could use pretty much any data storage that will let you store a simple table of data to track your people, such as a SQL database, Azure table storage, and so forth.

Using Excel on your desktop or web, create a new workbook. In that workbook, you will need to create a new table. It is critical that you save your data in a table in that workbook, as the Power Platform apps can’t interact with just a sheet of data in Excel. It has to be in a named table or you won’t be able to do anything.

For our table, we’re going to create a few fields of data to store.

• First name
• Last name
• Phone number
• Email address
• Twitter handle
• LinkedIn name
• Last Contact Reminder date

We could put in whatever other methods of contact we might have for the people we interact with, but that’s good enough for us for now. We’ll save our file to our OneDrive for Business account for ease of access to the Power Platform apps. You can put in a sample row of data or two to get you started, but you should end up with something that looks like the following:

That will be it for now.

Power Apps

The next step is creating a Power App to maintain the list of people in the Excel table. Power Apps makes it extremely easy to create such an app. Go to https://make.powerapps.com to create your Power App. On the Home tab, and also on the Create tab, you’ll see an option to create an app based on an Excel file.

Click on that button. If you don’t already have a connection set up to your OneDrive account, you’ll need to click on “New connection” to create a connection to your account. Once you have created the connection, you’ll then be able to select the file you created in the previous step.

Now select the table that you added to the spreadsheet in the first section. Then click the “Connect” button at the bottom right of the page.

After the loading animation screen, you’ll be presented with the designer for the application. This is actually all you need to do. The default application will allow you to create, update and edit any of the records in the table. It’s a complete application. The only thing you’ll need to do is save and publish the app.

Click the save icon at the top right, give the app a name and click “Save”.

Once it’s saved, click the publish button at the top right, and select “Publish this version”.

Now you can access the Power App you have created with the web interface, Android, iOS, or Windows Power App apps. Any time you need to add or edit one of the people you want to keep track of, just open the Power App to do so.

Power Automate Flow

Now that we have our data source and our maintenance app, we just need to create a flow in Power Automate to check our list and assemble a daily email of people to be contacted. For this, we’ll want to use a scheduled cloud flow that will run daily at 8 am.

Head over to https://make.powerautomate.com and In the Power Automate Create menu, under the “Start from blank” list, select “Scheduled cloud flow”. Give it a name and set it to repeat every 1 Day.

Add a new step, select “Excel Online (Business)” for the connector and select “List rows present in a table” for the action. In the action, select the Excel file you created in the first part and the table in that file that contains your list of contacts.

Next, we’re going to need a variable to hold the email body that we’re putting together. Add a new step to add an “Initialize variable” action. Give the variable a name and set the type to string. Leave the default value empty. For our example, we’ll call it strEmailBody.

Now, let’s loop through the rows in our table. Add a new step at the bottom and add an “Apply to each” action. For the output from the previous steps, select the body/value from the “List rows” action.

Inside of the loop box, add a “Condition” action. As we examine each row, we’ll look at the value of the last contact date and check to see if it’s more than 30 days ago. Because dates are a pain to work with in Power Automate and because Excel is weird about how it stores and presents date values, we’ll have to use formulas on each side of our condition to calculate a numerical value for the two dates that we can compare to one another.

For the left side, we’ll use the following expression formula:

addDays('1899-12-30', int(substring(item()?['Last Contact Reminder'], 0, 5)), 'yyyy-MM-dd')

This gets the first 5 digits from the date field in the Excel row, converts it to an integer, then adds that number of days to a starting date (1899-12-30), because that’s how Excel stores date values for a standard date field.

On the right side of our comparison we’ll add the expression formula:

addDays(utcNow(), -30, 'yyyy-MM-dd')

This subtracts 30 days from today’s date. For our condition comparison type, we’ll select “is less than”. Altogether this should tell us if the record is older than 30 days.

Now, because Power Automate doesn’t let you reference a variable as part of the calculation to update it, we’ll use a Compose action to build the output that will add the row we want to the email body. Then we’ll update the email body itself. This will take 2 steps.

Under the “If yes” pathway, add a Compose action first. In the compose we’ll build out the row to be added to the email and add it to the end of the strEmailBody value up to this point. You can design it however you like. And remember that you can format it as HTML, so feel free to add href links in there. For this example, I added the following:

Next, we’ll add a “Set variable” action. This will replace the value of strEmailBody with the value of the Compose action. I wish you could do this without the temporary step and append it to the strEmailBody variable, but you can’t reference a variable as part of updating its value. So you’re stuck with the intermediate Compose action.

Finally, after the “Apply to each” loop, we’ll send our email using whatever send email action is appropriate for your email system (Outlook, Gmail, etc). Power Automate supports a number of email systems.

Conclusion

That’s enough to get you a quick and dirty contact reminder app going. This requires that you manually update the contact list in the Power App, but it’s a start. And it’s a good simple example of how you can get Power Apps and Power Automate to work together to simplify one or two aspects of your work life. There’s certainly far more you could do with it from this basic starting point. Now go forth and contact your contacts with more contact!

The post Creating a Check-in Reminder App first appeared on Barret Codes.