Dossiers: Use SharePoint Lists Like a Database

I’ve been at skybow for four months at the time of this writing, and I want to pass on a few observations while I still have a fresh perspective – starting with how much I love dossiers. Maybe it’s because I’m a North American, but when I hear/see the word “dossier” I think about something passed between guys in trench coats in a 1960s-era spy thriller. Well, they’re not that. They’re better.

‍

I’ll tell you what dossiers are in just a moment. I first want to highlight the reason we created them in the first place:

‍

We have a love/hate relationship with SharePoint Lists, don’t we?

SharePoint lists are incredibly useful. You can quickly create what amount to database tables and start working with them in moments. I love them because:

• Unlike a “real” database like we’d create in SQL Server/SQL Azure or MySQL, we don’t need any special privileges from a DBA to provision a new schema or anything.

• Unlike Common Data Services, we don’t have to pay extra to use them.
• Unlike both of the above options, we don’t need to coordinate our SharePoint-located app assets with our data that lives “somewhere else.” I can backup/restore/migrate the site and its data without doing work to keep everything packaged and in sync.
• You get a ready-to-use user interface for your data, and it’s extensible.

‍

That said, it’s far from perfect. I’m particularly not fond of:

• The need to ensure that not too many rows are returned when viewing a list. Admittedly, the SharePoint team has been working to make this less of an issue, but it still remains a problem.
• There’s no real way to join tables. You have to either denormalize your data model and do crazy things for master-detail relationships, or you’re going to have to do redundant data entry.
• Related to the above, search in SharePoint is focused on individual list items, not sets of items working together. You can’t use search, even with property mapping in place, to query receipts for parking fees over $50 claimed by people in the Boston office, because the amount is a line item field and the office is a master item field.

‍

There are other issues, but the above three work to make using SharePoint lists for data a compromised experience. And skybow is about reducing the need to compromise.

‍

skybow Dossier Technology to the Rescue

How about we make SharePoint lists a little bit smarter? We could:

1. Add a search criteria web part to a list view so you can type in terms and retrieve a subset of the list – quickly – so you don’t have to page through an endless number of rows to find what you want.
2. Lets you create one list as a parent/master list and several child/detail lists linked to it. We’d create the lookup fields automatically.
3. Have fields in the master list contain aggregate calculations from the linked detail lists. Like the count of linked documents in a library. Or the sum of amounts of the line items in an expense report. Those aggregations would be updated by a background service whenever detail data changed; you don’t write any of the link/query logic.
4. Copy the values of some of the master item fields to detail item fields. It would make browsing a detail list easier. More importantly, SharePoint’s search index gatherer can index the row and see the master and detail data combined. As with aggregations, that inherited metadata would be automatically maintained by a background service.
5. Provide a way to implement calculated fields for any standard data type, and allow for much richer calculations than what’s available by default. And again, the calculations are performed/saved automatically by a background service.
6. Have this extra metadata easily inspectable by other software assets.

‍

Item 1 refers to the skybow List View Query web part, but items 2-6 make up what we call a dossier. It lets you grab a set of data all at once and view/edit it together. Like a case file. Or a …wait for it… dossier.

‍

Dossiers treat a set of SharePoint lists like a hierarchical database. When you can do that, you almost never need to store your data outside of the SharePoint site that uses it. You get more functionality and you save money.

‍

How skybow Solution Studio uses dossiers

Typically, in Solution Studio, the first thing you do is model your data as a dossier made up of SharePoint lists that work together as a set. Not every list has to be part of a dossier; we handle standalone lists, too.

‍

We’ll use a dossier’s metadata to auto-generate Rich Forms that let you browse/edit a master list item and all linked detail items and documents from a single form. It’s typically done with tabbed pages, but you can change it to a long page with embedded tables if that’s what you want. The best part is that the form doesn’t have to do as much work as you’d think; the dossier background services handle the inheritance, calculations, and aggregations.

‍

Solution Studio contains a lot of functionality, but almost every solution starts with – and revolves around – one or more dossiers.

‍

Yes, language geeks, we could have used a different term. Get over it.

Odd names are actually a time-honored tradition. Heck, I worked for years at a place whose software had a feature with a name that implied that laziness was good.

‍

Much of the software industry uses the term “file” to refer to a document – even though in the real world a file is a folder that contains documents. Imperfect terms are everywhere.

‍

If we were going to be as close to the world of paper as possible, we’d have used the term dossier, or case file, to refer to a single master item together with all detail items linked to it. The group of lists that are linked together and manage together could have been called a “List Set,” or “List Group,” or “Schema,” or even “List Hierarchy,” I suppose. But that didn’t happen.

‍

“Dossier” is the brand name for a skybow feature. And it’s so darned useful none of us should care what it’s called – we should just use it.

‍

If you'd like to see more (and you should), heres's a video that goes into the use of skybow Solution Studio dossiers in greater detail.

‍

‍