Fixing missing Hidden and Sealed attributes in Site columns

After a recent upgrade to sharepoint 2010, we were no longer able to create new site templates. Creating a template would fail with an error:

Unexpected System.InvalidOperationException: Error copying temporary solution file to solutions gallery: _catalogs/solutions/TestProduction.wsp at Microsoft.SharePoint.SPSolutionExporter.ExportWebToGallery

Digging a little deeper into the ULS logs, I was able to find a better explaination of the error:

SharePoint Foundation General 9fjj Monitorable SPSolutionExporter: Microsoft.SharePoint.SPException: Feature definition with Id 10a563f6-0afb-4d38-9ddc-241c61694ac6 failed validation,
file 'TestProductionListInstances\ElementsFields.xml', line 39, character 167:
The 'Hidden' attribute is invalid - The value '' is invalid according to its datatype 'http://schemas.microsoft.com/sharepoint/:TRUEFALSE' - The Enumeration constraint failed. at Microsoft.SharePoint.Administration.SPSolutionPackage.SolutionFile.FeatureXmlValidationCallBack

Something was wrong with our columns. The "Hidden" parameter was blank instead of true or false. Looking into the database confirmed the error. The xml schema of the column definition had : Hidden="" instead of Hidden="FALSE". I also noticed that "Sealed" was also blank, and would cause the same error when trying to save the site as a template. The error describes the elementfields.xml file. This file is part of the site template WSP file. To view the file, go to the solutions gallery and download the Site template WSP that gets created. Change the extension to a CAB and open it. You will find the ElementFields.xml file and can use the line and character from the ULS log error to find the field that causes the problem. In my case, it was about 40 differnet fields.

These were errors on the site columns, many but not all of them being custom columns we created. Sealed and Hidden are not things that are easily changed through the UI, so I had to use Powershell to update the columns. I probably could have used SP Manager too, but since there were 40+ columns duplicated over a few different site collections, it was easier to use powershell.

Updating the Sealed column is fairly easiy since it is a property on a column that can be set directly. When trying to change the 'Hidden' property, I would get the following error: PS C:\> $f = $web.Fields["Meeting Location"]
PS C:\> $f.Hidden = $false
Exception setting "Hidden": "Cannot change Hidden attribute for this field"
At line:1 char:4
+ $f. <<<< Hidden = $false
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyAssignmentException


So, if I can't change the field, how do I change it?. The answer is to change the xml schema. sharepoint will let you change the schema directly and skip around whatever check it has in place.


$field = $Web.fields[$i]
[XML]$schema = $field.schemaxml
$field.schemaxml = $schema.Innerxml
$field.update()


The next problem, how do you know what fields need to be changed? If you just check for $field.hidden -eq "" or something like that it will not work. It will tell you that the field is "FALSE" even when it is blank! So, we have to look at the xml:
[XML]$schema = $field.schemaxml
if ( $schema.InnerXML.Contains("Hidden=""""")){
do something}

All together the script looks like this:

$site = Get-SPSite "http://YourSiteCollectionURL"
$Web =$site.RootWeb

for($i = $Web.Fields.count -1; $i -ge 0; $i--)
{
$field = $Web.fields[$i]
[XML]$schema = $field.schemaxml
if ( $schema.InnerXML.Contains("Hidden=""""")){
$schema.field.Hidden = "FALSE"
$field.schemaxml = $schema.Innerxml
Write-Host $field "HIDDEN: " $schema.field.Hidden
$field.update()
}
if ( $schema.InnerXML.Contains("Sealed=""""")){
Write-Host $field "Sealed: " $schema.field.Sealed
$field.Sealed = "FALSE"
$field.update()
}
if ( $schema.InnerXML.Contains("Required=""""")){
Write-Host $field "Required: " $schema.field.Sealed
$field.Required = "FALSE"
$field.update()
}
}




This script will set all the hidden and sealed fields to FALSE for the site columns that have the field as blank. Another way to do this is to modifiy the database directly, but that isn't supported and you can easily break things.

I hope this can help someone else who runs into the same problem. I wasn't able to find a good solution on the web. MSFT support(wipro) did help, but it took them almost 2 months, and 3 different support techs to come up with a script that I then had to extensivly modify to get to actually solve the problem.

Sum Grouped Calculated Columns in SharePoint

After finally migrating to sharepoint 2010 from sharepoint 2007, i realized some of our custom Data view webparts were no longer working. They had been customized to add up the total hours and display it for each group.
The view had items grouped by user and a total number of hours for that user. This the hours was computed by a check in time and check out time. Sharepoint does not provide a way to calculate a total for calculated fields.
Most solutions tell you to use a dataview webpart to do this. Our old solution worked that way, but it was a hassle to add new views and re-use the code in other projects.

My solution was to throw out the old code and re-build it using out of the box, easy to create views and some javascript/jQuery magic.

The following code will find items by group, and compute a total for that group.
The javascript has two variables:
COLUMNTITLE: The name of the column  you would like to find the total of all the rows
TEXTHEADER: The text you would like to show in front of the total. This cannot be blank, and should be some unique text. It is also used to tell if the total has already been calculated and displayed.

This code will automatically display totals for groups that have been expanded. If the group has not been expanded, it cannot show the total because SharePoint loads the data using ajax. When the group is expanded, the total will be calculated and displayed.

This works on a SharePoint 2007 site upgraded to 2010, but still using the 2007 UI. I haven't tested this in other scenarios. The idea should be the same, some of the css tags might change. YMMV.


var TEXTHEADER = "Total Hours: ";// text to put in front of the total
var COLUMNTITLE = "TotalBillableTime";// which column of data do you want to sum up?
var COLUMNNUMBER =0; // placeholder variable.

//alert("found:"+FindColumnNumber(COLUMNTITLE));

$(function() {
    // attach to clicks to load the data when the user clicks to expand any closed sections
    COLUMNNUMBER = FindColumnNumber(COLUMNTITLE);
    AttachClicksToComputeTotalTime();
    // find the loaded blocks of data and populate the header
    // this can take some time, so we're calling it after a delay.
    // better method would be to attach to the ajax complete call of sharepoint if there is one.

    setTimeout(function(){FindLoadedBlocks();},1000);
   
});

function     FindLoadedBlocks(){
        // search for all tbody's that have tbod in the id and have isLoaded = true
        // compute the totals for these
         $('tbody[id^="tbod"][isLoaded="true"]').each(function($this){
            ComputeBlock(this.id);
          });   
}

function AttachClicksToComputeTotalTime(){
    // find all the header links that the user clicks to expand the data.
    // add an onClick event to compute and display the totals
    $("td.ms-gb2 a").click(function($this){
        FindBlock(this);
    });
}

function FindBlock(Headerblock){
    // given a header, find it's child tbody and wait until it is fully loaded to compute its data
        var parentSectionID = Headerblock.parentNode.parentNode.parentNode.id;
        var childSection = parentSectionID.replace("titl","tbod")+"_";
        var kidSection = $("#"+childSection);
        if($(kidSection).text().length<100 br="">            setTimeout(function(){FindBlock(Headerblock);},50);
            return;
       
        }    else{
        // it's done, let's load it.
            ComputeBlock(childSection);           
        }

}


function ComputeBlock(blockName){
    // for a block of data rows, compute the total and insert it into the header
        var parentSectionID = blockName.replace("tbod","titl").replace("__","_");       

    // when groups are expanded by default, a second tobdy is created
    // check if this tobody has data, if not, go to the next
         if($('#'+blockName).html().length<100 br="">             var block = $('#'+blockName).next();

        }else{
            var block = $('#'+blockName);
        }
       
        var rowSum = 0;
        childRows = $(block).find("tr").each(function(){ 

            // find the 6th column
            var x = $(this).find(":nth-child("+COLUMNNUMBER +")");
            var rowVal = parseFloat($(x).text().trim());
            if(!isNaN(rowVal)){
                rowSum+= rowVal;
            }

        });
        rowSum = rowSum.toFixed(2); // trim it to 2 decimal places

        // find the parent section
        var ParentSection =$("#"+parentSectionID+" tr td");
    // check that the parent section doesnt' already have the totals   
        if($(ParentSection).text().indexOf(TEXTHEADER)>0){

        }
        else{

            $(ParentSection).append("   "+TEXTHEADER +"   "+rowSum+"");
        }

}

function FindColumnNumber(ColumnTitle){
// find the column by name and figure out where it is.
    var pos = 0;
    var foundVal = 0;

    $(".ms-vh2").each(function(){
         pos++;
         if($(this).text() == ColumnTitle){
                 foundVal = pos;
         }
    });
    return foundVal;
}





each group section will look something like this:

 

Find all Checked out files

Here's some sql you can run against your Content DB to find all checked out files. This will work against all files in the content DB, so it will work across multiple site collections.. everything in the DB.

SELECT[tp_Modified],[tp_Created],[tp_IsCurrent],[tp_DirName],[tp_LeafName],tp_login,tp_titleFROM [WSS_Content_CNS].[dbo].[AllUserData]left join [WSS_Content_CNS].[dbo].[UserInfo] on tp_checkoutuserid = userinfo.tp_idwhere tp_CheckoutUserId is not null and tp_iscurrent=1 and tp_deletetransactionid = 0x0order by tp_modified desc,tp_title

KB2699988, iFrames, Anchor tags and the headaches that won't stop.

Recently, a page on one of our sites stopped working.

The page lists things and has anchor tags that will take you to the relavant part of the page. We are currently excusivly using IE8.

The trick about this page is that it is displayed on our sharepoint portal using a Page viewer webpart. The page is from a legacy app, and the easiest way to put it on the portal was using an iFrame.


The second notable piece of information is that the page viewer webpart is set with a heigh that is large enough to show the entire page inside the iframe without scrolling, something around 15000px. To the user, it looks as if the page is part of the portal.

Recently, the anchor tags at the top of the page in the iFrame stopped working. The page would no longer scroll down to show the relavant part of the iFrame'd page. Clicking the anchor tags would do nothing.


What's going on? These worked for 2 years without any problems!
After a lot of searching and testing, I found out that this was a potential security hole. The problem is that when the user clicks an anchor tag, the main page has to scroll down to show the part of the inner iframe(the page is like 15000px long).
Because the outer page scrolls, it's possible to setup an exploit that allows the outer page to tell if something exists on the inner page. Someone wrote about it at a blackhat conference: https://media.blackhat.com/bh-eu-10/presentations/Stone/BlackHat-EU-2010-Stone-Next-Generation-Clickjacking-slides.pdf. The explaination starts at page 38.


I found this because the same page does not work in Firefox or chrome. Fortunatly, Firefox is fairly open about their bugs and has a comments section to allow people to discuss the problem and the solution. The firfox bug (https://bugzilla.mozilla.org/show_bug.cgi?id=583889) talks about the problem. This was helpful to track this down as a security update and not something that we changed. They fixed it in early 2011.

 It looks like that this same problem was fixed in KB2699988. Reading through the hotfixes that are in KB2699988, nothing really jumps out at me that says that this was fixed or changed. The closest think I can find is "A memory leak may occur when a modal dialog box opens in an iframe in Internet Explorer 8 ". This to me doesn't have anything to do with the problem I'm running into, but if it does, then KB2695422 is the culprit.

Update: Here's a solution that should work - but only if you have access to change the iFrame page's code. - http://matthewmanela.com/blog/making-linking-between-anchors-in-an-iframe-work-in-firefox-11-and-above/comment-page-1/#comment-1787


 I haven't found a good solution to this yet. If you setup the iFrame so that the height is less than the page height, you will somewhat fix the problem. It will cause you to have scroll bars, and the iFrame will scroll. It won't cause the outer page to scroll though, so you're users won't be able to see it.

 The other solution I'm looking into is changing the code on the page inside the iFrame so that it uses JavaScript to scroll the page. Hopefully this will work for me, but for you, you may not be able change the code inside the iFrame.

 I will note that this isn't specifically a SharePoint problem. You can test this out by creating a page that has an iFrame in it. The iFrame height should be set long enough that it's taller than the page - something like 2000+ pixels.
(Replace the curly brackets with GT and LT's, obviously)
At the top of the iframe's page, put a link to an anchor Tag {a href="#end"} go to end {/a}. At the bottom of the iFrame'd page, put an anchr Tag like {a name="end"}end{/a} the End should be way down at the bottom of the page, and not visible(add some gangsta Lorum Ipsum, or just a bunch of {BR}'s to make the page really long,  but not so long that it is more than the 2000 pixels you set it to and introduces some scroll bars.

To the user, it would look like one long page with a link at the top and at the bottom. Clicking the 'go to end' link at the top of the page should scroll the page down to the bottom. It will if the page is not in an iFrame. If the page IS in an iframe, it won't work. Nothing happens.


I guess this just shows that Firefox and Chrome are more secure browers. They fixed this 'hole' (and I'm not sure I'm ready to agree that this is a legit security issue given the consequenses of disabling the 'feature') more than a year and a half before IE fixed it.

SharePoint 2010 - Documents Tab

while trying to make the transition from sharepoint 2007 to 2010, I've found that the default view of the ribon is confusing. What is the point of the browse tab? Especially if you're in a document library/list? In 07, useres have quick access to upload a document or create a new list item. In 2010, they must click a tab on the ribbon to be able to see that button.
To solve that problem, I want the documents or list items tab to be automatically selected for them.

I found two blogs that talk about this issue. I found that neither one really did what I want.

This site talks about how to set it as the default using the query string. Thought this might work for some, I can't change every link on every page to pass this as a parameter.
http://www.iotap.com/Blog/tabid/673/entryid/149/Displaying-default-tab-in-Sharepoint-2010-Ribbon.aspx
This second page talks about using javascript to set it to a custom tab based on some rules you setup in the javascript. I simplified this code for my use:
http://code.bkwdesign.com/2011/01/11/using-jquery-to-activate-default-sharepoint-tab/

There is probably a better way to do this using some built in sharepoint javascript funciton. I found one blog post that mentioned something like that, but I was unable to get it to work.

The following code will automatically select the Documents tab in a document library and the List Item tab in a list. In any other case it should not do anything, so it's safe to put on every page. You could also add in other options to handle pages librarys or some other case I haven't run into yet.
I added this code to my master page, but you could easily add it to a javascript file and add a reference to your master page or page layout or whatever, even put it in a Content Editor for one particular page.



Here's the code:


<script type="text/javascript">

function setTabDefault(tabid){
$("li[id=" + tabid + "] a span.ms-cui-tt-span").trigger('click');
}
$(document).ready(function () {

setTimeout("setTabDefault('Ribbon.Document-title');",150);
setTimeout("setTabDefault('Ribbon.ListItem-title');",150);

});

</script>

Installing SharePoint 2010 Without Internet Access

Recently, I was forced to install SharePoint 2010 on a network without internet access. Due to the fact that SP2010 requires about a dozen prerequisites, this is much more difficult than it should be.
There is a way to set up the installer to not attempt to access the internet to download the prerequisites.
First, You'll need a computer that has internet access so that you can download the prerequisites.
Fortunately, someone did the work for me to do this part.
The SharePoint PowerShell script to download all the prerequisite files

SharePoint can be downloaded as an ISO from Microsoft. You'll need to get this and copy all the files into a directory somewhere. Put all the prerequisite files in the ""PrerequisiteInstallerFiles" folder.
This list assumes you are installing on Server 2008 R2. Server 2008 has some slightly different items and hotfixes

Next, create a file called: "PrerequisiteInstaller.Arguments.txt". Open it up in notepad and add:

/SQLNCli:PrerequisiteInstallerFiles\sqlncli.msi /ChartControl:PrerequisiteInstallerFiles\MSChart.exe /IDFXR2:PrerequisiteInstallerFiles\Windows6.1-KB974405-x64.msu /Sync:PrerequisiteInstallerFiles\Synchronization.msi /FilterPack:PrerequisiteInstallerFiles\FilterPack\FilterPack.msi /KB976462:PrerequisiteInstallerFiles\Windows6.1-KB976462-v2-x64.msu /KB976394:PrerequisiteInstallerFiles\Windows6.0-KB976394-x64.msu /ADOMD:PrerequisiteInstallerFiles\SQLSERVER2008_ASADOMD10.msi /Speech:PrerequisiteInstallerFiles\SpeechPlatformRuntime.msi /SpeechLPK:PrerequisiteInstallerFiles\MSSpeech_SR_en-US_TELE.msi /ReportingServices:PrerequisiteInstallerFiles\rsSharePoint.msi


NOTE: This should all be on one line! No New lines!


When you run the SharePoint setup.exe, it'll run the PrerequisiteInstaller.exe which will read the arguments file, and load the files from the prerequisites installer directory instead of trying to go to the internet!

Mapping Office Shared Template Location to a SharePoint Library

A user asked if you can create a new word document from a template that is stored in SharePoint.
Normally, i'd say sure, lets make it a content type, add it to the doc library and just click new! But of course this doesn't work. how many users really do that anyway? None of mine.

So, i found out there is a Word Workgroup Templates folder that you can set. When you click new, my templates, it will show you all the templates from that folder. It even creates tabs for the sub folders. Very nice.

To set the folder in Word 2007

1. Word Options
2. General -> File Locations
3. Set the folder for Workgroup Template.

Unfortunatly, you can't just enter a sharepoint URL, or even the Shared folder path (\\portal\somesite\templates\). It will give you an error saying "You cannot use an internet address here. Enter a path that points to a location on your computer or on the network".

Solution:
Trick Word.

Map a drive to the SharePoint folder path. Then tell word to point to that folder.

I chose to map T:\ to \\portal\somesite\templates\ and then told word that my templates folder is t:\

Problem solved!