Advertisements

How to increase lookup column limit in SharePoint lists

Lookup columns include workflow status columns, traditional lookup columns to other lists, and person/group columns. This includes the two default people fields “Created by” and “Modified by”. If your list has more than 12 of these, you may receive the following error:

“This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (12) enforced by the administrator.”

list view threshold.PNGIn SharePoint Online, you’re not able to increase the lookup column limit. Lists created prior to the June 2013 CU update are capped at 8 lookup columns, while those afterward are allowed 12.

However, on-premise SharePoint (server) allows you to change this limit to your heart’s content.

Advertisements

5 ways you can use SharePoint list and library view settings to improve the user experience

SharePoint has many out-of-the-box (OOTB) ways to improve the way data is displayed in lists and libraries. Many of these can alleviate headache your users experience when adapting to a new way of working with their abundance of ever-growing information. Let’s check out a few things you can do right now, in less than five minutes:

  1. Prevent horizontal scrolling by carefully selecting displayed columns
  2. Sort items appropriately
  3. Filter to relevant info per view
  4. Group items into logical “buckets”
  5. Adjust item limits

Use Microsoft Flow to get the number of items in a SharePoint list or library

Sometimes you need the number of items in a list or library for reporting, notifications, or just curiosity. The following details three methods you can use to get the count of items for different purposes.

  1. Use Microsoft Flow to get the number of items and use in various ways
  2. Add “count” to the top of a classic view SharePoint list for all to see
  3. Quickly find “count” just for your information in site contents or list settings

(Video at bottom of first section)

The “Edit” icon column: a SharePoint essential for all lists and libraries

Asset 2edit

Yesterday in a SharePoint 200 session I gave at Lawrence Memorial Hospital, I shared one of my favorite SharePoint “nuggets” which is the “Edit” icon available out-of-the-box, and easily added by any level of user in just 4-5 steps.

This edit icon column can be added to any list or library view in SharePoint and allows you one-click access to edit the properties of a document or edit a list item or form. In addition to that it’s security-trimmed, meaning only people who have edit/contribute permissions will actually see the icon at all. Everyone else will only see an empty column.

To add the column, you must have the ability to create or modify views and list settings.

Note this is only available in classic view lists and libraries. In the O365/Modern experience you can simply select an item, click “Edit” and the right edit pane appears to allow a similar experience.

How to resolve SharePoint list view threshold error: “The view cannot be displayed because it exceeds the list view threshold…”

SharePoint lists have a default limit of 5,000 items per view. But lists can contain 30 million items (just not all available in one view). Since you’re reading this, perhaps you’ve already learned this from an error message such as:

The view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.

First of all, when in doubt, refer to the documentation provided by Microsoft. Read it carefully to understand limitations in your specific environment, explanations of various actions and rules and the permissions required to correct the issue.

Here’s my summarized version:

Using today’s date and/or current time in calculated columns and list view filters

I previously shared how to create a “Today” column in SharePoint that would always be up-to-date even if list items weren’t modified. These were no-code solutions that utilized either SharePoint Designer or Microsoft Flow. You can, however, use Today’s date/time to create views and calculated columns without workflow or script or the need to create another column.

Solution: Converting lookup values to text and working around SharePoint error “lookup list is in another web”

notsupportedinquery.PNG

Lookup columns aren’t friendly to a lot of things. Power BI reports, calculated columns, creating new items via workflow when both lists have lookup columns, if/then statements, etc. Especially when your lookup column is looking up to a list from another site, not the same subsite in which you’re working.

A previous scenario required that I create a new item in a different site’s list when conditions were met in the origin site’s list item. Both lists used the same lookup column, and I received the “lookup is in another web” error when trying to do a direct copy via workflow, from lookup column to lookup column. The solution ended up being creating a new item in a temporary, lookup-free list that received the lookup values just as text. Then SharePoint Designer copied those over to the final list, which received the text and happily converted it back to the appropriate lookup values. See the full solution here.

This post will focus on the same error message, but this time is triggered by a SharePoint Designer workflow in a different scenario where we just want to convert our lookup values to text so we can use them for various purposes.

To save you time, I also tried (and failed) at these potential solutions before finding success:

  • Setting workflow variables to the lookup values and trying to set the variables to text values, or use the variables in my if/then statements to create new text values (this defeats the purpose of using lookup columns, of course)
  • Using a number of combinations of Microsoft Flow and SharePoint Designer to get the data from the lookup column extracted then “pasted” back in as text

So let’s get to the solution. Feel free to comment with your scenario specifics – I’ve had a lot of experience with this error, and would be happy to help.

Automating item-level permissions in SharePoint document libraries and lists

workflowitemlevelpermissions

Item-level permissions come in handy for a number of situations. Here are some examples and food for thought:

  • Travel plans are submitted to a list, but only those in people columns (supervisor, director, traveler) are allowed to see or find the plan by search.
  • Allow “content owners” to edit documents, and everyone else to view only.
  • Allow non-admin individuals to set editing permissions for documents or list items by populating a people column

Using a SharePoint Designer 2010 Workflow and an impersonation step, we can:

  • Add list item permissions
  • Inherit list item parent permissions
  • Remove list item permissions
  • Replace list item permissions

This tutorial will use the “replace list item permissions” action. Whenever you’re replacing permissions, you must remember to INCLUDE YOURSELF or admin individuals in the replacement permissions or you won’t be able to access the content or help with troubleshooting. Let’s begin!