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

9 thoughts on “Microsoft Lists: Updated Issue Tracker

  1. You are one of the best MVP, i will work on this and will update you. I will follow your foot steps and promise i will share some fantastique codes sitting some where in mind storage. sharing is caring, thank you appreciated!

    Like

  2. Hi Norm! Thank you so much for this thoughtful and thorough post! I am just beginning to explore this template and it seems very useful. Your tips here will be very valuable to get it even better for our team!

    Do you have any idea why the template’s “Days Old” column isn’t automatically updating for me? I have to edit and save the entry for that number to calculate. Are you aware of a setting that might prevent that? It’s the standard column from the template, not anything I’ve custom made.

    I may end up implementing your new column that stops updating days old once an issue is resolved or otherwise closed so it might not matter in the long run. Nonetheless, curious if you have insights on the issue I’m having.

    Like

    1. Hi JoAnna,

      “Days old” is a calculated column and should work “out-of-the-box”. Check List settings, look at the “Days old” column and ensure that it is a calculated column with the following formula: =IF(LEN([Date reported])=0,0,DATEDIF([Date reported],TODAY(),”d”))

      As to why it is like this, I can’t say for certain. If you create a new Issue tracker (from template) and if it still doesn’t calculate then there is a chance that the feature set is not fully deployed to your tenant yet.

      Let me know how you make out.

      Thanks for reading.

      Norm

      Like

      1. Thanks for the reply Norm!

        I can’t figure out how to view the formula, I think I don’t have full rights to the Site I’m using this List on!

        Before I commented here, I posted on the Microsoft Community about this. The Microsoft Agent/Moderator suggested “TODAY()” doesn’t automatically update! They sent me this User Voice suggestion to upvote https://sharepoint.uservoice.com/forums/329214-sites-and-collaboration/suggestions/35868427-auto-refresh-item-formula-using-today-function-in

        Like

      2. You can view the formula by going to Settings, List settings, click on Days old and view the definition. If access is an issue, why not create your own Issue Tracker in “Your lists”?

        Like

      3. I believe that message only applies when you set the calculated column to TODAY(). In “Days old” TODAY() is used in a DATEDIFF calc. I confirmed this behavior in my test tenant.

        Like

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