Item ‘Modified By’ Value Doesn’t Change: Fixing a Damaged Column Schema
I ran into an odd situation the other day at one of my clients.
When people edited existing list or library items in *some* lists (we couldn’t discern a pattern) the Modified By (Editor) column value did not change. The date/time stamp (Modified) was updated, but not the person who made the modification. We found several good posts out there on them Interwebz, but I though it would be useful to pull things together and outline what we tried in case it could help others.
There were two posts we found that got us started:
- Modified By Column does not get updated from Victor Butuza at Microsoft
- MOSS 2007: WSS 3.0: How to add/delete/update site columns by using SharePoint WebService from Karthick’s Blog
The first post led us to look at the Modified By column’s schema; the second led me to think that I could do the fixes using the SOAP Web Services.
I was able to pull the schema for the Modified By Site Column using the GetColumns operation in the Webs Web Service. (Of course I turned to SPServices first – it’s my hammer.)
$().SPServices({
webURL: "/",
operation: "GetColumns",
completefunc: function(xData, Status) {
}
});
I was able to look at the returned XML in Firebug, copy it out, and find the schema for the Modified By column.
Bingo. From Victor Butuza’s post, we know that the schema should look like this:
<Field ID="{d31655d1-1d5b-4511-95a1-7a09e9b75bf2}"
ColName="tp_Editor"
RowOrdinal="0"
ReadOnly="TRUE"
Type="User"
List="UserInfo"
Name="Editor"
DisplayName="Modified By"
SourceID="http://schemas.microsoft.com/sharepoint/v3"
StaticName="Editor"
FromBaseType="TRUE"/>
Instead, it looked like this (I’ve changed several of the attribute values to protect the guilty, but you should get the point):
<Field ID="{d31655d1-1d5b-4511-95a1-7a09e9b75bf2}"
Name="Editor"
SourceID="http://schemas.microsoft.com/sharepoint/v3"
StaticName="Editor"
Group="_Hidden"
ColName="tp_Editor"
RowOrdinal="0"
Type="User"
List="UserInfo"
DisplayName="Modified By"
SystemInstance="SQL_DB_Name"
EntityNamespace="http://servername"
EntityName="Partners"
BdcField="OrganizationId"
Profile=""
HasActions="True"
SecondaryFieldBdcNames="0"
RelatedField="Partners_ID"
SecondaryFieldWssNames="0"
RelatedFieldBDCField=""
RelatedFieldWssStaticName="Partners_ID"
SecondaryFieldsWssStaticNames="0"
AddFieldOption="AddFieldInternalNameHint"
ReadOnly="TRUE"
Version="1">
</Field>
Some way or other, the Modified By (Editor) column had been changed. We noticed quite a few odd things in the schema above, which I’ve highlighted. It looks like attributes from some BDC connection had made it into the schema, which wasn’t good. More importantly, the important FromBaseType="TRUE" attribute was missing.
I tried for a while to get the SOAP operation UpdateColumns to work in a test environment. (I was very careful to do this in a snapshot of a VM because messing with the Modified By column could be catastrophic.) Caution proved wise, as each time I tried to update the Modified By schema, the Site Column management pages (_layouts/mngfield.aspx and _layouts/fldedit.aspx) no longer worked properly. Even though I believe I had the syntax correct (based on Karthick‘s post above – note that the MSDN documentation is pretty sparse), I simply couldn’t get UpdateColumns to work without breaking things.
So I turned to Powershell. I’m no Powershell guy, but it’s a scripting language like any other and I found some good starting points out on the Web to get me going. What I ended up with was a script that repaired all of the attributes of the Modified By column in a single list.
$s = get-spsite http://servername
$w = $s.OpenWeb("/sitename/")
$l = $w.Lists["listname"]
$f = $l.Fields.GetFieldByInternalName("Editor")
write-host "BEFORE field at " $w.Url " List " $l.Title " is " $f.schemaxml
#add at the end of the schema the needed string and update the field and list
$f.SchemaXML = $f.SchemaXML -replace ' SystemInstance="SQL_DB_Name"',''
$f.SchemaXML = $f.SchemaXML -replace ' EntityNamespace="http://servername"',''
$f.SchemaXML = $f.SchemaXML -replace ' EntityName="Partners"',''
$f.SchemaXML = $f.SchemaXML -replace ' BdcField="OrganizationId"',''
$f.SchemaXML = $f.SchemaXML -replace ' Profile=""',''
$f.SchemaXML = $f.SchemaXML -replace ' HasActions="True"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldBdcNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedField="Partners_ID"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldWssNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedFieldBDCField=""',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedFieldWssStaticName="Partners_ID"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldsWssStaticNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' AddFieldOption="AddFieldInternalNameHint"',''
$f.SchemaXML = $f.SchemaXML -replace '/>',' FromBaseType="TRUE" />'
$f.Update()
$l.Update()
write-host "FIXED field at " $w.Url " List " $l.Title " is " $f.schemaxml
$w.Dispose();
$s.Dispose();
I also wrote a Powershell script to find all of the lists where this aberration had occurred in the Modified By column:
$siteURL = "http://servername"
$site = Get-SPSite($siteURL)
$errors = 0
$thisWebNum = 0
foreach($web in $site.AllWebs) {
$thisWebNum = $thisWebNum + 1
write-host $thisWebNum " Web " $web.Url " Created on " $web.Created
$listCounter = $web.Lists.Count
for($i=0;$i -lt $listCounter;$i++) {
$list = $web.Lists[$i]
$thisListNum = $i + 1
write-host "(" $thisListNum "/" $listCounter ") [" $list.Title "] Created on " $list.Created
$f = $list.Fields.GetFieldByInternalName("Editor")
if ($f.SchemaXML -NotMatch 'FromBaseType="TRUE"')
{
$errors = $errors + 1
write-host " Issue in schema " $f.schemaxml
}
}
$web.Dispose();
}
$site.Dispose();
write-host "TOTAL ISSUES: " $errors
This script told us that we had 283 lists with the issue. Clearly this has been going on for a long time and no one had caught it, though we still can’t see any patterns in the date/time stamps or which lists have the issue.
We’ve fixed the two lists where the client folks had noticed the Modified By issue as well as the Site Column itself. We’re pausing at this point just to make sure that we don’t see any oddities based on the fix, but we’re optimistic that we know what was happening and that we have a valid fix. After we let things settle for a little while, we’ll run the fix script on the rest of the lists with the issue by combining the two scripts to loop through all of the lists with the issue.
Have you ever run into a broken schema issue like this in your environment? If so, how did you fix it?
<UPDATE date=”2013-10-17″>
We hadn’t seen any issues since we’d applied the fix I described above, so today I ran through the rest of the messed up lists and applied the fix. The Powershell script below loops through all of the sites in the Site Collection, then through all of the lists in each site, and fixes the Editor column where it’s got issues.
$siteURL = "http://servername"
$site = Get-SPSite($siteURL)
$errors = 0
$thisWebNum = 0
foreach($web in $site.AllWebs) {
$thisWebNum = $thisWebNum + 1
$listCounter = $web.Lists.Count
for($i=0;$i -lt $listCounter;$i++) {
$list = $web.Lists[$i]
$thisListNum = $i + 1
$f = $list.Fields.GetFieldByInternalName("Editor")
if ($f.SchemaXML -NotMatch 'FromBaseType="TRUE"')
{
$errors = $errors + 1
# fix the schema and update the field and list
$f.SchemaXML = $f.SchemaXML -replace ' SystemInstance="GivingData"',''
$f.SchemaXML = $f.SchemaXML -replace ' EntityNamespace="http://servername"',''
$f.SchemaXML = $f.SchemaXML -replace ' EntityName="Partners"',''
$f.SchemaXML = $f.SchemaXML -replace ' BdcField="OrganizationId"',''
$f.SchemaXML = $f.SchemaXML -replace ' Profile=""',''
$f.SchemaXML = $f.SchemaXML -replace ' HasActions="True"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldBdcNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedField="Partners_ID"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldWssNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedFieldBDCField=""',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedFieldWssStaticName="Partners_ID"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldsWssStaticNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' AddFieldOption="AddFieldInternalNameHint"',''
$f.SchemaXML = $f.SchemaXML -replace '/>',' FromBaseType="TRUE" />'
$f.Update()
$list.Update()
write-host "FIXED field at " $w.Url " List " $l.Title " is " $f.schemaxml
}
if ($errors -gt 0)
{
write-host $thisWebNum " Web " $web.Url " Created on " $web.Created " had " $errors " errors"
}
$errors = 0;
$web.Dispose();
}
$site.Dispose();
</UPDATE>
Sometimes if the modified by says “SYSTEM ACCOUNT” then you have to set the default content cache account for the web application.
Dear Marc,
Thank you for the clear examples;
You have also given us an example to find all defected Modified By columns.
On this I made another example script that will write the transcript to a txt file.
That can be used as a reference for later:
$siteURL = “http://servername”
$site = Get-SPSite($siteURL)
$errors = 0
$thisWebNum = 0
$filepath = “C:\temp\srcheditorfield_log_” + $(get-date -f yyyy-MM-dd_HHmmss) + “.txt”
Start-Transcript -path $filepath
try
{
foreach($web in $site.AllWebs) {
$thisWebNum = $thisWebNum + 1
write-host $thisWebNum ” Web ” $web.Url ” Created on ” $web.Created
$listCounter = $web.Lists.Count
for($i=0;$i -lt $listCounter;$i++) {
$list = $web.Lists[$i]
$thisListNum = $i + 1
write-host “(” $thisListNum “/” $listCounter “) [” $list.Title “] Created on ” $list.Created
$f = $list.Fields.GetFieldByInternalName(“Editor”)
if ($f.SchemaXML -NotMatch ‘FromBaseType=”TRUE”‘)
{
$errors = $errors + 1
write-host ” Issue in schema ” $f.schemaxml
}
}
$web.Dispose();
}
$site.Dispose();
write-host “TOTAL ISSUES: ” $errors
}
catch [Exception] {
$_.Exception.Message
}
Stop-Transcript