Understanding the Basics of Collaboration in SharePoint 2010 : Working with Lists and Libraries (part 3) - Managing List Columns

5. Managing List Columns

The power of lists resides in creating columns that will help to provide users with the metadata they want and need. As when designing a SQL database or other business application, it is important to understand the types of metadata users will require and how the data relates to other corporate information. For example, if a corporation wanted to upload all project files into a SharePoint document library, metadata would be required to link the files together. Thus, a column would need to be created that would hold the project name or identifying number so a user would be able to find all documents relating to a specific project. Other columns might be created that would link information such as clients and business groups. All SharePoint columns have a specific type and validation for associated data. Columns can be added to

• Show text and other basic information

• Provide predefined choices for the user for enhanced grouping or filtering

• Link to other site data

• Calculate values based on data in other columns

Figure 15 shows the list of standard column types available in SharePoint 2010.

Figure 15. Standard column types available on the Create Column page

5.1. Creating, Editing, and Deleting Columns

You can insert columns to prompt a user to provide additional information for an item in the list. To add columns to a list, complete the following steps.

1. Open the list and click the List tab under List Tools.

2. Click the Create Column icon.

3. Type a short, descriptive name for the column.

4. Choose the column type for the information to be stored in the list.

5. Specify any additional settings as needed. The settings displayed are dependent on the column type and will dynamically change to reflect the column type you choose. You will find settings such as Required Information and Enforce Unique Values in this section.

   ---

   Note:

   If you choose the Enforce Unique Values option, you will be asked to index the column. It is important to click OK so SharePoint will index the column to improve system performance. Further, if you are modifying an existing field to make it unique, SharePoint will validate the data and return an error if the data already in the column is not unique.

   ---

6. Add column validation as needed. You can specify a formula that will validate the data in this column when new items are saved to this list. The formula must return a true or false validation and cannot refer to other fields in the list. Example: If a column is named Length Of Project, a valid formula would be [Length of Project] > 0.

7. Type a description for the validation formula and an example of what is needed for the validation to pass. Example column description: Please enter the number of days the project will last: 40.

8. Click OK to create the column.

When you have completed these steps, the list refreshes and returns the user context with the added column.

---

Note:

If you plan to use column names in queries or through a Web service, SharePoint Application Programming Interface (API), or other method, columns names should not contain spaces for better behavior within queries and API calls. If the column is created as LengthofProject and then modified to show Length Of Project, it will be possible to reference the column through the various APIs as LengthofProject, yet have it display to users with the spaces included.

---

To see a list of columns created for a list, click List Settings in the Ribbon. All columns in the list display. If the column name is selected, you can modify the column settings as needed. Be careful when changing a column type, because SharePoint will warn you that data might be lost. For example, changing a rich text box to a single line of text will result in SharePoint dropping any data that is longer than the new type will support. The validation rules might need to be adjusted as well. SharePoint does not allow certain column types to be changed to other column types. If this is required, the columns must be deleted and re-created. Understanding the column data and its requirements avoids having to make column type changes to lists.

You can delete a column by clicking List Settings in the Ribbon, just as when you modify a column. When you select Delete, all the data in that column is deleted, and recovery is impossible using the SharePoint Recycle Bin.

---

Note:

Use caution when deleting a column, because data in a deleted column cannot be retrieved later.

---

5.2. Types of Columns

There are three common column types in SharePoint 2010: information, lookup, and calculated columns. 

1.4.5.2.1. Information Columns

The traditional information-gathering columns used in SharePoint, such as Single Line Of Text and Hyperlink Or Picture, allow the user to enter supporting information for the list item. For example, in a project list, supporting information could include a project description, the start and end dates of the project, and the project owner. Figure 16 shows an example of a new project list with these types of information columns.

Figure 16. List view of a project list with information columns added

5.2.2. Lookup Columns

Lookup columns, enhanced to show additional column information from the referenced list, allow a user to link list data together. To create a lookup column in list view, complete the following steps.

1. Click List under the List Tools tab.

2. Click Create Column to display the Column page.

3. Type the column name.

4. Choose Lookup (Information Already On This Site).

5. Set the additional column settings for the column as shown in Figure 17.

   Notice the new options available in SharePoint 2010. Lookup columns are now able to show more than one column from the referenced list. To show additional column information from a lookup list, select the check box to Allow Multiple Values under the In This Column drop-down box. After you have selected the check box, columns will be created for each selected column metadata. This capability is important so the user can be presented with the necessary information with fewer clicks. In Figure 1-26, if a user needed the ability to view a contact’s Mobile Number and Home Phone alongside the current list data, the Allow Multiple Values, Home Phone, and Mobile Number check boxes would all be selected.

   Figure 17. Creating a lookup column

   

6. Set the relationship by choosing whether to enforce relationship behavior when the list item is deleted. When an item in the target list is deleted, a cascade delete will delete all related items in this list. A restricted delete prevents deletion of a list item in the selected target list if one or more referenced items exists in the list. For example, a project list is created that details information about the projects in the engineering group. A secondary list, called Project Tasks, is also created, and it has a column lookup to the project list.

7. If the Enforce Relationship Behavior check box (shown in Figure 18) is selected and Restrict Delete is selected, the user cannot delete a project until all referenced Project Tasks have been deleted. If the Enforce Relationship Behavior option is selected and Cascade Delete is selected, as shown in Figure 1-27, all items in the Project Tasks will be deleted if the project list item is deleted.

   Figure 18. Enforce Relationship Behavior option

   

8. With Referential Integrity selected, you will be prompted to index the column. Click OK.

9. Click OK to create the column.

Figure 19 shows the list with an added lookup column.

Figure 19. Default list view with added lookup column

The lookup column called Contact has a value of 1 in Figure 19, and the Contact:First Name column is also viewable in the list, thereby showing two columns from the referenced list for users. Since this lookup column was set to Cascade Delete when the column was created, this list entry will be deleted in the event that the user SharePointAdmin (the Contact:FirstName column entry) is deleted.

5.2.3. Calculated Columns

Calculated columns bring content together from other columns in the list. In the section titled Section 5.1, you were asked to type the length of the project . Since the project start and end dates are part of the list item metadata, the project length can be calculated by the system. A calculated column can be created by following these steps.

1. Open the list and click List under the List Tools tab.

2. Click Create Column to display the create Column page.

3. Type the column name.

4. Click Calculated Column.

5. Create a formula based on existing columns, as shown in Figure 20.

6. Click OK.

   Figure 20. Adding a calculated column to a list

   

5.3. Indexing Columns

With SharePoint 2010, large lists are now a supported feature. SharePoint now enables sites to have virtually unlimited numbers of items in lists and libraries. However, the core of the SharePoint platform is Microsoft SQL Server, and performance is affected when large amounts of data are queried without the use of indexes. Therefore, it is important to index large lists to streamline overall performance.

When setting up indexes for large lists, consider the following points.

• Microsoft has increased the number of columns in a list index to 20 in SharePoint 2010.

• Each column index consumes resources in the database. Farm or content administrators should only add indexes to columns that will be actively used in views.

• Columns that have been set to allow multiple values cannot be indexed and will not be listed.

To add an index to a site, complete the following steps.

1. Click List under the List Tools tab and then select List Settings.

2. Click Indexed Columns.

3. When the current indexes on the list are shown, click Create A New Index.

4. Click the column that you want to use for the primary column in the index.

5. Select the secondary index column. If this field is left blank, the resulting index will be a single column index. If a second column is specified, the resulting index becomes a compound index in which only certain fields can participate.

6. Click OK.

5.4. Sorting and Filtering

Sorting and filtering list data, especially when there are a large number of items in a list, is beneficial not only for SharePoint performance as a whole, but also in assisting you in finding information quickly and easily in a list.

Lists in SharePoint 2010, like previous versions, allow users to sort and filter data using techniques generally found in other Microsoft Office applications. Each heading in the list view allows you to sort or filter the data based on your needs or requirements. For example, if you want to sort your tasks by date, a simple click on the Date column header to reformat the list data sorted by date. By clicking the Date column header again, the data in the column will be sorted in reverse date order, and you will see the list refreshed to show it in that order in the new list view. As shown in Figure 21, when the arrow in the header bar is pointing up, the list is sorted in ascending order; when the arrow in the header bar is pointing down, the list is sorted in descending order.

Figure 21. List sorting and filtering

Filtering, on the other hand, differs from sorting in that it allows you to view only the data in a list that meets specific criteria. Filtering lets you to sift through large amounts of data to find only the data you need, based on the information contained in a column you select. If you want to view all tasks due today, for example, click the arrow next to the Date Due column heading, and a drop-down list displays the available dates to use as a filter for the list. When you select today’s date to use as a filter, only the tasks in the list due on that date will appear in the list view. You will see a funnel-shaped icon displayed in the column header when filtering is enabled.

---

Note:

Not all columns support filtering. Notable columns are Multiple Lines Of Text and hyperlink.

---

5.5. List Views

Although user sorting and filtering are useful, an even more powerful feature is list views, in which you can use predefined sorting and filtering to determine what you see in the list—you do not need to resort or add a filter each time you view the list. List views allow users, content managers, and administrators to prefilter and/or sort lists to provide the user with only data relevant to the user. List views describe the data, columns, sorting of data, and the style used to present the data. Views, unlike traditional reports, are dynamic and refresh every time the page loads.

List views can be either public or private, meaning that the content manager or administrator can set up predefined views for all users to see, or a user can create a personal view that describes the list data in a way that suits his or her specific needs at the time. All of the sorting and filtering preferences can be saved for use later.

Farm and content administrators should consider the following points when working with list views.

• Only one indexed column can be used in a view filter. Even though filtering on different columns is possible, the first column in the view should be indexed to reduce the overall number of items returned.

• A view can encompass more than one search criteria; however, any view that uses an OR filter does not have any index benefits described here.

• Defining an item limit in the view does not provide the same results as a filtered view.

• You should always use a filter that will return no more items than needed. If a list has 10,000 items, for example, but 9500 of them are not needed daily, add a filter to the default view.

To create a list view, complete the following steps.

1. Click List under the List Tools tab and then select List Settings.

2. Click Create View.

3. Choose either a view format or the option to start from an existing view.

4. Type a view name.

5. Choose either a public or personal view.

6. Choose the columns and set the order in which the columns will be displayed.

7. Choose the sorting method needed—if none is chosen, the list will sort by ID.

8. Choose the columns to filter.

9. Choose whether to allow inline editing. If inline editing is enabled, each row will have an Edit button that enables edit features without requiring the user to leave the view. Inline editing is only available when the style is set to Default.

10. Choose whether to allow tabular view. These check boxes allow you to select multiple items in a view and perform bulk actions such as Delete.

11. Choose the columns to group by.

12. Choose totals, if needed.

13. Change the style for the view if needed.

14. Display all items with or without folders.

15. Choose an item limit to limit the amount of data that is returned to users of this view. You can either make this an absolute limit or allow users to view all the items in the list in batches of a specified size.

16. When a view has been enabled for mobile access, it is also possible to make the view the default view for users of mobile devices. This allows content views to be tailored for both the PC (which may have additional columns and/or graphics) and for mobile users, providing the latter with a simpler, lightweight view. When a mobile user views the list, she can click the key field (specified in the view in mobile settings) to view the entire content record.

17. Click OK.

When the view is created, the page is refreshed and the new view appears. To change the view back to the default, click the List tab in List Tools and click the arrow under Current View, as shown in Figure 22; then select Default from the drop-down list.

Figure 22. Changing the list view

To see the inline editing in action, select the check box to the left of the list item you want to edit. SharePoint highlights the row and the Ribbon changes to the Items action tab under List Tools, as shown in Figure 23. A new Edit button appears to the left of the first column. If you click the Edit button, you can list items within the view.

Figure 23. Edit list items within the view

To save the changes you make, click the blue Save icon. To cancel the edits you made, click the red X icon. To add new items using this view, click the green plus sign (+) icon found below the last item in the list.