I have an interesting delimma - I'm attempting to build a universal reporting tool for our orders that can expand as the tool expands (columns added for new product types, etc.).
I went into the system key and pulled all of the column names for my ordering table (dbo.telcondet) and placed the column names into a table named dbo.Conduit_ColumnNames -- I have this set to update as new columns are added, etc. That's working fine.
Now, with a list of the dbo.Conduit_ColumnNames, I can create forms on the previous page where my users can select what values they what output into a table (that is, what columns to choose in telcondet - or the SELECT statement for the query, which is output as a variable). So far, so good.
For the WHERE statement, I was originally checking every attribute possibility like this example:
<cfif IsDefined("form.ProbabilityOfClose"
>
<cfset ProbabilityOfClose = "and ProbabilityOfClose IN (#form.ProbabilityOfClose#)">
<cfelse>
<cfset ProbabilityOfClose = ''>
</cfif>
<cfif IsDefined("form.ExpMonthClose">
<cfset ExpMonthClose = "and ExpMonthClose IN (#form.ExpMonthClose#)">
<cfelse>
<cfset ExpMonthClose = ''>
</cfif>
....ect. ect.
I soon realized I was writing the same statement over and over and only changing what could be pulled from a <cfloop of the column names. This is where I ran into problems. I attempted to substitute ProbabilityOfClose, ExpMonthClose etc. like the following:
<cfloop query = "rsProducts">
<cfif IsDefined("form.Subsidiary">
<cfset #rsProducts.ColumnName# = "and #rsProducts.ColumnName# IN (#form.#rsProducts.ColumnName##)">
<cfelse>
<cfset #rsProducts.ColumnName# = ''>
</cfif>
</cfloop>
It doesn't working. I get an invalid parser construct right at the third line where the period is. Additionally, for the table output, I have the exact same issue (see full code below). I have tried every way of concatanation I can think of, and I cannot get it to work. I really want to be able to build the table like this, rather than have to go in and hard code the entire table as it will expand and contract.
The full code is listed below as I wished for it to work:
<cfinclude template="../../../Connections/connCONMKTSL02.cfm">
<!---set SelectOutput to single quotes for the select statment--->
<cfset SQSelect = #replace(form.selectOutput,"'","","all"#>
<!---Selects all of the column headers and column names based on columns chosen on previous page--->
<cfquery name="rsProducts" datasource=#MM_connCONMKTSL02_DSN# username=#MM_connCONMKTSL02_USERNAME# password=#MM_connCONMKTSL02_PASSWORD#>
SELECT ColumnName, CommonName FROM dbo.Conduit_ColumnNames WHERE ColumnName IN (#PreserveSingleQuotes(selectOutput)#)
</cfquery>
<!---Sets all of the Where variables--->
<cfloop query = "rsProducts">
<cfif IsDefined("form.Subsidiary">
<cfset #rsProducts.ColumnName# = "and #rsProducts.ColumnName# IN (#form.#rsProducts.ColumnName##)">
<cfelse>
<cfset #rsProducts.ColumnName# = ''>
</cfif>
</cfloop>
<!---pulls from main record table columns based on previous select--->
<cfquery name="rsTecondetPull" datasource=#MM_connCONMKTSL02_DSN# username=#MM_connCONMKTSL02_USERNAME# password=#MM_connCONMKTSL02_PASSWORD#>
SELECT #SQSelect# FROM dbo.telcondet
WHERE drecvd BETWEEN '#form.Date1#' AND '#form.Date2# 11:59:59 PM' <cfloop query = "rsProducts">#PreserveSingleQuotes(rsProducts.ColumnName)#</cfloop>
</cfquery>
<cfscript>
rsProducts_NumRows = rsProducts.RecordCount;
</cfscript>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table border="1" cellspacing="1" cellpadding="4">
<!---column headers--->
<tr><cfoutput query="rsProducts">
<td bgcolor="##3366CC"><font face="Arial, Helvetica, sans-serif" color="##CCCCCC">#rsProducts.CommonName#</font></td>
</cfoutput> </tr>
<!---Output of query--->
<cfoutput query = "rsTecondetPull">
<tr>
<cfloop query = "rsProducts">
<td><font face="Arial, Helvetica, sans-serif"> #rsTelcondet.#ColumnName##</font></td>
</cfloop>
</tr>
</cfoutput>
</table>
</body>
</html>
Scott Neth
Web Designer/Cyberpunk
I went into the system key and pulled all of the column names for my ordering table (dbo.telcondet) and placed the column names into a table named dbo.Conduit_ColumnNames -- I have this set to update as new columns are added, etc. That's working fine.
Now, with a list of the dbo.Conduit_ColumnNames, I can create forms on the previous page where my users can select what values they what output into a table (that is, what columns to choose in telcondet - or the SELECT statement for the query, which is output as a variable). So far, so good.
For the WHERE statement, I was originally checking every attribute possibility like this example:
<cfif IsDefined("form.ProbabilityOfClose"
><cfset ProbabilityOfClose = "and ProbabilityOfClose IN (#form.ProbabilityOfClose#)">
<cfelse>
<cfset ProbabilityOfClose = ''>
</cfif>
<cfif IsDefined("form.ExpMonthClose"
><cfset ExpMonthClose = "and ExpMonthClose IN (#form.ExpMonthClose#)">
<cfelse>
<cfset ExpMonthClose = ''>
</cfif>
....ect. ect.
I soon realized I was writing the same statement over and over and only changing what could be pulled from a <cfloop of the column names. This is where I ran into problems. I attempted to substitute ProbabilityOfClose, ExpMonthClose etc. like the following:
<cfloop query = "rsProducts">
<cfif IsDefined("form.Subsidiary"
><cfset #rsProducts.ColumnName# = "and #rsProducts.ColumnName# IN (#form.#rsProducts.ColumnName##)">
<cfelse>
<cfset #rsProducts.ColumnName# = ''>
</cfif>
</cfloop>
It doesn't working. I get an invalid parser construct right at the third line where the period is. Additionally, for the table output, I have the exact same issue (see full code below). I have tried every way of concatanation I can think of, and I cannot get it to work. I really want to be able to build the table like this, rather than have to go in and hard code the entire table as it will expand and contract.
The full code is listed below as I wished for it to work:
<cfinclude template="../../../Connections/connCONMKTSL02.cfm">
<!---set SelectOutput to single quotes for the select statment--->
<cfset SQSelect = #replace(form.selectOutput,"'","","all"
#><!---Selects all of the column headers and column names based on columns chosen on previous page--->
<cfquery name="rsProducts" datasource=#MM_connCONMKTSL02_DSN# username=#MM_connCONMKTSL02_USERNAME# password=#MM_connCONMKTSL02_PASSWORD#>
SELECT ColumnName, CommonName FROM dbo.Conduit_ColumnNames WHERE ColumnName IN (#PreserveSingleQuotes(selectOutput)#)
</cfquery>
<!---Sets all of the Where variables--->
<cfloop query = "rsProducts">
<cfif IsDefined("form.Subsidiary"
><cfset #rsProducts.ColumnName# = "and #rsProducts.ColumnName# IN (#form.#rsProducts.ColumnName##)">
<cfelse>
<cfset #rsProducts.ColumnName# = ''>
</cfif>
</cfloop>
<!---pulls from main record table columns based on previous select--->
<cfquery name="rsTecondetPull" datasource=#MM_connCONMKTSL02_DSN# username=#MM_connCONMKTSL02_USERNAME# password=#MM_connCONMKTSL02_PASSWORD#>
SELECT #SQSelect# FROM dbo.telcondet
WHERE drecvd BETWEEN '#form.Date1#' AND '#form.Date2# 11:59:59 PM' <cfloop query = "rsProducts">#PreserveSingleQuotes(rsProducts.ColumnName)#</cfloop>
</cfquery>
<cfscript>
rsProducts_NumRows = rsProducts.RecordCount;
</cfscript>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table border="1" cellspacing="1" cellpadding="4">
<!---column headers--->
<tr><cfoutput query="rsProducts">
<td bgcolor="##3366CC"><font face="Arial, Helvetica, sans-serif" color="##CCCCCC">#rsProducts.CommonName#</font></td>
</cfoutput> </tr>
<!---Output of query--->
<cfoutput query = "rsTecondetPull">
<tr>
<cfloop query = "rsProducts">
<td><font face="Arial, Helvetica, sans-serif"> #rsTelcondet.#ColumnName##</font></td>
</cfloop>
</tr>
</cfoutput>
</table>
</body>
</html>
Scott Neth
Web Designer/Cyberpunk
