ColdFusion 8 cfgrid - Filter Grid Data, Show/Hide Columns and using the YUI Buttons Library
The next step in the Project will be the User Administration page. The idea for the administration pages is simple:
You can implement a similar functionality using other controls, even showing lists of visible and hidden columns, allowing the user to switch between them and apply it.
Next I am going to build on this and implement add/edit/delete functions.
Update: Download full Source Here
- Pagination (min 10 records per page)
- Sorting on all columns
- Ability to Filter grid Data by various columns
- User Preferences to show/hide columns on the grid
- Add/Edit/Delete Function Access from grid page
The ColdFusion 8 cfgrid implementation allows us to accomplish points 1 and 2 out of the box. CF8 Ajax featurs will allow us to accomplish point 3. Further looking into the ExtJS library means we can do 4 too. We will worry about point 5 for later. First, check out the little Demo: cfgrid demo
So, the Users table has some simple columns, the ones we will worry about for this demo are: FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone. We have a CFC with two functions for the demo - getUsers() which returns the grid query and getUserColumns() which is used to return the columns we can filter by as a query.
So, our Grid looks pretty simple:1 <div style="min-heigh:200px;">
2 <cfgrid name="usersgrid" pagesize="5" format="html" width="100%" height="200"
3 bind="cfc:Users.getUsers({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filtercolumn},{filter})">
4
5 <cfgridcolumn name="FirstName" header="First Name">
6 <cfgridcolumn name="LastName" header="Last Name">
7 <cfgridcolumn name="UserName" header="User Name">
8 <cfgridcolumn name="DisplayName" header="Display Name">
9 <cfgridcolumn name="UserAccountingCode" header="GL Code (User)" display="false">
10 <cfgridcolumn name="Phone" header="Phone No." display="false">
11
12 </cfgrid>
13 </div>
We also build the top Filter By Select box, Filter Text Input, and the Filter button.
2 <cfgrid name="usersgrid" pagesize="5" format="html" width="100%" height="200"
3 bind="cfc:Users.getUsers({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filtercolumn},{filter})">
4
5 <cfgridcolumn name="FirstName" header="First Name">
6 <cfgridcolumn name="LastName" header="Last Name">
7 <cfgridcolumn name="UserName" header="User Name">
8 <cfgridcolumn name="DisplayName" header="Display Name">
9 <cfgridcolumn name="UserAccountingCode" header="GL Code (User)" display="false">
10 <cfgridcolumn name="Phone" header="Phone No." display="false">
11
12 </cfgrid>
13 </div>
1 Filer By: <cfselect id="filtercolumn" name="filtercolumn" bind="cfc:Users.getUserColumns()"
2 display="ColumnName" value="ColumnName" bindOnLoad="true" />
3
4 Filter Text: <cfinput type="text" id="filter" name="filter">
5
6 <cfinput type="button" name="filterbutton" value="Filter" id="filterbutton"
7 onclick="ColdFusion.Grid.refresh('usersgrid',false)">
Very simply here, we get the columns that the user can filter by from and populate the select box. The filter button calls the Grid refresh function when clicked. On the grid, we have tied the bind to the {filtercolumn} and {filter} controls. As a result, our grid will also change if the user just tabs out of the filter input box, but since that is not always clear to an application user, clicking the filter button after typing some text will accomplish the same result. Changing the filter by dropdown will also automatically filter the grid. So, this is a very simple implementation of a grid filter, no JS needed. You can also find other (some similar) cfgrid filter implementations, just search for "cfgrid filter" in your favourite search engine.
The second thing then I had to do was give the user ability to show/hide some of the columns. As you can see, our default usergrid view has UserAccountingCode and Phone columns set to hidden. So, we need a way to turn them visible based on user input.
This also gave me the opportunity to implement the Yahoo User Interface Button Library. I was particularly interested in the Checkbox Button implementation.
So, first I specified the div that will hold the buttons.
2 display="ColumnName" value="ColumnName" bindOnLoad="true" />
3
4 Filter Text: <cfinput type="text" id="filter" name="filter">
5
6 <cfinput type="button" name="filterbutton" value="Filter" id="filterbutton"
7 onclick="ColdFusion.Grid.refresh('usersgrid',false)">
1 Click to show the columns:<br>
2 <div id="buttons" class="yui-skin-sam">
3 </div>
The next objective was to build the buttons on page load. The buttons will be built by parsing through the cfgrid columns and determining which are hidden and creating a show button for each hidden column.
2 <div id="buttons" class="yui-skin-sam">
3 </div>
1 function getGrid()
2 {
3 grid = ColdFusion.Grid.getGridObject('usersgrid');
4 cols = grid.getColumnModel();
5 for(var i=0; i<cols.getColumnCount();i++)
6 {
7 colid = cols.getColumnId(i);
8 column = cols.getColumnById(colid);
9 if(column.hidden == true && column.header != 'CFGRIDROWINDEX')
10 {
11 var showButton = new YAHOO.widget.Button({
12 type: "checkbox",
13 label: column.header,
14 id: column.header,
15 name: column.header,
16 value: colid,
17 container: "buttons",
18 title: "Show Column",
19 checked: false });
20
21 showButton.addListener("click",showColumn)
22 }
23 }
24 }
We first get the grid object and then use ExtJS functions to get the ColumnModel. We iterate over the ColumnModel and get each column on the grid. We check to see whether that column is hidden or not, if not we build the Yahoo Button and pass in some configuration attributes. The value of the checkbox is set to the ColumnId as this will allow us to switch Column visibility easily onclick. We also add the event listener showColumn for the click event to the button. The one thing I did not know till now was that a column called CFGRIDROWINDEX was automatically added to the grid, and we don't want to turn it visible so we skip over it.
2 {
3 grid = ColdFusion.Grid.getGridObject('usersgrid');
4 cols = grid.getColumnModel();
5 for(var i=0; i<cols.getColumnCount();i++)
6 {
7 colid = cols.getColumnId(i);
8 column = cols.getColumnById(colid);
9 if(column.hidden == true && column.header != 'CFGRIDROWINDEX')
10 {
11 var showButton = new YAHOO.widget.Button({
12 type: "checkbox",
13 label: column.header,
14 id: column.header,
15 name: column.header,
16 value: colid,
17 container: "buttons",
18 title: "Show Column",
19 checked: false });
20
21 showButton.addListener("click",showColumn)
22 }
23 }
24 }
1 function showColumn(e)
2 {
3 //alert(this.get("value"));
4 var showColId = this.get("value");
5 showCol = cols.getColumnById(showColId);
6 if(showCol.hidden == false)
7 {
8 showCol.hidden = true;
9 }else
10 {
11 showCol.hidden = false;
12 }
13 grid.reconfigure(grid.getDataSource(),cols);
14 }
The showColumn function listens for the event, gets the ColumnId and toggles the display on/off. We then use the ExtJS function reconfigure(Datastore,columnmodel) to rebuild the grid and pass in the changed columnmodel.
I also applied some custom skinning to the YUI Buttons. The final code for the page is:
2 {
3 //alert(this.get("value"));
4 var showColId = this.get("value");
5 showCol = cols.getColumnById(showColId);
6 if(showCol.hidden == false)
7 {
8 showCol.hidden = true;
9 }else
10 {
11 showCol.hidden = false;
12 }
13 grid.reconfigure(grid.getDataSource(),cols);
14 }
1 <head>
2
3 <!-- Individual YUI CSS files -->
4 <link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.6.0/build/button/assets/skins/sam/button.css">
5 <!-- Individual YUI CSS files -->
6 <link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.6.0/build/datatable/assets/skins/sam/datatable.css">
7
8 <!-- Individual YUI JS files -->
9 <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/yahoo-dom-event/yahoo-dom-event.js"></script>
10 <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/element/element-beta-min.js"></script>
11 <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/button/button-min.js"></script>
12
13 <style>
14
15 .yui-skin-sam .yui-button {
16 background:#ffffff;
17 color: #071b45;
18 border-color:#a3a3a3;
19 }
20
21 .yui-skin-sam .yui-button button,
22 .yui-skin-sam .yui-button a {
23 font-size: 75%;
24 color:#071b45;
25
26 }
27
28 .yui-skin-sam .yui-button-hover {
29 background: #DDDDDD;
30 color:#071b45;
31 }
32
33 .yui-skin-sam .yui-radio-button-checked .first-child,
34 .yui-skin-sam .yui-checkbox-button-checked .first-child {
35 border-color:#a3a3a3;
36 }
37
38 .yui-skin-sam .yui-radio-button-checked,
39 .yui-skin-sam .yui-checkbox-button-checked {
40 border-color:#a3a3a3;
41 background:#DDDDDD;
42 color:#071b45;
43 }
44
45 </style>
46
47 <script>
48 function showColumn(e)
49 {
50 //alert(this.get("value"));
51 var showColId = this.get("value");
52 showCol = cols.getColumnById(showColId);
53 if(showCol.hidden == false)
54 {
55 showCol.hidden = true;
56 }else
57 {
58 showCol.hidden = false;
59 }
60 grid.reconfigure(grid.getDataSource(),cols);
61 }
62
63 function getGrid()
64 {
65 grid = ColdFusion.Grid.getGridObject('usersgrid');
66 cols = grid.getColumnModel();
67 for(var i=0; i<cols.getColumnCount();i++)
68 {
69 colid = cols.getColumnId(i);
70 column = cols.getColumnById(colid);
71 if(column.hidden == true && column.header != 'CFGRIDROWINDEX')
72 {
73 var showButton = new YAHOO.widget.Button({
74 type: "checkbox",
75 label: column.header,
76 id: column.header,
77 name: column.header,
78 value: colid,
79 container: "buttons",
80 title: "Show Column",
81 checked: false });
82
83 showButton.addListener("click",showColumn)
84 }
85 }
86 }
87
88
89 </script>
90 </head>
91
92
93
94 <cfoutput>
95
96 <cfform>
97 <table border="1" width="90%">
98 <tr>
99 <td>
100 Filer By: <cfselect id="filtercolumn" name="filtercolumn" bind="cfc:Users.getUserColumns()"
101 display="ColumnName" value="ColumnName" bindOnLoad="true" />
102 Filter Text: <cfinput type="text" id="filter" name="filter">
103 <cfinput type="button" name="filterbutton" value="Filter" id="filterbutton"
104 onclick="ColdFusion.Grid.refresh('usersgrid',false)">
105 </td>
106
107 </tr>
108 <tr>
109 <td id="gridtd" style="padding-top:10px;">
110 <cfgrid name="usersgrid" pagesize="5" format="html" width="100%" height="200"
111 bind="cfc:Users.getUsers({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filtercolumn},{filter})">
112
113 <cfgridcolumn name="FirstName" header="First Name">
114 <cfgridcolumn name="LastName" header="Last Name">
115 <cfgridcolumn name="UserName" header="User Name">
116 <cfgridcolumn name="DisplayName" header="Display Name">
117 <cfgridcolumn name="UserAccountingCode" header="GL Code (User)" display="false">
118 <cfgridcolumn name="Phone" header="Phone No." display="false">
119
120 </cfgrid>
121 </td>
122 </tr>
123 <tr>
124 <td>
125 Click to show the columns:<br>
126 <div id="buttons" class="yui-skin-sam" style="height:100%;width:100%;">
127
128 </div>
129 </td>
130 </tr>
131 </table>
132 </cfform>
133
134 </cfoutput>
135
136 <cfset AjaxOnLoad("getGrid")>
2
3 <!-- Individual YUI CSS files -->
4 <link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.6.0/build/button/assets/skins/sam/button.css">
5 <!-- Individual YUI CSS files -->
6 <link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.6.0/build/datatable/assets/skins/sam/datatable.css">
7
8 <!-- Individual YUI JS files -->
9 <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/yahoo-dom-event/yahoo-dom-event.js"></script>
10 <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/element/element-beta-min.js"></script>
11 <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/button/button-min.js"></script>
12
13 <style>
14
15 .yui-skin-sam .yui-button {
16 background:#ffffff;
17 color: #071b45;
18 border-color:#a3a3a3;
19 }
20
21 .yui-skin-sam .yui-button button,
22 .yui-skin-sam .yui-button a {
23 font-size: 75%;
24 color:#071b45;
25
26 }
27
28 .yui-skin-sam .yui-button-hover {
29 background: #DDDDDD;
30 color:#071b45;
31 }
32
33 .yui-skin-sam .yui-radio-button-checked .first-child,
34 .yui-skin-sam .yui-checkbox-button-checked .first-child {
35 border-color:#a3a3a3;
36 }
37
38 .yui-skin-sam .yui-radio-button-checked,
39 .yui-skin-sam .yui-checkbox-button-checked {
40 border-color:#a3a3a3;
41 background:#DDDDDD;
42 color:#071b45;
43 }
44
45 </style>
46
47 <script>
48 function showColumn(e)
49 {
50 //alert(this.get("value"));
51 var showColId = this.get("value");
52 showCol = cols.getColumnById(showColId);
53 if(showCol.hidden == false)
54 {
55 showCol.hidden = true;
56 }else
57 {
58 showCol.hidden = false;
59 }
60 grid.reconfigure(grid.getDataSource(),cols);
61 }
62
63 function getGrid()
64 {
65 grid = ColdFusion.Grid.getGridObject('usersgrid');
66 cols = grid.getColumnModel();
67 for(var i=0; i<cols.getColumnCount();i++)
68 {
69 colid = cols.getColumnId(i);
70 column = cols.getColumnById(colid);
71 if(column.hidden == true && column.header != 'CFGRIDROWINDEX')
72 {
73 var showButton = new YAHOO.widget.Button({
74 type: "checkbox",
75 label: column.header,
76 id: column.header,
77 name: column.header,
78 value: colid,
79 container: "buttons",
80 title: "Show Column",
81 checked: false });
82
83 showButton.addListener("click",showColumn)
84 }
85 }
86 }
87
88
89 </script>
90 </head>
91
92
93
94 <cfoutput>
95
96 <cfform>
97 <table border="1" width="90%">
98 <tr>
99 <td>
100 Filer By: <cfselect id="filtercolumn" name="filtercolumn" bind="cfc:Users.getUserColumns()"
101 display="ColumnName" value="ColumnName" bindOnLoad="true" />
102 Filter Text: <cfinput type="text" id="filter" name="filter">
103 <cfinput type="button" name="filterbutton" value="Filter" id="filterbutton"
104 onclick="ColdFusion.Grid.refresh('usersgrid',false)">
105 </td>
106
107 </tr>
108 <tr>
109 <td id="gridtd" style="padding-top:10px;">
110 <cfgrid name="usersgrid" pagesize="5" format="html" width="100%" height="200"
111 bind="cfc:Users.getUsers({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filtercolumn},{filter})">
112
113 <cfgridcolumn name="FirstName" header="First Name">
114 <cfgridcolumn name="LastName" header="Last Name">
115 <cfgridcolumn name="UserName" header="User Name">
116 <cfgridcolumn name="DisplayName" header="Display Name">
117 <cfgridcolumn name="UserAccountingCode" header="GL Code (User)" display="false">
118 <cfgridcolumn name="Phone" header="Phone No." display="false">
119
120 </cfgrid>
121 </td>
122 </tr>
123 <tr>
124 <td>
125 Click to show the columns:<br>
126 <div id="buttons" class="yui-skin-sam" style="height:100%;width:100%;">
127
128 </div>
129 </td>
130 </tr>
131 </table>
132 </cfform>
133
134 </cfoutput>
135
136 <cfset AjaxOnLoad("getGrid")>
Nice demo. Thanks for posting it.
Could you post the full source, I'm a little lost on how you did a couple of the functions.
Thanks,
http://www.coldfusion-ria.com/Blog/Examples/CFGrid...
What does it do and how does it help in this application?
Thanks.
1. I wanted to put the YUI Button library to use, and as a result learn more about how to use it. I do like how the YUI Buttons are handled, adding events is easy and due to the event listener I am able to reference to the clicked button using :
var showColId = this.get("value");
2. I am leaning towards use of some YUI buttons in my demo application.
As I mention towards the end, a similar functionality (to show/hide grid columns) can also be implemented with other controls (checkbox, lists, etc).
Use of YUI Button would be nice if it is part of YUI that came with CF8, and no additional script required. :)
I have been looking at YUI, because of the readily available UI library. I know for jQuery I can find plugins that allow me to do the same things, and they are building their own official UI library at http://ui.jquery.com/. So, people are free to make up their mind in terms of what they want to use for their UI needs.
I have a Question about the cfgrid:
How can i set the height to 100% as well? Not only into pixel? The same as i can do it with the width?!
My Problem is to set the cfGrid autoheigt/100% :-(
[cfgrid name="usersgrid" width="100%" height="200"]
Thank you in Adv!
So what you can do is... either provide a way to dynamically change the number of rows, or.. use jQuery to dynamically resize cfgrid.
I see it in the zip file of code..but not when it's executing..
Is that just not part of the active demo?
The only thing that would cause it to not work with MySQL is the sql usage. Check to make sure the sql statements are working with MySQL.
I added the 2 files to my pages and changing the values so these can fit with my datas.
When i launch my cfm page, nothing happend, page still stay loaded and i have no error message ...
What did i make wrong ? Did i forget something ?
Thanks,
Marc
config : CF9 with Oracle DB