Lightning Conductor Calculated Columns Part 1

By

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

Lightning Conductor Calculated Columns - Create View

 

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

 

Lightning Conductor Calculated Columns - App Part Properties

 

3.  Select the ‘Data Source’ tab you will next select the ‘Results source’. In the drop down menu, select ‘Items matching a content type’.

 

Lightning Conductor Calculated Columns - Search Result Source

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.

 

Lightning Conductor Calculated Columns - Scope Refiner

 

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*’.

 

Lightning Conductor Calculated Columns - Content Type

 

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.

 

Lightning Conductor Calculated Columns - Filters

 

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.

Lightning Conductor Calculated Columns - Managed Properties

 

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

 

Lightning Conductor Calculated Columns - EditorOWSUser

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;})();

 

Lightning Conductor Calculated Columns - Modified By

 

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

 

Lightning Conductor Calculated Columns - Author

 

You will need to use the following:

[Author].substr([Author].lastIndexOf(‘;’)+1)

 

Lightning Conductor Calculated Columns - Author Calculation

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.

Lightning Conductor Calculated Columns - Result

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.

 

Lightning Conductor Calculated Columns - Custom Content Type

 

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

image

You will need to use the following:

[ContentType].substr([ContentType].indexOf(‘\n’)+1)

 

Lightning Conductor Calculated Columns - substr

 

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

 

Lightning Conductor Calculated Columns - Selecting Columns

 

14. In this next step we will filter on the ‘LastModifiedTime’ column. See images below.

 

Lightning Conductor Calculated Columns - Last Modified Time

Lightning Conductor Calculated Columns - Filter

15. In this next step we will apply conditional formatting on the ‘LastModifiedTime’ column. See images below.

image

image

 

16. In the ‘Display’ tab you will want to select the following:

Show item type icon

Group by folders

 

Lightning Conductor Calculated Columns - Group By

 

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.

Lightning Conductor Calculated Columns - Formatting

You can start using the Lightning Conductor for your SharePoint Online tenant by downloading the Add-In directly from the SharePoint App Store.