Recently we had a project that deployed a simple, creative solution that we thought might be relevant for our readers.
The use case is managing agreements in a document library on SharePoint. We're going to deviate a bit from their specific use case so we can generalize it a bit. Our example today will be based on contractor agreements.
The premise is straightforward—each contractor and organization has a formal agreement valid for one year. Contractors are onboarded at various points of the year, so the dates are always unique.
We need a way to see agreements coming up for renewal to know which ones to contact to get the correct information before their expiration, as contractors with an expired agreement are not allowed on the job site.
To start, we've created a Contractor Agreements Document Library.
You can see that our document library has several metadata columns on it—this information gets entered when we upload the agreement upon signature. Here is a breakdown of the columns you see:
The field has been given a formula only to calculate the status = Active; otherwise, it will do nothing, which you can see in the screenshot's red box.
The formula for this is =if(Status="Active",[Expiration Date]-Today(),"")
These are all of the columns we need at this point. There could be others that you would add based on your use case. SharePoint provides various field types to choose from, so you can build out metadata around your document (or on your list, if you're building a list instead) as you see fit.
We want to embed some conditional formatting to highlight columns or the entire row based on some of that metadata. To do this, we'll click the drop-down next to the column on which we want to establish our conditional formatting. In our example, we want to do the following:
First, select the column of metadata you want to base your formatting on and click the drop-down next to the column title. Select Column settings and then Format this column, as shown in the screenshot.
A flyout will open on the right side, presenting two options: Format view and Format columns
First, we want to add the logic to highlight the column cell yellow if the day is within the 11-30 range.
To do this, click Manage Rules as seen on the screenshot above, ensuring the Choose Column has the correct column noted within it. A new menu will open on the flyout.
Click Add rule (not shown in screenshots) to display the rule logic and fill in the criteria. In our example, we need two conditions as follows:
In the Show list item as area we select the yellow tone and click Save.
Next, we want to add the conditional logic to highlight the entire row red if the Days Until Expiry field has a value of 10 or less. To do this, we return to the Conditional Formatting menu and select Format View.
We click Add Rule (not shown in screenshots) to get into the rule editor.
The process is much the same for formatting columns as we did above. Build out the rule logic on the provided menu, as shown in the screenshot, then click Save (not shown).
Now that we've added our conditions, we can test them out. The screenshot below shows the Days Until Expiry column highlighted yellow because the number is between 11 and 30.
The screenshot below shows the entire row highlighted as red because the Days Until Expiry number is less than 10.
Now, when our team reviews this document library, they'll quickly see which ones are coming up, including those close to expiry.
There you have it! A great way to leverage the conditional formatting features of SharePoint. Note that these features exist for both the Document Library and List functions.
Questions about how you could be maximizing SharePoint for your organization? Contact C5 Insight today!
The complementary paper includes over 12 years of research, recent survey results, and CRM turnaround success stories.
This 60-second assessment is designed to evaluate your organization's collaboration readiness.
Learn how you rank compared to organizations typically in years 1 to 5 of implementation - and which areas to focus on to improve.
This is a sandbox solution which can be activated per site collection to allow you to easily collect feedback from users into a custom Feedback list.
Whether you are upgrading to SharePoint Online, 2010, 2013 or the latest 2016, this checklist contains everything you need to know for a successful transition.