Updating a Query Field, while Retaining a Query Object
This is an expansion on Jim's blog post Create an Excel document from any ColdFusion query object. I had commented a while ago that, "One thing to note is that when you are sending a query that uses a date value, it won't print out right in the excel doc. I had to send my query through a loop and do a DateFormat() update to my date field and then send the modified query to the excel function.". Jamie has since asked how I did that, so I will expand in a new blog entry.
Basically, we want to create a temporary query object in Coldfusion and while transferring the data from the query used on the database we will re-format the fields that need it.
In my example, I am going to use the same basic query as Jim, but with 2 minor tweaks. I am adding a field, for the sake of having a date. Also, I'm going to use the variables' names until the end, because I was having difficulties getting this to work using the user-friendly names.
Here is the query we will use:
select
emp_id,
emp_fname,
emp_lname,
emp_email,
emp_birthday
from employees
order by emp_lname,emp_fname,emp_id
</cfquery>
Next, we will handle the meat of this entry - setting up the temporary query object and looping through the old query. Notice how when I set up qTempEmpDetails, the 2nd argument to QueryNew() is a list of the data types for the columns in the first argument. Also, note how I set the birthday field as a varchar as opposed to date. If I was to set it as date, even with the DateFormat(), it will display in timestamp format ({ts '2009-06-10 00:00:00'}).
<cfloop query="qEmpDetails">
<cfscript>
queryaddrow( qTempEmpDetails );
QuerySetCell( qTempEmpDetails,"emp_id",qEmpDetails.emp_id );
QuerySetCell( qTempEmpDetails,"emp_fname",qEmpDetails.emp_fname );
QuerySetCell( qTempEmpDetails,"emp_lname",qEmpDetails.emp_lname );
QuerySetCell( qTempEmpDetails,"emp_email",qEmpDetails.emp_email );
QuerySetCell(qTempEmpDetails,"emp_birthday",DateFormat(qEmpDetails.emp_birthday,"mm/dd/yyyy") );
//DateFormat() to change the birthday
</cfscript>
</cfloop>
As we want to have the user friendly column names, I am going to call on an entry by Ben Nadel to change the names of our columns, because apparently a query of queries doesn't like it when I use the [Employee ID] notation.
columns = qTempEmpDetails.GetColumnNames(); //Grab all the column names for this query
columnList = ArrayToList(columns); // turn them into a list
for(i=1; i LTE listlen(columnList); i=i+1){ //loop through the list
columnName = ListGetAt(columnList,i); //grab the column name that is currently used
switch(columnName){ // use a switch statement to set the appropriate user friendly column name
case 'emp_id':
columns[i] = 'Employee ID';
break;
case 'emp_fname':
columns[i] = 'Employee First Name';
break;
case 'emp_lname':
columns[i] = 'Employee Last Name';
break;
case 'emp_email':
columns[i] = 'Employee Email';
break;
case 'emp_birthday':
columns[i] = 'Employee Birthday';
break;
default: //use a default of the original column name so we don't have any columns without names.
columns[i] = columnName;
break;
};
}
qTempEmpDetails.SetColumnNames(columns);
</cfscript>
Lastly, we'll resend this query to Jim's excel function:
// Note my components are stored in the folder /shared/components;
// The component name is 'Utils.cfc'
utilsObj = createObject('component', 'shared.components.Utils');
utilsObj.queryToExcel(qTempEmpDetails,'employee_details');
</cfscript>
Well, the first part is pretty straightforward. We just loop through a query and build a temporary query with the newly formatted data. The tough part was getting the column names to be user friendly again. Does anyone know if there is a better way to accomplish this when working with a query that we have built in Coldfusion?
Also, thanks Jaime for giving me this to run with in a blog entry!
'Til next time, Bridget
I was able to resolve my problem dealing with the date formatting issue thanks to your great contribution to this blog website.
I followed your instructions, applied the technique which is well documented, and was able to make it work for my application.
I just posted the updated link to my report for my users and now I will be able to sleep tonight.
Again, I really appreciate your help, thanks!
Query New can be built on the fly and the second argument isn't required (but then you leave it up to CF which does require extra processing power to determine the type). If you post a snippet of your code, I can try and help.