CFLib.org – Common Function Library Project

QuerySetCellByKey(theQuery, keyField, keyFieldValue, columnName, newValue)

Last updated June 28, 2002

author

Shawn Seley

Version: 1 | Requires: CF5 | Library: DataManipulationLib

Description:
Allows changing of a query cell by knowing the primary key (or any other identifying field value) within the same row. This is useful for altering existing queries without having to keep track of (or manually finding) the respective row number. Throws an error if keyFieldValue is not found.

Return Values:
Returns a query.

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>

Modify with QuerySetCellByKey(my_query, "Username", "CUser", "Age", "31"):<br>
<br>
<cfset QuerySetCellByKey(my_query, "Username", "CUser", "Age", "31")>
<CFDUMP VAR="#my_query#">
</cfoutput>

Parameters:

Name Description Required
theQuery The query to modify. Yes
keyField The column to search against. Yes
keyFieldValue The value to search for. Yes
columnName The column to modify. Yes
newValue The value to set. Yes

Full UDF Source:

/**
 * Allows changing of a query cell by knowing a key field value within the same row.
 * 
 * @param theQuery 	 The query to modify. (Required)
 * @param keyField 	 The column to search against. (Required)
 * @param keyFieldValue 	 The value to search for. (Required)
 * @param columnName 	 The column to modify. (Required)
 * @param newValue 	 The value to set. (Required)
 * @return Returns a query. 
 * @author Shawn Seley (shawnse@aol.com) 
 * @version 1, June 28, 2002 
 */
function QuerySetCellByKey(theQuery, keyField, keyFieldValue, columnName, newValue){
	var key_field_value_list  = Evaluate("ValueList(theQuery.#keyField#)");
	var row_number            = ListFindNoCase(key_field_value_list, keyFieldValue);
	querysetCell(theQuery,columnName,newValue,row_number);
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson