Given that users are familiar with Microsoft Office Excel applications, find it absolutely normal to use conditional formatting in SharePoint lists.
In fact, SharePoint lists are web pages that cannot be formatted as easy as in Excel. Most experts recommend three methods of formatting lists custom: JS scripts, XSL styles or acquisition of specialized applications.
Script on which I propose is based on XSL style and is customizable for both the SharePoint site of on-premises and for Office 365.
The script also contains many formatting of the default SharePoint designated to improve the end users experience, but it can make more improvements.
Custom list structure:
You have to use in XSL the internal name of columns:
If you want to identify the internal names of columns you have to use a custom XSL with the template:
<xsl:template match=”/” xmlns:x=”http://www.w3.org/2001/XMLSchema”>
<!– Defining variable userd for all records from a Links list –>
<xsl:variable name=”Rows” select=”/dsQueryResponse/Rows/Row” />
<!– Display the list as a unsorted list. Also you can choose to create a table. –>
<!– Extract the columns of an object. –>
<xsl:value-of select=”name()” /> |
If you want to test my script into a test development you can download it from the address: https://gallery.technet.microsoft.com/XSL-Conditional-Formatting-576f55bd
Internal Names of the columns in this sample:
ID | PermMask | FSObjType | Title | FileLeafRef | FileLeafRef.Name | FileLeafRef.Suffix | Created_x0020_Date | Created_x0020_Date.ifnew | FileRef | FileRef.urlencode | FileRef.urlencodeasurl | File_x0020_Type | HTML_x0020_File_x0020_Type.File_x0020_Type.mapall | HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon | HTML_x0020_File_x0020_Type.File_x0020_Type.mapico | ContentTypeId | Stock | Stock. | SS | SS. | RS |
- ProductName = Title
- Stock = Stock
- Safety Stock = SS
- Required Supply = RS – calculated column. Formula: =IF([Safety Stock]-Stock>0;[Safety Stock]-Stock;0)
Depending your locale the formula could be different. Replace ”;” character with ”,”. Also the internal name could be different.
To proceed to the following steps, you have to be sure that you have appropiate permissions.
- Download the script from previous Link;
- Create a custom list in SharePoint on-premises or Office 365;
- Give the name of the list Stocks… or other names… is not important for this sample;
- Create columns with short name (eg. SS) and rename columns with a display name;
- Create new records, and fill the sample data to accieve condition;
- Create a new View and name it Formatting. Is not so important but you have to modify the XSL script if you choose to specify a different name;
- Customize your XSL script and uploade it on the SharePoint site in Site Assets library;
- Open the Formatting view from the custom list Stocks and from the Settings menu choose Edit Page option;
- From the Edit page choose Web part properties:
- Expand Miscellaneous and in text box XSL Link type: /SiteAssets/cf_Stocks.xsl
- Click Apply button, Ok and close Page editing.
If you want to choose another condition for you custom solution you have to modify the XSL file and to edit another condition on the line 74 from my script:
Also you can apply multiple formats using different xsl:when conditions.
Hope it helps and thank you for future feedback!