Keep track of your activities with Microsoft Lists and Power Automate
This tutorial will help you create a custom list to keep track of what's important to you, your team or even your organization. The solution provided here is based on a very simple concept: one or more users take note of activities, thanks of a view properly formatted with JSON. This custom view groups each record by a date specified by users. However, keep in mind that it doesn't correspond to the creation date. In this way, users have maximum flexibility to keep track of events, information and tasks occurred in the past too, if required. That's why users have to insert a date manually when a new record is added. Obviously, it's always possible to use the current date as a reference (eg. if users have to track something on a daily basis).
A new record is added through the "+ New" default button. After a new record is added, users are able to edit a record "in place", thanks to an extensive use of inlineEditField property. Alternatively, they can edit a record by using the well known standard right side pane.
This template has been tested with a 1920x1080 screen resolution. All Microsoft apps icons come from this URL: https://developer.microsoft.com/en-us/fluentui#/styles/web/office-brand-icons
The image below provides a quick illustration of the main goal of this template: providing professionals with a tool to work better and in less time. Modularity and flexibility of this template allow to tailor a list to different business scenarios, use cases and people needs.
Here is a visual description of the user interface:
1) LIST CREATION: Create a new list from scratch and add the following columns:
| COLUMN NAME | COLUMN TYPE |
|---|---|
| Title | It's the default Title column where you provide a short activity description |
| STATUS | Single choice column to select the activity status. This column has the following choices: In progress, Completed, Planned, Rejected |
| PEOPLE | Person column with multiple selection enabled. You may want to use it to associate other people to a specific item. |
| DATE | Date column |
| DESCRIPTION | Multiple lines of text. Rich text disabled |
| Attachments | This is a standard column. Make sure that it's shown in "Allitems" standard view |
| ATTACHMENTS_NUMBER | Number column without thousands separator |
| FAVOURITE | Single line of text |
| Created | This is a standard column. Make sure that it's shown in "Allitems" standard view |
| Created By | This is a standard column. Make sure that it's shown in "Allitems" standard view |
| Modified | This is a standard column. Make sure that it's shown in "Allitems" standard view |
| Modified By | This is a standard column. Make sure that it's shown in "Allitems" standard view |
2) STATUS COLUMN FORMATTING: Click the column name -> Column settings -> Format this column -> Switch to advanced mode.
Copy the JSON code provided here and paste it into the column formatting box as shown in the image below:
3) LIST VIEW CREATION: Create a new list view and provide a custon name (Don't delete the default All Items view!):
4) ATTACHMENTS COLUMN: Hide the attachments column. Yes, you've got it right... you have to hide it from this custom view to make it work properly.
5) GROUP BY DATE: Go to the list settings, scroll down and click on the custom view you've just created. Scroll down again and apply the following settings:
6) JSON CODE: Copy the JSON code available here
7) LIST VIEW FORMATTING: Paste the JSON code into the view formatting box (Advanced formatting mode)
8) INSERT YOUR LIST ID INTO THE JSON CODE:
Go to row number 773 of the JSON code and paste your list ID. If you don't know where your list ID is, here is how to find it:
9) FINAL RESULT SAMPLE: Here is a sample of the final result you should get in Microsoft Teams after following the steps of this tutorial:
This additional tutorial will help you create a flow to get the number of attachments when added to a record. This process is not mandatory, given that the attachments icon is displayed when one or more files are added to a list item. Anyway, the total number of attachments could provide a more detailed info (see the image below):
HOW IT WORKS
A Power Automate flow uses a service account (or a personal account not meant for actual use of this solution) to check if one or more attachments are added to an item. This flow is triggered every time a new item is created or modified by other users.
Here is an overview of the Power Automate flow:
HOW TO AVOID INFINITE LOOP
At this point you may have noticed something weird: if a Power Automate account updates an item with the attachments number, then the trigger will run again, given that the item has been modified. This behaviour would lead to an infinite loop, where this flow would run again... again... and again just because the service account is doing what we setup!
How to prevent this unwanted behaviour?
As explained in a tutorial made by Reza Dorrani, you have to setup a trigger condition. In my example, the trigger condition checks if the item has been modified by a service account used to run the flow. If yes, the trigger condition will automatically stop the flow, then avoiding infinite loops.
WAIT A MINUTE! WHY NOT USING LENGTH OPERATOR IN JSON?
Because it doesn't work with JSON. Here is a visual proof:
Therefore, we need another way to fetch the attachments number: Power Automate.
ONE MORE THING...
