Correcting ambiguous Sitecore field scopes

As you probably know, all fields in Sitecore can have one of three field scopes: Versioned (aka Normal), Unversioned and Shared. Versioned fields have individual version numbers for each language. Unversioned fields have individual values for each language in the same way as versioned fields, but there can be only one value per language. Shared fields are just a single value regardless of language and item version. There are no such thing as a “versioned shared” field type.

This is configured using two check boxes on a field level: Shared and Unversioned. If none are checked, the field becomes a versioned field. As you see, there’s an ambiguous “invalid” state where both check boxes are checked. In this case, Shared has precedence.

For some reason, Sitecore comes with a whole bunch of those ambiguous field types. I’ve checked a few versions on Sitecore 8.x and 9.x and they all have 100+ fields with this ambiguous states. You can easily find those fields yourself by performing the following query in your master and core databases:

SELECT t.ID as TemplateID, t.Name as TemplateName, as FieldId, fi.Name as FieldName
FROM SharedFields u 
JOIN SharedFields s ON u.ItemId=s.ItemId
JOIN Items fi ON u.ItemId=fi.ID
JOIN Items g ON fi.ParentID=g.ID
JOIN Items t ON g.ParentID=t.ID
WHERE u.FieldId='{39847666-389D-409B-95BD-F2016F11EED5}' -- unversioned checkbox
AND s.FieldId='{BE351A73-FCB0-4213-93FA-C302D8AB4F51}' -- shared checkbox
AND u.Value='1' AND s.Value='1'
ORDER BY TemplateName, FieldName

You’ll probably find a whole bunch of fields, such as the common standard field __Hidden, __Read Only and so on.

Note that this won’t do any real harm to your solution, as Shared will have precedence so all those field will be treated as shared. But the ambiguity may cause confusion.

You can simply fix this by performing the following update in your databases:

DELETE FROM SharedFields WHERE ID in (
  FROM SharedFields u 
  JOIN SharedFields s ON u.ItemId=s.ItemId
  JOIN Items fi ON u.ItemId=fi.ID
  WHERE u.FieldId='{39847666-389D-409B-95BD-F2016F11EED5}'
  AND s.FieldId='{BE351A73-FCB0-4213-93FA-C302D8AB4F51}'
  AND u.Value='1' AND s.Value='1'

Ensure you make a backup first! After the update, you need to clear the Sitecore caches to ensure the templates are reloaded from the database.