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.
Thanks! Great explanation of this puzzling issue I just encountered, I feel like you saved me hours of troubleshooting. I owe you a beer.
AAAAHHH Thanks! This was driving me nuts! Unfortunately I think my problem is not so easily resolved.
I have required fields that are lookup fields. When these fields are added to the datasheet view, I can’t edit any of the items due to some invalid text injected into the view for this particular column!
Irritated.
Turns out my statement isn’t fully true. It seems this issue only occurs with certain types of lookup fields. When I drop them down they list each item as #string#blahblah.. this is what’s throwing it off. I think it’s due to this particular look up field linking to a calculated field.
Yep, very helpful, thanks very much. If Brettski is buying the first beer, I’ll get the next round.
I’ll second Brettski. I encountered this problem while using a column of one list as a lookup column in a second. If this first list had other required columns that allowed multiple values, the column value being looked up would appear once for every value in the multiple values columns.
Thanks for putting me on the right track! Making the multiple value columns not required in the first list got around this problem.
I’ll add 1 comment here.
I was having the same problem and had NO current lookup fields and NO allowed multiple value fields.
Turns out that at 1 time I did, had made entries, and then changed the nature of that field to something else.
Once I showed that field in the view of interest, problem went away.
Correction.
It was my “Assigned To” field. I have a required, allow multiple people, Assigned To field which, when left out of the view, does create multiple lines in both data sheet view and when exporting.
I was looking for a true LOOKUP field as stated previously on this page.
I have got the same issue. When the Multilookup field is not included in a list view and when we use SPList.GetItems(SPQuery), this returns a duplicate Item which differs by the value of MultipleLookup values. Ex. If Item 1 has multiple lookup value of a,b then it comes twice with multipleLookup value of a and b with the same ID.
So this issue is only fixed by users till Microsoft decided to do something.
Thanks a lot. It saved me lots of time.
Thank GOD!
I was the same as Todd… the Assigned To field was doing me in. (And this problem was doing my head in!)
I’ll do a round of shots after everyone else has got the beers in.
Cheers,
Popeye.
Now, does anyone know of a way to suppress the index numbers, e.g., in the example above “Midwest;#4;….” I believe that #4 means that “Midwest” is item 4 in its original list. My users are complaining about them….
Thanks!
It also shows up in SP2010