Duplicates in SharePoint Datasheet view / Export to Spreadsheet (Excel)
Recently I encountered a bug in SharePoint where sometimes duplicate items appear when viewing items in Datasheet view in SharePoint. This also happens when using the Export to Spreadsheet (Excel). If duplicates appear in Datasheet view, they will also appear during Export to Spreadsheet and vice versa.
This happens only under specific circumstances. The base list must have a required lookup column/field that is multi-value. The view must not include this column.
A required, multi-value lookup column is created to demonstrate the problem:
An example of the list data in standard view, showing the “Region” column:
I created a new view without the “Region” column to show the duplicates bug. Standard view appears normal:
Same view, but when switched to Datasheet view, duplicate records show:
Same view in Export to Spreadsheet shows duplicates:
If the column is not required, everything works fine. If the column exists in the view, everything is fine. It is only when the required column is omitted from the view that you see duplicates.
If the column is included from the view, everything is normal during Edit in Datasheet mode:
Duplicates are now gone in Export to Spreadsheet as well:
There seems to be no fix for this problem. The work-around is, if you have a required multi-value lookup column, to always add the column to views, especially datasheet views. If a user creates their own view without the column, they may run into this problem. User education is the only solution in this case.
Although this seems like a rare circumstance, it does appear that multiple SharePoint users are encountering this problem. This affects both WSS 3.0 and MOSS 2007.
More on SharePoint “User not found” error with Reporting Services
Following up my initial post on the SharePoint “User not found error with Reporting Services in integrated mode, I wanted to post another reason for the “User not found” error.
If, somewhere in the site author tree, you have user accounts that no longer exist, you will encounter the “User not found” error while viewing the report and browsing the list of shared data sources.
I found two good blog articles about this issue as well as a solution here:
- Wherefore Art Thou Author?
- Resetting the Author on a SharePoint site, or Wherefore Art Thou Author Redux
I modified the code a bit to just show the site author without resetting it. I also changed the code to explicitly dispose of the SharePoint objects.
The utility takes two parameters, the first is the site URL. If you only pass one parameter, it looks up the site author. If you pass a second parameter (in the format of DOMAIN\USER), the site author will be reset to the value you supply.
Group by month, year, or anything else using a calculated column in SharePoint
I found an excellent example of how to group by month in a blog recently. It does require using SharePoint Designer, however. An alternative to grouping this way is to create a calculated column and group on that.
The end result looks something like this:
:
Basically, you just create a new calculated column in the list. In this case, I called it “Published Month”, type of “string”, with a value of =TEXT(Published,”yyyy-MM”)
After creating the calculated column, just set the “Grouping” for the list. One caveat is that your calculated column is going to be sorted on as well, so it must be in the proper order. For example, the format string “yyyy-MMM”, which produces “2008-Apr”, sorts alphabetically, instead of in date order.
SharePoint calculated fields reference
If you ever use calculated fields (a.k.a. calculated columns) in SharePoint 2007 (WSS 3.0), there is an excellent set of examples from Microsoft WSS 2.0. There is also a newer set of examples for WSS 3.0, but they haven’t deprecated any of the formulas, so everything still applies.
Update: I also found a basic SharePoint calculated field reference, if examples aren’t your thing.