SharePoint Folders and the Challenge of Metadata

Introduction

Recently, I was working with a client that had a specific need for SharePoint folders. The client was going to be moving large numbers of records (mostly PDF files) from an old records management system they had on-premises to SharePoint Online (SPO), and the way that the client’s records management system kept some types of metadata associated with the records it applied to was by placing the records in a particular “container;” i.e., the container itself had certain properties, and the placement of records within that container would, by extension, mean that those records would possess the same properties.

“Container,” in this case, is really synonymous with “file folder” for the purposes of this discussion. Placement of a file within a folder arbitrarily possessing the color “blue,” for instance, means that the file itself should also possess the color blue by extension, as shown on the left. All the files in the blue folder are therefore blue themselves.

Yeah, But Metadata?

Bear with me, I’m getting there.

These days, everyone who owns a computer has at least a working understanding or grasp of how files and folders interact on their desktop or laptop computer. The Windows File System does a dandy job of modelling the virtual constructs of these real-world items. Files go into folders – easy enough.

To continue with the file folder analogy: it’s not at all uncommon for folks to write on folders to add information that helps them identify and categorize the papers and documents contained within folder. So, it’s perfectly reasonable to expect that people would want to write on folders (in the virtual sense) when creating them in SharePoint to help identify the files placed within.

And that’s precisely where things get a little challenging.

What’s The Problem?

Although Windows folders can track metadata or properties in a limited sense, accessing and interacting with that metadata can be hit or miss. For instance, my Camera Roll folder on the right has metadata indicating when it was created. It probably has some additional properties over on the Customize tab (I’m guessing) that may be accessible, but they may be read-only. And if I wanted to add my own arbitrary properties to describe this folder, such as the previous “blue” designation of color, I’d be hard-pressed to do so.

Brief aside: we almost had files and folders with genuine metadata back in the Windows Vista days with WinFS, or Windows Future Storage. Unfortunately, WinFS never saw the light of day ... which is tragic, because I think it would have helped tremendously with the management, storage, and classification of files.

If you’ve ever tried to work with folders in SharePoint document libraries, you already know that the situation isn’t much better. SharePoint document library folders are not really friendly from a metadata perspective, either. And I want to be clear about something here: I’m using the term “metadata” not in the narrow, managed metadata sense, but rather in the broader sense; i.e., any additional (field) data that could be applied to a folder.

But SharePoint Folders Support Metadata, Right?

Many folks who ask that question feel like SharePoint folders should support metadata without having any sense of certainty about that feeling … and that’s a horrible state to find yourself in. So let’s clear things up a bit.

“Yes,” SharePoint folders do support metadata/field data – and have all along. We just have to jump through some hoops to understand a few things up-front in order to effectively work with that metadata. In particular, we’ve been operating with a particular working understanding of files and folders – namely, that files go into folders.

When it comes to SharePoint, this model isn’t entirely accurate. Allow me to take a shot at explaining something that is probably more complicated than the average SharePoint user realizes.

Elaborating Further (a.k.a., “Words Alone Won’t Work”)

When our kids get flustered and stop speaking English or just start making noise/emoting, we’ll sometimes yell at them to “use your words.” I was thinking about how I might explain the relationships that exist between properties, list items, files, and folders in SharePoint … and I gave up. This was a case (to me) where a picture was worth a thousand words.

I thought “a class diagram would really help here” and then set about trying to find an appropriate diagram on the Internet that would represent what I was trying to illustrate. After about 20 minutes of searching, following links, reviewing some (not-so-great) UML diagrams, and ultimately coming up empty-handed, I put this UML class diagram together:

UML Class Diagram Illustrating SharePoint List Items (SPListItem Objects) and Key Relationships

If you don’t “speak” UML, I’ll try to highlight the key classes and relevant relationships for you:

1. Every SPList has a collection of zero or more SPListItem objects. In plain English: every SharePoint list can possess any number (including zero) of list items. Probably no surprise there.
2. SPListItem objects are simple to grasp in concept, but they are remarkably complex in design and implementation. One feature that is fairly common among many SharePoint object types is a collection of arbitrary properties (or a “property bag”), and an SPListItem certainly adheres to this. These properties are accessed, somewhat unsurprisingly, through the Properties property of the SPListItem which is implemented as a .NET Hashtable object. The Properties collection can contain any number (including zero) of key/value pairs.
3. Remember the part about folders containing files – and my remark about that not being entirely accurate? Well you might be able to see why that is from the class diagram. Technically, an SPListItem can be associated with an SPFolder (through the Folder property of the SPListItem) and an SPFile (through the File property of the list item). In a self-referencing hierarchical structure of nodes and leaves, SPFile and SPFolder objects can reference other SPFile and SPFolder objects – making traversal of a folder hierarchy or file hierarchy (respectively) possible. And, like an SPListItem and many other SharePoint object types, files and folders have an exposed Properties property that goes to – you guessed it – that Hashtable containing the same key/value pairs as our list item.
4. Up until now, it probably wasn’t too hard to follow along. But in an effort to make nearly “everything” accessible from everywhere and every type in SharePoint, Microsoft sort of made the SharePoint object model sort of … dirty. Case in point: the Fields property or collection associated with an SPListItem. This is a collection of SPField objects which are, in effect, key/value pairs on steroids. An SPField has numerous other properties and object references that I left out of my diagram to avoid confusing things further. Most SharePoint practitioners are familiar with SharePoint Fields, and they take many forms: Columns, Site Columns, etc. So a lot of that SharePoint list item data actually resides in the Fields collection. You may, however, be noticing and wondering about the relationship between SPField and the Properties Hashtable. I’ll get to that …
5. An SPFolder object also has access to the SPField items associated with its particular SPListItem through the folder’s ListItemAllFields collection. Worth noting is that this property doesn’t have backing support within the SPFolder object but is actually an alias/reference back to the parent list item and it’s Fields collection:

1. And as if to confuse matters worse, individual SPField objects can be accessed through the Properties Hashtable; or rather, the key/value data in an SPField can be accessed (both set and retrieved) as “members” of the Properties collection.
   
   Getting at data associated with a specific SPField in this manner exposes fewer options (because we can’t access the full richness of the SPField‘s properties and methods), but it does give us a quick and relatively easy way to work with fields as properties.

Where’d You Find That? (Alternatively, How To Confirm This On Your Own)

If you’re familiar with my work or my writing, you know that I generally prefer to find (or discover) things on my own. One type of work I do that I derive great enjoyment from is what I like to call “digital spelunking.” In essence, I dive into files to figure out what they do, how they work, or sometimes even why they produce certain effects. I’ve extracted database connection strings and (unfortunately placed) credentials that were stored within files. Organizations that have suddenly found themselves without their developer have hired me to get inside their developer’s SharePoint solution packages and .NET assemblies to extract critical information – a service I offer over on Collab365 MicroJobs, if you’re interested.

To put the UML class diagram together, I opened up my copy of .NET Reflector and used it to start poking around a variety of SharePoint assemblies.

Reflector is a great product, but it isn’t free. There are free alternatives (I’ll point you to a Scott Hanselman blog post if you’re interested in disassembling on your own), and they make the job of understanding how things are connected much easier – if you speak the language.

So, we were talking about folders in SharePoint …

PowerShell Is Your Friend

I like to say that “PowerShell is like methadone for developers.” If I want to get going and start doing some C# development, but I can’t for some reason, then writing some PowerShell script will satisfy my need to code … but it’s not a replacement.

As it turns out, the SharePoint PnP (Patterns and Practices) crew put together a number of PowerShell cmdlets for working with files and folders, and they do a fantastic job of giving PowerShell script writers the tools they need to get things done in SharePoint farms, both on-premises and in the cloud. But they chose not to expose metadata and properties with their cmdlets, and I’m willing to hazard a guess as to why. Since you’re now familiar with the SPListItem type and the class diagram I presented earlier, I’ll wager you are too.

Well, the nice thing about PowerShell is that it gives us full access to the richness of the .NET Framework. The SPFolder objects that are returned from using cmdlets like Get-PnPFolder and Resolve-PnPFolder still have all the methods and properties you would expect them to have. So accessing and manipulating folder metadata isn’t all that hard to do. You just need write PowerShell with a bit of a .NET developer’s eye.

Everything Is Awesome!

In my company’s SharePoint Online tenant, I maintain a demo site I created for the express purpose of metadata demonstrations. Behold!

Don’t you wish you had a site as cool as this?

I built the site back in 2014 (in the earlier days of SharePoint Online), so no, it doesn’t have any of the modern goodness we’ve come to expect from an SPO site. I’ve often wondered if the Lego Group uses SharePoint. Maybe Andrew Connell knows. He has really diversified his portfolio in terms of technology, but when I think “legos” and “SharePoint,” AC is the first guy who comes to mind. He goes pretty bonkers over his legos.

Anyway, the Lego site will be the perfect testbed for some SharePoint folder action. Let’s say I need to create a set of folders in the Unorganized Bricks document library:

The “busted hotness” that is my Lego demo site

We need to change the way we’re looking at the site to make our metadata scenario more visible, so I switched the view and engaged Quick Edit mode:

Editing a folder’s metadata in Quick Edit mode

Quick Edit mode is one of only a few ways that folder metadata can be accessed. It’s a good thing that Microsoft dumped all the old ActiveX controls (remember those?) that SharePoint previously used for things like Quick Edit and went to HTML+JavaScript implementations. The current Quick Edit implementation has actually been getting some new(er) capabilities, as well. Most of them require modern lists and libraries, though, so I’m not sure my stallwart lego libraries will demonstrate any of that.

The Script

I mentioned a (CreateSPFolders.ps1) script to create folders in our document library, so here’s one that will ingest a comma-separated value (CSV) file to create folders and assign metadata value – assuming the fields/columns specified within the CSV already exist within the document library. If the fields don’t already exist, there are a separate set of steps you’d need to undertake to get those ready – and I won’t go into those here (this blog post is getting long enough already).

The CSV file that the script looks for should be named folders.csv, and it should reside within the same file location as the script itself. The CSV file format is pretty flexible; the first row contains column/field names with the exception of the first column. The first column contains the relative path of the folder that should be created in the document library. “Relative,” in this case, has a point of reference beginning at the root of the document library. Additional CSV columns beyond the first will be interpreted as metadata/field data for assignment, with the column header being matched to the SharePoint document library’s field names. Sample folders.csv:

Path,Piece Count,My Design?
/For Car,360,Yes
/For Rocketship,1500,No
/For Bulldozer,980,No

In the case of my document library, there are only a couple of metadata fields that I can populate: Piece Count and My Design? Running the CreateSPFolders.ps1 script yields the following:

The Test document library following CreateSPFolders.ps1 run with the previously shown folders.csv

So, What Happened Here?

There are a number of script elements worth pointing out, as well as some effects and behaviors that are worth highlighting.

You may have noticed that although the folders got created and the Piece Count field was successfully populated for each folder, our My Design? field did not get populated. This happened because the My Design? field is a boolean field (Yes/No in SharePoint), and we’re putting values in as strings/straight text:

$folderProps[$mappedProp] = $propVal

No data type coercion is attempted. If we wanted the value to “stick,” we’d have had to insert a PowerShell $true or $false. Obviously, we’d need additional logic to attempt data type interpretations of the values in the folders.csv file, or apply what we know of the field from our preprocessing of the document library’s fields collection. I didn’t go into that here to keep things as simple as I could.

The names of the fields in the document library’s Properties collection are case-sensitive, so when you are assigning field values by adding them to the Dictionary, you must use the case of the fieldname you want to affect. If you don’t, nothing will stop you from adding that field value to the Properties collection, but you won’t be able to access the value or view it in SharePoint. For example:

Properties collection of the Test document library. VSCode debug data.

If I had assigned “piece count” instead of “Piece Count” (notice that case difference), it would have appeared in the collection but not in SharePoint.

Note, too, that My Design? was actually added to the Dictionary and case was observed. But since the value was added as straight text and not as a boolean data type, it does not appear in the SharePoint Quick Edit View:

Another watch-out with field values on folders that sometimes causes some confusion is that after changes are made and completed on a specific folder, it’s best to call the Update() method on the folder and then ExecuteQuery() on the folder’s Context. Doing so will ensure the changes are propagated back to SPO and applied to the underlying document library:

#Propagate the changes back to SPO to ensure they stick
$workingFolder.Update() 
$ctx.ExecuteQuery()

If there’s ever any question regarding whether or not there are changes that need to go back to SPO, the HasPendingRequest flag of the Context object can be consulted and used to determine whether or not a server round trip should be made.

Conclusion

This post started off as something simple but quickly grew into something more. At the very least, I hope some of you find it handy as a reference and a script source. Let me know your thoughts/feedback!

References

1. ZDNet: Bill Gates’ Biggest Microsoft Product Regret: WinFS
2. UML-ClassDiagrams.org: UML Class and Object Diagrams Overview
3. Microsoft Docs: SPList Class
4. Microsoft Docs: SPListItem Class
5. Microsoft Docs: SPListItem.Properties Property
6. Microsoft Docs: SPFolder Class
7. Microsoft Docs: SPFile Class
8. Microsoft Docs: SPField Class
9. Microsoft Docs: SPFolder.Properties Property
10. Microsoft Docs: Folder.ListItemAllFields Property
11. Microsoft Docs: SPFieldCollection Class
12. Redgate Software: .NET Reflector
13. Collab365 MicroJobs: Solution Package Dissection
14. Redgate Software: .NET Reflector
15. Scott Hanselman: What’s better than ILDasm? ILSpy and dnSpy are tools to Decompile .NET Code
16. Microsoft Docs: PnP PowerShell Overview
17. Microsoft Docs: SharePoint Files and Folders cmdlets
18. Github: PnP-PowerShell Contributors
19. Companies: Lego Group
20. Site: Andrew Connell
21. Wikipedia: ActiveX
22. Microsoft Tech Community: Enhanced Quick Edit for SharePoint Lists and Libraries
23. How-To Geek: What is a CSV File, and How Do I Open It?
24. Powershell Script: CreateSPFolders.ps1
25. Microsoft Docs: SPFolder.Properties Property
26. Microsoft Docs: SPFolder.Update Method
27. Microsoft Docs: Dictionary<TKey, TValue> Class
28. Microsoft Docs: ClientRuntimeContext.ExecuteQuery method
29. Microsoft Docs: ClientRuntimeContext.HasPendingRequest property
30. Microsoft Docs: ClientContext class