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

65 thoughts on “Microsoft Lists: Updated Issue Tracker”

  1. Pingback: Send email reminders from Microsoft Lists using Power Automate – Norm Young

  2. 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

    Reply

  3. 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

    Reply

    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

      Reply

      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

      4. 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

  4. Pingback: Send a single reminder for multiple items using Lists and Power Automate – Norm Young

  5. 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

    Reply

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

    Like

    Reply

    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

      Reply

  7. 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

    Reply

    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

      Reply

      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

  8. 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

    Reply

  10. 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

    Reply

    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

      Reply
  11. Pingback: Send reminders to Teams from Microsoft Lists using Power Automate – Norm Young

    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

      Reply

  13. 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

    Reply

    1. Hi Sean,

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

      NY

      Like

      Reply

      2. 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

      Reply

    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

      Reply

      1. Hi Sean,

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

        Thanks.

        NY

        Like

  16. Pingback: Send an Adaptive Card to Teams from Microsoft Lists using Power Automate – Norm Young

  17. 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

    Reply

    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

      Reply

      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

  18. Pingback: A first look at “Visualize the list” in Microsoft Lists – Norm Young

  19. Hi Norm. I’m new to Power Automate and I’m trying to implement your Days Old flow, calculating based on the Status. I’ve created the flow mimicking all your inputs, but for some reason it’s not working for me. No errors and the flow is running successfully, but when I try to change the status of an item and the date reported and re-run the flow, it updates like the original Days Old. Any idea what could be wrong?

    Like

    Reply

    1. Hi Jake,

      The Flow should stop calculating the days old value if the status conditions are met. Be sure to double-check the status values in the odata filter query. Also, check that the days old value does not increment the following day.

      Let me know how you make out.

      Thanks for reading!

      NY

      Like

      Reply

  20. Unsure how you got the single quotes working for the ODATA query with the
    Won’t fix
    String.

    As a note, the second code box

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

    has the special single quote character, copied that over to the initial

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

    And the flow started working yay

    Like

    Reply

  21. Hello neighbour – I’m in Hamilton. Wondering if there is a way of integrating the Teams chat function into the Issue Tracker – so that when someone opens an “issue”; I can chat with them within the context of the particular issue. I tried to use the “Comment” box, but that sent my message to everyone in the team. How can I communicate directly with the person who opened up the issue? Hint: I’m trying to use the Issue Tracker as a ticket management system.

    Like

    Reply

    1. Hey Richard,

      Thanks for reaching out neighbour! You should be able to create an “Instant cloud flow” that uses the “SharePoint for a selected item” trigger and the Microsoft Teams “Create a chat” action. This is a great concept and is on my blog to-do list.

      Thanks for reading!

      NY

      Like

      Reply

  22. Hi Norm,

    Thanks for your submission, it’s really helpful!

    I just had a query, as I have created a calculated field (DaysOld) in my Sharepoint List to calculate how many days from RequestedDate the submission is.
    I don’t have a DueDate per say so was wondering how I could create a MS Flow that would trigger once my calculated field (DaysOld) was over 30 days – instead of a DueDate due in 30 days.

    I’m fairly new to List’s and MS Flows so any help is greatly appreciated.

    Thanks,

    Ed.

    Like

    Reply
  23. Pingback: How to create a Teams chat from Microsoft Lists – Norm Young
  24. Pingback: Migrate Microsoft Lists to Dataverse for Teams environment – Norm Young

  25. Hello Norm,

    I appreciate your content. I have a question regarding your video on the Issue Tracking List template.

    I’m trying to find information on how to sync the features of the Issue Reporting App. I’m wondering if you could provide some resources on how to sync a “Report an issue” submission to auto-populate on the Issue Tracking List. I’d be interested to use this app for our company’s Incident Reporting, but I can’t seem to figure out how to link the Issue Reporting submissions to the List.

    From your Demo, it seems as though you are pulling your Issue Tracking data from an independent Excel spreadsheet to the List? How would you get the data that is entered on the Issue Reporting App to populate on the Issue Tracking List?

    Hope this request makes sense.

    Like

    Reply

  26. This worked great. Thank you so much – elevates our project tracker to hold individuals more accountable to deadlines. Thanks again.

    Like

    Reply

  27. Hi there 🙂
    First of all – Thanks for the detailed guide!
    I’m working exactly as you instructed.
    still, I’m getting the next error:

    The API ‘sharepointonline’ returned an invalid response for workflow operation ‘Get_items’ of type ‘OpenApiConnection’. Error details: ‘The API operation ‘GetItems’ is missing required property ‘body/value/17/Title’.’

    By the way, the weird thing about the flow that it works for sometime and then it stops. When I try to fix it and build the whole flow from the top I get the error.

    If you have any advise I would like to hear please 🙂

    Like

    Reply

      1. Hi Norm,

        I really appreciate your help!

        It took me a while to understand that my lists has some blank values.

        Thank you for your help!

        Dvir

        Like

  28. Hello Norm,
    I want to be able to track how long an issue stays in each status so we can track how fast we are working through the issues. Is there a way to automatically populate another column which will calculate the number of days the issue is in Progress and Completed from the date it was reported/created.

    Like

    Reply

  29. Hi Norm

    I have been looking at your page. And what a great page. 🙂
    I have a list. With build up like this.
    Info – Info – Date to expire – Reminder date on expire.
    Where “reminder date on expire” is a date on a list. But I can’t get get my flow to send an email when we hit the reminder date on expire. Can you help?

    Like

    Reply

    1. Hi Rasmus,

      Thanks for the kind words, I appreciate it!

      Sounds like an issue with the OData filter query. Try hardcoding the date as a test to make sure that Get items works and then confirm that the date formatting is working as expected.

      Thanks for reading and let me know how you make out!

      NY

      Like

      Reply

      1. Hi Norm

        That did the trick. Setting date to the right thing and then “field_xx eq ‘@{variables(‘varReminderDate’)}'” works.
        Now my problem is that I also use “send an email”
        There are no errors. But it sends no email. Tried to make a trace. And we can see that nothing happens from my outlook. Have you seen this before?

        Like

Leave a reply to Norm Young Cancel reply