asked on

How do I access query data in a ColdFusion Function?

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!

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

... 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

SiobhanElara

ASKER

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

_agx_

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?

_agx_

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?

SiobhanElara

ASKER

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!

_agx_

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