Sunday, August 3, 2008

CFGRID and CFC Binding How To

I avoided using a CFC binding initially because I didn't grok how and, before I knew that my route made pagination impossible, a query in the HTML CFGRID seemed so much easier. But here is how the binding part is done.

In the CFC file you're going to have the following:

1. A function name with arguments
2. Your query
3. Not always but in my case, a query return for the grid.

The function is going to have remote access and return a structure. Here is my code for it:

<cffunction name="comments" access="remote" returntype="struct">
<cfargument name="page" required="true">
<cfargument name="pageSize" required="true">
<cfargument name="gridsortcolumn" required="true">
<cfargument name="gridsortdirection" required="true">

Page means, "Do you want this to do paging, where things are broken into separate pages?" PageSize means, "How big (how many records) should each page be?" The sorts are fairly obvious I think...

If you have the above it means you will have to have the page and pageSize defined in your CFGRID tag in your CFM page, and you can control it from there. I did not define my grid sorts and it didn't break.

Next you have your query. Whatever query you were using to output records prior to CFGRID will probably work fine, but I ended up having to hack mine to do all kinds of stuff that CFGRID made a royal pain in the butt, so I fixed it through SQL instead of CFML. Will cover that in another post.

Then you have your query "return". You find this in documentation under functions, queryconvertforgrid function.

In my case it has three values:
1 - the name of my query
2 - page
3 - pagesize

And I mean those strings (page and pagesize) not true/numeric. Here's my code, where my query is called selectAll:

<cfreturn queryconvertforgrid(selectAll,page,pagesize)/>

Now to put the CFC together you'll want these three things in order. I have a file called getcomments.cfc and here is the code, with the query revised for public use of course. Note that the CFC did not find my datasource which is a variable set in my Application.cfm file, so I had to manually CFSET that value before running the query.

<cffunction name="comments" access="remote" returntype="struct">
<cfargument name="page" required="true">
<cfargument name="pageSize" required="true">
<cfargument name="gridsortcolumn" required="true">
<cfargument name="gridsortdirection" required="true">

<cfquery datasource="myds" name="selectAll" maxrows="1000">
My query goes here

<cfreturn queryconvertforgrid(selectAll,page,pagesize)/>

Then in my CFM file, my HTML format CFGRID used this code to call it:

<cfform name="myform" id="myformid">
name = "myGrid"
<cfgridcolumn name="id" display="no" >
<cfgridcolumn name="DateEntered" display="yes" header="Date" mask="EEE DD-MMM-YY H:NN A">
<cfgridcolumn name="Alias" italic="yes" display="yes" header="From">
<cfgridcolumn name="See" width="35" display="yes" >
<cfgridcolumn name="theComment" fontsize="1.2em" textColor="##661A99" width="700" display="yes" header="Comment">

Notice that there is some styling in one of the columns and that works, and date formatting in the date column which did not work at all so I did something different, which I'll cover in another post. The 'see' value there is a big string from a special SQL call that gave me an HREF link, and an image, and appended the record ID, because stupid CFGRID wouldn't let me get rid of underlining every damn thing if I used the HREF attribute.


Anonymous said...

I am new to CFGRID and CFC so your blog was a huge help! I am having trouble adding a hyperlink to the rows... I will check your others entries.

Thank you very much!


Anonymous said...

How do you pass in a variable to the binding cfc? I've got a page where I want to filter what shows in the grid to be specific to the user, based on a session variable. How do I pass in the value of that session variable on the call to the CFC so the query in the CFC can use it?

RedCairo said...

"How do you pass in a variable to the binding cfc?" Will respond this evening when I get off work. - PJ

Anonymous said...

The only way I have been able to do it is using form elements, so, i do it like so (gotta add back in the > and <'s):

input type="hidden" name="hSFV" id="hSFV" value="#attributes.ScoreFilterValue#" /

cfgrid name="myGrid" format="HTML" height="450" width="400" striperows="Yes" striperowcolor="gainsboro" maxrows="50" selectmode="row" bind="cfc:query.cfc.ActionPlanSummaryCFC.GetActionPlanSummaryData({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{hSFV})"

Anonymous said...

in the cfgrid tag: