Thursday, September 27, 2012

Cannot remove border of a table in Content Editor Web Part

When we add a <table> tag in content editor web part, we cannot customize or remove the cell border even when we set the css styles.

Workaround for this is by overriding the .ms-WPBody TD style in the aspx page where the web part is added.


<asp:Content ContentPlaceHolderId="PlaceHolderTitleAreaClass" runat="server">
<style type="text/css">
.ms-WPBody TD
{
         border:0;
}
</style>
</asp:Content>

Sorting Outline number in MOSS programmatically


To create a calculated field to sort the outline numbers programmatically. Following code helps to create the fields and calculated values on feature activation.

Feature Receiver Code


  string strListName = "Checklist";
        string fieldName = "Outline";
        string strSortFieldname = "CustomOutlineSort";
        string strCalculatedFieldname = "OutlineSorted";
        string OutlineFieldValue = string.Empty;
        string newOutlineFieldValue;

        /// <summary>
        /// FeatureActivated
        /// </summary>
        /// <param name="properties"></param>
        public override void FeatureActivated(SPFeatureReceiverProperties properties)
        {
            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
               {
                   using (SPWeb web = (SPWeb)properties.Feature.Parent)
                   {
                       web.AllowUnsafeUpdates = true;
                       SPList objSPList = web.Lists[strListName];
                       //Create Readonly field 'CustomOutlineSort'
                       objSPList.Fields.Add(strSortFieldname, SPFieldType.Text, false);
                       SPFieldText txtField = (SPFieldText)objSPList.Fields[strSortFieldname];
                       txtField.ReadOnlyField = true;
                       txtField.Update();

                       //Create calculated field 'OutlineSorted'
                       objSPList.Fields.Add(strCalculatedFieldname, SPFieldType.Calculated, false);
                       SPFieldCalculated CalcField = (SPFieldCalculated)objSPList.Fields[strCalculatedFieldname];
                       CalcField.Formula = @"=" + strSortFieldname;
                       CalcField.Update();

                       objSPList.Update();
                       web.AllowUnsafeUpdates = false;

                       CalculateSortValue(properties);
                   }

               });
            }
            catch (Exception ex)
            {
                throw new SPException("An error occured when activating the feature. Make sure that the list 'Checklist' exists in this site.");
            }
        }


private void CalculateSortValue(SPFeatureReceiverProperties properties)
        {
            try
            {
                using (SPWeb oSite = (SPWeb)properties.Feature.Parent)
                {
                    oSite.AllowUnsafeUpdates = true;
                    SPList oList = oSite.Lists[strListName];

                    //Check if Outline, CustomOutlineSort and OutlineSorted fields are created.
                    if (oList.Fields.ContainsField(fieldName) && oList.Fields.ContainsField(strSortFieldname) && oList.Fields.ContainsField(strCalculatedFieldname))
                    {
                        foreach (SPListItem oListItem in oList.Items)
                        {
                            //Check if Outline field value is not empty
                            if (oListItem[fieldName] != null)
                            {
                                newOutlineFieldValue = string.Empty;
                                OutlineFieldValue = oListItem[fieldName].ToString();
                                string[] sections = OutlineFieldValue.Split('.');

                                //loop all the parts splitted with dot and left pad with '0' and make total width as 2 characters.
                                foreach (string section in sections)
                                {
                                    newOutlineFieldValue = newOutlineFieldValue + section.PadLeft(2, '0');
                                }
                                //append '00'to make total of 8 characters
                                for (int i = sections.Length; i < 4; i++)
                                {
                                    newOutlineFieldValue = newOutlineFieldValue + "00";
                                }
                            }
                            //Store the customized value in readonly field 'CustomOutlineSort'.
                            oListItem[strSortFieldname] = newOutlineFieldValue;
                            oListItem.Update();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new SPException("An error occured while calculating sort values for 'Outline' column in 'Checklist' list");
            }
        }



EventHandler code for ItemAdded and ItemUpdated events

//Sorting Outline Numbers in SharePoint list

string listName = "Checklist";
        string fieldName = "Section";
        string strSortFieldname = "CustomSectionSort";
        string strCalculatedFieldname = "SectionSorted";
        string OutlineFieldValue = string.Empty;
        string newOutlineFieldValue;

  /// <summary>
        /// Method to make the outline number sortable. The logic used is, the value will be splitted by dot and left pad
        /// each part with '0' to width of 2. After padding, merge all the parts and save the value in 'CustomOutlineSort' field.
        /// For instance 1.1.1 will be saved as 010101.
        /// </summary>
        /// <param name="properties"></param>
        private void CalculateSortValue(SPItemEventProperties properties)
        {
            try
            {
                SPWeb oSite = properties.OpenWeb();
                SPList oList = oSite.Lists[listName];
                SPListItem oListItem = properties.ListItem;

                //Check if Outline, CustomOutlineSort and OutlineSorted fields are created.
                if (oList.Fields.ContainsField(fieldName) && oList.Fields.ContainsField(strSortFieldname) && oList.Fields.ContainsField(strCalculatedFieldname))
                {
                    if (properties.ListItem[fieldName] != null)
                    {
                        newOutlineFieldValue = string.Empty;
                        OutlineFieldValue = properties.ListItem[fieldName].ToString();
                        string[] sections = OutlineFieldValue.Split('.');

                        foreach (string section in sections)
                        {
                            newOutlineFieldValue = newOutlineFieldValue + section.PadLeft(2, '0');
                        }
                        for (int i = sections.Length; i < 4; i++)
                        {
                            newOutlineFieldValue = newOutlineFieldValue + "00";
                        }
                    }
                    oListItem[strSortFieldname] = newOutlineFieldValue;
                    oListItem.Update();
                    oList.Update();
                }
            }
            catch (Exception ex)
            {
             
            }
        }

Friday, September 21, 2012

Sorting Outline Numbers in SharePoint list using Calculated columns

Requirement: We have a column in a list named SNumber. The value for this is would be similar to outline numbers in Word documents.

1.1
1.2
1.3
1.10
1.1.1
1.1.2
1.2.3.3
etc

Problem: We were not able to sort the values, and the values are sorted in the following order. The SharePoint calculated field formulas are not strong enough to achieve this.

1.1
1.10
1.2
1.3
etc


Workaround: We have multiple options to achieve this, this can be done using Event Handlers. Here this is done using multiple calculated field.

The minimum and maximum value would be
1.0 to 99.99.99.99

The logic used to achieve this is to split and pad the values. For instance, converting the following value
1.10.2.3   to 01100203

01.10.02.03 (prefix '0' before each part separated by dot)
01100203 (and  merge all the parts)

For this, I've created 8 calculated columns, you just need to replace the field name SNumber with your field name.

Column Name
Formula
part1
=IF(LEN(LEFT(SNumber,INT(FIND(".",SNumber)-1)))
<2,"0"&LEFT(SNumber,INT(FIND(".",SNumber)-1)),
LEFT(SNumber,INT(FIND(".",SNumber)-1)))
section2
=RIGHT(SNumber,LEN(SNumber)-INT(FIND(".",SNumber)))
part2
=IF(ISNUMBER(INT(FIND(".",section2)-1)),
IF(LEN(LEFT(section2,INT(FIND(".",section2)-1)))<2,
"0"&LEFT(section2,INT(FIND(".",section2)-1)),
LEFT(section2,INT(FIND(".",section2)-1))),
IF(LEN(section2)<2,0&section2,section2))
section3
=RIGHT(section2,LEN(section2)-IF(ISNUMBER(INT(FIND(".",section2))),
INT(FIND(".",section2)),LEN(section2)))
part3
=IF(ISNUMBER(INT(FIND(".",section3)-1)),
IF(LEN(LEFT(section3,INT(FIND(".",section3)-1)))<2,
"0"&LEFT(section3,INT(FIND(".",section3)-1)),
LEFT(section3,INT(FIND(".",section3)-1))),IF(LEN(section3)=0,"00",
IF(LEN(section3)<2,"0"&section3,section3)))
section4
=RIGHT(section3,LEN(section3)-IF(ISNUMBER(INT(FIND(".",section3))),
INT(FIND(".",section3)),LEN(section3)))
part4
=IF(ISNUMBER(INT(FIND(".",section4)-1)),IF(LEN(LEFT(section4,
INT(FIND(".",section4)-1)))<2 amp="" div="" section4="">
LEFT(section4,INT(FIND(".",section4)-1))),IF(LEN(section4)=0,"00",IF(LEN(section4)<2,"0"&section4,section4)))
customSNumber
 =IF(ISNUMBER(part1&part2&part3&part4),part1&part2&part3&part4,0)

Now, just sort the list by customSNumber column