Enesys RS Data Extension 3.8 provides additional flexibility and power for merging multiple lists with the new “ListsMerge” statement.
When it comes to merge multiple lists using Enesys RS Data Extension, two options are available:
Roll Up lists/items from multiple sites in a site collection
If you need to roll up items from lists in a site collection, you can use the “RollUpLists” statement. For instance you may want to roll-up tasks or issues from multiple sub-sites. The “RollUpLists” statement makes it really easy because you just need to specify the name of the list and the starting site and it will retrieve all items in your site and sub-sites (as long as a list with the specified name exists).
Merge unrelated lists no matter the site
Now if you would like to merge items from an arbitrary set of lists that are not necessarily located in the same site collection and not necessarily having the same name, you will need to add a “List” statement for each list you would like to merge and also add a “SqlQuery” statement for applying a “Union” operation between the lists.
This is the latter approach that is made more flexible and versatile with the new “ListsMerge” statement.
Let’s have some sample scenario so that we can demonstrate how we can merge lists with Enesys RS Data Extension 3.7 or earlier versions and see what is offered with the new “ListsMerge” statement.
Say we would like to display a list of documents (from multiple document libraries) grouped by the name of the one who last modified the document. Basically, we would like a report like the one shown in the following screen shot:
The displayed documents are coming from 3 different libraries in two different sites:
|Document Library ||relative Site Url |
|ERSDE Documentation ||/sites/ersdedemo/ |
|Training ||/sites/ersdedemo/ |
|Shared Documents ||/sites/ersdedemo/roomreservation/ |
Merging Lists Using “Union” operator
In Enesys RS Data Extension 3.7 and earlier, you would:
- add the 3 SharePoint libraries, using a “List” statement,
- select the specific fields you need in your report, for each list
- add a “sqlQuery” statement and merge the 3 lists using the UNION operator.
The following screenshot displays the completed query within the Query Designer:
On the left, in the Query Statements panel, you can see the 3 document libraries and a “SqlQuery” statement. In the main panel, you can see the “SqlQuery” statement SELECT query being used to merge the 3 document libraries metadata.
That’s quite simple and there’s nothing wrong with this approach. You can still use it with Enesys RS Data Extension 3.8. However, it can become somewhat tedious if you have more lists than that or if you would like to add/change the lists/libraries involved from time to time. Say you would like to merge two additional lists. You will need to add two “List” statements, select the specific fields you need in your report for each list and modify your “Select” clause to UNION the two additional lists.
Additional power and flexibility was the reason for providing “ListsMerge” statement as you will see in the rest of the article.
Merging Lists Using “ListsMerge” statement
The new “ListsMerge” statement lets you merge a set of lists specified by another statement. You will configure “ListsMerge” statement so that the lists to be merged are specified in another statement which means that you can store the lists in whatever way you want as long as you can use one of the statement provided by Enesys RS Data Extension to retrieve them.
For instance, you may want to use a SharePoint list to maintain the lists/libraries that you would like to merge. Using this approach, adding or removing a list from your report is just a matter of adding or deleting an item in your SharePoint list without having to change your report.
If you are not too impressed by this flexibility and prefer keeping things (including the set of list you want to merge) inside the query/report, you can still use the “ListsMerge” approach and use an “XmlQuery” statement to enumerate the set of lists using some literal Xml fragment. You may wonder why you would want to use this approach versus using “UNION” as described earlier. Both are static and need to change the report when you want to merge additional lists or remove some lists. The important advantage of this approach is that you will specify the fields needed in your report just once as part of the “ListsMerge” statement. Adding a list is just a matter of adding a list name and a relative URL to the site hosting the list.
Let’s now implement our merge scenario using the new “ListsMerge” statement.
Merging Lists Using “ListsMerge” and a SharePoint list to enumerate the lists
In this approach we will use a SharePoint list to enumerate the libraries (as explained in our scenario) that we would like to merge in our report. Basically we will need to create the following elements:
- a SharePoint list that holds the lists/libraries that need to be merged
- a query with the following elements:
- a “List” statement that points to our SharePoint list that enumerates the libraries/lists we want to merge
- a “ListsMerge” statement that points to our List statement that contains the lists to be merged.
The SharePoint list is used to enumerate the libraries that we would like to merge as part of our report. We just need a simple custom list with two fields: one for specifying the name of the list/library, one for specifying the site URL where the list is stored.
The following screenshot shows such a list named “Technical Doc – Libraries”:
The name of the list is used to remind us that the list enumerates various libraries that are related to technical documentation.
The default Title field is used to specify the name of the list and a “SiteUrl” field was added so that we can specify the URL to the site containing the list. As you can see from the list content, we have specified our three document libraries.
First, we need a “List” statement in order to retrieve the lists we want to merge.
We add a list statement to retrieve items from our “Technical Doc – Libraries” list:
We select the Title and SiteUrl fields as shown in the following screen shot:
Now we can create a “ListsMerge” statement that points to the “List” statement we have just created:
When adding a “ListsMerge” statement, you will need to specify a few things as shown in the following screen shot:
First thing, obviously, you need to select an existing statement that returns the name and URL of the lists you would like to merge. The ListsMerge assumes that your statement will return lists information using the following two fields:
- Title: the name of the SharePoint list.
- RelativeSiteUrl: the site URL where the list is located.
If the source statement (that enumerates the lists that are to be merged) does not have those fields, you won’t be able to go any further as you can see on the following screen shot:
The “RelativeSiteUrl” field cannot be found in our source list (Technical Doc – Libraries). If you can remember – or navigate above – we created a field named SiteUrl instead of RelativeSiteUrl. Obviously, this was on purpose just to show how you can handle those kinds of situations. Quite easy in fact. Just add an intermediate “SqlQuery” statement just for the sake of renaming fields. The following screen shot shows the new “SqlQuery” statement (named ListsToMerge) and the associated SELECT clause.
The purpose of the “ListsToMerge” statement is to return data from “Technical Doc – Libraries”, changing “SiteUrl” column to “RelativeSiteUrl” column as it is expected by the “ListsMerge” statement.
Now we can create our “ListsMerge” statement and select “ListsToMerge” as the source and as you can see in the following screen shot, both mandatory fields will be validated:
Now for the last step, you need to specify the list that will serve as a reference list to infer the schema so that you can select the fields you need and possibly specify some filter conditions. You can either select a list of your own (by clicking Select List) or get the first list available from the source statement you specified and that contains the lists that are to be merged (by clicking on Get First List). The latter being the easiest approach that should be fine in most cases.
By clicking on “Get First List”, the list “ERSDE Documentation” list is retrieved as shown in the following screen shot:
Once you click OK, the “ListsMerge” statement is very similar to a “List” statement. You can select the specific fields you need in your report as shown in the following screen shot:
You can notice that besides the selected fields you get two additional fields that represent the name of the list (Source_Title) and the relative URL to the site that hosts the list (Source_Url). This can prove helpful if you need to group the merged items by Site or List in your report.
We won’t get into the details of designing the report layout. As you can imagine from the initial Scenario Presentation at the top of this article, this is a very simple layout based on a table component:
That’s it. Now when you execute the report or query, Enesys RS Data Extension will get the lists that are to be merged from the “Technical Doc – Libraries” SharePoint list and pass them to the “ListsMerge” statement. The “ListsMerge” statement will merge items from the specified lists.
The use of a SharePoint list for enumerating the lists that you want to merge is quite flexible and dynamic in the sense that you don’t need to change the report each time you would like to add or remove some lists from the report. Moreover, you can easily restrict access to the SharePoint list so that only authorized persons are allowed to add or remove a list.
This is however not the only approach available. If you prefer not relying on an external source (a SharePoint list in our example) and would rather having the lists that you want to merge specified directly within the query, you can use an “XmlQuery” statement.
Merging Lists Using “ListsMerge” and XML data to enumerate the lists
In this approach we will use an XML data fragment to enumerate the libraries that we would like to merge in our report. The “XmlQuery” statement lets you write XML data directly within the query. Therefore you don’t need to rely on an external source to enumerate the lists.
First we will create a new XmlQuery statement. In the initial Window that pops up, we just select that the “XML data is directly specified” and then click Edit to enter some XML data that will enumerate the lists we would like to merge. It’s also necessary to select “An XPath formula will be used” Path option as outlined in the following screenshot (no XPath value is necessary):
The XML tags we are using do not really matter except for the list name (Title) and site URL (RelativeSiteUrl). Enesys RS Data Extension will properly infer the data and returns a table having Title and RelativeSiteUrl fields that contain the values provided in the XML fragment.
Now that we have an “XmlQuery” statement returning data (a set of lists) in a format compatible with “ListsMerge” statement, we just need to create a “ListsMerge” statement and select the “XmlQuery” statement we just created, as shown in the following screen shot:
For details about the various options when adding a “ListsMerge” statement, please see the previous chapter. The entire process is similar except that we are using an “XmlQuery” statement to get the lists that are to be merged instead of using a SharePoint list.
Once our “ListsMerge” statement initial configuration is done we can select the fields we need in our report as shown in the following screen shot:
We get the exact same result than with the previous approach (using a SharePoint list). On one hand, this approach is less flexible and you will need to update your report if you would like to change (add or remove) the lists that need to be merged. On the other hand, things can feel a bit more secure by not relying on an external source for enumerating the lists.
Both approaches are perfectly valid depending on your needs.
The “ListsMerge” statement brings additional flexibility and power when it comes to merge multiple lists.
By having some external source (a SharePoint list, your own business logic available through a web service, …) to enumerate the lists that are to be merged, you can set up a very dynamic approach.
Even when you would like to merge a specific set of lists and are not considering any changes, it may be easier to maintain a “ListsMerge” statement than multiple “Lists” statements.