Lightning Conductor Calculated Columns Part 1
The Lightning Conductor is a SharePoint rollup tool which can perform a rollup view of your SharePoint lists within Office 365 SharePoint Online or SharePoint On-Premises. You can build multiple views of content such as Task views, Calendar views, Document Rollups and variations of each. As well as adding the columns from the lists such as Task Name, Start Date, Due Date, Priority etc, you can also build your own calculated column. These Lightning Conductor calculated columns can display simple calculations such as ([DueDate]-[StartDate]/86500000), or you can use them for advanced results such as KPIs or buttons that perform an action such as marking a Task as complete. Through this series of posts, we will be providing several examples of Lightning Conductor calculated columns.
This is the first in a multi-part blog post on creating calculated columns within the Lightning Conductor Add-In for SharePoint Online.
Within this post I’ll explain how you can use the Lightning Conductor Add-in, to display documents that were last modified in the last 7 days with a folder structure, using the Search Rollup Engine Provider.
Note: The Lightning Conductor is also available for SharePoint On-Premises as a WSP. It is also possible to add Lightning Conductor Calculated Columns within the Web Part version which can be viewed here: https://lightningtools.com/products/lightning-conductor-web-part-2013/
To rollup documents modified in the past 7 days with a folder structure while using the ‘Search Rollup Engine Provider’, please follow all the steps provided below:
1. Select the ‘Actions’ menu on the ‘Lightning Conductor App Page’.
2. In the ‘App Part’ tab you will need to select the ‘Search Rollup Engine Provider’ in the ‘Configure Data Source Provider’, then in the ‘Configure Display Provider’ select the ‘Grid View Display Provider’.
3. Select the ‘Data Source’ tab you will next select the ‘Results source’. In the drop down menu, select ‘Items matching a content type’.
The reason why we select the ‘Items matching a content type’ results source is because you can’t filter your results using the ‘Documents’ results source. This is because the ‘Documents‘ results source does not support passing additional query parts (filter are passed as additional query parts).
4. In the ‘Scope’ section you will want to select where you want your items to return from. You can however leave this section blank if you wish to roll up from everywhere. See image below for an example.
5. In the ‘Data Source’ tab you will see a ‘ContentTypeId’ field, you will want to select the ‘Documents’ content type, which is also known as ‘0x0101*’.
6. In the ‘Columns Tab’ you will want to filter on the ‘FileExtension’ column using the following filters:
Contains docx
Or Contains pdf
Or Contains pptx
Or Contains xsl
NOTE: this is just an example, you can add other file extension types to the filter.
The reason why you might want to filter on the ‘File Extension’ is because it will return unwanted document types. For example: aspx file types.
7. In the ‘Managed Properties field’ located in the ‘Columns’ tab, you will need to add the following text: ‘EditorOWSUSER’ if you wish to have access to ‘Modified By’ column.
The reason you might want to do this is so you can see who last modified the documents.
8. You will then need to create a ‘Calculated Column’ based on the ‘EditorOWSUSER’ column. The reason you need to do this is because it returns unwanted text. Using a calculated column will allow you to remove the unwanted text. The image below is comparing the ‘EditorOWSUSER’ column and the ‘Modified By’ calculated column.
NOTE: You will be unable to filter on the ‘Calculated Column’.
You will need to use the following:
(function(){var a=[EditorOWSUSER];a=a.substr(a.indexOf(‘|’)+1);return a.indexOf(‘|’)>0?a.substr(0,a.indexOf(‘|’)):a;})();
9. You will then need to create a ‘Calculated Column’ based on the ‘Author’ column. The reason you need to do this is because it returns unwanted text. Using a calculated column will allow you to remove the unwanted text. The image below is comparing the ‘Author’ column and the ‘Author’ calculated column.
NOTE: You will be unable to filter on the ‘Calculated Column’.
You will need to use the following:
[Author].substr([Author].lastIndexOf(‘;’)+1)
10. In the ‘Managed Properties field’ located in the ‘Columns’ tab, you will need to add the following text: ‘FileName’ if you wish to display the name of the document file in the Lightning Conductor Add-in.
11. In the ‘Managed Properties field’ located in the ‘Columns’ tab, you will need to add the following text: ‘ContentType’ if you wish to display the types of Content Type in the Lightning Conductor Add-in.
12. You will then need to create a ‘Calculated Column’ based on the ‘ContentType’ column. The reason you need to do this is because it returns unwanted text. Using a calculated column will allow you to remove the unwanted text. The image below is comparing the ‘ContentType’ column and the ‘ContentType’ calculated column.
NOTE: You will be unable to filter on the ‘Calculated Column’.
You will need to use the following:
[ContentType].substr([ContentType].indexOf(‘\n’)+1)
13. Select the columns you wish to display. In my example, I’ve selected the following to display:
‘Title’ column
‘FileName’ column
‘ContentType’ column
‘Author’ calculated column
‘Modified By’ calculated column
‘LastModifiedTime’ column
14. In this next step we will filter on the ‘LastModifiedTime’ column. See images below.
15. In this next step we will apply conditional formatting on the ‘LastModifiedTime’ column. See images below.
16. In the ‘Display’ tab you will want to select the following:
‘Show item type icon’
‘Group by folders’
As you can see from the image below the Lightning Conductor Add-in allows you to roll up any documents that have been modified in the last 7 days using the Search Rollup Engine Provider.
You can start using the Lightning Conductor for your SharePoint Online tenant by downloading the Add-In directly from the SharePoint App Store.
