Skip to main content
Windward

Grouping with ForEach Tags

Overview

This article will show you how to group items using nested ForEach Tags.  Nesting ForEach tags consists of creating 2 loops, an inner loop and outer loop, that cycle through each other in order to create a proper grouping.  

When Do I Use This Feature?

Nested ForEach loops are best used in cases where items from two or more datasets need to be correlated with one another to create an integrated list or table. The final table or list would share at least one common node of data, such as a customer number. 

 

In the example below, you will be shown how to create an outer loop containing a list of customers and associate it with an inner loop containing a list of orders.  The result will give you a full list of customers referencing each order that the customer placed.  This concept allows you to group orders by the customers that requested them.

 

This feature is typically used when data items are stored in different locations in your data source (such as different tables or different nodes).  A common field (column, node, attribute, value) will be needed in both data items in order to filter them in the inner ForEach tag to create the relation between the two loops and data items.  The resulting Output will first print the data values for the outer loop and then print all the related data values for the inner loop before repeating and incrementing the outer loop again.

Sample Template

The sample template (linked below) uses Windward's public MS SQL database to show an example of grouping. This sample shows Orders grouped by Customer from the Northwind database.  Examples for XML, JSON and OData are listed on further pages in the sample.

ForEach Grouping Sample - Template.docx

Data Source Supported

All data sources are supported

Examples in the attached template for:

  • SQL
  • XML
  • JSON
  • ODATA

Instructions

Preview

Using the sample template, here is the template and Output for comparison.

 

 

Step 1 

Creating the Outer ForEach Loop of Customers:
 

In this example we will first create a ForEach loop which lists all customers in our example database.  In the Wizard, drag the two datasets into the columns field (make sure the two you select share a common node). The inner ForEach loop will be able to reference both datasets that you associate with the outer ForEach loop.

  • For more information on how to create a ForEach loop and the criteria for data selection, please refer to our page on explaining ForEach Tag usage.

ForEachGrouping1.png

 

 

 

Step 2

Creating the Inner ForEach Loop of Orders:

 

Next, create an inner ForEach loop containing the fields and values that will be grouped and Output.  This can be done by selecting the Company Name field from the Customers table and the OrderID field from the Orders table.  The generated Output preview shows each company with all associated orders next to it.  The Output is now grouped first by company and then by orders.  

 

NOTE: At this point if you Output, the inner ForEach loop would print every company and every order for each iteration of the outer ForEach loop.  This would generate a LOT of data and not give the desired result.  The next step demonstrates adding a variable filter to the inner ForEach loop select to return ONLY the orders associated with the current outer ForEach loop iteration.  

 

For example:  The first iteration of the outer ForEach loop returns "Alfreds Futterkiste." By filtering the inner ForEach loop for the company "Alfreds Futterkiste," the inner ForEach loop will only return the orders associated with that company.   The next time the outer ForEach loop iterates, the inner ForEach loop will only return the orders associated with the company "AnaTrujillo Emparedados y helados".

 

ForEachGrouping2.png

 

 

Step 3 

Linking a Common Field between Customers and Orders:

 

To resolve the problem at the end of step two, a filter needs to be applied to the inner ForEach loop select statement.

  • Open the Wizard of the inner ForEach loop.

  • Set a filter in the inner ForEach loop Wizard by setting a condition where the common field from the inner ForEach loop is equal to the common field from the outer ForEach loop variable (in this case the Customers.CustomerID node that was previously associated with the outer FR Loop, which can be selected from a dropdown menu in the filter selection box as pictured.

(For more instruction on setting variables, see the Windward Tutor article on Selection Variables.)

 

In this example the the outer ForEach loop lists the "Customers" using the Customers.CompanyName node, and the inner ForEach loop lists the "Orders" using the Orders.OrderID node. The "Customers" and "Orders" tables are linked by common node "CustomerID."

 

ForEachGrouping3.png

 

 

Step 4

Inserting Out Tags That Refer to the Grouped Data Set Created in Step 3:

 

First, notice that the Out Tag for the company name is listed BEFORE the inner ForEach loop.  This is done so the Company Name does not repeat each time the inner ForEach loop iterates.

 

With the inner and outer ForEach loops filtered, the Out Tags in both loops need data assigned to them to generate the correct values upon Output. Both Out Tags need to have values assigned to them coming from the outer and inner ForEach loops.  This is done so that every time each ForEach loop iterates an new value is Output instead of the same value each time.  

 

For the Out Tag for "Company Name," use the Tag Editor or Data Tree to assign a data node (in this case Customers.CompanyName).  It will then show up as having the value ${Customers.CompanyName}, causing it to print the name of each customer's company each time the outer ForEach loop iterates.
 

ForEachGrouping4.png

 

 

 

 

Next, use the Data tree or Tag editor to assign the node Orders.OrderID  to the Out Tag for the "Orders" column. It will not have the variable value ${Orders.OrderID} causing it to print the value of each associated order for the outer ForEach Tag company.  

 

 

 

 

Final Output

The final Output will return separate tables for each customer that lists the company name in the first row followed by several rows of orders associated with each company:

 

 

GroupingFEBB02.png

Nested inner loops and pages breaks

Windward doesn't allow break='sheet'  property for inner ForEach Tags; it will only work for the outer ForEach Tag. We offered this as a feature at one point, but discovered it caused more problems for users than it solved. Therefore, there is just no way to include breaking on an inner ForEach Tag.

 

What's next?

For more information on ForEach grouping and special formatting for your template, see Table Grouping Using Nested ForEach Tags.

  • Was this article helpful?