Dynamically Populating ArrayCollections from a CF Structure of Queries

Challenge: A Flex/AIR application requires a large number of comboBoxes to be
populated from a single lookup data table, each of which may be used repeatedly,
and in multiple sub-components or forms within the overall application.  The capability to
minimize remote object calls is highly desired for performance, minimizing code
written, and simple to add new lookup types and array collections as needed.

Solution: Utilizing CF structures of query results, as well as named structure keys rather than dot notation, ArrayCollections can be populated without explicit knowledge of their actual variable names or which query result should be associated with it.

This code sample is utilized in a Cairngorm-based application, but can easily be
implemented in plain Actionscript or other micro-frameworks. Additionally, the
data is located in a SQL Server environment, and accessed via Remote Objects via
ColdFusion 8. 

Sample data in the lookup table (and apologies for the lack of formatting):
lookup_type    lookup_name    lookup_value    lookup_descr    lookup_order    active_flag
office_location    PGH    1    Pittsburgh    1    1
office_location    DC    2    Washington, DC    2    1
office_location    COLO    3    Colorado    3    1
office_location    LA    6    Los Angeles    6    1
office_location    REMOTE    7    Remote Location    7    1
office_location    OTHER    8    Other Location    8    1
degree_type    Doctorate (PhD)    1    Doctorate (PhD)    1    1
degree_type    Doctorate (JD)    2    Doctorate (JD)    2    1
degree_type    Doctorate (Other)    3    Doctorate (Other)    3    1
degree_type    Masters (MA)    4    Masters (MA)    4    1
degree_type    Masters (MS)    5    Masters (MS)    5    1
degree_type    Masters (MBA)    6    Masters (MBS)    6    1
degree_type    Masters (Other)    7    Masters (Other)    7    1
degree_type    Bachelor (BA)    8    Bachelor (BA)    8    1
degree_type    Bachelor (BS)    9    Bachelor (BS)    9    1
degree_type    Bachelor (Other)    10    Bachelor (Other)    10    1
degree_type    Associates (AA)    11    Associates (AA)    11    1
degree_type    Associates (Other)    12    Associates (Other)    12    1
membership_type    Professional Society Fellow    1    Professional Society Fellow    1    1
membership_type    Professional Society Member    2    Professional Society Member    2    1
membership_type    Standards Committee Member    3    Standards Committee Member    3    1
membership_type    Advisory Board Member    4    Advisory Board Member    4    1
membership_type    Editorial Board Member    5    Editorial Board Member    5    1
membership_type    Other    6    Other    6    1

In the modelLocator (or in the MXML Application module), define the ArrayCollection variables for the distinct lookup_types in the table, ensuring that the variable names follow a consistent naming convention; for this example, the table's distinct lookup_type string preceded by "AC_".  (Regardless of the naming convention you choose, your convention itself must include a value that is represented within the data itself, as the SQL query and result handling are dependent on it.)

        public var AC_office_location:ArrayCollection;
        public var AC_degree_type:ArrayCollection;
        public var AC_membership_type:ArrayCollection;

Add string variables to the modelLocator representing the lookup_type string:

        public var Lookup_office_location : String = "office_location";
        public var Lookup_degree_type : String = "degree_type";
        public var Lookup_membership_type : String = "membership_type";

Lastly, create an array variable of strings for the lookup_type values:

        public var LookupTypeArray:Array = new Array(Lookup_office_location,
            Lookup_degree_type,Lookup_membership_type);

In the primary application module (or where appropriate), use creationComplete to call for the population of the array collections:

            public function getSISLookupTables():void
            {
                var getEvent:Get_Lookup_Tables_DataEvent
                     = new Get_Lookup_Tables_DataEvent(model.LookupTypeArray);
                CairngormEventDispatcher.getInstance().dispatchEvent(getEvent);
            }

If you are using something other than Cairngorm, simply call your remote object method, passing the array of lookup strings as an argument.

The CFC function will accept the array as an argument, and use the array elements to perform a series of queries, and each query result set will be stored in a structure to be returned to the Flex/AIR application.  In this example, the function uses CFQUERY rather than CFSTOREDPROC for illustration purposes.

        <cffunction name="Get_Lookup_Tables_Data"
                access="REMOTE" output="no" static="yes"
                hint="This retrieves lookup table values based upon a lookup_type description string"
                returntype="struct">

            <cfargument name="lookupTypeArray" type="array" required="true" /> 
               
                <cfset thisStruct = structNew()>
                <cfloop from=1 to=#arrayLen(arguments.lookupTypeArray)# index="i">
                      <cfquery datasource="#Application.DB#" name="getLookups">
                     
                      SELECT [lookup_type]
                          ,[lookup_name]
                          ,[lookup_value]
                          ,[lookup_descr]
                          ,[lookup_order]
                          ,[active_flag]
                      FROM [databasename].[dbo].[lookups]
                        WHERE
                        [lookup_type] = '#arguments.lookupTypeArray[i]#'
                        AND
                        [active_flag] = 1
                      ORDER BY
                          [lookup_order] asc
                      </cfquery>
                     
                    <cfset newStructElementName = arguments.lookupTypeArray[i]>
                    <cfset thisStruct["#newStructElementName#"] = getLookups>
                   
                </cfloop>

             <cfreturn thisStruct>
            
        </cffunction>

Because we are using named structure keys rather than dot notation, Flex maintain the case-sensitivity when it deserializes the result.  This is key to the simplicity of the solution of this challenge.


The result handler (shown here in Cairngorm), is simple and short; since we used a standard naming convention for our array collection variables and for the lookup_type string variables, and since the returned structure used named keys, all that is required is to create the named key string for the array collection to be populated and to reference the array containing the name itself within a single for loop:

        public function result( event : Object ) : void
        {
        var i:int = 0;
        var j:int = model.LookupTypeArray.length;
        for (i;i<j;i++)
            {
            var thisACName:String = "AC_"+model.LookupTypeArray[i];
            model[thisACName] = event.result[model.LookupTypeArray[i]] as ArrayCollection;
            }
        }

 This example represents the solution of a single remote object call, a single result handler, resulting in a dynamic population of ArrayCollections for use in comboBoxes or other selection controls.  Adding a new lookup type to the database table requires only the addition of the new ArrayCollection variable, lookup_type string variable, and adding the string variable to the LookupTypeArray in order to get the new collection added to your application.  The remote object call, result handler and CFQuery/CFStoredProc will never need modification.

Simply for reference and any performance comparisons you wish to make on your own, this single call in my environment generates 30 (larger) array collections in Cairngorm in an average of 1.5 seconds.