SharePoint column validation format difference between classic and modern experiences

The following column validation formula worked fine in SharePoint on-prem (2016 specifically, in my case), but returned an error when used in the exact same context in SharePoint Online’s modern UI:

EndDate<=Today()

The expected behavior, in SharePoint Server/on-prem OR SharePoint Online/O365, is that if someone enters a date beyond the current date, they’ll get an error message and cannot submit the form until it’s corrected and the validation formula resolves to TRUE.

Troubleshooting in SharePoint Online

I used this formula in SharePoint Server/on-prem, and it worked fine. Then I tried using the modern UI in SharePoint Online by using the column’s menu > Column settings > Edit.

But when you try to save the exact same formula (specifically from the modern experience side panel) you get the error “A formula has a syntax error.”

Then I decided to try the classic view of settings to compare on-prem and online as closely as possible. I went to Settings > List settings and selected my column.

And, as you already know, it WORKED when entered on this classic column settings page (in SharePoint Online still) instead of the modern column settings side panel accessed directly from the list view.

When I go back through the modern UI now that my formula saved successfully, I see what caused the problem. The modern UI requires that you begin the formula with an equals sign (=). When I created the formula through the classic column settings method, it automatically added the equals sign for me in the background.

Solution

So if you’re creating column validation formulas in the modern experience (or even in classic), just remember to add an equals sign (=) to the start of your formula.

Click to enlarge

The difference is simply which formats are accepted.

  • Classic: Start formulas with or without equals sign
  • Modern: Start formulas with equals sign

Create a direct link/URL to version history for a SharePoint list item or file

Imagine getting an email with a direct link to review the version history of an item or file. Or being able to have a single click from a list view to an item’s version history without going through menus.

The link itself is easy to structure manually. You could also structure it automatically using SharePoint Designer workflows or Power Automate flows.

No matter your method, you’ll need to be able to get two pieces of information: the list GUID and the item/file ID.

Getting required info

Get the list GUID

The list GUID is easy to get. Just go to the list –> List Settings and copy everything in the URL after List=.

Click to enlarge

Example:

http://SiteURL/_layouts/15/listedit.aspx?List=%7BAC2DE34F%5GH6IJ%KL789M%N01OP2%QR3STUV4WX4YZ56%7D

Get the item/file ID

In SharePoint Designer or Power Automate, you can just use the ID field provided as a lookup option. This way it’s always dynamic.

But to get the ID manually without workflow, view/open the item and check the end of the URL: for the number following “ID=”. Example:

http://SiteURL/ListName/DispForm.aspx?ID=532

For document libraries, or if you’re using modal dialogs (pop-ups), your URL won’t show ID. In this case, you can add the default ID column to your list view to get it.

Tip: For list items (not documents) you can also hover your pointer over the link to the item to see the URL preview at the bottom of your browser before clicking it.

Building the link

Manually construct it

The basic structure, no matter which method you use, is as follows:

https://SiteURL/_layouts/15/Versions.aspx?list=GUID&ID=ID

Just replace the SiteURL, list GUID, and item/file ID.

Tip: The easiest way may be to copy the entire URL when getting the list GUID, change listedit to versions, and add &ID=ID to the end.

Use SharePoint Designer

In SharePoint Designer, you can use String Builder when creating hyperlinks in emails, or when populating fields (such as a hyperlink column).

Click to enlarge

Use Power Automate

In Power Automate, you can initialize a variable to structure the version history URL so that you can use it repeatedly in different steps/conditions without having to structure it every time. We do this similarly to how we did for SharePoint Designer above. Type almost the entire URL which doesn’t change, then use the dynamic content panel to add ID to the end.

Click to enlarge

Then you can send an email and use a little code to hyperlink text with the variable used as the URL. If you’re not comfortable with code, you can just put the entire URL in the body of the message. The following shows an Outlook step in “Code View.”

First, click “Code View”

Alternative ideas

Calculated column

Note: You could use a calculated column for this, but the [ID] field needed will only populate once, then remove itself on the next property edit. The only way around this is creating another column like ID1 as single line of text and using a workflow (SPD or Power Automate) to set that field to the ID number. Then you can use [ID1] in your calculated column with a formula such as this:

="https://site/_layouts/15/Versions.aspx?list=GUID&ID="&[ID1]

Hyperlink column

Consider using SharePoint Designer or Power Automate to set a “hyperlink” type field to the URL. This could then be used in a list view to have a one-click link to version history.

Adjust column widths in classic SharePoint lists using CSS (including “Quick Edit”/datasheet views)

Once upon a time two years ago (two years?!?) I shared how you can adjust SharePoint column widths in traditional views using JQuery.

But adjusting the same widths in datasheet mode (quick edit, for example) is a bit different. After a bit of fiddling around, I found an answer that will allow you to adjust column widths for both standard and datasheet view types using just CSS.

The difference is in how you reference the column names in the css:

  • Standard (catches both filterable and non-filterable columns such as multi-line text): th.ms-vh2-nofilter div[DisplayName=’Column1′],th.ms-vh2 div[DisplayName=’Column1′]{…
  • Datasheet: th[Title=’Column1′] {…

Everything that follows that first line is the same in both types of views, fixing a minimum and regular width property for the column(s).

Standard Views (not quick edit)

<style>
th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

Datasheet/Quick Edit Views

<style>
th[Title='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

All Views

So if you’re including just one script reference for all views in your list, you’d be safe to include all formats in the script.

<style>
th[Title='Justification'],th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
</style>

Multiple columns

For multiple column width adjustments, just include another block for each column as seen here:

<style>
th[Title='Justification'],th.ms-vh2-nofilter div[DisplayName='Justification'],th.ms-vh2 div[DisplayName='Justification']{
    min-width:500px!important;
    width:500px!important;
}
th[Title='Other Column'],th.ms-vh2-nofilter div[DisplayName='Other Column'],th.ms-vh2 div[DisplayName='Other Column']{
    min-width:200px!important;
    width:200px!important;
}
</style>

Set and manage notification alerts on SharePoint discussion boards, lists, & libraries

In both modern and classic SharePoint experiences, you can navigate to any discussion board, list, or library and set a custom alert just for you (or for others if you’re an admin) to get emails on new items, modifications, and more. All of the setup and management guidance below is classic, but modern is almost exactly the same except for how you initially access the “Alert me” options.

Set up a new alert

First, navigate to the list or library. Once there, you’ll see “Files/Items” and “Library/List” at the top. In the example below, we’re setting an alert on a document library so our options are “Files” and “Library.” Click the second option for the library, then “Alert Me.”

Select “Set alert on this library/list”

In the first part of the dialog, choose the Subject line of the email notifications (Alert Title), who should get it (just you if non-admin), and how they should get it. Your administrators may not have text messaging set up, so that may not be an available option.

Finally, choose what you care about (new items, modifications, deletions), the context/criteria for notifications, and how frequently you want emails (immediately, daily, or weekly). I find the daily option is great for discussion boards so users aren’t overwhelmed with back and forth discussion and can instead read a digest and catch up in a single email.

Click OK.

When ribbon menu isn’t there (classic)

Sometimes when you’re on a page with multiple web parts, you won’t see the Files/Library or Items/List menu options. This is because you need to “select” the relevant web part on the page by clicking in “white space” on the web part. The ribbon menu should then appear. And unless settings were changed by your site owner, you can sometimes click the title of the web part to go to just that app where you’ll see the menu as well.

Modify existing alert (end-user)

From the email notifications you receive, you can just click “Modify my alert settings” to see all alerts you have on that particular site, and be able to delete them or edit the settings.

You can also get there the same way we started. Ribbon menu –> List/Library –> Alert Me –> Manage my alerts

Manage all user alerts (admin)

Go to site settings

Under “Site administration” select “User alerts”

Choose for whom you’re modifying/deleting the alert(s) and click Update

You’ll now see all alerts that user has set up on that site (or alerts someone set up on their behalf). From here, you can delete any unwanted alerts, or report back to the user or interested party on frequency and topics/lists with alerts.

Add a print button to SharePoint list items

One of my more popular requests is an easier way to print list items (either to PDF or paper). Using SharePoint Designer, we can add a “Print Item” button to the display forms for all list items. This button appears in the “Actions” section of modal dialogs AND standalone display forms in their own window. See above for an example of a modal dialog implementation.

You can do this rather quickly, but will need to be a site admin with access to SharePoint Designer to make the change.

Note: This only works for classic view lists.

  1. Download the print icon
  2. Add print icon to Site Assets
  3. Add a custom action to list

1. Download the print icon

2. Add print icon to Site Assets

  1. Open your site in SharePoint Designer
  2. Go to Site Assets and import the downloaded print icon (using import/browse OR drag-and-drop)

3. Add a custom action to list

  1. Open your list in SharePoint Designer
  2. Add new custom action for display form ribbon (upper left dropdown)
  3. Name it “Print Item”
  4. Set “Navigate to URL:” to
    javascript:window.print()
  5. Browse for the button image URL (I used 32×32) and select the print.png image you added to Site Assets
  6. Change “Manage” in Ribbon Location to “Actions” and click OK

    Ribbon.ListForm.Display.Actions.Controls._children

Test away!

Troubleshooting

I don’t see the “Ribbon Location (Tab.Group ID:)” option!

You need to double-check step two of “Add a custom action to list.” You must create the action from this dropdown field and specifically select “Display form ribbon” to get this option.

It’s not showing up!

  1. Make sure you types “Actions” (plural) and not “Action”
  2. Refresh your SharePoint list (Ctrl+F5 for a hard refresh)
  3. Make sure you’re using the classic experience. This does not work in modern. You can accomplish this by forcing the classic experience for all users (recommended for consistency):

If you’d rather let users choose, just be sure to educate them on the ability to switch back and forth.

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.

Continue reading “How to increase lookup column limit in SharePoint lists”

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

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

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)

Continue reading “Use Microsoft Flow to get the number of items in a SharePoint list or library”

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.

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

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:

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