Library Aggregator
The Jarmit Library Aggregator rolls-up or aggregates files and their metadata from multiple document libraries from anywhere within your SharePoint Online tenant into a single table.
Example use cases:
- Unify expense claims from all departments in one page.
- Group all product documentation together.
- See all your documents from the active project sites.
- View all pending order requests for the clients assigned to you.
The user interface has powerful features such as paging, sorting and column and global filters. Users will only be able to see the files they have been assigned permission to in SharePoint.
While editing the web part on a SharePoint page, the Jarmit Library Aggregator displays a special configuration tool to make preparing the web part an easy step-by-step process for non-experts. For advanced users, the Jarmit Library Aggregator can be manually configured to allow for more complex queries.
Multilingual support: English and Spanish
How it works - User Interface
At the top of the Jarmit Library Aggregator web part, there is the language selector in the right-hand corner. The default language will be automatically set by the language the user is currently viewing SharePoint in, but can be changed at any time.
The ‘Clear’ button is used to remove all filters and sorting from the table of files below.
On the right-hand side is a search box which applies a global search amongst all the columns shown for the text entered.
Finally, the column headers show the column name with a sorting icon to the right and, if configured, a filter icon to the left.
Sorting can be done on one or more columns. Clicking once on the sorting icon will apply an ascending sort on that column. Clicking a second time with toggle the sorting direction to descending.
After applying sorting on one column, to apply a second level of sorting on another column, hold down the metakey (CTRL on windows, CMD on mac) and click the sorting icon on the next column you wish to sort by.
Numbers beside the sorting icon will display in which order the sorting is being applied.
Filters
Text filter
The text filter allows you to apply one or two filters. The ‘Add rule’ link in the filter dialog shows the second filter, and the top dropdown lets you decide if both filters must match for the file to appear (Match All) or if the file will be shown if either of the filters match (Match Any).
Text filters can be of several types:
- Starts with
- Contains
- Not contains
- Ends with
- Equals
- Not equals.
Date filter
Date filters can be:
- Date is
- Date is not
- Date is before
- Date is after
The date filter also lets you apply one or two filters using the ‘Add rule’ as before. This can be very useful if looking for files where the date in the column you are filtering should be between two dates. In this case, use ‘Match All’ and one filter with ‘Date is after’ and the second filter with ‘Date is before’.
Number filter
The types of filter available to the number filter are:
- Equals
- Not equals
- Less than
- Less than or equal to
- Greater than
- Greater than or equal to
One or two filters can be applied, matching either or both, and the filter value only permits numerical values to be introduced.
Between filter
An alternative to applying two number filters is the between filter, which shows a double-ended slider between a minimum and maximum value.
To use, just slide the lower and upper limit to show the files with this column’s value between the two.
Yes/No filter
The Yes/No or boolean filter is the simplest of them all.
Select the checkbox in the filter showing a tick, and the files whose column value is Yes will be shown.
When you click the checkbox again, it will show a ‘X’ and filter for files whose column value is No.
When the checkbox is empty, there is no filter.
In filter
The ‘in’ filter lets you choose from a list of options and any file whose column value is any of the values you select to filter on, will be shown.
The list of values to choose from is set by the person who edits the web part and, in the case of there being a long list of possible values, the filter contains its own filter to find the value you are looking for.
How it works - Editor's Interface
The Property Pane
The Jarmit Library Aggregator property pane has two sections: global settings and license settings.
Global Settings
There are only two settings to make the Jarmit Library Aggregator work: the columns configuration and the query configuration.
The columns configuration defines which columns will appear for the users, their type, filter, pipe and transform along with other additional properties according to the type of column and data to be treated.
The query configuration defines what data is requested from each document library to be shown. This includes a list of fields, which fields are ‘expanded’ and any filters, all based on the standard OData SharePoint REST API calls.
Both configuration settings are defined in JSON and look terribly complicated, but not to worry – they are only visible so advanced users can modify them to achieve special, complex results.
Details of the schema for both settings and example advanced configurations are given below.
For everyone else, there is a special configuration tool built into the web part to make creating these two configuration settings as easy as 1-2-3. But more of that later.
License Settings
Finally, the licensing section of the property pane allows you to add your license code, which we send you after purchasing.
The ‘Apply’ button at the bottom saves your web part configuration.
The configuration tool
Obviously, not everyone is an expert SharePoint developer – we’d be out of a job if they were 😜
For this reason, the Jarmit Library Aggregator has been created with a special ‘configuration tool’ that only appears when the web part is being edited.
This tool leads you through the process of selecting the primary document library, which fields are to be shown, defining their individual column filter and any data transformation or formatting that is required, and finally which additional libraries are to be combined along with a common data filter to be applied to each library.
Primary library selection
To begin the configuration process, you have to choose a ‘primary’ document library, from which you will choose the fields you will show.
It doesn’t matter which of the libraries you choose as the primary amongst all that you want to combine.
Paste the URL of the SharePoint site containing your chosen primary document library in the form and click ‘Load’.
The URL should be like https://your-tenant.sharepoint.com, https://your-tenant.sharepoint.com/sites/non-root-site or https://your-tenant.sharepoint.com/sites/non-root-site/subsite
The list of available document libraries in the site will be shown in the dropdown below. No hidden document libraries or libraries based on other templates will appear. If you want to use these, you must use the manual configuration.
Select the desired document library from the dropdown and its fields will be loaded.
Column selection and configuration
Below the dropdown, a list of possible metadata fields will appear. This list will be the same as the list of fields you can add to a SharePoint library view, including several internal fields.
Two special columns appear first: File and Folder.
File column
The File column is required, so it is selected automatically. By default, the ‘text’ filter and ‘link’ transform are chosen. The text filter activates a filter that allows filtering based on the file name, and the link transform converts the file name into a link which opens the file in a new tab in the browser.
Both the filter and transform can be switched off by selecting ‘none’ in each dropdown.
If desired, you can define a new column heading rather than ‘File name’, but it cannot be empty.
Folder column
The folder column is similar to the File column, but is not required. To show this column, check the ‘Show’ checkbox.
This column is set with the ‘link’ transform by default, which shows as a clickable folder icon, opening the containing folder of the file in a new tab.
The ‘link’ transform can be switched off, setting it to ‘none’ and a text filter can be activated, if desired.
As with all the column definitions, the column name ‘Folder’ can be changed, but cannot be empty.
Text and Note columns
The text column type corresponds to metadata fields of the type ‘single line of text’.
The note column type corresponds to metadata fields of the type ‘multiple lines of text’.
Both types of column have the same configuration options.
Apart from modifying the column name, which by default is the display name of the library field, a text filter can be activated on the column.
Choice and MultiChoice columns
The choice column type corresponds to metadata fields of the type ‘choice’.
The multichoice column type corresponds to metadata fields of the type ‘choice’ when the option ‘Checkboxes (allow multiple selection) has been set in the library settings.
Apart from modifying the column name and selecting the text filter, you can also define the ‘in’ filter, which provides a dropdown of values in the column filter.
The list of options in the dropdown filter is defined in the ‘dropdownlist’ text box with each option on a new line.
Apart from the value of ‘fieldtype’, you can recognize a MultiChoice column from a normal Choice column as it has a ‘*’ before its icon.
Number column
The number column type corresponds to metadata fields of the type ‘number’ without the option ‘Show as percentage’ selected.
Filter options are ‘none’ (no filter), ‘number’ (numeric filter) and ‘between’ which requires values for minValue and maxValue and is shown as a slider for the user to select lower and upper values to filter on.
Pipe options, which define how the data is displayed, are ‘none’, ‘decimal’, ‘percent’ and ‘currency’. If currency is selected, a currency code must be entered. The currency code is the ISO 4217 value.
The percent pipe assumes values between 0 (0%) and 1 (100%). The decimal pipe will show the number according to the current language selected.
Percent column
The percent column type corresponds to metadata fields of the type ‘number’ with the option ‘Show as percentage’ selected.
Filter options are the same as the number columns (‘none’, ‘number’ or ‘between’), but the pipe options only allow ‘none’, ‘decimal’ or ‘percent’.
Currency column
The currency column is, again, similar to the number column with the same filters (‘none’, ‘number’ or ‘between’), but again the available pipes are different: ‘none’, ‘decimal’ or ‘currency’.
Once more, the currency code introduced should be the three character ISO 4217 value.
DateTime column
Lookup and LookupMulti columns
The lookup and lookupmulti columns have complex configuration possibilities due to the fact that the column in the lookup parent list or library can be one of several different types including a single line of text, number, date/time, etc…
But not to worry – the configuration tool has your back!
As you set the filter, pipe and transform options, the values of the other options will automatically filter to show you only the allowed values and the additional required settings such as minimum and maximum values, date format, etc. will automatically appear when they need to be configured.
The ‘lookupfield’ value is required and should be the internal name of the column in the associated list or library.
To reset the column configuration to choose a different one, simply put the filter, pipe and transform settings back to ‘none’ to start over.
Similarly to the Choice and MultiChoice columns, the LookupMulti column has a ‘*’ before its icon to differentiate it from a simple Lookup column.
Boolean column
The boolean column corresponds to the Yes/No SharePoint column type and has a very simple configuration.
You can choose to have no filter or the ‘boolean’ filter on the column, and that’s all folks!
User and UserMulti columns
The user and usermulti columns are like the lookup and lookupmulti columns in that they have a ‘lookupfield’ which has to be configured. The difference is that this setting has a fixed set of values that it can be:
Title, Name, EMail, MobilePhone, SipAddress, Department, JobTitle, FirstName, LastName, WorkPhone, UserName, Office, ID, Modified, Created.
While most of these won’t be useful for most people, we have included them all for completeness.
The configuration tool will automatically change the options available for each setting for these types of columns according to the values selected.
For example, ‘date’ filter, pipe and transform are only available for lookupfield values ‘Modified’ and ‘Created’.
Similar as before with the lookup columns, to start over, reset the lookupfield to ‘Title’ and filter, pipe and transform to ‘none’.
URL0 and URL1 columns
While both the URL0 and URL1 column types originate from the ‘hyperlink or picture’ SharePoint column, they are treated differently as they represent very different types of information.
For URL0 (link) columns, setting the transform to ‘link’ creates a clickable link for the user to open and set the pipe value to ‘html’.
In a similar manner, URL1 (picture) columns can have their transform value set to ‘image’ which will show the URL in the data as a clickable image in the table. When the transform is set to ‘image’, the pipe will have the value ‘html’ selected automatically.
Additional library selection
Below the Fields selection, you can add the additional libraries which share the same columns you have chosen to display.
Paste the URL of the SharePoint site or subsite and click the Load button. A list of document libraries will appear for you to select one. Then click the Add button.
The configuration tool will then check to make sure all columns are present and correct according to the fields you have chosen above.
If there are any problems, the configuration tool will alert you to them and the library will not be added. If the library is added to the table below, no problems were found.
To remove a library from the configuration, just click the red bin icon.
Once you start adding additional libraries, you cannot change the fields selection and settings. To modify the fields to display, you must first remove all additional libraries. This is to ensure the checks done when adding additional libraries will be correct.
Optional data filters
You can optionally add a filter to all the files to be shown in the Jarmit Library Aggregator. These filters are standard OData filters with some limitations as to what SharePoint will allow.
SharePoint REST API OData filters explained
Firstly, SharePoint only supports filtering on certain types of columns. These column types are:
- Single line of text
- Choice
- Choice (multiple selection)
- Yes/No (with ‘eq’ or ‘ne’ and values 1 for yes/true, 0 for no/false and null for no value set)
- Date and Time
- Number (with or without ‘show as percentage’)
- Currency
- Lookup (with or without ‘allow multiple values’, specifying lookupfield e.g. lookup/Title eq ‘value’)
- Person or Group (with or without ‘allow multiple selections’, specifying lookupfield e.g personfield/Title eq ‘Mad Max’
Then, depending on the type of data the column holds, SharePoint supports different types of filters.
For numeric values:
- lt – less than
- le – less than or equal to
- gt – greater than
- ge – greater than or equal to
- eq – equal to
- ne – not equal to
The numeric operators can also be applied to date/time values by comparing with a date formatted like:
datecolumn lt datetime'2023-05-15T00:00:00Z'
For textual values (strings):
- startswith(column_name, ‘text to check’)
- substringof(‘text to check’, column_name)
- eq – equal to
- ne – not equal to
You may find information on the internet, including Microsoft’s own documentation which indicates date/time functions such as year(columnname), month(columnname), etc… but unfortunately if you try these, they will fail with a ‘query invalid’ message. These functions only work on the older SOAP based web services such as _vti_bin/listdata.svc which are not supported in the web part.
Finally, you can combine filters with the operations ‘and’ or ‘or’.
Example filters
substringof('urgent', testtext)
– returns files where the single line of text column ‘testtext’ contains the value ‘urgent’
(testnumber ge 100) and (startswith('high', testchoice))
-returns files where number column ‘testnumber’ is greater than or equal to 100 AND the choice column ‘testchoice’ starts with the text ‘high’.
(filesent eq 1) or (percentcompleted gt 0.75)
– returns files where the Yes/No column ‘filesent’ is Yes OR the number column configured for percent ‘percentcompleted’ is greater than 75%.
Limit the number of files to show
If, for whatever reason, you want to restrict the number of files to be displayed you can add the OData ‘top’ option to the end of the optional filter.
Valid values for ‘top’ are 1 to 5000.
for example:
priority eq 'high'&$top=20
will return the first 20 files where the field ‘priority’ equals ‘high’.
If there is no filter, you can still add the ‘top’ option like this:
&$top=100
Advanced configurations
The configuration tool can help users bring together files from multiple document libraries easily, but it has its limitations.
Both the Column and Query configurations in the property pane can be manually modified to achieve more advance results. To make this easier, we recommend copying and pasting the base code generated by the configuration tool into a programming application which supports JSON, such as Visual Studio Code which is free as in beer and can be installed on Windows, Mac or Linux. Inside Visual Studio Code, you can format the JSON to make it easier to read by right-clicking and selecting ‘Format Document’ or Shift-Alt-F.
Firstly, the optional data filters are applied to each query in the configuration tool, meaning they are applied to all libraries. If you want to use different filters on specific libraries, you can modify the filters manually in the Query configuration.
Microsoft has a helpful diagram showing the filters that can be applied here, but ignore the ‘date and time’ section – these operators do not work on the SharePoint REST API. The selecting fields and expansion sections of the diagram are taken care of by the configuration tool. Sorting is defined by default and all columns are sortable by the user, and the Jarmit Library Aggregator web part automatically takes care of paging the results to assure all data is loaded.
Here is a useful filter you may want to use:
- Restrict files returned to a specific folder – &$filter=startswith(FileRef, ‘/libraryname/foldername/’) or &$filter=startswith(FileDirRef, ‘/libraryname/foldername/subfolder/’)
Query configuration
The query configuration is relatively simple.
There is one property called ‘queryConfig’ which holds an array of objects with the properties ‘type’, ‘endpoint’ and ‘body’.
The ‘type’ and ‘body’ properties are included for a future version when CAML queries will be supported, but for now ‘type’ should always be ‘normal’ and ‘body’ should always be an empty string.
The ‘endpoint’ property is for the SharePoint REST API URL. It must point to a standard document library and include ‘$expand=File’.
If you require a version of the Jarmit Library Aggregator that can connect to libraries based on a different template, get in touch, and we can prepare you a special version.
Column configuration
The column configuration is, on the other hand, a bit more complicated.
The configuration tool incorporates all the rules required to correctly generate the column configuration for each type of metadata column supported, but if you like getting your hands dirty, here are the details.
Firstly, the list of supported metadata field types is:
- Single line of text (Text)
- Multiple lines of text (Note)
- Choice (Choice)
- Choice with ‘allow multiple selection’ (MultiChoice)
- Number (Number)
- Number with ‘show as percentage’ (Percent)
- Currency (Currency)
- Date and Time (DateTime)
- Lookup (Lookup)
- Lookup with ‘allow multiple values’ (LookupMulti)
- Yes/No (Boolean)
- Person or Group (User)
- Person or Group with ‘allow multiple selections’ (UserMulti)
- Hyperlink or Picture with ‘format URL as hyperlink’ (URL0)
- Hyperlink or Picture with ‘format URL as picture’ (URL1)
Metadata field types not supported as yet:
- Calculated
- Image
- Task Outcome
- External Data
- Managed Metadata
- Location (modern)
These metadata field types may be supported in a future release. If you need one of these fields now, let us know.
The attributes ‘field’ (internal field name), ‘fieldname’ (name to be shown in column header), ‘fieldtype’ (one of the values above), ‘filter’ (what column filter to enable), ‘pipe’ (display formatting) and ‘transform’ (special data transformation to be applied to raw data) are all required for each column definition.
The other attributes ‘lookupfield’, ‘dateFormat’, ‘currencyCode’ and ‘dropdownlist’ are required depending on field type, filter, pipe and transform selected.
File and FileDirRef columns
There are two special column types pre-defined in the configuration tool: File and FileDirRef.
The File column is required – it shows the file name, including the extension. The only attributes that can be modified are ‘fieldname’ to call the column something different, ‘filter’ which can be ‘text’ or ‘none’ and ‘transform’ which can be ‘link’ to display the file name as a link to the file or ‘none’ to show the field name as plain text.
All other attributes must be as shown in the image above.
The FileDirRef column allows you to show a folder icon which opens the folder in a new tab where the file resides and shares the same configuration rules as the File column above.
Text and Note columns
These column types share the same configuration rules.
As with all column definitions, ‘field’ must be the internal field name. As metadata fields can be created with spaces or accented characters in the display name, the internal names may not be identical. An easy way to check the internal name of a field is to edit the library settings and open the link to the column name. The URL that opens will have the internal field name at the end, such as ‘&Field=internalname’
Apart from changing the ‘fieldname’ to be something other than the field display name, which can also be done on all fields types, the only other attribute that can change is ‘filter’. It can take the values ‘none’ or ‘text’.
‘fieldtype’ must be ‘Text’ or ‘Note’ correspondingly, ‘pipe’ and ‘transform’ can only be ‘none’.
Choice and MultiChoice columns
Again, these column types share the same configuration rules.
The ‘fieldname’ attribute can be changed as usual, but the only other attribute that can have different values is ‘filter’.
‘filter’ can take the values ‘none’, ‘text’ and ‘in’. The ‘in’ value defines a dropdown filter which requires a list of values.
These dropdown filter options are specified in the ‘dropdownlist’ attribute which is an array of objects with two properties: label and value.
The label value represents what will be shown to the user, while the value attribute is used to filter the data.
By default, the configuration tool uses the same value for both attributes, but if required, you can specify different label and value texts.
Number column
The number column has three filter options: ‘none’, ‘number’ and ‘between’. If the ‘between’ filter is used, the ‘minValue’ and ‘maxValue’ attributes must be set and must be numbers.
The pipe attribute can be ‘none’, ‘decimal’, ‘percent’ or ‘currency’. ‘decimal’ will show the number with the decimal and thousands separator corresponding to the active language. ‘percent’ will multiply the value by 100 and add ‘%’. ‘currency’ requires the ‘currencyCode’ attribute to be defined.
Percent column
The percent column can have filter values of ‘none’, ‘number’ or ‘between’. As before, if the ‘between’ filter is used, the ‘minValue’ and ‘maxValue’ attributes must be set and must be numbers.
The pipe attribute can be ‘none’, ‘decimal’ or ‘percent’ with the same display results as the number column.
Currency column
The currency column can take filters ‘none’, ‘number’ or ‘between’. As with the other columns, if the ‘between’ filter is selected, the ‘minValue’ and ‘maxValue’ attributes must be set.
The pipe attribute can take the values ‘none’, ‘decimal’ or ‘currency’. With ‘currency’ selected, the ‘currencyCode’ attribute but also be set.
DateTime column
The DateTime column can take the values ‘none’ and ‘date’ for all three attributes ‘filter’, ‘pipe’ and ‘transform’.
To be able to use the date pipe to display the date in a specific format rather than the raw data sent from SharePoint, you must use the ‘date’ transform and the ‘dateFormat’ attribute is required.
Pre-defined date formats can be found here, and custom format options here.
Lookup and LookupMulti columns
The Lookup and LookupMulti columns have the most complex set of possible configurations, depending on the type of lookup column selected from the parent list or library. Both column types have the same set of rules for their configuration.
Applying a little common sense will allow you to use them without problems.
Possible filters are ‘none’, ‘text’, ‘number’, ‘between’, ‘date’ and ‘in’.
Possible values for pipe are ‘none’, ‘date’, ‘decimal’, ‘currency’ or ‘percent’.
The transform attribute can be ‘none’ or ‘date’.
The ‘lookupfield’ is required and should be the internal name of the field in the column of the parent list or library.
The other attributes are required according to the values of filter, pipe and transform:
- ‘minValue’ and ‘maxValue’ when filter is ‘between’.
- ‘dropdownlist’ when filter is ‘in’.
- ‘dateFormat’ when transform is ‘date’ and pipe is ‘date’.
- ‘currencyCode’ when pipe is ‘currency’.
Boolean column
The boolean column, on the other hand, is quite simple. The only attribute apart from ‘fieldname’ that can change, is the filter, which can be ‘none’ or ‘boolean’.
User and UserMulti columns
The User and UserMulti columns are similar to the Lookup and LookupMulti columns as they also require the ‘lookupfield’ attribute, but this attribute can have only one of a fixed list of values:
Title, Name, EMail, MobilePhone, SipAddress, Department, JobTitle, FirstName, LastName, WorkPhone, UserName, Office, ID, Modified, Created.
The ‘date’ filter, pipe and transform are only allowed when the lookupfield is either ‘Modified’ or ‘Created’ as they are the only date/time values.
The ‘dateFormat’ attribute is necessary when transform and pipe are ‘date’ as prevously and when filter is ‘in’, the ‘dropdownlist’ attribute must be completed.
URL0 (Link) and URL1 (Picture) columns
While the URL0 (Link) and URL1 (Picture) columns come from the same SharePoint field type, they have different configuration options.
While the URL0 (Link) column can be filtered as ‘text’, the URL1 (Picture) cannot.
Both column types have different transforms available: ‘link’ for the URL0 (Link) column and ‘image’ for the URL1 (Picture) column. These correspond to how the raw data is turned into HTML anchor or img tags.
When either of these two transforms are set, the pipe value must be ‘html’.
Installation instructions
Fill out the registration form below and click the button to download a zip file containing the .sppkg SPFx solution file.
This solution will only work in SharePoint Online. If you are interested in a version for SharePoint 2016/2019/Subscription Edition, please let us know.
If you wish to deploy this solution to your entire SharePoint Online environment, you need to add this solution to your tenant app catalog.
To do this, you must be a SharePoint Administrator or a Global Administrator.
If you do not yet have a tenant app catalog (usually created at the URL https://your-tenant.sharepoint.com/sites/appcatalog) you can create one easily.
- Open your SharePoint administration site (https://your-tenant-admin.sharepoint.com/)
- In the left sidebar, select ‘More features’
- Then locate the section ‘Apps’ and select ‘Open’.
This will start the automatic creation of the tenant app catalog if it does not exist.
You can also install this solution in a site level app catalog if you only want it to be available in specific SharePoint sites.
For more information on how to create site collection app catalogs and how to use them, please read this Microsoft article.
When you add the .sppkg file to the app catalog, you will see a deployment dialog. To make the Jarmit Library Aggregator web part directly available to all SharePoint sites, you can check the ‘Make this solution available to all sites in the organization’ setting.
If you don’t check the ‘Make this solution available to all sites in the organization’ setting during deployment, you can add the web part to individual SharePoint sites by going to the site contents page, and select New -> App.
The new modern experience will show a list of solutions you can add to the site. Click the ‘Add’ button underneath the Jarmit Library Aggregator solution.
It will take a few seconds for SharePoint to add the web part to the site. Once it appears in the site contents page, you can add it to a SharePoint page.
While editing a SharePoint page, you can find the Jarmit Library Aggregator web part in the toolbox in the ‘Advanced’ section.
When you add the web part to a page, you start a 14 day fully functional trial period. After 14 days, the web part will cease to work and display a ‘trial finished’ message.
After purchasing a license, you can edit the page, open the web part property pane and copy the license code into the text box to activate the license. Your current web part configuration will not be affected.
Pricing
Registration Form
To download the Jarmit Library Aggregator web part, please fill out the following form.
Use your Microsoft 365 email to register and receive 1 free lifetime subscription to 365Desk.io (limit 1 per Microsoft 365 tenant).