A few months ago I documented the Brettro Product Numbering Standard. Since then, Microsoft introduced Lists as part of Microsoft 365. After moving my editorial calendar to a list a few weeks ago, I decided to move the list where I kept and created product numbers for Brettro creative files from an Excel spreadsheet into Microsoft Lists.
Getting Ready
One option to create a new list is to import an Excel file. Since my current tracking document is an Excel spreadsheet I thought I’d give that a try. But I needed to do a few things before importing the list.
Prepping the Excel File
Every Microsoft List includes a “Title” column. It can be renamed to anything, but it starts as “Title.” My spreadsheet didn’t have a “Title” column, so I created one and populated it with the content in my “Slug” column. Looking back, it seems a bit duplicative to have done that, but it made sense at the time.
The next thing I needed to do was define a table in my spreadsheet. This was new to me, so I had to do a web search to learn how.
With that done, I needed to save the spreadsheet to my OneDrive. You have a choice to upload it to OneDrive during the import process, but I didn’t realize that until I’d started.
Create a SharePoint Document Library
Prior to creating this list, I was saving all Brettro’s creative files in my OneDrive. In planning the list, though, I realized I could use Power Automate to create folders to store the files related to the list entry automatically. I decided to have the Automate flow do that in a SharePoint document library, so I created a document library and using the “Move” function in OneDrive, I moved all my existing files to the library. And while this was overall pretty fast, it did take some time.
Now that the prep work is done, I can create my list!
Creating the List
The import-from-Excel process is very easy. In Microsoft Lists:
- Click New list button
- Choose From Excel
- Select the Excel file and click Next
- Assign column types to the columns; in my case:
- Set “Slug” column to Title
- Set “Product Category” column to Choice
- Set “Creation Date” to Do Not Import
And with that, the list was created.
Fine-Tuning the List
With the list created and the contents of my spreadsheet imported, I needed to do some fine tuning.
- Confirmed that the “ID” column matches “Unique ID” column: all the product numbers I’d created previously had used a column in the spreadsheet called “ID.” It was important to make sure the unique ID number assigned to each entry matched that original ID column. A quick check determined that they matched.
- Add all the choices to “Product Category”: the Product Numbering Standard has 20 different categories for products. I haven’t created a product in each category yet, so the “Product Category” column choices needed to be updated to include all of them.
- Added the “Link to Folder” column: as I was preparing my Power Automate flow, I decided it would be nice to have a link in the list to the folder for the product. I added this column so that the flow could add that information once the folder was created.
Adding Some Automation
Microsoft’s Power Automate is an incredibly powerful automation tool for use in pretty much every Microsoft 365 product. I’m not going to go into detail about the Automate flow creation, except to say that the flow I created:
- Populates the “Product Workspace & Filename” column in my list. The information is used to not only name the directory where files are stored but also the source document files.
- Creates a folder in the directory structure. Using the “Product Category” column from the list, the flow can determine which directory in the directory structure to create a new folder and then create the folder with the correct filename.
- Updates the list with a link to the folder. The list entry is then updated with a link to the newly created folder so that if you’re looking at the list, you can jump to the related files very quickly.
Changing the Standard
I developed my Product Numbering Standard before version control was really built-in to places where files were saved. SharePoint document libraries do have great version control, so with the move of all my creative files to SharePoint, I’ve removed the version number requirement from both the folder structure and the product ID.
Possibilities
Microsoft Lists have a great deal of potential. I have a few other ideas in mind for lists to create, though I’d like to someday do a deeper dive into how Lists, Microsoft Planner and Microsoft To Do all complement each other.
