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:
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.
“Save” isn’t as familiar/intuitive to non-SharePoint users as language such as “Submit” can be. Change “Save” to “Submit” by adding a script editor or content editor web part (CEWP) to the newform.aspx page for your list.
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.
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!
This tutorial works for any Microsoft-Flow connected social media platform, but we’ll specifically go through the steps for setting up a Twitter tweet and Facebook post submission system. We’ll be utilizing Microsoft Flow’s new “Approval” feature. Here’s our (and maybe your) scenario.
- We want to allow broader participation in social media content, while still maintaining a close grip on the quality and management of our platforms. This is more inclusive, increasing engagement and also giving you more eyes and ears throughout the organization while maintaining control
- Individuals will submit their ideas (can be via direct email to a list, a form, PowerApp, etc. – we’ll use a list)
- Social media manager or team will approve or reject submissions which will then be automatically posted to the applicable social media network if approved. See bottom of post for additional challenges to enhance this system.
Let’s get started!
This is a fairly simple solution that takes a date column, compares it to another date and gives you an answer in years (or days, or whatever you want). You’ll need to already have date columns to work with, and if comparing the date to today (years of age, membership, service, etc.) you’ll need a today column (hidden from the view above). This previous post will help you rig a “today” column that is always accurate without needing to update list items manually. Of course all of the following solutions work for any two dates (i.e. day span of vacation request), I’m just sharing specific examples that would involve “Today”.
Perhaps you, like me, built an exciting Microsoft Flow workflow and let it go into the wild without much additional thought. But at some point, you drop a lookup column into the mix and your Flow stops working. It tells you the field is not supported in query, even if that specific field isn’t being utilized in the Flow. I believe this has something to do with REST, but we won’t dwell on the cause – let’s get to the workaround.
The scenario I’ll be using is my cross-site publishing alternative using Microsoft Flow where I’m basically copying data from list items in one site collection to create new list items in a different site collection. This is helpful when someone does some sort of data entry once, and other people are then entering much of the exact same data. This copies all of the overlapping data to a new list item for the second site collection to reduce duplication of work.
It sounds simple but with a lookup column in the destination list we get the error. For this I’ll be using SharePoint Designer and Microsoft Flow (of course) in combination, though you could certainly try it all in Microsoft Flow. I just find parts of the process simpler in SPD. And while your origin data may be different (MailChimp, Twitter, etc.), and your exact scenario may differ, this workaround should still have value in concept.
Update 12/8/17: There’s a better way to do this using Microsoft Flow, if that’s available to you.
You may, at some point, find yourself working with calculations among dates, including “today’s date” which conceptually seems simple but requires a bit of work to function correctly. You may have even created a “today” column that defaults to “current date” or attempted a calculated column only to find that the date will not automatically update each day or that calculated columns cannot show dynamic data like that. Fret no more.
Today columns are essential for use in calculated columns that tell you things like “days until event”, “days without incident” or “years of service” without needing to click any buttons or take any additional steps. Here are some ideas for how you can use the “Today” column in calculations. We’re going to create our solution via SharePoint designer workflow and a new Today column.