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

40 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

  3. Hello Norm – I’m enjoying your blog. I’m a new user of the Lists – Issue Tracker template. I’m curious as to if you might have a suggestion for how to automate the generation of the Issue ID so that each new issue is numbered in sequence? Thank you for any thoughts.

    Liked by 1 person

  4. Norm – please disregard my question on generating an ID. I’ve just figured out how to enable the already existing field.

    Like

    1. Hi Valerie,

      Thanks for the great feedback, I appreciate it. I suspect that you are using the built-in ID column. That is a good approach.

      I have seen it where the business process requires “coding” added to the ID. For example, ISSUE-01 or REQUEST-02 etc. In those cases, you would use a condition action with an expression based on metadata to generate the business “coded” ID. It can get tricky if you need sequential numbers in the “coding”. Perhaps this could make for a new blog post?

      Thanks for reading!

      NY

      Like

  5. Hi Norm,

    Flow is looking great and I can’t wait until I figure out what is wrong with Get Items – Issue Tracker (Reminders) step. I am constantly getting following error while testing:

    “The expression “Due Date eq ‘2021-03-07’ and (Status ne ‘Completed’ or Status ne ‘By design’)” is not valid. Creating query failed.”

    (I have removed “Won’t fix” status from Lists and Flow since it is not needed in my case).

    Currently my “Filter Query” looks as follows:
    Due Date eq ‘@{variables(‘varReminderDate’)}’ and (Status ne ‘Completed’ or Status ne ‘By design’)

    Any ideas would be much appreciated.

    Regards,
    Marcin

    Like

    1. Hi kosekbiega,

      WordPress is messing up the single-quotes in the expression. It should look like this: Due Date eq ‘@{variables(‘varReminderDate’)}’ and (Status ne ‘Completed’ or Status ne ‘By design’)

      Try replacing the single quotes and let me know how you make out.

      Thanks for reading!

      NY

      Like

      1. Hi Norm,

        Thank you for your quick reply but I am not quite sure what do you mean since this is what I actually entered:

        Due Date eq ‘@{variables(‘varReminderDate’)}’ and (Status ne ‘Completed’ or Status ne ‘By design’)

        Please see a screenshot of Get Items action:

        https://ibb.co/L5FW5HT

        Thank you in advance.

        Regards

        Like

      2. Hi Norm,

        I have found the error. I went to List settings and verified Due Date column settings. Statement in the Filter Query should start from “Duedate”. So the final statement should look like this:

        Duedate eq ‘@{variables(‘varReminderDate’)}’ and (Status ne ‘Completed’ or Status ne ‘By design’).

        Regards

        Like

  6. Hi Marcin,

    I believe WordPress is converting all of the single and double quotes into a format that Power Automate does not understand.

    The expression should read: Due Date eq ‘@{variables(‘varReminderDate’)}’ and (Status ne ‘Completed’ or Status ne ‘By design’)

    Replace ‘ with ‘ and let me know how you make out.

    Thanks for reading!

    NY

    Like

  7. Hi Norm – very helpful blog you have here! I’ve hit a wall with your suggestion that I can’t figure out, the “get item” filter query keeps returning the same error;
    The expression “Status ne ‘Completed’ or Status ne ‘By Design’ or Status ne ‘Won’t Fix'” is not valid. Creating query failed.

    Any suggestion on what I’m missing? I’m using the Issue Tracker template, but the days old column isn’t counting.

    Like

    1. Hi Jusin,

      Thanks for those kind words.

      WordPress is converting single quotes ( ‘ ) into a string that Power Automate cannot interpret. Check the single quotes in the odata filter query and let me know how you make out.

      Thanks for reading!

      NY

      Like

    1. Hi Sean,

      The OData filter is an advanced option in the SharePoint Get items actions. Code listed below:

      Status ne ‘Completed’ or Status ne ‘By design’ or Status ne ‘Won’t fix’

      Let me know if you need more help and thanks for reading!

      NY

      Like

  8. I’m still getting an error Norm:

    The expression “Status ne ‘Completed’ or Status ne ‘By design’ or Status ne ‘Won’t fix’” is not valid. Creating query failed.
    clientRequestId: fd5ab5ad-5151-4a91-95e1-156695668cfa
    serviceRequestId: fd5ab5ad-5151-4a91-95e1-156695668cfa

    Like

    1. Hi Sean,

      It looks like the single quotes. WordPress is formatting ‘ into ‘. Update the quotes to ‘.

      NY

      Like

      1. Are you saying that it is placing an apostrophe when it should be a single quote or vice versa?

        Like

    1. Hi Norm, I figured it out in the end. Not sure why this confounded me so much. I’m putting it down to ‘Monday Morning’ Thanks so much for your help. I plan on spending a lot of time on your site.

      Like

    1. OK figured it our again. Dont know if you know the answer to this though as I know you are not the author of the original list of course but I wondered if, when you assign an issue to a user, are they then supposed to be notified of being assigned?

      Like

      1. Hi Sean,

        They don’t get notified but you could set that up using Power Automate or List Rules.

        Thanks.

        NY

        Like

  9. Hi there Norm,
    Awesome post, works perfectly and helped a lot with something I’m working on so thanks for that! I’m trying to adapt it slightly though to stop counting the days once an issue is marked as ‘Closed’ and to set the Days Old field to be the difference between the Date Reported and Date Resolved, so I can get an accurate number of days it was open for.. however no matter how I try to do it, it results in it erroring in various ways (most of the time due to ISO 8601 format, even though both fields are the exact same in terms of date and time settings.) – Any ideas how it could work with what you’ve made?
    Thanks in advance.

    Like

    1. Hi Adam,

      Thanks for the great feedback, I appreciate it!

      In this very simple example I am using div, sub and ticks functions to do the calculation: div(sub(ticks(variables(‘varStartDate’)),ticks(variables(‘varEndDate’))),864000000000).

      You might need to use the formatDateTime function as well:
      div(sub(ticks(formatDateTime(variables(‘varStartDate’), ‘yyyy-MM-dd’)),ticks(formatDateTime(variables(‘varEndDate’), ‘yyyy-MM-dd’))),864000000000)

      I hope this helps and thanks for reading!

      NY

      Like

      1. Hi Norm,

        Thanks for the quick reply – I tried the noted formula, however getting ‘invalid expression’ (before or after amending it to my own variables) – double checked everything is bracketed, but seems Flow doesn’t like it. 😦

        Like

      2. Hi Adam,

        Those errors are usually caused by formatting/syntax issues. Without seeing your Flow I can’t provide much feedback.

        I like to use Power Automate but that doesn’t mean it’s the only way of doing things. You could use a SharePoint calculated column. Check out this article: https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14)#calculate-the-difference-between-two-dates

        I hope this helps.

        NY

        Like

Leave a Reply to Valerie George Cancel 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