Displaying Grouped Data Provided By ColdFusion in the Flex 3 AdvancedDataGrid

Introduction

ColdFusion makes it easy to display query results grouped by one of the query result columns on a web page. You just need to use the group attribute (1) of the cfoutput tag. Showing grouped data that was provided by a ColdFusion query in Flex 2 was not so easy. However, with the introduction of the AdvancedDataGrid component (2) in Flex 3, it's now simple to showed grouped data that initially came from a ColdFusion query.

The AdvancedDataGrid

The AdvancedDataGrid control (2) is part of the Flex 3 Builder Professional suite of controls. It’s not included in the Flex 3 Standard edition. One of the AdvancedDataGrid control’s features (3) is the ability to display hierarchical and grouped data (4). The AdvancedDataGrid control uses an expandable navigation tree in a column to control which rows are visible in the grid.

Converting Flat Data to Grouped Data

When you use ColdFusion to get results of a database query into Flex that data is considered flat data. For example this query result from ColdFusion:

would be converted to a flat ArrayCollection object in Flex. To show the above query results grouped by the NAME column you need to convert the ArrayCollection to a hierarchy.

Flex 3 added a new Collection class, GroupingCollection (5) that can be used to create grouped data (which is a hierarchy) from flat data. So after you get the query results from ColdFusion, you need to convert those results from a flat data ArrayCollection object to a GroupingCollection object (4). The AdvancedDataGrid can use a GroupingCollection object as its dataProvider.

To create a GroupingCollection object using the ArrayCollection that contains the query results returned by ColdFusion you need to follow these steps

1. Decide which column in the query results you need to group on. You can group on multiple columns, but for this example we will group on only one column. Use that column name to create a GroupingField object (7). A GroupingField object just represents a single query column that you want to group the data on.
• var nameGroupFld:GroupingField = new GroupingField("NAME") ;
2. Create a Grouping object. A Grouping object defines an array of GroupingField objects that are used to group data. You set the fields property of the Grouping object to an array of GroupIngField objects.
• var courseNameGroup:Grouping = new Grouping();
• courseNameGroup.fields = [ nameGroupFld ];
3. Create a GroupingCollection object and set its source property to the ArrayCollection that contains the query results returned by ColdFusion.
• var coursesGroupColl:GroupingCollection = new GroupingCollection();
• coursesGroupColl.source = coursesAryCol;
4. Set the GroupingCollection object’s grouping property equal to the Grouping object you created above.
• coursesGroupColl.grouping = courseNameGroup;
5. Call the refresh method of the GroupingCollection object to update the view
• coursesGroupColl.refresh();

Display The Grouped Data

To display the grouped data you created above you just need to set the AdvancedDataGrid's dataProvider to the GroupingCollection object: coursesADG.dataProvider = coursesGroupColl; Of course you can also use data binding: dataProvider = "{coursesGroupColl}" When you call the refresh method on the coursesGroupColl object, the data displayed in the AdvancedDataGrid is updated to show the new grouped data.

Example

View a complete example (right click on the Flex application to view all the source files).

Summary

The AdvancedDataGrid control and the Flex 3 GroupingCollection class provide an easy way to convert data returned from ColdFusion into grouped data that can be displayed in a Flex application. The AdvancedDataGrid has many more features (8) then just displaying grouped data.

Remember the AdvancedDataGrid is not part of Flex Builder 3 Standard. You have to purchase the Flex Builder 3 Professional version to get the AdvancedDataGrid control. Only you can decide if the $200 difference in the upgrade price is worth the additional features of Flex Builder 3 Professional (9).

References

1. ColdFusion 8 CFML Reference - ColdFusion 8 cfoutput group attribute
2. Flex Data Visualization Developer's Guide -Flex 3 AdvancedDataGrid
3. Flex Data Visualization Developer's Guide - Using the AdvancedDataGrid Control
4. Flex Data Visualization Developer's Guide - Displaying Grouped Data
5. Flex 3 Language Reference - GroupingCollection
6. Flex 3 Language Reference - Grouping
7. Flex 3 Language Reference - GroupingField
8. Flex Data Visualization Developer's Guide - About the AdvancedDataGrid Control
9. Flex 3 Upgrade Details - Comparison of Flex Builder 3 Standard vs. Flex Builder 3 Professional