Microsoft Lists: Updated Issue Tracker

In my last blog post I took a closer look at the Issue Tracker template that comes part of the new Microsoft Lists application. A part of the review was to compare “Issue Tracker” to “Issue Tracking” (the previous iteration of Issue Tracker) to help highlight the value of the new template and to also show some areas for improvement. In this post I will show how to update Issue Tracker to address those areas for improvement.

We’ll start by adding the following new columns:

NameTypeImageRational
CommentsMultiple lines of textA great way to keep teams up to date.
Due DateDate and TimeThis is very practical and better represents real world for project and operational task management.
Days Old NewNumberWe will replace the existing “Days Old” column with one that continues to calculate the age of an issue but stops the calculation and column formatting if the item “Status” is set to Completed or By design or Won’t fix.

In my first look at Issue Tracker, “Person or group” columns were displayed as round images. It looks great when a user has a profile image but when there is profile image the default avatar is displayed. To my surprise the column formatting has been updated to include the person’s name. Check out the before (left) and after (right) images below. Great job Microsoft Lists team!

Days Old

We will use Power Automate to keep the “Days Old New” column up to date and column formatting to visually highlight the cells when they are still “active” (New, Blocked, In progress).

Start by deleting the existing “Days Old” column and rename “Days Old New” to “Days Old”. Both commands are accessed from the “Column settings” menu.

Create a new Flow from Issue Tracker by clicking “Automate” > “Power Automate” > “See your Flows”. Create a new “Scheduled – from blank” Flow.

Provide a name for the Flow i.e. “Issue Tracker – Daily”; set the start time to 12:00 AM; repeat every 1 per day; and click “Create”.

Make sure to update the “Recurrence” trigger to your local timezone.

Add a “Initialize variable” action; set the “Name” to “varDateReported”; and “Type” to “String”.

Add a SharePoint “Get items” action; set “Site Address” to your list location; “List Name” to the name of the list; and set “Filter Query” to the following code:

Status ne 'Completed' or Status ne 'By design' or Status ne 'Won't fix'

Add a “Apply to each” action and set “Select an output from previous steps” to “value” from the “Get items” step.

Add a “Set variable” action; set “Name” to “varDateReported”; and “Value” to “Date reported” from the “Get items” step.

Add a SharePoint “Update item” action and set:

  • “Site” to your list location and “List Name” to your list name.
  • “Id” to “ID” from the “Get items” step.
  • “Title” to “Title” from the “Get items” step.
  • “Status Value” to “Status Value” from the “Get items” step.
  • “Days Old New” to the following expression:
div(sub(ticks(utcNow('yyyy-MM-dd')),ticks(variables('varDateReported'))),864000000000)

My completed Flow looks like the image below.

When the Flow runs, the “Days Old” column will be calculated if the “Status” column is anything but Completed, By design or Won’t fix. Next up, we will update the column formatting to only highlight cells when they are still “active”.

Format the column by clicking “Column settings” for “Days Old” and select “Format this column”. Select “Conditional formatting”.

Set the rules to:

  • If “Days old” is greater than 30 days.
  • And If “Status” is not equal to “Completed”.
  • And If “Status” is not equal to “By design”.
  • And If “Status” is not equal to “Won’t fix”.

Change the formatting styles to:

  • “Fill color” to “Black transparent 95%”.
  • “Font color” to “Dark red”.
  • “Icon” to “Warning”.
  • “Icon alignment” to “Align right”.
  • “Borders” to “Border all”.
  • “Border style” to “Dashed”.
  • “Border color” to “Dark red”.

Once complete the “Days Old” formatted column will look like the image below.

Due Date

We will mimic the Power Automate and column formatting used in “Days Old” to send reminders to “Person or group the issue is assigned to” and to visually highlight the cells when they are overdue.

Edit the “Issue Tracker – Daily” Flow and add a “Initialize variable” action; set the “Name” to “varReminderDate”; “Type” to “String” and “Value” to the following expression:

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

Add a SharePoint “Get items” action; set “Site Address” to your list location; “List Name” to the name of the list; and set “Filter Query” to the following code:

DueDate eq '@{variables('varReminderDate')}' and (Status ne 'Completed' or Status ne 'By design' or Status ne 'Won’t fix')

Add a “Send an email (V2) action and set:

  • “To” to “Person or group the issue is assigned to Email” from “Get items – Issue Tracker (Reminders)”.
  • “Subject” to “Issue Tracker reminder – “; add “Title” from “Get items – Issue Tracker (Reminders)”.
  • “Body” to “Title from “Get items – Issue Tracker (Reminders)”; ” – due in 30 days. See item for further details.”; “Link to item” from “Get items – Issue Tracker (Reminders)”.

My updated Flow looks like the image below.

When the updated Flow runs, the “Person or group the issue is assigned to” user will get a reminder email for those items that are due in 30 days that do not have a “Status” column value of anything but Completed, By design or Won’t fix. Our final update will add column formatting to “Due Date” column.

Format the column by clicking “Column settings” for “Due Date” and select “Format this column”. Select “Conditional formatting”.

Set the rules to:

  • If “Due Date” is before “Today (relative)”.
  • And If “Status” is not equal to “Completed”.
  • And If “Status” is not equal to “By design”.
  • And If “Status” is not equal to “Won’t fix”.

Change the formatting styles to:

  • “Fill color” to “Black transparent 95%”.
  • “Font color” to “Dark red”.
  • “Icon” to “Error”.
  • “Icon alignment” to “Align right”.
  • “Borders” to “Border all”.
  • “Border style” to “Dashed”.
  • “Border color” to “Dark red”.

Once complete the “Due Date” formatted column will look like the image below.

The out-of-the-box Issue Tracker is a massive step forward for users and teams looking to “track, manage and bring issues to a close by making it easy to set priorities in the status column and send notifications to team members when issues arise” (quoted from Microsoft Lists Look Book). The Issue Tracker template can be used “as is” to great effect or customized to meet requirements through additional columns, column formatting and automation.

I’m excited by the investments being made by Microsoft to Lists and the opportunities for automation through Power Automate.

Thanks for reading.

NY

One thought on “Microsoft Lists: Updated Issue Tracker

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s