Excel/ Access Functionality

[ironyx]

We are about to work on a project that is required to take a spreadsheet, loaded from an access database, that has thousands of records and emulate it using coldfusion. The application loads all of the data, then filters it based on login and dynamic filters. On the database side, based on each reporting category we have the tables as datamarts, summarized as much as possible. I was wondering what developers though would be the best approach in how we would be able to do this most efficiently in coldfusion. One suggestion here was to load the data first but that seemed to be to be inefficient. I was thinking that keeping the data traffic minimal but not sure how this would be done with so many query choices. There were 20-30 drop down options on some pages and all dynamic. If you need more information I would be happy to provide it. I was just looking for general overarching approach opinions and maybe a direction on where to research.

Thanks!
Va.

[c_wigginton]

If the number of options being returned for filtering is dynamic, use a formfield name lookup. i.e.

Let's say have a filter form that will submit a series of filters that must be applied as selection criteria on a database query. Some of these filters are created on the fly, may have null values or even support multiple selections. Iterate over the form elements and perform a type lookup using a UDF based on the field name, This could be as simple as structkey lookup. In this example I created a small fieldInfo object which lets me assign multiple values to the fieldInfo parameters. Though I didn't illustrate it here, you could even add additional field info such as maxlength.

<cffunction name="getFieldType" returntype="struct">
<cfargument name="fieldName" type="string">
<cfscript>
var fieldInfo = StructNew();
fieldInfo.isList = false;

switch(Arguments.fieldName){
case 'numFilter':
fieldInfo.type = 'cf_sql_integer';
break;
case 'name':
case 'dept':
fieldInfo.type = 'cf_sql_varchar';
break;
case 'multiple':
fieldInfo.type = 'cf_sql_integer';
fieldInfo.isList = true;
break;
default:
fieldInfo.type = '';
break;
}

</cfscript>
<cfif fieldInfo.type eq "">
<cfthrow message="unknown field name">
</cfif>
<cfreturn fieldInfo>
</cffunction>


<cfquery name="qryFilter" datasource="myDSN">
select mycol1,mycol2,mycol3 from mytable
where
1=1
<cfloop collection="#form#" item="key">
<cfset targetInfo = getFieldType(key)>
<cfif len(form[key]) and targetInfo.isList>
and #key# in(<cfqueryparam cfsqltype="#targetInfo.type#" list="yes" separator="," value="#form[key]#">)
<cfelseif len(form[key])>
and #key# = <cfqueryparam cfsqltype="#targetInfo.type#" value="#form[key]#">
</cfif>
</cfloop>
</cfquery>