Column Types: Managed Metadata

The word metadata is for many a foreign word. The IT folks and database analysts understand what it means and they describe it as “data about data” – I don’t know about you, but that doesn’t mean much to me. So it took me a while to embrace what this field is all about.

And while this means nothing to most people, I classify it sometimes as a site column on steroids. A site column can help you with setting similar data that will be used throughout your site and your site collection. A great example for using a site column might be to create a department list that you could use in multiple lists on your site. Something I noticed about this however, is that if for some strange reason you might want to have two Department columns in your list, then using the site column feature would limit you – because you can only use it once per list.

This is where the Managed Metadata column can help because you can use the same metadata field over and over again in a list – there’s no limitation. The data from which we pull information in a metadata column is coming from the site collection level – i.e. these are things that are globally useful to everyone within the DSA, not just your department. What’s also nice, is if the data changes (a department name changes for example) that information is populated throughout the division, so the information is always accurate.

Managed metadata ensures that whatever data is selected, it’s “normalized”, which means that people can’t change it when entering the data.

That means my department, named Communications & Innovative Technologies can’t become any of the following: Communications and Innovative Technologies, Communications, CIT, C&IT, or any other iteration. It means that our campus health center, Schiffert Health Center can’t become SHC, Shiffert, Schiffert, Health Center, Schiffert Health, etc. Using “normalized” data structures lets the database behind SharePoint run accurate reports. And because the data is managed centrally, it means that individual site owners don’t have to worry about making those types of changes.

Let’s see what Microsoft has to say about the Managed Metadata column type:
Use this column type to enable site users to select values from a specific term set of managed terms and apply these values to their content. Users can create and configure a Managed Metadata column to map to an existing term set or term, or they can create a new term set specifically for a Managed Metadata column. Managed Metadata columns have several unique features and characteristics that help users select and apply terms to content, such as support for “type-ahead,” as well as support for disambiguation of terms, descriptions, synonyms, and multi-lingual values.

Managed Metadata columns promote the consistent use of metadata across sites because they provide users with a list of terms that they can apply to their content. These terms come from a term set that is managed centrally in the Term Store Management Tool by a Term Store Administrator or other individuals with permissions to manage terms. Whenever the term set that a specific Managed Metadata column is bound to is updated, the updated terms will automatically become available to users wherever that Managed Metadata column is available. You can customize the Managed Metadata column in the following ways.

Multiple Value field Selecting the Allow multiple values checkbox enables the column to contain more than one value. Note that enabling this feature will prevent sorting data in list views.

Display format The value selected from the term set can be displayed either as a single value or with the full hierarchical path.

Select the Display term label in the field checkbox to display value of the terms set as a single value. For example: City.

Select the Display the entire path to the term in the field checkbox to display the value of the terms set as a full hierarchical path. For example: Location, Continent, Country/Region, City.

Term Set Settings Display terms from an enterprise managed term set, or create a custom term set to share with others in a site collection.

Use a managed term set Enter one or more terms, separated by semicolons, and select Find to filter the options to only include those which contain the desired terms. After finding the term set that contains the list of values to display in this column, click on a term to select the first level of the hierarchy to show in the column. All levels below the term you select will be seen when users choose a value.

Create a custom term set Enter a description for your custom term set and enter your terms set hierarchy directly into the box provided, or click Edit using Term Set Manager to open and use the Term Management tool.

NOTE: While a custom term is available to all users in a site collection, the terms in the set will not be available as Enterprise Keywords.

Advertisements

Column Types: Calculated Field

One of my favorites is the Calculated field. This is where we can concatenate and run IF/THEN statements, and a number of other formulae that you can use in Excel. I am a fan of anything that can be automated, so when I can automatically make calculations or add the info from multiple fields together, I’m a happy gal.

In addition to the basic formula options Microsoft suggests below, I have used the calculated field to provide an automatic start date based on duration, or if the standard lead time is 7 days, but certain circumstances require a longer lead time, then I used an IF statement to see if the lead time had a value, and if so, to calculate the start date by taking the due date and subtracting the lead time, but if there was no lead time specified, to default to subtracting 7 days from the due date.

The formula looked like this:
=IF(ISBLANK([Publish Date]),””,IF([Lead Time],[Publish Date]-[Lead Time],[Publish Date]-7))

A caveat for SPOCKs: if you plan on using Content Types, the calculated fields apparently don’t travel with the Content Types when you add them to existing lists – you have to add the site columns manually before you import the Content Types. For more information about Content Types, please contact me.
Microsoft tells us the following about this field:
Use this column field type to display information that is based only on the results of a calculation.

When you add a calculated column to a list or library, you create a formula that contains operators, such as subtraction (-) and addition (+), functions, specific values, and references to other columns. Formulas can calculate dates and times, perform mathematical equations, or manipulate text. For example, on a Tasks list, you can use this type of column to calculate the number of calendar days required to complete each task based on the Start Date and Date Completed columns (=[Date Completed]-[Start Date]). In a Contacts list, you can use a Calculated column to combine the first and last names of the contacts and separate them with a space, based on First Name and Last Name columns (=[First Name]&” “&[Last Name]). Note that the formula in a calculated column can only reference other columns in the same list or library.

Column Types: Hyperlink or Picture

The hyperlink or picture column is used to display an image or provide a link. Used by picture libraries, links lists, and promoted links lists. Caveat for using the picture field is that the pictures must already be uploaded somewhere. It’s not a spot where you can upload an image to have it attached to a new list item. This means that there must be an asset or picture library present for these images to exist.

Here is what Microsoft has to say about the Hyperlink or Picture field:

Use this column field type to store a hyperlink to a Web page or to display a graphic on the intranet or Internet.

A Hyperlink or Picture column stores the Uniform Resource Locator (URL) for a Web page, graphic, or other resource. Depending on the display format that you choose, it displays either a hyperlink that can be clicked to access the resource, or a graphic instead of the URL for the graphics file.

To display the URL as a hyperlink, select the Hyperlink format. When people enter information about an item, they can enter the URL and descriptive text that appears in the column, instead of the URL. To display a graphic, instead of the URL for the graphics file, select the Picture format. When people enter information about an item they must enter the complete URL for the graphics file, such as http://www.example.com/image.gif, and they can optionally enter descriptive, alternative text for the graphic, which appears for people who turn off graphics in their browsers or rely on screen-reading software to convert graphics on the screen to spoken words.

Column Types: Person or Group

The People/Group field is used to link specific people to a task or list item. This field looks within the Active Directory and within the SharePoint Groups, the information of which ultimately is updated through Banner. Through workflows, the information in this field can be used to email information from SharePoint directly to specific people. Most prominently, this field is used for the “created by”, and “modified by” fields for all lists, and for the “assigned to” field for task lists. One caveat with this field is that one cannot sort or group by this field if “allow multiple selections” is activated.

Here is what Microsoft has to say about this field:
Use this column field type to provide a searchable list of people and groups from which people can choose when they add or edit an item. For example, on a Tasks list, a Person or Group column named Assigned To can provide a list of people that a task can be assigned to. The contents of the list depends on how directory services and SharePoint groups have been configured for the site. To customize the contents of the list, you may need to contact your administrator.

You can customize a Person or Group column in these ways:

Allow multiple selections Allow people to select as many options as they like or limit the number of selections to only one option.

Include or exclude groups of people Specify whether the list includes only individual people, or additionally includes e-mail distribution lists and SharePoint groups. For example, on a Tasks list, you might want to include only individual people to ensure that a specific person is responsible for each task. On a Projects list, you might want to include e-mail distribution lists and SharePoint groups to ensure that a team is associated with each project.

Limit the list to site users only Specify whether the list includes all people and groups in the directory service or only those people and groups who have access to the site as members of a SharePoint group.

Specify which information to display Choose which information you want to display about people or groups. For example, on a Contacts list for a large organization, you might choose to display a person’s name, picture, and details such as skills and expertise. On a Contacts list for a small team, you might choose to display only a person’s name or e-mail address.

Column Types: Yes/No

If all you need is a simple checkbox to be checked for yes or unchecked no, then the Yes/No column type is your friend. We used this one for the Keystone Happenings list, where we wanted certain documents to be “promoted” to the top of the list, and other items are shown below that. That way all the instructions can be shown at the top of the list, and all the happenings can appear below. The completed field within a task item often acts as a Yes/No field as well. When you export the list out to an Excel file, it will show TRUE or FALSE in the column.

Here is what Microsoft has to say about this column type:

Use this column field type to store true/false or yes/no information, such as whether someone will attend an event. A Yes/No column appears as a single check box when people enter information about an item. To indicate Yes, team members select the check box. To indicate No, team members clear the check box.

The data in a Yes/No column can be used in calculations for other columns. In these cases, Yes is converted to a numeric value of one (1) and No is converted to a numeric value of zero (0).

You can customize a Yes/No column by choosing a default value for it. A default value is the selection that appears automatically when someone adds a new item. People can select a different value if they need to do so. For a Yes/No column you can specify whether the check box is selected automatically, indicating a Yes value, or not, indicating a No value.

Column Types: Date/Time

The date/time field allows you to show the date, the time, or both. You also have the option of choosing standard date display, or the “friendly” date, where SharePoint tells you about things that happened or will happen yesterday, tomorrow, Saturday, last Tuesday, etc. I personally prefer to see actual dates, so I generally turn off the “friendly date” feature and choose standard. However, if you find it useful, please use it in your SharePoint environment.

Every list has a date field for created or modified dates. Task lists have dates for start and due dates. The announcement list on the DSA Home site has an expiration date, linked to a filter so that old messages won’t continue to show. On that list, we added a calculation as the default value so that each announcement would have an expiration date and so the expiration date field is not blank by default.

Here is what Microsoft has to say about the date/time field:
Use this column field type to store calendar dates, or both dates and times. The date format varies based on the regional settings for the site.

You can customize a Date and Time column in these ways:

Include only the date or both the date and time Specify whether you want to include only the calendar date or both the calendar date and time of day.

Display a default value Automatically display a specific date or date and time when someone adds a new item, while also allowing people to enter a different value if they need to do so. A default value helps people enter information faster. For example, if the column stores the date when an expense is incurred and most expenses are incurred on the first day of the fiscal year, you can specify the first day of the fiscal year as the default value. As a result, that date appears automatically when a new item is added to the list, and team members do not have to enter the date.

A default value can be a value that you specify, the date an item is added to a list or library, or the result of a calculation, which is called a calculated value. Calculated values are helpful when you want to display a specific date or time automatically but the date or time might vary depending on the item. To use a calculated value, you enter a formula as the default value. The formula can calculate a value based on information in other columns or system functions such as [today], to indicate the current date. For example, if you want the column to display a date that is 30 days after the current date, type the equation =[TODAY]+30 in the Calculated Value box.

Column Types: Number & Currency

I don’t really use the currency field much, but then I haven’t had much cause to. I generally use the number field. Both of them have their uses, and apparently currency provides a slightly higher degree of accuracy. Each list will automatically have one number column called ID. This is so that SharePoint can differentiate one line of data to the next.

Microsoft has this to say about Numbers and Currency columns:

Tips for choosing a Number or Currency column

Both the Number and Currency column types store numerical values.

  • Use the number column field type to store numerical values that are not monetary values, or to store numeric data for mathematical calculations that are not financial calculations or do not require a high degree of accuracy.
  • Use a currency column to store numeric data for financial calculations or in cases where you do not want round numbers in calculations.

Unlike a Number column, a Currency column is accurate 15 digits to the left of the decimal point and 4 digits to the right. Both the Number and Currency column types provide predefined formats that determine how data appears.

You can customize a Number column in these ways:

Specify minimum and maximum values Limit the range of numbers that people can enter. For example, if the column stores the amount of work that has been completed for a task as a percentage, you can specify zero as the minimum value and one hundred as the maximum value. Similarly, if the column stores the number of attendees for an event and you want to limit attendees to a specific number, you can enter the maximum number of attendees as the maximum value.

Include decimal places Specify whether the numbers contain decimal places and the number of decimal places to store. If the column may need to store numbers that have more than five decimal places, you can choose Automatic when selecting the number of decimal places to include. Automatic is also a good choice if the column stores the results of calculations and you want the result to be as precise as possible. However, if you want to ensure that all values in the column have the same number of decimal places, it’s a good idea to limit the number of decimal places to zero, for whole numbers only, or another number of decimal places through five.

Display a default value Automatically display a specific number when someone adds a new item, while also allowing people to enter different numbers if they need to do so. A default value helps people enter information faster. For example, if the column stores the number of computers that each team member has and every team member has at least one computer, enter 1 as the default value. As a result, 1 appears automatically when a new item is added to the list, and team members do not have to enter the number.

A default value can be either a number that you specify or the result of a calculation, which is called a calculated value. Calculated values are helpful when you want to display a specific number automatically but the number might vary depending on who added the item, when the item was added, or other reasons. To use a calculated value, you enter a formula, instead of a specific number, as the default value. The formula can calculate a number based on information in other columns or system functions.

Format the number as a percentage Indicate whether to display and store the number as a percentage, and to treat it as a percentage when it is used to calculate other values.

You can customize a Currency column in these ways:

Specify minimum and maximum values Limit the range of currency values that people can enter. For example, if the column stores reported expenses for an event and your organization limits reporting to amounts within a specific range, you can specify those limits as the minimum and maximum values.

Include decimal places Specify whether values include decimal places and the number of decimal places to store. For currency values, you can choose two decimal places or, if you prefer to store only round values, you can choose zero. For non-monetary values used in calculations that require a high degree of accuracy, you can choose to limit the number of decimal places to zero through five or, for more precise values, choose Automatic to automatically use the appropriate number of decimal places for the result of the calculation.

Display a default value Automatically display a specific value when someone adds a new item, while also allowing people to enter a different value if they need to do so. A default value helps people enter information faster. For example, if the column stores expenses incurred for setting up new accounts and that expense is typically the same for all new accounts, you can specify that amount as the default value. As a result, that value appears automatically when a new item is added to the list, and team members do not have to enter the number.

A default value can be either a value that you specify or the result of a calculation, which is called a calculated value. Calculated values are helpful when you want to display a specific value automatically but the value might vary depending on the item, who added the item, when the item was added, or other reasons. To use a calculated value, you enter a formula, instead of specific text, as the default value. The formula can calculate a value based on information in other columns or system functions.

Choose a currency format Ensure that all of the values in the column are based on the same currency by selecting a specific currency format for the column.