We help IT Professionals succeed at work.

How do I access query data in a ColdFusion Function?

High Priority
134 Views
Last Modified: 2019-07-17
I'm trying to figure out how to access query data in a ColdFusion function. I have a query (qMyQuery) that's been returned from another function that will look something like this:

   | year   |  data
1  |  2014  |  1234
-------------------
2  | 2015  |  2345
-------------------
3  |  2016  |  3454

Open in new window


I'm sending it to a function that will figure out if the latest number is an increase or decrease and set the variables myQueryClass and myQueryDirectionText accordingly. This is what I'm sending:

<cfinvoke component="cfc.helpers" 
		method="setCSS" 
		returnvariable="arrayCSS" 
		forQuery="qMyQuery"
		queryData="#qMyQuery#"
		variablePrefix = "myQuery"
		>

Open in new window


And here is the function:
<cfcomponent>
	<cffunction name="setCSS" access="public" returntype="array">
		<cfargument name="forQuery" type="string" required="yes" hint="the name of the query for which to set the css">
		<cfargument name="queryData" type="query" required="yes" hint="the query content">
		<cfargument name="upIsGood" type="boolean" required="yes" default="TRUE" hint="is an increase a good thing">
		<cfargument name="variablePrefix" type="string" required="yes" hint="what to preface the class/txt variables with">
		<cfargument name="directionTextUp" type="string" required="yes" default="Increasing" hint="the text for an increase in data">
		<cfargument name="directionTextDown" type="string" required="yes" default="Decreasing" hint="the text for an decrease in data">
		<!--- start function --->	
		<cfif upIsGood>
			<cfif queryData.recordcount GT queryData[recordcount-1]>
				<cfset arrayCSS[variablePrefix & "Class"] = "success">
				<cfset arrayCSS[variablePrefix & "DirectionTxt"] = ARGUMENTS.directionTextUp>
			<cfelse>
				<cfset arrayCSS[variablePrefix & "Class"] = "danger">
				<cfset arrayCSS[variablePrefix & "DirectionTxt"] = ARGUMENTS.directionTextDown>
			</cfif>
		</cfif>
		<!--- if unchanged --->
		<cfif queryData.[recordcount] EQ queryData.[recordcount-1]>
			<cfset arrayCSS[variablePrefix & "Class"] = "info">
			<cfset arrayCSS[variablePrefix & "DirectionTxt"] = "Unchanged">
		</cfif>
		<!--- if first data --->
		<cfif queryData..recordCount EQ 1>
			<cfset arrayCSS[variablePrefix & "Class"] = "info">
			<cfset arrayCSS[variablePrefix & "DirectionTxt"] = "First Data">
		</cfif>
		<cfreturn arrayCSS>
	</cffunction>
</cfcomponent>

Open in new window


I can not for the life of me figure out what should be used in place of (for example) queryData.recordcount. I've tried variations on Evaluate, I've tried brackets, I've tried #variables#, but they all throw different errors. What am I doing wrong? Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
if the latest number is an increase or decrease

Do you mean check if the value in the current row is greater than the value in the previous row?

     2014 | 1234
     -------------------
     2015 | 2345          <== 2345 Increase from 1234
     -------------------
     2016 | 3454            <== 3454 Increase from 2345

If so, you're missing the query column name. Structure notation requires both a query column name and row number, i.e.  

          queryName["columnName"][rowNumber].
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
... also, to compare the values in each row, you must loop through the query.  Here's an example:


Function

	
<cffunction name="setCSS" access="public" returntype="array">

	<!--- Note: You don't need "Required" when a "Default" is supplied --->
	<cfargument name="data" type="query" required="yes" hint="the query content">
	<cfargument name="column" type="string" required="yes" hint="query column containing values to compare">
	<cfargument name="prefix" type="string" required="yes" hint="what to preface the class/txt variables with">
	<cfargument name="upIsGood" type="boolean" default="true" hint="is an increase a good thing">
	<cfargument name="textUp" type="string" default="Increasing" hint="the text for an increase in data">
	<cfargument name="textDown" type="string" default="Decreasing" hint="the text for an decrease in data">

	<!--- This should be localized --->
	<cfset LOCAL.results = []>
	
	<!--- Using variables to avoid repeating concatenation multiple times --->
	<cfset Local.cssClass = arguments.prefix & "Class">
	<cfset Local.cssText  = arguments.prefix & "DirectionTxt">
							
							
	<cfloop query="arguments.data">

		<cfset Local.cssProps  = {}>
		
		<cfset local.isFirstRow = arguments.data.currentRow EQ 1>
		<cfset local.newValue  = arguments.data[ arguments.column ][ currentRow ]>

	        <!--- If it's not the first row, get previous value for comparison --->	
		<cfif !local.isFirstRow>
			<cfset local.oldValue = arguments.data[ arguments.column ][ currentRow-1 ]>
		</cfif>	
		
		<!--- First row --->
		<cfif local.isFirstRow>
		
			<cfset Local.cssProps[ Local.cssClass ] = "info">
			<cfset Local.cssProps[ Local.cssText ]  = "First Row">
		
		<!--- No change --->
		<cfelseif local.newValue eq local.oldValue>
		
			<cfset Local.cssProps[ Local.cssClass ] = "info">
			<cfset Local.cssProps[ Local.cssText ]  = "Unchanged">
		
		<!--- NOT tracking changes --->
		<cfelseif !arguments.upIsGood>
		
			<cfset Local.cssProps[ Local.cssClass ] = "info">
			<cfset Local.cssProps[ Local.cssText ]  = "UNKNOWN">
			
		<!--- Increase --->
		<cfelseif local.newValue gt local.oldValue>

			<cfset Local.cssProps[ Local.cssClass ] = "success">
			<cfset Local.cssProps[ Local.cssText ]  = arguments.textUp>
		
		<!--- Decrease --->
		<cfelse>

			<cfset Local.cssProps[ Local.cssClass ] = "danger">
			<cfset Local.cssProps[ Local.cssText ]  = arguments.textDown>
		
		</cfif>

		
		<cfset arrayAppend( LOCAL.results , Local.cssProps )>

	</cfloop>

	<cfreturn LOCAL.results >
</cffunction>	

Open in new window


Example


	
    <cfset qTest = queryNew("TheYear,TheValue"
                                              , "integer,integer"
                                              , [ {TheYear=2014,TheValue=1234}
                                                 , {TheYear=2015,TheValue=2345}
                                                 , {TheYear=2016,TheValue=3454}
                                                 , {TheYear=2017,TheValue=1234}
                                              ])>

   <cfset result = setCSS( data="#qTest#"
                                           , column="TheValue"
                                           , prefix = "myQuery" )>
					
   <cfdump var="#result#">

Open in new window

Author

Commented:
Oops, I might not have worded that correctly. I don't need to know if EACH line is greater than the last, just if the MOST RECENT year's data is higher of lower than the immediately previous year (in this case, if 2016 is higher or lower than 2015.) As such, I shouldn't have to loop through the query, correct? I've updated my function below and it appears to be working. (And I can't believe I forgot to include the name of the column.)

<cfcomponent>
	<cffunction name="setCSS" access="public" returntype="struct">
		<cfargument name="forQuery" type="string" required="yes" hint="the query for which to set the css">
		<cfargument name="queryData" type="query" required="yes" hint="the query content">
		<cfargument name="upIsGood" type="boolean" default="TRUE" hint="is an increase a good thing">
		<cfargument name="variablePrefix" type="string" required="yes" hint="what to preface the class/txt variables with">
		<cfargument name="directionTextUp" type="string" default="Increasing" hint="the text for an increase in data">
		<cfargument name="directionTextDown" type="string" default="Decreasing" hint="the text for an decrease in data">
			
		<cfset LOCAL.cssClass = ARGUMENTS.variablePrefix & "Class">
		<cfset LOCAL.cssText  = ARGUMENTS.variablePrefix & "DirectionTxt">
			
		<!--- start function --->	
		<cfif upIsGood>
			<cfif ARGUMENTS.queryData["data"][queryData.recordcount] GT ARGUMENTS.queryData["data"][queryData.recordcount-1]>
				<cfset LOCAL.arrayCSS[LOCAL.cssClass] = "success">
				<cfset LOCAL.arrayCSS[LOCAL.cssText] = ARGUMENTS.directionTextUp>
			<cfelse>
				<cfset LOCAL.arrayCSS[LOCAL.cssClass] = "danger">
				<cfset LOCAL.arrayCSS[LOCAL.cssText] = ARGUMENTS.directionTextDown>
			</cfif>
		</cfif>
		<!--- if unchanged --->
		<cfif ARGUMENTS.queryData["data"][queryData.recordcount] EQ ARGUMENTS.queryData["data"][queryData.recordcount-1]>
			<cfset LOCAL.arrayCSS[LOCAL.cssClass] = "info">
			<cfset LOCAL.arrayCSS[LOCAL.cssText] = "Unchanged">
		</cfif>
		<!--- if first data --->
		<cfif ARGUMENTS.queryData["data"][queryData.recordcount] EQ 1>
			<cfset LOCAL.arrayCSS[LOCAL.cssClass] = "info">
			<cfset LOCAL.arrayCSS[LOCAL.cssText] = "First Data">
		</cfif>
		<cfreturn LOCAL.arrayCSS>
	</cffunction>
</cfcomponent>

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Edit:

Yeah, in that case you don't have to loop. However, you need to tweak the code or it'll throw an error when the query only contains 1 record or is empty. That may be unlikely, but it's possible, so the function should handle that scenario.

Also, the function doesn't always return a value.  What should happen if the current and previous values are different and arguments.upIsGood = false?
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Actually the current function throws an error in 3 cases:

1. if query only contains 1 record
2. query has 0 records
3. arguments.upIsGood = false

What are you expecting the function to return in cases #2 and #3?

Author

Commented:
I've actually got it set up to do what it needs to now with some if/then reordering; I just hadn't gotten to that point because I couldn't get past the errors it was throwing from the very beginning. Thanks so much for your help!
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Good to hear. Just wanted to be sure you knew about the 3 errors :-) Anyway, glad it helped!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.