Add a thumbnail column for documents and media in a SharePoint Online document library

Mark Rackley recently tweeted about the ability to create a calculated column in SharePoint online document libraries that would automatically render thumbnails for documents. In the GIF from his tweet, it shows how this works for media files.

Naturally curious, I had to see how this worked for documents of .docx, .pdf, .pptx, etc. types. What I found is that it only currently supports some file types:

Supported file types (there’s likely even more I didn’t test):

  • Word (.docx)
  • PDF (.pdf)
  • Emails (.msg)
  • Images (.png, .gif, .jpg, etc.)
  • Media (.mp4)

Not-yet-supported file types:

  • Excel (.xlsx)
  • OneNote (.one)
  • PowerPoint (.pptx)

Create a thumbnail column in SharePoint Online document libraries

1. Add a new column to your document library (library settings > Create column).

2. Set the column name to Thumbnail. As for type, you have two options:

  • Leave type as Single line of text. Thanks to Dario Cassinerio for sharing that Single line of text type works as well as (and more simply than) Calculated set to [Title].
  • Mark Rackley suggests sticking with Calculated set to [Title] (see example screenshot) to prevent users from editing the text field in forms.

3. Click OK.

Supported file types will have thumbnails rendered (like .docx and .pdf in the example below) and others will just be blank (like .pptx and .xlsx in the example below).

Click to enlarge

Here is an animation demonstrating the entire process, start to finish using Single line of text as column type:

Click to enlarge

And another animation but using the Calculated column set to [Title] type:

Click to enlarge

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

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>

Change “Specify your own value” label to “Other” in SharePoint choice columns

In classic list views/forms and on-premise environments, your choice columns allow you let users specify their own value. But the label is literally “Specify your own value:”. This can be changed.


Note: If you’re using the modern experience, you don’t have this issue.


To change the label, add the following script to your newform.aspx page, changing the name of the field referenced (see below). You can add this script in a script editor web part as a snippet or save it as a .js file to your Site Assets and reference it in a content editor web part.

To get the actual field label, use F12 (developer tools) when viewing the form and select the checkbox. Copy everything after the ‘#’ sign or whatever the column name is, ending in ‘FillInRadio.’

Once you’ve added that script (and updated the field name with your own), save the page and you’ll see your new label:

Using current date and/or time as default column value in SharePoint

Date and time fields can have a default value of “Today” or “Now” that populates if you don’t enter another value in the field before saving an item. In your date/time column settings: 

  • If you choose type “Date only” you’ll get the current date
  • If you choose “Date and time” you’ll get the current date and time

You can instead check the box for “Use calculated value” and use “=Now()” as the formula to return the exact same result. However, the benefit/difference of using the calculated option with =Now() instead of the “Today’s date” option is that if your users change from “Date only” to “Date and time” type down the road, “=Now()” will include both date and time values already. It doesn’t hurt to have time included and not displayed.

Finally, you can use the calculated value option to do true calculations for things like “Due date” or “Reminder” where you use a formula like:

  • =Now()+7 is exactly a week from the current date and time
  • =Now()+(1/24*n) where you’ll replace “n” with a number of hours from the current time

Note that these values will be static, meaning once the date/time populates, it doesn’t update when you edit the item. It’s merely a default value inserted upon creation if you don’t manually enter a different value.

Also, when using Now() with hourly calculations you’ll want to double-check your time zone settings (Site settings –> regional settings) and adjust your formulas accordingly if you’re unable to identify a fix.

If just using “Date only” type, you can instead use =Today() and not worry about time zone so much unless your calculations will involve units less than one day.

Related topics:

If you need a “live” always-updated value regardless of items being modified, you’ll need to create a separate “Today” column using Microsoft Flow or SharePoint Designer:

This post also includes ideas for calculations using today’s date.

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.

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

Use Microsoft Flow to create a “today” column for use in SharePoint list calculations

Note: I previously shared how to do this in SharePoint Designer. The following method utilizing Flow is better, and does not use loops/pauses.

It’s well-known that SharePoint calculated columns don’t permit [Today] to be used as a formula for a calculated date column. And the “default to today’s date” setting only works upon creation, and doesn’t update daily. But we can create a standard date column and have Microsoft Flow automatically update it daily for us, therefore allowing us to effortlessly perform calculations against today’s date such as:

  • Age =(TodayDate-Birthday)/365
  • Years of Service =(TodayDate-StartDate)/365
  • Days Past Due =(TodayDate-DueDate)
  • Weeks until summer break =(SummerStart-TodayDate)/7

Here’s how to create your own, always accurate/updated, today column (see bottom of post for video):

Continue reading “Use Microsoft Flow to create a “today” column for use in SharePoint list calculations”

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.

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

Date calculations using “Today” in SharePoint lists for years of service, days without incident, etc. (includes using blank date values)

calculations

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”.

Continue reading “Date calculations using “Today” in SharePoint lists for years of service, days without incident, etc. (includes using blank date values)”

Creating a “Today” column in SharePoint that always gives today’s date

Update 12/8/17: There’s a better way to do this using Microsoft Flow, if that’s available to you.

update7

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.   Continue reading “Creating a “Today” column in SharePoint that always gives today’s date”