Using Content Types to Hide Edit Form Fields in a List

I was recently asked if I could make some list fields read-only because a department was having problems with users filling out fields they weren’t supposed to. They attempted to control this by creating a column called “END OF CUSTOMER INPUT SECTION. INTERNAL USE ONLY BEYOND THIS POINT.” One of the first rules I’ve learned when helping users leverage the power of SharePoint is “What the customer WANTS is not necessarily what the customer NEEDS.” Although the customer was requesting that Permissions be used to control who can edit what fields, I knew that wasn’t the solution, not the least of which because it isn’t even possible to set permissions at the column level. So how did I approach this?

First I looked at the list settings. 112 columns. Yikes! This many columns would be a nightmare to fill out. Imagine creating a new list item and being presented with 112 fields. No wonder users ignored warnings and continued to fill out the form. After filling out a mind-numbing 75 fields what’s another 35 fields?!

Next I asked how this list was used and who were the users who interacted with the list (the audience). The process was explained this way: a user requests a new project by filling out a new list item. The list manager is notified via an Alert that a new project was requested. The list manager assigns the project to a Project Liaison, who works with a finance person and a design person who will input additional information. With 4 user groups identified, we then placed them into four functional areas:

1. Customer

2. Finance and Approvals

3. ADT Design Status

4. Project Status

What Fields do the Users Need to See?

Now that we have identified 4 user groups, the next question is “What fields do each group need to see?” I asked for a list of column names, in the correct order, for each user group. With that in hand I determined that the best approach to meet the customer’s NEED (only display certain columns to a certain audience) was through the use of Content Types.

Creating Content Types

Here is how to create a Content Type:

1. Site Settings -> Site content types

2. Click Create

3. Give it the name of one of your user groups

4. Select “List Content Type” and “Item” under Parent Content Type (because we are creating a list Content Type).

5. When you create your first Content Type, create a new group with a period in front of it (.Demand in the example above). This way the new Content Type will sort to the top of the Site Content Types page. (Thanks to Susan Hanley for this tip!)

6. Click OK

Do this for each of your user groups.

Configuring Our List to Use Content Types

1. List Settings -> Advanced settings

2. Select “Yes” under “Allow management of content types?”

3. Click OK

Adding Content Types

From the List Settings page, click “Add from existing site content types”

Select .Demand from the dropdown to see the Content Types you just created. Add them one by one. Click OK.

Assign List Columns to Content Types

1. Click on a Content Type

2. Click “Add from existing site or list columns”

3. Select columns from: List Columns

4. Add the appropriate columns then click OK

5. Repeat for each Content Type

You can click “Column order” if you need to change the order of a column.

Because the Customer user group will be creating a new list item, make “Customer” the default content type by clicking on “Change new button order and default content type” and changing the Position from Top to 1:

Wrapping Things Up

With the Content Types added, this is the new user experience:

1. A Customer creates a new Item. They are presented with only the fields they need to fill out (37 fields instead of 112)

2. The Item is saved as a Customer content type. When a member of another user group edits the item it will open up in the Customer content type (or whatever the content type happened to be when the item was previously edited and saved). Users are trained to select the appropriate Content Type from the dropdown list in order to see their pertinent fields:

The “Item” content type is still available for the list manager who needs access to every field.

To improve the user experience we can add instructions to the default display form:

1. From the List tab click the Form Web Parts dropdown and select Default Display Form

2. Add a Content Editor web part and add the appropriate instructions. This is what mine looks like:

Now the user experience is much friendlier with only the necessary fields displayed!

by Darrell Houghton via Everyone's Blog Posts - SharePoint Community

Working with Large Lists

Recently I have been working with large lists (10,700+ records) on a site where I only have Site Owner privileges. SharePoint protects server performance by query throttling lists above 5,000 records, although this limit can be adjusted in Central Administration.  When a user attempts to view the list he will see the following message:

Because of the extra work required by the server to query a large list, work on the table is restricted to a maintenance window defined by the administrator, and is generally outside of regular business hours.

It’s no fun working outside of work, so how can we work on a large list like this during the day? Enter the magical world of indexing.

Indexing

It is instructive to understand how indexing affects throttling. From MSDN:

The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database. For example, suppose you are working with a list that contains 10,000 items. If you were to build a query that returns the first 100 items sorted by the ID field, the query would execute without issue because the ID column is always indexed. However, if you were to build a query that returns the first 100 items sorted by a non-indexed Title field, the query would have to scan all 10,000 rows in the content database in order to determine the sort order by title before returning the first 100 items. Because of this, the query would be throttled, and rightly so—this is a resource-intensive operation. http://ift.tt/1Dh32Bi

So in other words, consider how your records can be filtered. If there is a column you can use to filter records to return less than 5,000 rows, then you’re gold! Just make that column an indexed column. Of course, because creating an index requires accessing all items in the list, you’ll have to wait for the maintenance window to create your Indexed Column.

How to Create an Indexed Column

From List Settings click on the “Indexed columns” link under the Columns section.

The next screen will show you any indices you have previously created. You can create up to 20 indices. However, each additional column index requires storage space in the database and adds some overhead to every operation to maintain the index. So you should add indexes only to columns that will be used actively for filtering in views.

Click on “Create a new index” to get to…

If you have a particularly large list you can create a secondary column to create a compound index. Compound indexes can enable you to speed up queries across related values. However, Indexing more than one column may not get the boost in performance you are expecting when using more than one indexed column in a query. You should choose your indexed columns carefully to maximize query performance while avoiding unnecessary overhead; generally you only want one index column per view/query.

How to Use an Indexed Column in a View

Once your indexed column is created you can now create views off of this column and avoid the list view threshold error message. You will utilize either the Sort or Filter sections of the Edit View properties screen.

Sort

Select an indexed column as the first sort field: 

As mentioned in the MSDN quote, the ID column is indexed by default, so as long as the Item Limit is less than 5,000 items you won’t receive the list view threshold error message.

Filter

You can also filter by an indexed column. Select “Show items only when the following is true”, then select your indexed column in the dropdown window. As you can see, indexed columns are tagged as “Indexed” under the “Indexed Columns” heading:

Next you need to specify your filtering equation:

In my case I selected “is equal to” and entered the appropriate number:

You can filter on additional equations and/or columns.

Create additional views to expose your data in chunks smaller than 5000 records.

Summary

Large lists can be intimidating if you don’t know the proper way to configure them. Remember the magic of using indexed columns and you’ll be set to shine when tasked with taming a large list.

by Darrell Houghton via Everyone's Blog Posts - SharePoint Community

SharePoint Lookup Columns and the Mystery of the Missing Records

I was tasked with updating some list data, including changing a text field column to a lookup column. The lookup list was located in another subsite, and since a lookup list only works with lists within the same root site or subsite, I created a root level Site Column with the information I needed. This made the data available to all child sites. Then it was a matter of going to Site Settings -> “Add from existing site columns” and selecting the Site Column I just created.

Next I created a view with the text field column ("JSN") and my new Site Column lookup ("JSN Lookup") side by side, then put the view in Quick Edit. This made it easy to quickly update the lookup column with the data from the JSN column. Later I would delete the JSN column.

Everything was going peachy until I noticed I couldn't find some data in the lookup list. 

Where’s X4890.CM??

This was all very strange because I confirmed the data was in the lookup list. So why wasn’t it showing up in my drop down list?

I double-checked my lookup field in case I missed some configuration that was filtering out some of the list items. No luck. I checked the lookup list to see how I had the list sorted. The default view was to show everything by JSN sorted alphabetically. Hmmm. I was stumped. After some Googling I found a very simple answer:

By design, SharePoint sorts the lookup column by ID when in Quick Edit. When I edited the item using the Standard view, the column was sorted alphabetically.

There’s X4890.CM!

This is something to keep in mind if you or your users like to use Quick Edit.

by Darrell Houghton via Everyone's Blog Posts - SharePoint Community

"List does not exist" error message

Recently, when I went to view a list I ran into the following error message:

Silly Microsoft! My list isn’t deleted, I see it right here in my site contents. After brushing off the error message as totally missing the mark and unhelpful, I went to my go-to troubleshooting tool to find some helpful answers: Google. After some research it seemed that the culprit may be in the view configuration.

I noted that the list had a couple dozen different views. I went through each view and discovered that 2 of the views worked properly (more evidence that something was probably wrong with the view configuration).

This is how I went about troubleshooting the problem:

1. I made a duplicate of one of the views that did not work.
2. Then I systematically removed each column one by one from the display and tested the view until the view worked

The offending column was the following lookup column:

The “BP Room #” column was referencing a list that had been deleted. Next I investigated how to reconnect a list to a lookup column and discovered it wouldn’t be easy. I learned that the lookup column is connected by several properties:

1. WebID – This is the web location where the list is stored
2. ListID – This is the source list where the information comes from
3. ShowField – This is the field from the source list that is displayed

Once the lookup list is deleted, these properties are broken. In order to maintain the lookup field integrity, it makes sense there would be no way to (easily) repair the lookup connection. I did find a Powershell command here, but I don’t have farm access so this wasn’t an option. It looked like my only choice was to create a new lookup column. In my case this happened to a list with over 10,000 records, so there was extra work involved to make the list right again. Hopefully this won’t be a problem in your scenario.

Other Insights

I was thinking about the “List does not exist” error message I received and realized the message was referring to the missing lookup list. Mea culpa! Forgive me Microsoft! 

by Darrell Houghton via Everyone's Blog Posts - SharePoint Community

Error Received when Going to About Me on MySites

Dontcha just love it when an error occurs only for some people and not others? It makes for some challenging troubleshooting, but nothing too difficult for us seasoned SharePoint professionals!

The error occurred when a user would click on their About Me link, either in the Current Navigation or in the drop down under their name. They would receive the following error:

In my research, I found one blog post that claimed the culprits were unsupported characters (ie., ! @ # $ %) in the Ask Me About section of the user’s About Me section. The solution was to navigate directly to the Edit Details page by going to http://<mydomain>/_layouts/15/EditProfile.aspx?Interests=1 and deleting the offending character(s) from the Ask Me About section.

While trying this solution with a user, I was disappointed to see that she didn’t have any of those offending characters in her Ask Me About section. However, after navigating to the Edit Details page, she was able to navigate to her About Me page using the usual navigation links!

I’m guessing by going directly to the Edit Details page, SharePoint re-established the connection to the turbo encabulator, which we all know is necessary for the dingle arm to work correctly. However, if anyone else has a better explanation as to why this worked, please post in the Comments section.

by Darrell Houghton via Everyone's Blog Posts - SharePoint Community