Normally, I write solutions to problems. This time around, I need some suggestions to get ideas on how to design a solution to a problem.
The credit department where I work accumulates/produces documents to support the credit limit they grant our customers. We have categorized them as :
- Financial Statements
- Agency Reports
- Parent Guarantees
- Other
We have defined a content type for each of these types of documents. With each of these documents, one common field is the Company Name field that describes the company that the document refers to.
The Company is unique in it’s structure. Each Company can be a Parent company or it can be the Parent to other companies. I created a Company list that had the fields Name and Parent where Parent was a lookup field type to the Company list. We currently have the list populated by about 4500 company names with the parent relationships defined.
List: Company
- Company Name, Text Field (required)
- Parent, Lookup of Company List (Company Name field) (not required)
In the document list that contains the four content types, each content type has a Company field that is a lookup to the Company List.
When we were discussing how to design this “application”, the user talked about how he would like to have a view where he could see all documents that related to a company, whether these documents applied directly to the company or one of it’s subsidiaries. Another requirement would be that the documents for one particular company would be easy to find/view.
As for volume, each company (so far at 4500) will have at minimum 1 of each document types but more than likely will contain 10 – 20 documents with a small growth rate. There is no document disposition being considered. From their standpoint, once a document goes into the system, it becomes a historical record for that company. So – we’re looking at about 45,000-90,000 documents initially and it will grow.
My original thought was that to stay within the 2000 item soft limit per view, I would create a folder in the Document Library for each Company created in the Company list. I would then have the users save their documents in the folder that corresponded to the company name. To achieve the view of the data where they could see all documents that applied to the parent and child companies, I would use the relationships defined in the Company list to create a custom view using the DataView Web Part.
So – here are my questions:
- Do I need a separate folder for each company? Am I limiting myself?
- If we just use a Company Name field using the lookup list, would we run into performance problems?
- If we do use the folder, is there a way to automate the creation of the folder when I create a new Company in the Company List.
- If we do use folders, is there a way to prevent documents from being created outside of the folders?
- Can a document inherit a property from it’s parent? (i.e. could a document get it’s company name by being saved into a specific folder with the right company name?
- (If folder) How do I prevent users from saving documents into the document library outside of a folder?
I’m sure there are more questions that I need to be asking. It has been so long since I worked in Designer or the Web interface. I’ve been living in Visual Studio for the last year. Leave comments / remarks / insults. Anything is appreciated.
6 responses to “A SharePoint Design Question”
1. No folders needed. But in the future with 2010 they may become more useful.
2. With that many docs and possible filters you will have some level of decreased perf. But i would think you could accomidate this to make it neglidgable.
3. CODE BABY! Workflow
4. You may be able to enforce this with workflow. Force people (permission wise) to only upload to a staging area and use WF to route. (in 2010 this is easier. using a “drop off library” etc.
5. You could do this with WF (not sure if SPD, but definately with a good event receiver and some WF code)
6. i dont know of a way if they have perms to the library. I am atleast 65% sure that in 2010 you may be able to….
Much of these requirements have certain requirement for an event receiver. If you have a lot of levels below company, you’d have to do a recursive loop in the data view wepbart which might slow down performance. If you’re plannine 45K documents or more, you shouldn’t do a dvwp XSLT filtering, has to be absolutely CAML filter (otherwise you’ll wait for some minutes beforre webpart is rendered).
What about following:
1. Make additional list “Company Groups”
2. In Companies list you can add a lookup to the list mentioned above (Group). You can even hide it using content types
3. Make a SPD workflow: if “Parent” field is empty (meaning it’s a parent), copy its name in “Groups list” and add a lookup to the record. If it’s a child, copy its parent’s “group” value.
That way you can filter by Group, having an excellent condition for your non XSLT filter. The problem with this scenario is that you can’t do queries from specific level down.
Other alternative is to create a content type that inherits from folder and name it Company. Then add all needed additional columns to the “company/folder” content type. And use the “Financial Statements”, “Agency Reports”, “Parent Guarantees”, “Other” as an additional column instead of folders. And then you can do CAML queries in your DVWP based on 1. Document type column, and RootFolder.
It would require some digging in, but it could lead to somewhere.
Kind regards,
Boris Gomiunik
Chris – I concur about the use of folders. I have advocated getting away from the use of folders since I was introduced to Document management. So, my first design did not use folders. Then I found out about the volume and started thinking about folders. That presented more problems so I am back to finding a solution that doesn’t require folders. We’ll see.
Thanks for the comments so far! I love collaboration!
sounds to me like your client has a training issue with SharePoint search. Custom views is one way of viewing data, but the client should have more power by setting up search queries (especially for the items that are related to a company). Those queries could be saved, alerts could be attached to the result set, etc.
There was a really good post recently talking about what you can and can’t do with folders and metadata. http://www.endusersharepoint.com/2010/01/29/sharepoint-folders-vs-metadata/comment-page-1/
In regards to enforcing where items are stored. That reeks of content organizers in sp2010. I’m thinking the setting of metadata based on the parent would be an event handler. You could save your document library as a content type and then attach the event receiver to that content type when you deploy.
hope that helps
Ahhhhh. SharePoint 2010. If I had a dollar for every time I blurted – If only we had 2010! – I’d be able to retire and never think about this again! 🙂
David:
This is a pretty classic problem. Company entity hierarchies are always problematic; every time you think you have a perfect model, someone comes up with some other off-shore entity joint owned by three parent companies which are in a blind holding entity or something. Bottom line is that you’ll only be able to deal with certain circumstances. The most common is certainly the parent-child relationship.
I think that you’ll be fine on the display side with a DVWP. You can build recursive templates fairly easily that “crawl” the tree to the top node. However, Boris is right that the volume of documents will impose performance issues at some point, probably sooner rather than later. I think the real question back to you is what the use cases are and what are reasonable SLAs? If your users are going to look at the company structures once or twice a day and it takes 30 seconds to come up, that’s very different than hundreds of views an hour.
The “2000 item limit”, like Mel Brooks’ 2000 year old man, is fictional. I’ve seen lists with a few hundred items crawl and items with tens of thousands sail. There are lots of variables in all of this. I’m in the middle of a series over at EndUserSharePoint.com which talks about a real world scenario where I had a similar set of challenges, focused on software development projects. In the next installment (if I ever get it written), I’m going to talk about how I built a customized bulk upload page which saved documents to folders based on their metadata using the SharePoint Web Services and my jQuery Library for SharePoint Web Services. So whatever you decide about the optimal document storage approach, it’s do-able, and it’s potentially do-able with no server-side code.
M.