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.
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”
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.
Date calculations using “Today” in SharePoint lists for years of service, days without incident, etc. (includes using blank date values)
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”.
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.
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”
Automatic iCal (.ics) hyperlinks for SharePoint calendar items using calculated column
Updated 12/22/19
Important: This concept of using calculated columns will only work as a one-time trick. After adding the column, links will be correct. But because [ID] isn’t meant to be used in calculated columns, it removes itself from the formula if an item is edited after the column has been added. This method is only ideal for one-time uses, or for lists that are not expected to have content additions or changes. See this other post about how to get the same hyperlinks through workflow instead, for a more reliable method.
This project allows users a quick option to save something they see on a SharePoint calendar to their own calendar. Spend 5 minutes on these few instructions and your users will have a convenient way to get involved going forward.
Note that in modern calendar experiences, there’s a built-in “add to calendar” option for event items. This post is for classic experience calendars and calendar items/events.
Basically we’re going to add a calculated text column called iCal which will use the list’s GUID (easy to get, don’t worry) and the specific calendar item’s default ID number to generate a clickable .ics (iCal) file link. Let’s get started!
Adjust SharePoint list column widths in classic view using jQuery
This is a popular request that I’ve recently modified, thanks to the recommendation of a colleague, to be responsive to different device or browser sizes. By default, SharePoint lists respond to their viewer’s size but once customized with a fixed pixel width will lose that feature. This solution utilizes percentages so you can create column widths ideal for any screen.
Update 6/18/19: I’ve recently shared how to adjust widths using CSS and it covers standard AND datasheet views, while the method below is written only for standard views (but could certainly be adapted for datasheet, I imagine).
Continue reading “Adjust SharePoint list column widths in classic view using jQuery”
Remove select/de-select checkmark column, column headers, edit/new options, and border in SharePoint list view web parts
In a recent post, I discussed hyperlinking URL title text and adding tooltips. I used the above screenshot which also illustrates what we’ll accomplish in this post which is to remove the select/deselect checkmark column, column headers, toolbar edit/new options and chrome border. This leaves us with a simple title and list. Pick and choose what you’d like to remove for your specific needs, and let me know if you run into any issues.
Continue reading “Remove select/de-select checkmark column, column headers, edit/new options, and border in SharePoint list view web parts”
Highlight specific column headers in a SharePoint list
Highlighting specific column headers can be useful in drawing attention to specific information areas to make sure they’re complete or just to aid users in seeing the most important info quickly.
You can insert this script in a Script Editor webpart or save it in a css file in your Site Assets and reference the link in a content editor web part on the list page (much more accessible and easy to edit later).
Simply replace “Column1” with the exact name of your column header. Repeat the script as many times as you wish for each column header you want to highlight. Remember to change the script if you change a column name. And if this yellow isn’t your thing, check out this color wheel to help pick a new color code to use in your script.
[code]
<style>
.ms-vh-div[DisplayName="Column1"]
{
background-color:#FFFF66;
}
.ms-vh-div[DisplayName="Column2"]
{
background-color:#FFFF66;
}
</style>
[/code]
Simplify site-wide SharePoint list and form revisions by utilizing lookup columns
You may have several forms or lists using dropdown menus across your site that you would have to update if, say, an individual resigned, or a department changed its name, or a building relocated. Manage this type of information (individuals, departments, buildings, etc. frequently used in lists and forms) in separate lists that we’ll then use to create site-wide lookup columns to replace the many individual dropdowns across our sites that are repetitious. Basically, we’ll update the information in one place and know that it’s updated everywhere it’s needed across our site (or site collection if you’re familiar enough to go the extra mile with collection content types or Microsoft Flow, if your permissions aren’t at the site collection level).
Continue reading “Simplify site-wide SharePoint list and form revisions by utilizing lookup columns”