CFLib.org – Common Function Library Project

Description:
Provides direct access to query cells by knowing the primary key (or any other identifying field value) within the same row. No need to keep track of row numbers. No need to loop through an entire query if you only need to get at a single row. Useful for effectively joining queries which may not be easily joined otherwise, and allows for more effective reuse of existing, cached, and/or server/application scoped queries. Throws an error if keyFieldValue is not found.

Return Values:
Returns a string.

Example:

<CFSET my_query = QueryNew("Username,Name,Age")>
<CFSET QueryAddRow(my_query, 1)>
<CFSET QuerySetCell(my_query,"Username", "CUser")>
<CFSET QuerySetCell(my_query,"Name","Chris User")>
<CFSET QuerySetCell(my_query,"Age", 30)>
<CFSET QueryAddRow(my_query, 1)>
<CFSET QuerySetCell(my_query,"Username", "AUser")>
<CFSET QuerySetCell(my_query,"Name","Albert User")>
<CFSET QuerySetCell(my_query,"Age", 10)>
<CFSET QueryAddRow(my_query, 1)>
<CFSET QuerySetCell(my_query,"Username", "BUser")>
<CFSET QuerySetCell(my_query,"Name","Brad User")>
<CFSET QuerySetCell(my_query,"Age", 20)>

<cfoutput>
Query:<br>
<br>
<CFDUMP VAR="#my_query#">
<br>
<br>

Read from the query:<br>
<br>
BUser is <b>#QueryGetCellByKey(my_query, "Username", "BUser", "Age")#</b> years old.<br>
The 10 year old is named <b>#QueryGetCellByKey(my_query, "Age", "10", "Name")#</b>.<br>
</cfoutput>

Parameters:

Full UDF Source:

/**
 * Provides direct access to query cells by knowing a key field value within the same row.
 * 
 * @param theQuery      The query to search. (Required)
 * @param keyField      The column value to return. (Required)
 * @param keyFieldValue      The value to search for in keyFIeld. (Required)
 * @param columnName      The column value to return. (Required)
 * @return Returns a string. 
 * @author Shawn Seley (shawnse@aol.com) 
 * @version 1, June 28, 2002 
 */
function QueryGetCellByKey(theQuery, keyField, keyFieldValue, columnName){
    var key_field_value_list  = Evaluate("ValueList(theQuery.#keyField#)");
    var row_number            = ListFindNoCase(key_field_value_list, keyFieldValue);

    return theQuery[columnName][row_number];

}