Quick grid bindings

Sep
28

Quick grid bindings

52 comments Posted by: Laura

It turns out that the function described in the previous post is useful for other things besides "filtering as you type". We can use it to "filter" a grid based on the selection of another grid, that is "bind" a grid to another. The process is very similar to what you would do in multiple selects related, and although the function will not work for selects because it uses datagrid's specific methods, it could be easily modified to do so (if you need multiple selects related, check this post ).

The function is the same, so I will not paste it here, what changes are the controls and the first datagrid has some code in its onchange attribute:

onchange="applyFilter(datagrid1.selectedItem.myColumn, datagrid2, ['columnToFilter'])"

In this example we are trying to get all the contacts in the second datagrid that match the “department” column with the id selected in the first datagrid.

<cfgrid name="departmentList" query="departmentsQuery" rowheaders="false"  width="120" onchange="applyFilter(departmentList.selectedItem.id, contactList, ['department'])">
   <cfgridcolumn name="name" header="Department">
</cfgrid>

<cfgrid name="contactList" query="contactsQuery" rowheaders="false">
   <cfgridcolumn name="name" header="Employee Name">
   <cfgridcolumn name="gender" header="Gender">
   <cfgridcolumn name="age" header="Age">
</cfgrid>

Live example

Download the source

Category: CFForm | ColdFusion |

52 Comments so far

Write yours

1. Bradford wrote on September 28, 2005 at 8:11 AM

This should open up this possiblities. Thanks this should be quite useful. Could this technique work with CFtree?

2. Laura wrote on September 28, 2005 at 11:40 AM

Bradford,
It should work fine with cftree. Just add the right settings to the tree onchange. Something like:
applyFilter(myTree.selectedNode.getProperty('data').value, contactList, ['department'])

3. Neil Bailey wrote on September 28, 2005 at 4:24 PM

has anyone figured out a way to fire an onChange event for a group of radio buttons? I know its off topic, but its driving me NUTS...

4. Michael White wrote on September 28, 2005 at 4:31 PM

The article of yours I'd like to see optimized is the Checkbox on a grid routine. I use it to mark a database record "Approved" with an index loop and it really slows down when you have more than 25 or so records. I don't know if this kind of thing can help that though.

5. Nahuel wrote on September 28, 2005 at 11:18 PM

Neil,
You have to use the "onClick" event on each radio button instead of "onChange"

Michael,
You are talking about this post?
http://www.asfusion.com/blog/entry/checkboxes-in-a-cfgrid
If that is true, where do you find the problem?

6. David Brannan wrote on September 29, 2005 at 12:10 AM

Will this example work if you haven't applied the 7.01 updater to Cold Fusion?

7. Michael White wrote on September 29, 2005 at 6:42 AM

the Select All Option was slow with over 50 records... I guess we can continue this in the correct blog entry with checkboxes in a grid.

8. Rick Smith wrote on September 29, 2005 at 12:57 PM

This is great! Thanks for the update. A couple of questions though:

1) How would I go about linking each row in the contactlist to its own? I've spent quite a bit of time trying to link up each row and having no success with the href or hrefkey as outlined in the CF7 LiveDocs or Ben's WACK7 (for either the cfgrid tag or the cfgridcolumn tag).

2) How might I get rid of the left cfgrid (departmentList) and replace it with a cfselect dropdown menu?

Thanks in advance.

9. Rick Smith wrote on September 29, 2005 at 5:12 PM

Figured out #1... in order to link a cfgrid row you need to use onchange="getURL('your.url'+gridname.selectedItem.itemID)". I'm sure all you experts knew that already :)

I'm still a bit baffled converting the left selection box into a dropdown menu... but I'll keep tinkering.

10. Laura wrote on September 29, 2005 at 8:10 PM

David,
No, you need the updater to run this example.

Rick,
Your answer for #2 at
http://www.asfusion.com/blog/entry/more-quick-bindings

11. John wrote on October 01, 2005 at 10:22 AM

I need it so that the contacts gird is empty until someone selects a department.

I tried calling the filter function
from the onload even on the cfform tag with a bad department id but it didn't work. I think the form is getting loaded before the grids get populated with data.

Kourosh

12. Laura wrote on October 05, 2005 at 9:41 PM

John,
I made a post about it:
http://www.asfusion.com/blog/entry/knowing-when-the-cfform-data-arrives

13. Brian wrote on October 22, 2005 at 4:08 AM

Is there a way to multiple select items in the first grid and have them add to the right cfgrid as they are selected?

14. George wrote on February 27, 2006 at 2:42 PM

This works great. I have one a question though. My first grid is populated and when I select something the 2nd grid works good. But when the form is loaded, how can I have the 2nd Grid blank to start out?

15. Thomary wrote on March 02, 2006 at 7:03 AM

Is there a limit on binds in a single cfform?

I have many working binds. I added a few more and they are not working?

16. Thomary wrote on March 02, 2006 at 7:13 AM

Sorry, Found it... duhhh. Didn't add the fields into the query.

17. Dave Billingsley wrote on May 05, 2006 at 10:12 AM

This is just what I was looking for. Very nice. Thanks.
A question, though...
When I code to auto select the first row of the departmentList it doesn't cascade to select the related rows in the contactList.
I used your code to not have anything show in the contactList until a departmentList row has been manually selected. But it would be preferable to have the auto-select functionality. How do I do this?
Thanks in advance.

18. Laura wrote on May 05, 2006 at 10:23 AM

Dave,
What method are you using to select the first row?

19. Dave Billingsley wrote on May 05, 2006 at 10:32 AM

<CFFORMITEM TYPE="script">
function onFormLoad()
{
<cfif isDefined("get_departments") AND
get_departments.recordcount gt 0>
departmentList.selectedIndex = 0;
</cfif>
}
</CFFORMITEM>

20. Laura wrote on May 05, 2006 at 10:36 AM

Dave,
You need to use the code at http://www.asfusion.com/blog/entry/knowing-when-the-cfform-data-arrives
you can combine the first 2 examples and run the filter once you have selected the item.
After doing
departmentList.selectedIndex = 0;
you do
_root.applyFilter(departmentList.selectedItem.id, contactList, ['department'])
make sure you put both contactList and departmentList into scope with (before your modelChanged function)
var contactList:mx.controls.DataGrid = contactList;
Look at the examples there for more info.

21. Dave Billingsley wrote on May 05, 2006 at 11:38 AM

I think I did this as you instructed, but still getting what John asked for in Post 11. To be honest, I don't do
any ActionScript coding so finding the error of my ways is difficult.
Thanks

22. Laura wrote on May 05, 2006 at 11:48 AM

Dave,
You cannot use onload. I would recommend that you read the post I pointed to. The change is simple, but you need to use the sample code of that post.

23. Manuel wrote on May 17, 2006 at 3:44 PM

I'm a little stuck. My 2nd grid will not filter. Any ideas would be great.

<cfscript>
qemp_weeks = createObject("component","emp_weeks").getAll();
qemp_days = createObject("component","emp_weeks").getDays();

</cfscript>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">;
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Weekly Attendance Record</title>

</head>

<body>

<cfform format="flash" name="empforms" width="100%">

<cfformitem type="script">

function applyFilter( term:String, grid:mx.controls.DataGrid, columns:Array ):Void {

         var filterTerm:String = term.toString().toLowerCase();

         if(filterTerm.length > 0) {
            if(_global.unfilteredData[grid.id] == undefined){
               if (_global.unfilteredData == undefined){
                  _global.unfilteredData = {};
               }
               _global.unfilteredData[grid.id] = grid.dataProvider.slice(0);
            }

            var filteredData:Array = [];

            for(var i = 0; i< _global.unfilteredData[grid.id].length; i++) {
               var item:Object = _global.unfilteredData[grid.id][i];
               var added:Boolean = false;

               for(var j = 0; j< columns.length; j++){
                   if(!added){
                     var value:String = item[columns[j]].toString().toLowerCase();
                     if(value.indexOf(filterTerm) != -1)   {
                        filteredData.push(item);
                        added = true;
                     }
                  }
                  else {
                     break;
                  }
               }
            }

         grid.dataProvider = filteredData;

         }
         else {
            if(_global.unfilteredData[grid.id] != undefined) grid.dataProvider = _global.unfilteredData[grid.id];
         }
      }

</cfformitem>

<cfformgroup type="hbox">
      <cfformgroup type="panel" label="Weekly Attendance" style="indicatorGap:0; fontSize:14;">


               <cfgrid name="tslist" query="qemp_weeks" rowheaders="false" onchange="applyFilter(tslist.selectedItem.wkID, dayslist, ['tsWkID'])">
                  <cfgridcolumn name="wkID" header="Week ID">
                  <cfgridcolumn name="wk_Start" header="Week Start" mask="MMMM D, YYYY" />
                  <cfgridcolumn name="wk_End" header="Week End" mask="MMMM D, YYYY" />

               </cfgrid>
               <cfgrid name="dayslist" query="qemp_days" rowheaders="false">
                  <cfgridcolumn name="tsWkID" header="Week ID">
                  <cfgridcolumn name="tsDate" header="Date" mask="MMMM D, YYYY" />
                  <cfgridcolumn name="tsFrom" header="From" />
                  <cfgridcolumn name="tsTo" header="To" />
                  <cfgridcolumn name="tsReason" header="Reason" />
                  <cfgridcolumn name="tsTotal" header="Total" />
               </cfgrid>
      </cfformgroup>

      <cfformgroup type="panel" label="Timesheet Preview" style="indicatorGap:0; fontSize:14;">



      </cfformgroup>
</cfformgroup>

24. Laura wrote on May 18, 2006 at 10:50 AM

Manuel,
Are you sure you got the case of your columns right? ActionScript is case sensitive, so you need to make sure it matches the database or query columns exactly.

25. Jeremy wrote on July 05, 2006 at 7:03 PM

here's my issue

<cfgrid name="projectsGrid" query="projectsList" rowheaders="no" height="200" onchange="applyFilter(projectsGrid.selectedItem.projectID,phaseGrid,['projectID'])">

for example:

if i select a row with a project ID of '1', the filter brings back project ID's starting in '1'. for example, project ID 11, 12, 15, etc.

any ideas how to limit the filter to the project ID selected?

jeremy

26. Laura wrote on July 06, 2006 at 3:06 PM

Jeremy,
See my reply here: http://www.asfusion.com/blog/entry/knowing-when-the-cfform-data-arrives#comment-691
You need to make that change to the applyFilter() function.

27. Mary wrote on November 10, 2006 at 7:38 AM

This code works fine for me. Thank you. Rather than select the row, is it possible to have it CHECK the cfgridcolumn name="isChecked" checkbox?

28. Tim wrote on November 20, 2006 at 3:40 AM

Hello,

I've been stuck with on a problem for a while. I have 2 grids bound together as in your example, but taking data from 2 linked database tables. The filter function (applyFilter or Search) work exatly how it should filtertering the 2nd grid.

My problem is that I need the user to edit the 2nd (filtered) grid (insert, edit or delete). This works when I have no filtering but when I apply the filter it will not keep any edits.

For example, when I insert a new row into the filtered grid, change the filter to something else, then change it back again and all my changes have disapeared. Also when I run a query I always have an empty array for any value in the grid.

I am using a CFQUERY to update/insert/delete my data to the database.

Thanks for the great insight into Flash Forms!

29. Andy wrote on November 28, 2006 at 6:13 AM

I am trying to provide the same functionality that Tim asked about on 11/20. Is there anyway to allow Update/Delete functionality to the second grid?

30. Brian wrote on December 28, 2006 at 11:59 AM

Had to post here as I couldn't find anything else that resembled my problem. Whith that said, I REALLY hope you can help.

I know that I can bind to the displayed value of a cfselect - simple. But this one is escaping me: how can I populate the cfinput fields from a query populated cfselect to display the values from the returned query?

example code:
<cfloop index="c" from="1" to="5">
<cfformgroup type="horizontal">
<cfselect name="SelectWheel#c#" queryPosition="below" query="AllWheelRet" value="WheelID" display="WheelID" label="Select Wheel #c#: " width="75">
<option value=""></option>
</cfselect>
<cfinput type="text" width="50" name="Size#c#" label="Size: " />
<cfinput type="text" width="150" name="Mnfr#c#" label="Manuf: " />
<cfinput type="text" width="150" name="Model#c#" label="Model: " />
</cfformgroup>
</cfloop>

What I'm trying to do is populate the approriate fields with the data selected from the select menu. The select menu holds the record ID - I want the manufacturer from that record to populate to the mnfr field, model to model, etc.

Can this be done?

31. Charlie wrote on January 02, 2007 at 6:43 AM

Brian, I have the same question. Seems like the values would be available from the query in the form of an array, but I haven't found a way to get to them.

32. Chris wrote on January 02, 2007 at 2:34 PM

Is there a way to store what row of the grid you are on for future use also is there way of telling the grid what row to go to on a load?

33. Laura wrote on January 06, 2007 at 8:37 PM

Brian,
When data for a selects gets to the form, the original data gets lost. There is a way to get to it, but I would not recommend you to use it. I would use Remoting instead if you really want to use a select.

Chris,
Where do you want to store the row? You can tell the form what row to select as described in this post:
http://www.asfusion.com/blog/entry/knowing-when-the-cfform-data-arrives

34. Brian wrote on January 09, 2007 at 8:19 AM

Thanks for the response Laura. At this point, however, I am near desperation and need to be able to get this done. I have attempted remoting, but constantly run into one problem or another. If the resolution you mentioned isn't really a best practice, I'd appreciate a provate mail to my email provided.

Thanks a bunch - you guys are great and your site rocks!

35. Declan wrote on January 16, 2007 at 2:14 AM

Hey there, little stuck on a cfgrid

I want it to be blank when the page first loads, any ideas?

I have already used:

grid.DataProvider == null;

but no joy

Cheers,
Declan

36. Declan wrote on January 16, 2007 at 2:18 AM

Hey there, little stuck on a cfgrid

I want it to be blank when the page first loads, any ideas?

I have already used:

grid.DataProvider == null;

but no joy

Cheers,
Declan

37. mike wrote on February 13, 2007 at 1:22 AM

Hi,

Is it possible to know what cell was clicked in a cfgrid using actionscript? Or what cell is currently being edited?

thanks,
Mike

38. mike wrote on February 17, 2007 at 1:39 AM

solved my question, =)

39. kll57 wrote on March 05, 2007 at 8:05 AM

Hi,
I have another cfgrid binding question. I have an application similiar to the address book. The query for the grid has several columns, but the grid only displays 5 columns. When I select a row from the grid it populates the Preview panel which is set up as 2 tab navigator pages using <cfsavecontent> tag. This displays ok, but I'd like to set up the 2nd tab page to displays it's data in a grid. How do I populate one grid based on a selection from another grid? Thanks for any help.

40. Andy Sandefer wrote on March 05, 2007 at 8:10 AM

kll57,
You need to setup a listener. Look at the filter as you type tutorial on this site and there is another really good one regarding binding grids to other grids as well as selects, etc.

41. kll57 wrote on March 05, 2007 at 10:03 AM

42. kll57 wrote on March 05, 2007 at 10:29 AM

Andy,
Thanks for responding so quickly. I'm not sure if I was clear enough in explaining what I'm trying to do. The first grid is already bringing back the data. I'm currently displaying the data in the edit tab page using:

<cfinput type="text" name="colname1" label="Colname1" bind="{(tgrid.selectedItem!=undefined)?tgrid.selectedItem.COLNAME1:''}" />

<cfinput type="text" name="colname2" label="Colname2" bind="{(tgrid.selectedItem!=undefined)?tgrid.selectedItem.COLNAME2:''}" />

<cfinput type="text" name="colname3" label="Colname3" bind="{(tgrid.selectedItem!=undefined)?tgrid.selectedItem.COLNAME3:''}" />

I'd like to display it in an editable grid instead.
ie:
<cfgrid name="agrid">
<cfgridcolumn name="COLNAME1" header="Colname1">
<cfgridcolumn name="COLNAME2" header="Colname2">
<cfgridcolumn name="COLNAME3" header="Colname3">
</cfgrid>

I tried something like this and got an error:
<cfgridcolumn name="tgrid.selectedItem.COLNAME1" header="Colname1">

Then I thought I should be putting a onChange event on the second grid that would bind it to the 1st grid but I couldn't figure out the syntax. I tried this, didn't get an error but it returned no data:
<cfgrid name="agrid" onChange="{(tgrid.selectedIndex.data)}">
<cfgridcolumn name="COLNAME1" header="Colname1">
<cfgridcolumn name="COLNAME2" header="Colname2">
<cfgridcolumn name="COLNAME3" header="Colname3">
</cfgrid>

Hope this makes sense. By the way I'm populating the form and grid using flash remoting. Not sure if this makes a difference.

Thanks for any helpl

43. Garcia wrote on March 14, 2007 at 8:54 AM

Hi,

Im having the same trouble as kll57, i have 2 grids the first 1 is filled by a query from the DB an the other is in blank, what i want to do is when a user selects a row in the first grid and click on the button add the second grid gets filled whit the row data of the first grid that was selected.

44. HMD wrote on April 20, 2007 at 11:32 AM

Hi all,

got a quick query. The grid change works magic for me, especially as I am using tabnavigator for separting the pages with the information on.
Now: When selecting an entry on the master grid, naturally a second grid on a seperate flash-form changes, reflecting only the specific information.

I use the following code:

<cfgrid name="contactList" query="contactsQuery" rowheaders="false" height="500" onchange="applyFilter(contactList.selectedItem.user_id,conList,['hmdcomp.user_id'])">

** conList = is the sub-grid on a different flash-page in the form
** contactList = is the master grid
** detailsList = another sub-grid
** hmdcomp = table for master grid
** user_id = key entry for all entries in database

The key question: Is it possible to apply the selection from the master grid to a second sub-grid, so to speak, "gang" the selection to change a grid that has the name "detailsList"?
Basically I need to let all grids now the "user_id" of the selected reference in the master grid.

I have been trying for 5 hours straight and going insane....

All the best and thanks for your help in advance:

Henning

45. Ron T wrote on May 24, 2007 at 7:43 PM

The bindings work great for me except for one thing... they are performing a "starts with" when filtering instead of an exact match. Can this be easily changed?

Fantastic job...

Ron

46. Laura wrote on May 24, 2007 at 7:54 PM

Ron,
See my reply above: http://www.asfusion.com/blog/entry/quick-grid-bindings#comment-45E090C7-3048-2A53-704CCDAA43FD9B2E

47. Jim wrote on June 02, 2007 at 10:37 PM

As a twist to this I am trying to populate a dynamic tab page with dynamic sub tabs. I actually have this working. I have a cfgrid on the sub tab that displays a recordset that is related to the subtab which is related to the parent tab. Everybody follow so far?

Ok here is the trouble.

I have put my cfgrid in a hdivided panel and have inserted a second cfformgroup to display the details of the item selected in the grid.

Using another cfgrid just does not fit with how we are working. I just need a series of about 5 or six input boxes and one textarea field that are bound to the cfgrid.

I have tried to follow both WACK7 book and CF Dev Journal completing the real estate app tutorial. Both have different approaches to the propblem but to no avail.

My code fails at the bind attribute of the cfinput tag.

Thanks

Jim

48. HMD wrote on June 04, 2007 at 9:50 AM

Jim,

try something like:
<cfinput type="text" name="name_first" value="" bind="{contactList.selectedItem.name_first}" label="FIRSTNAME:" size="20">

contactList = name of the cfgrid
name_first = name of the row/column you are referencing in the grid

All the best,

H.

49. Jim wrote on June 04, 2007 at 10:09 AM

Hey thanks for the response. Here is what I ended up using.

<cfinput type="text"
                      name="file_#GetRules.RuleKey#"
                      label="{Grid_#GetRules.RuleKey#.selectedItem.file}"
                      bind="{Grid_#GetRules.RuleKey#.selectedItem.file}">

My problem was getting back to the dynamic name assigned to the grid because of the name restiction, you know the one where you cannot duplicate a control with the same name.

Thanks again

Jim

50. Jimmi Journey wrote on June 08, 2007 at 1:45 PM

2 <cfgrids I have...
One intended to filter the next

one query for each.

Without filter syntax, grids load fine.

grids are isolated, Just two grids on this form.

when I add the onchange code, I loose the grids... blank page.(No error mesages or clues)

However, some confusion I had, as you named the colum header "Departments" though it displays data of "Name"... and as I cant see your query for either grid... I cant determine what exactly is associated with what....
i.e. is there a hidden colum named "Departments" on the second query/grid?

so in short (not short enough)

I have 2 grids, 2 queries, and 2 tables

*********
Table 1 (tbljobs)associated with query 1 and grid one(gridJobs)
*********

* Job (autonumber)
- JobName
- Notes

*********
Table 2(tblRequests)associated with query 2 and grid two(gridRequests)
*********

*Request(autonumber)
-Job
-Item

Now, obviously perhaps what I want to do... is if user selects row from "gridJobs"...."gridRequests" reveals data for requests associated with selected Job in gridJobs.

After this is achieved... I would like to be able to edit the filtered rows... but not on the grid... just from input fields associated with gridJobs.

BTW... I'm using the flash grids... I'm new to CF... but I think the <cfgrid> works without flash... might make a difference.

51. Tony Bentley wrote on December 26, 2007 at 11:46 AM

Thanks so much for this information! It works like a charm.

52. Simon wrote on February 26, 2008 at 5:29 AM

Hi, I am using bind="{data.dataProvider[data.selectedIndex]['column']}" onChange="data.dataProvider.editField(data.selectedIndex, 'column', textfield.text)"
to bdin my text fields to my datagrid, does anybody know how to do the same for a cfselect, as it doesn't seemto work, it changes it when I select a different option but it does not initially display the bound selection

many thanks in advance

Leave your comment

Comment etiquette: As a gesture to those subscribed to this post, please keep your comments relevant to the post.

Preview:

Refresh Preview

1. You wrote on

AsFusion