SharePoint 2010 Datasheet View Bugaboo
I ran into this today and thought it might be something worth jotting down to save others the same pain.
When you’re editing a list in Datasheet view in SharePoint 2010 and you’re working with a Calculated column, you can see the formula at the bottom of the view for the cell in which you have your cursor. All well and good. See the screen snip below; the yellow highlighting is mine.
Since it’s a Datasheet view, however, it’s pretty easy to mistakenly type a number into the cell without noticing that it’s a Calculated column. When you do this, you get the prompt below. Again, my highlighting.
Now, here’s where I messed myself up. I didn’t read the message carefully enough and just thought it was telling me that it would take a while to be helpful. In fact, what was happening was that SharePoint was about to replace the formula in the column with a new formula, =50. This meant that every cell then had the value 50 rather than the previous calculation.
It was easy enough to fix, once I figured out what I had done, but some calculations I was doing in a DVWP sure looked crazy for a bit!
Marc,
Great info. FYI, I have run into the same issue with 2007.
Mark
Marc – Thanks for the tip.
Marc, I appreciate your willingness to be transparent and help others of us learn from your mistakes. I’ve worked with a lot of people in the past who would never admit making a mistake, let alone publicize one to help others out.
Keith
Keith:
One might say that you’ve been working with the wrong people. ;-) You’re welcome!
M.
Echoing Keith Hudson- thanks for sharing so freely.
Hi Mark, thanks for this! I just experienced the same thing here with a not-yet-launched project for soon to be exectutive and manager level contributors — boy did I get big-eyed when I saw what happened! This could be quite a problem. Were you by chance able to come up with an alternative, like protecting the Calculated column from accidental data entry? I’m looking for a way to do so…thanks!
Tracy:
The best solution is just to not use the Datasheet view. IMO, it really was never intended for real end users. It’s much better to build forms which allow editing in a more controlled manner. Those forms can certainly allow multiple item edit.
If you must use the Datasheet view, only use it with views that don’t have calculated columns or any other columns you want to keep “safe”.
M.
I have a client who heavily uses the datasheet view in SP2010 for changing 100s of items at a time – they would like read-only fields so that support staff can’t accidentally delete some fields – but there are a handful of fields they DO need to edit.
I’ve created a limited view for them, but this obviously doesn’t keep them from deleting important information at the form level. I’ve also tested many of the 3rd party column security options that are out there – but none of them work well, if at all, with the datasheet view. (Bamboo does mark the field as read only, but hides the data with code).
Do you have any suggestion for the type of form that would allow them to edit multiple items at a time, but lock down or hide other fields from view? This list can get up to 50,000 items, although we’ve built in archiving that should keep it from growing that large going forward.
Thanks.
Barb:
I agree that the Datasheet view can because big problems. It’s just too easy to be destructive accidentally.
You could use a DVWP which allows editing of multiple items. That means that you gave to change each value one at a time, no fun drag to fill.
Also consider linking to an Excel spreadsheet. It’s a richer environment for changes, as long as you don’t need to distribute that capability widely
M.