Microsoft Lists: It’s bigger than you think

When I initially stumbled across the Microsoft Lists app in our tenant I dismissed it slightly not knowing what use it would be (we already have various task lists, why would I use another list app?). But while Microsoft Lists only emerged as a standalone product a couple of years ago, lists have been around in SharePoint a lot longer than that. Document libraries in SharePoint are even based on lists so the list functionality has technically been around since SharePoint started and as such has been quietly updated in the background alongside SharePoint this whole time and is much more powerful than you might initially think.

I recently automated a process to store Microsoft Form responses into Microsoft Lists using Power Automate to streamline an overly complicated process for recording purchases and expenses and below is a summary of how it currently works in case anyone finds the information useful.

The organisation I work for, like most organisations, makes purchases through both business accounts and also via a number of company credit cards held by various staff and our finance department had a ‘system’ for tracking the purchases and expenses staff made using Excel spreadsheets (I am using the word system here loosely!).

This system involved staff having to go to a particular spreadsheet (there were two saved in different locations – one for credit card expenses and one for business account purchases) and then fill in the details of their purchase under the tab for the correct month. This was further complicated by the need to include the correct budget and expenditure codes in the entry, with a list of these codes found in a third document. They then needed to obtain the receipt for the purchase and save it in a ‘receipts’ folder (saved in another location) in another folder with the name of the month the purchase was made (or based on some arbitrary period for reasons I can’t fathom) and rename the document using the template ‘Year.Month.Date-Company_Name.Transaction_Amount’ (e.g. 2022.8.10-Amazon-125.99.pdf). In total (including the instructions document) this system involved four separate documents, one stored in a different location and with each containing multiple tabs, and also a folder containing multiple folders with names such as ‘4th-26th July 2022’ and ‘7th June to 1st July’…

I could see a number of problems with this system as soon as I started using it, and especially so for some of our slightly less computer-literate staff members:

1. It was all too easy to fill out the wrong spreadsheet AND/OR the wrong tab in the spreadsheet which would surely cause data to get lost or be overlooked.
2. All staff filling in the spreadsheets could see (and potentially accidentally edit or delete) all other entries on the spreadsheet which is a recipe for disaster!
3. The instructions for following this system, including the locations of the spreadsheets and folders, were on an email sent out to all staff and a separate document which meant there would be staff who don’t make purchases often who would not know where to look and would need to ask each time.
4. Staff saving receipts in the wrong folders and/or titling them incorrectly would further add to the confusion of finding the correct receipt relating to a particular purchase.
5. Both spreadsheets were identical and as one of the benefits of a spreadsheet is the ability to filter information by tables then there seemed to be no reason to have two identical spreadsheets including multiple tabes saved in two different locations when one filterable spreadsheet would have done the job.

And all these issues would be on the users end at the time of recording their purchases. Our finance manager would then still have to go through the process of collating all the purchases and expenses from these various locations (assuming they had been stored correctly) and potentially also have to go to the trouble of correcting any errors before being able to use the information. I decided to offer up a cleaner solution.

Microsoft Forms is a really quick and simple method of collecting data and up until recently I had been content in the data collected being stored or exported to an Excel document. That was until I discovered the power of Microsoft (SharePoint) Lists. But let’s start off simple.

With Forms I could quickly create an online form and make it accessible to everyone in the organisation (as a link on an intranet or a tab in an appropriate Teams channel everyone has access to for example). I created a simple form which starts off with the question ‘Was the purchase made on a Business Account or with a company credit card?’. Then, if ‘credit card’ is chosen, the second question (using forms ‘branching’ feature) is a choice of which credit card was used (from a selection of four staff credit cards). All the remaining questions are the same for both business account and credit card purchases so negating the need for two separate documents to store the data as all data could now be stored in one place and categorised based on account purchase or credit card purchase, and then further categorised by the credit card used. I was also able to remove some of the fields that were in the original spreadsheets as they became unnecessary once using Microsoft Forms (for example the staff member is automatically recorded on form submission so there was no need now for them to fill in their name anywhere).

The list of form fields used is below:

• Was the purchase made on a Business Account or with a company credit card? – radio buttons choice field
• Which credit card? – radio buttons choice field (this field only shows up if ‘credit card’ is selected in the first question)
• Date of transaction – date picker field
• Company – text field
• Description – long text field
• Transaction value – text field
• Other currency – text field (added as there was a requirement to separate purchases made in a different currency)
• VAT – text field
• Purchase approved by – text field
• Budget code – dropdown list of budget codes
• Expenditure code – dropdown list of expenditure codes
• Notes – long text field
• Receipts – file upload field (restricted to Word, Excel, PDF and image files)

At the top of the form, below the header, I added the purchasing and expenses instructions and, as the form contained dropdown lists of both the budget and expenditure codes, this made both the instructions document and the budget codes documents unnecessary. Four documents had become one! Now, onto storing the data…

A Microsoft (SharePoint) list is essentially a collection of data that has some kind of structure applied to it such as a table, spreadsheet or simple database. SharePoint Lists are a much more powerful and flexible way of storing the data from our form and presenting it for our finance manager than any Excel document.

I started by creating a Microsoft List using the same columns as the purchases/expenses form (slightly rearranged into a more logical order for viewing the data):

• Description – text column
• Type of Purchase – choice column (‘Business Account’ & ‘Credit Card’)
• Credit Card Used – choice column
• Company – text column
• Date – date and time column
• Receipt? – choice column (Yes and No)
• Transaction Value – number column (formatted to two decimal places and with a currency symbol)
• Other Currency – number column
• VAT – number column
• Purchased By – text column
• Approved By – text column
• Budget Code – choice column
• Expenditure Code – choice column
• Notes – text column

Note that the columns that are created as ‘choice’ columns will be easily filterable later.

I also added a few additional columns which will be used later:

• Year and Month
• Receipts
• Follow Up
• Attachments
• ID

I added formatting to some of the choice columns (choice pills background colours for the ‘Type of Purchase’, ‘Credit Card’ and ‘Budget Code’ columns to make it easier to differentiate between them) and I added conditional formatting to a few columns (the ‘Receipt?’ column has a green or red background for ‘yes’ and ‘no’, and the ‘Transaction Value’ column has a different coloured background for purchases over a certain amount).

The ‘Year and Month’ column was added by going into the List Settings screen in order to add a column with a type of ‘calculation’. The formula used for the calculation to display the date in a readable format for us is:

=TEXT(Date,"yyyy-mm (mmmm)")

This enabled me to then edit the ‘view’ settings to enable ‘grouping’ and then group by the ‘Year and Month’ column meaning the entire list would display as collapsible groups with the title of the year and month such as ‘Year and Month: 2022-08 (August)’ which further helps the finance manager to view an organised list of purchases and expenses.

The ‘Receipts’ column also has some added JSON code in it (by switching to advanced mode when formatting the column) which is used later to generate a link to the uploaded receipts folder and a filter applied based on their ID to display only the receipts uploaded for that purchase. The JSON code is below (just add in your tenant URL and edit the path/name for the list and the filter parameters as appropriate):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "debugMode": true,
  "elmType": "div",
  "children": [
    {
      "elmType": "a",
      "txtContent": "? View Documents",
      "style": {
        "text-decoration": "underline",
        "color": "inherit",
        "font-size": "inherit",
        "font-weight": "inherit"
      },
      "attributes": {
        "class": "sp-field-quickAction sp-field-iconRightAligned",
        "color": "blue",
        "target": "_blank",
        "href": "='https://TENANT_URL/sites/Finance-PurchasingandExpenses/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsites%2FFinance-PurchasingandExpenses%2FShared%20Documents%2FPurchasing%20and%20Expenses%2FCredit%20Card%20Transactions&FilterField1=ReceiptsID&FilterValue1=' + [$ID] + '&FilterType1=Number'"
      }
    }
  ]
}

This creates a link in the ‘Receipts’ column which goes to the SharePoint site where the uploaded receipts have been stored and automatically applies filters based on the ID so that only the relevant receipt/s are shown.

Now that both the form and list have been setup the only thing left is a method of getting those form responses copied over and stored into the list. Power Automate is that method! The first step is to create a flow which is triggered when a new form response is submitted. My flow steps are detailed below.

The flow is triggered when a new response is submitted to the form (if you can’t find the form in the dropdown you can get the form ID for entering into this trigger from the actual forms URL). I then added a ‘Get response details’ step to get the responses from that form submission and also (for use later in the list to display the users ‘friendly’ name) include a ‘Get user profile’ step on the form responders email address.

The next steps I initialised some string variables:

• varFiles – with an initial value of the ‘receipt’ uploads section from the form.

• transactionValue – with an initial value of the transaction amount from the form.
• transactionValueTemp – used later when removing invalid characters.
• vat – with an initial value of the vat amount from the form.
• vatTemp – used later when removing invalid characters.

I then added an array variable to hold some unwanted characters to be stripped out of the value fields (in case anyone added a currency symbol when typing in the values).

This array variable held the following expression which creates the array of characters including some currency symbols as well as a blank space.

createArray('£','€','$',' ')

I then added an ‘apply to each’ on the array which sets the variables for the transaction and vat values.

…using the following expression for the transactionValueTemp variable:

replace(variables('transactionValue'), item(), '')

and this expression for the vatTemp variable:

replace(variables('vat'), item(), '')

Then the next two steps in the ‘apply to each’ step assign the original transactionValue and vat variables with the values added into the temp variables.

Now because the ‘Transaction Value’ and ‘VAT’ fields from the form are of type text, and the corresponding columns in the list are of type number, they can’t be selected directly when mapping them in the ‘create item’ step coming up. They also can’t be stored in integer arrays to begin with as they may contain fractions. They need to be first converted to numbers using a float function in a compose step, and then the results from that compose step can be mapped to the appropriate list columns.

float(variables('transactionValue'))

But because the second compose step would cause the flow to fail if there was nothing in the VAT field (this was an optional field in the form) I put that one inside a condition which first checks whether the json of the VAT response from the form was not equal to null (ie it does contain information) using the following expressions in the condition:

json(outputs('Get_response_details')?['body/id_of_the_field_goes_here'])

Is not equal to…

null

and if it is not equal to null then another compose step is added to convert that from a string into a number:

float(variables('vat'))

The problem now was that because the compose step for the VAT was inside a condition it meant I couldn’t use the output directly in the ‘create item’ step coming up because it would only exist if the previous steps had been true. So I then added a third ‘compose’ step directly after the condition which would either hold the data from the previous compose step or nothing. I was then able to use the output of this third ‘compose’ step in the ‘create item’ step for VAT.

After this I then added another condition to check whether the form included any attachments using a similar method as above (check whether the JSON output of the ‘Receipts’ field is not equal to null).

Inside this condition a step is then added to create an item in SharePoint (using the previously created SharePoint List) and mapping the various form fields to the appropriate columns in the SharePoint List.

The ‘Transactions Value’ and ‘VAT’ columns are mapped to from the appropriate ‘outputs’ from the previous step which converted the string value to a number (the ‘Transactions Value’ column gets it’s info from the first compose step while the ‘VAT’ column gets it’s info from the third compose step), and the ‘Purchased By’ column is mapped to the ‘display name’ property from the previous ‘get user profile’ step.

(I also added exactly the same ‘create item’ step to the ‘If no’ part of the condition with the only difference being that the ‘Receipt?’ value was set to ‘No’ instead).

Next I added an ‘apply to each’ step to the bottom of the ‘If yes’ part of the condition to process all uploaded attachments (in case multiple receipts were uploaded) with the steps ‘Get file content’ to get the content of the uploaded file by it’s ID, and then an ‘Add attachment’ step which adds the files as attachments to the relevant list item. Then two more steps ‘Create file’ and ‘Update file properties’ which also creates a copy of the attachments in a previously created folder in SharePoint and then updates the properties of the file to add an ID number to the file (which is provided by the ID column in the SharePoint list) and can then be referenced in the additional JSON code for the list when providing the links to the documents and filtering them by ID.

Both the ‘add attachment’ and ‘create file’ steps I also set to name the file using expressions and form content so that the generated files would be named appropriately for the finance manager (e.g. Date-Company-TransactionValue-Name). The ‘Name’ part of the filename was added to the end so that it also includes the documents file extension and I also added a guid() expression to create a randomly generated number inside the filename just in case of duplicate filenames.

There are still a number of ways this process could be improved (I would like to work out a way of having the file extensions included when creating the attachments which doesn’t involve having to include the original filename in the file creation) or added to (it could include email notifications or even an ‘approvals’ process which is not currently needed) but for now our finance department and staff have a much more streamlined process. Staff now simply need to go to the one form in Teams which has instructions at the top, fill it in with the appropriate budget codes provided as a drop down list, upload their receipt(s) as an attachment and then click submit. Power Automate then does all the work of creating the list item, adding attachments and saving receipts in the appropriate place with an appropriate filename. Then all the data in Microsoft Lists can be viewed by month or sorted/filtered in whichever way our finance manager wants to view it. We can create ‘views’ based on different filters or by calendar or board and all expenses can be filtered by specific credit cards. Additional columns can be added to the list at any time if it’s decided that we need more ways to categorise things and to top it all off it’s all nicely colour-coded too!

The full flow is below in case anyone wanted an overview…