Filtering a grid as you type in a cfform
Following a previous post about filtering records "as you type", we made an extended version. In this example, instead of filtering a list, we are filtering a grid and records are narrowed to those matching the text entered in the text input. What's different here is that we can choose the column on which we are filtering.
The code:
<cfsavecontent variable="actionFilter">
if(_global.arrMembers == undefined) _global.arrMembers = data.dataProvider.slice(0);
var arrMembers = _global.arrMembers;
var arrDisplay:Array = [];
var fortext = forInput.text.toLowerCase();
var selected = column.selectedItem.data;
for(var i = 0; i < arrMembers.length; i++)
{
if(arrMembers[i][selected].substr(0,fortext.length).toLowerCase() == fortext)
{
arrDisplay.push(arrMembers[i]);
}
}
data.dataProvider = arrDisplay;
</cfsavecontent>
<cfform name="myForm" format="flash" width="400" height="300">
<cfformgroup type="panel" label="Search our Members">
<cfformgroup type="horizontal">
<cfinput type="text" name="forInput" width="120" onchange="#actionFilter#" label="Filter by:">
<cfselect name="column" label="in:" onchange="forInput.text=''" width="90">
<option value="name">Name</option>
<option value="gender">Gender</option>
<option value="age">Age</option>
</cfselect>
</cfformgroup>
<cfgrid name= "data" query="memberList" height="200"
rowheaders="false">
<cfgridcolumn name="name" header="Name">
<cfgridcolumn name="gender" header="Gender">
<cfgridcolumn name="age" header="Age">
</cfgrid>
</cfformgroup>
</cfform>
Update: We made an updated version of this code that uses a cfformitem type="script" (the preferred method now) instead
Steve Walker
John
I am having no luck with it.
Laura
It should work as is as long as you have a query named "memberList" with columns: id, name, age, gender.
Otherwise, you will need to change the corresponding query name in the cfgrid tag, change the name of the cfgrid columns to those in your query and the option values in the cfselect also to match your columns.
John
I swapped out the <cfscript> block for the <cfquery> that follows below, but no luck.
The grid is populated, however the filter blanks it out when it should return some rows.
<cfquery name="memberList" datasource="your_datasource">
select
1 as id,
'Joe' as name,
'25' as age,
'Male' as gender
from dual
union
select
2 as id,
'Jane' as name,
'35' as age,
'Female' as gender
from dual
union
select
3 as id,
'Henry' as name,
'50' as age,
'Male' as gender
from dual
union
select
4 as id,
'Alice' as name,
'55' as age,
'Female' as gender
from dual
union
select
6 as id,
'Cindy' as name,
'25' as age,
'Female' as gender
from dual
</cfquery>
Thanks for your help. John
John
I found the answer to the problem I was having when I used a <cfquery> (oracle db). The column names returned by the oracle query are uppercase, unless they are in quotes, therefore the actionscript was not finding any matches. Below is my fix.
var selected = column.selectedItem.data.toUpperCase();
Or you could quote the column names in the query.
select
1 as "id",
'Joe' as "name",
'25' as "age",
'Male' as "gender"
from dual
union
select
2 as "id",
'Jane' as "name",
'35' as "age",
'Female' as "gender"
from dual
etc...
John
Pedro
The only way it could be better is if you could filter on any part of the record (rather than the first character). I know being able to type 'an' and return 'Alan' along with 'Ann' and 'Annie' might seem silly in this implementation but I can see many time when this would be helpful.
If only I had the talent to modify the code to do this.
Good work!
Jason
Casey
Steve Walker
Nahuel
To do that you may change my code and use the function GridData.insertRow(gridName)
GridData.deleteRow(gridName)
To update the grid. And you will get what was changed when you submit the form.
Steve,
That is possible but you need to maintain multiples copies of the data to keep track of your filtering.
Casey,
Does any other flash form work for you? If not, and assuming you are on Windows, check the installation and whether you have a virtual directory for cfide and jRunScripts (needed for remoting). You can run the web server connectors to set it up.
chris
Brendan Ganning
Brendan Ganning
Anyone know how to fix this?
<cfsavecontent variable="actionFilter">
if(_global.arrRecipes == undefined) _global.arrRecipes = data.dataProvider.slice(0);
var arrRecipes = _global.arrRecipes;
var arrDisplay:Array = [];
var fortext = forInput.text.toLowerCase();
var selected = column.selectedItem.data;
for(var i = 0; i < arrRecipes.length; i++)
{
for(var x = 0; x < arrRecipes[i][selected].length; x++){
if(arrRecipes[i][selected].substr(x,fortext.length).toLowerCase() == fortext)
{
arrDisplay.push(arrRecipes[i]);
}
}
}
data.dataProvider = arrDisplay;
</cfsavecontent>
Brendan Ganning
for(var i = 0; i < arrRecipes.length; i++)
{
for(var x = 0; x < arrRecipes[i][selected].length; x++){
if(arrRecipes[i][selected].substr(x,fortext.length).toLowerCase() == fortext)
{
arrDisplay.push(arrRecipes[i]);
break;
}
}
}
This may be too slow with large recordsets, but with small ones it will work well.
Charles-Henry Vespierre
I would like to know how reset my filtered datagrid to get all my data again. i ve added a button but which command have i to use ?
Thanks so much
And keep going!!
Nahuel
To do that you can add this line of code in the onClick of your button:
data.dataProvider = _global.arrMembers;
Raphael Anzenberger
Very fine job that you have done here. Thanks for sharing it with the CF community.
This question is relative to Jason's comment on the fact that, when submitted, the form diplays empty arrays if the filter is used. You have suggested the use of GridData.insertRow(gridName)
GridData.deleteRow(gridName). Could you please give us a working example of this, if time allows of course?
Many thanks again,
Raphael Anzenberger,
Michelin Tire Corp. North America.
Nahuel
For all of you that want to get an Array with the removed items (instead of an empty array), you can replace the actionFilter function with this one:
<cfsavecontent variable="actionFilter">
if(_global.arrMembers == undefined) _global.arrMembers = data.dataProvider.slice(0);
else data.dataProvider = _global.arrMembers.slice(0);
var fortext = forInput.text.toLowerCase();
var selected = column.selectedItem.data;
var i = 0;
while(i < data.dataProvider.length)
{
if(data.dataProvider[i][selected].substr(0,fortext.length).toLowerCase() == fortext)
{
i++;
}
else
{
data.selectedIndex = i;
GridData.deleteRow(data);
data.selectedIndex = undefined;
}
}
</cfsavecontent>
I don't know exactly what is what you want to achieve. I think that this code might help you to start.
Raphael Anzenberger
Thanks for taking the time to respond to this query. I wanted to clarify the comment I made, and I will use an example to illustrate. I have a grid which holds the name of n people. When I use the filter, using department = IT for instance, the grid returns 2 rows. When I submit the form, the action page does not return the names of these 2 people, ie the returned array for the form is empty.
The code you posted returns the names of the people who were deleted; I think that what Jason and I had in mind, was for the grid to return the names of the people who were "filtered", ie the 2 rows in the given example above.
Sorry to take some much comment space on your blog. Thank you for re-considering this query, again, if your time allows.
Raphael Anzenberger.
Raphael Anzenberger
I must add the following to the above comment:
This is true only if the following scenario is true:
- if the cfform contains the attribute selectmode="edit", and the user modifies the record, the returned array for FORM is empty when the filter is used.
- if this attribute is not added and the user selects a row, the form returns the data from the row correctly.
Raphael Anzenberger.
Christian Russo
Nahuel
The problem is that the Array created has only the items that have changed. Those 2 rows that you mention are the items that haven't changed. To do what you want, maybe you could start with an empty dataProvider and insert two new items with the method GridData.insertRow(gridName) and copy the filtered data in these new rows. In this way, the array will have only two rows with the filtered data. But, IMHO, it's to much work to do that. Maybe you could do this on the server instead ( I don't know, I'm just throwing some ideas)
__________________________________________
Christian,
I'll see if I can do an example regarding your question
Nahuel
You need to replace the actionFilter with this new code:
<cfsavecontent variable="actionFilter">
if(_global.arrMembers == undefined) _global.arrMembers = data.dataProvider.slice(0);
var arrMembers = _global.arrMembers;
var arrDisplay:Array = [];
var fortext = forInput.text.toLowerCase();
var selected = column.selectedItem.data;
var i = 0;
var n = 0;
data.dataProvider.sortOn("CFGRIDROWINDEX");
while(i < arrMembers.length)
{
var item;
if(data.dataProvider[n].CFGRIDROWINDEX == n - 1)
{
item = data.dataProvider[n++];
}
else
{
item = arrMembers[i];
}
if(item[selected].substr(0,fortext.length).toLowerCase() == fortext)
{
arrDisplay.push(item);
}
i++
}
data.dataProvider = arrDisplay;
</cfsavecontent>
Felipe
But this fix is not working it steel giving me empty arrays on the form and it got back to only search in the first part of the record.
Felipe
The Scarecrow
I removed the select list from the filter and just filter on last name in my example. The select list basically became a seperate filter to filter on department of the person.
I also have a number of cfinput text that have a bind to the grid.
My problem is that if a grid item is selected, the text fields are filled. But if a new filter is applied to the grid the text fields all display "undefined" (which is expected) but how can I set it to an empty string if it is undefined ?
Nahuel
You can check that with something like this:
{( myGrid.selectedItem == undefined ) ? "" : myGrid.selectedItem;}
Scarecrow
Work like a charm, I gues you can't use
if(blah) do this else this
in the bind
IdaChen
<cfloop query="listMBAa">
<cfinput type="checkbox" name="boxMBAa_#appID#" label="#fullName#">
</cfloop>
Is there anyway to filter through the list of checkboxes like it does with the grid? Thanks a lot
IdaChen
<cfloop query="listMBAa">
<cfinput type="checkbox" name="boxMBAa_#appID#" label="#fullName#">
</cfloop>
Is there anyway to filter through the list of checkboxes like it does with the grid? Thanks a lot
IdaChen
<cfloop query="listMBAa">
<cfinput type="checkbox" name="boxMBAa_#appID#" label="#fullName#">
</cfloop>
Is there anyway to filter through the list of checkboxes like it does with the grid? Thanks a lot
IdaChen
<cfloop query="listMBAa">
<cfinput type="checkbox" name="boxMBAa_#appID#" label="#fullName#">
</cfloop>
Is there anyway to filter through the list of checkboxes like it does with the grid? Thanks a lot
Nahuel
I made a new example that shows how to do the same but using an editable grid.
http://www.asfusion.com/blog/entry/filtering-a-cfgrid-as-you-type--revisited-
diana
Michael White
Michael White
michael White
Nahuel
Thanks for your interest, actually we have a wish list in our about page: http://www.asfusion.com/blog/about
mafdoc
I've tried to change everything. Either the cfform doesn't load or the filter doesn't work.
I get the data but when I type into the field all the data disappears. I think it's the var selected. Please help...
<cfsavecontent variable="invFilter">
if(_global.arr1Members == undefined) _global.arr1Members = invgrid.dataProvider.slice(0);
var arr1Members = _global.arr1Members;
var arr1Display:Array = [];
var fortext1 = forMyInput.text.toLowerCase();
var selected = invcolumn.selectedItem.invgrid;
for(var i = 0; i < arr1Members.length; i++)
{if(arr1Members[i][selected].toString().substr(0,fortext1.length).toLowerCase() == fortext1)
{arr1Display.push(arr1Members[i]); } }
invgrid.dataProvider = arr1Display;
</cfsavecontent>
cfinput name="forMyInput" onChange="#invFilter#"
cfselect name="invcolumn" onChange="forMyInput.text=''"
cfgrid name="invgrid"
I don't have any experience with AS.
Thanks for any help.
mafdoc
Nahuel
Try to change
var selected = invcolumn.selectedItem.invgrid;
to
var selected = invcolumn.selectedItem.data;
Let me know if that works
mafdoc
Thanks for your help though.
Michele
I used the filtering records on a cfform grid example to filter my records by active, inactive or all records. Then I wanted to be able to filter that recordset by searching for a specific string in a specified column (as this example does). Since both examples use the same cfsavecontent tag with minor differences, I was able to combine the solutions and it seems to be working perfectly.
Here's the modified cfsavecontent tag:
<cfsavecontent variable="filterGrid">
if(_global.arrClinics == undefined) _global.arrClinics = data.dataProvider.slice(0);
var arrClinics = _global.arrClinics;
var arrDisplay:Array = [];
var fortext = forInput.text.toLowerCase();
var selected = column.selectedItem.data;
for(var i = 0; i < arrClinics.length; i++)
{
if(arrClinics[i].Status == status.value || status.value == 'All')
{
if(arrClinics[i][selected].toString().substr(0,fortext.length).toLowerCase() == fortext)
{
arrDisplay.push(arrClinics[i]);
}
}
}
data.dataProvider = arrDisplay;
</cfsavecontent>
The references to "Clinics" and "Status" are specific to my project, so you would change them.
Another note. I changed the order of the fields in the text input search. So it goes Look in: (cfselect) for: (text input). I think this is more intuitive because if the user changes the cfselect the text field is cleared.
Happy coding everyone and thank you, thank you, thank you Laura & Nahuel!
Isidro
Dan Lavender
I've been having trouble with the following line in these cfgrid tutorials:
if(_global.arrMembers == undefined) _global.arrMembers = data.dataProvider.slice(0);
I've been creating an FAQ page where you select the question and the answer appears in a
TextArea. The cfgrid is called "questions" and has the columns "Question" and "Category". So my
original code based on the above was:
if(_global.arrMembers == undefined) _global.arrMembers = questions.dataProvider.slice(0);
It didn't set _global.arrMembers because questions.dataProvider.slice(0) didn't appear to do
anything!?
I got around this by altering the code in a couple of areas. First I created _global.arrMembers
by looping through dataProvider and adding the values to a temporary array like so:
if(_global.arrMembers == undefined){
var tempArray:Array = [];
for(var i=0;i<data.dataProvider.length;i++){ //> this hides the DW errors!
tempArray.push(data.dataProvider.getItemAt(i));
}
_global.arrMembers = tempArray;
}
Then I changed the IF statement that populates arrDisplay to this:
if(arrMembers[i].Question.substr(0,keywordText.length).toLowerCase().indexOf(keywordText.toLower
Case()) != -1)
Notice "Question" is the name of the cfgrid column
Don't know why the original code didn't work - great blog though!
corey
{( myGrid.selectedItem == undefined ) ? "" : myGrid.selectedItem;}
Can you please give a more detailed example.
Thank you.
Alex F
Thanks a lot.
Laura
add .toString() before every .toLowerCase()
I would recommend to use the updated version of this example:
http://www.asfusion.com/blog/entry/filtering-a-grid-as-you-type---using
Aleii
Love it!!!
George Lu
Hi Steve,
I've looked your website (http://www.epicsoccer.org/fields.cfm) and it looks exactly what I want. Could you please tell me how did you do that (i.e. select a row from the grid and popular data below as form fields)?
Thanks,
George
Laura
See this example and accompanying article:
http://www.asfusion.com/blog/entry/coldfusion-flash-forms-cfdj-article
George Lu
Thank you for your direction. I'll take a look :)
George
George Lu
Thank you again for helping me. I've implemented the edit fields for cfgrid as the example shown but this only allow to put one drop down box. I can't add a second drop down box for editing. Please help.
PS. this article is part I and the edit part will be in part II. Where can I find the part II?
Thanks,
George
George Lu
I've found part II but still can't get second drop down box works. Since I have got filtering a grid as well I can't change flash remoting to the one in example.
George
Pedro
Great site, it has been EXTREMELY helpful!!
Pedro
Laura
You can use the variable myGridName.dataProvider.length. If you use it as a label, then you would do something like:
<cfformgroup type="panel" label="Records found: {myGridName.dataProvider.length}">Jackson
I want to be able to return all of the rows from a certain SQL dbo:
dbo. TABLENAME_content01
There are usually less than 80 rows in this table, but they change every evening. Here are my two problems.
Problem 1:
How do I get this example to return all of the rows in that database?
Problem 2:
How do make the rows clickable (drill down) to an edit page that reads a string variable that I pass from the row I click?
This is how I perform this funcion now (non-flash CFGRID):
<table border="1" cellpadding="1" cellspacing="1">
<tr>
<td>Last Name</td>
<td>First Name</td>
<td>Phone Number</td>
<td>eMail Address</td>
</tr>
<cfoutput query="allcontent01">
<tr>
<td><a href="Main-List-content1a-edit.cfm?PERSONId=#allcontent01.PERSONId#">#allcontent01.PERSONLASTNAME#</a></td>
<td># allcontent01.PERSONFIRSTNAME#</td>
<td># allcontent01.PERSONCOPHONE#</td>
<td># allcontent01.PERSONEMAILADDRESS#</td>
</tr>
</cfoutput>
I prefer the flash CFGRID concept, and would like to get this working. You are all so helpful, please provide guidance…
Thanks!
Landry
michael white
onClick="{GridData.insertRow(myGrid);}" because it's actionscript, not javascript? You have a cfformitem type="script" I assume?.
Landry
<tr><td colspan="2">
<cfgrid name="VendorGrid" height="140" width="750" query="Transact" rowheaders="false"
autowidth="true" selectmode="edit" insert="yes" format="applet" delete="yes">
<cfgridcolumn name="Account" header="Account">
...
</cfgrid>
</td></tr>
<tr><td colspan="2">
<cfinput type="button" name="Submit7" value="Insert" onClick="GridData.insertRow(VendorGrid);">
</td></tr>
seefor
<cfquery name="NG_Switches" datasource="siffer">
SELECT *
FROM dbo.ATM_IP
WHERE fabric_name like 'NG-%'
ORDER BY dbo.ATM_IP.fabric_name ASC
</cfquery>
<cfquery name="SD_Switches" datasource="siffer">
SELECT *
FROM dbo.ATM_IP
WHERE fabric_name not like 'NG-%' AND switch_type not like 'Ci%' AND switch_type not like 'On%'
ORDER BY dbo.ATM_IP.fabric_name ASC
</cfquery>
<InvalidTag type="text/javascript">
function go(where){window.location.href=where;}
</script>
<div id="ctr" align="center">
<div class="login">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center" colspan="2" >Telnet</td>
</tr>
<tr>
<td> <select name="Menu" onChange="go(this.value)">
<option>NG ATM Switch</option>
<cfoutput query="NG_Switches">
<option value="telnet://#NG_Switches.ip_address#">#NG_Switches.fabric_name#</option>
</cfoutput>
</select></td>
<td> <select name="Menu" onChange="go(this.value)">
<option>SD ATM Switch</option>
<cfoutput query="SD_Switches">
<option value="telnet://#SD_Switches.ip_address#">#SD_Switches.fabric_name#</option>
</cfoutput>
</select></td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td align="center" colspan="2" >HTTP</td>
</tr>
<tr>
<td><select name="Menu" onChange="go(this.value)">
<option>HD ATM Switch</option>
<cfoutput query="NG_Switches">
<option value="http://#NG_Switches.ip_address#">#NG_Switches.fabric_name#</option>
</cfoutput>
</select></td>
<td> <select name="Menu" onChange="go(this.value)">
<option>SD ATM Switch</option>
<cfoutput query="SD_Switches">
<option value="http://#SD_Switches.ip_address#">#SD_Switches.fabric_name#</option>
</cfoutput>
</select></td>
</tr>
</table>
<div class="clr"></div>
</div>
</div>
</div>
<br><br>
This is the new code(make it look better) I'm trying to write with the same affects:
<cfquery name="NG_Switches" datasource="siffer">
SELECT *
FROM dbo.ATM_IP
WHERE fabric_name like 'NG-%'
ORDER BY dbo.ATM_IP.fabric_name ASC
</cfquery>
<cfquery name="SD_Switches" datasource="siffer">
SELECT *
FROM dbo.ATM_IP
WHERE fabric_name not like 'NG-%' AND switch_type not like 'Ci%' AND switch_type not like 'On%'
ORDER BY dbo.ATM_IP.fabric_name ASC
</cfquery>
<cfform name="myForm" format="flash" width="500" height="650">
<cfformgroup type="panel" label="Search our markets" height="250">
<cfselect query="NG_Switches" queryposition="below" group="fabric_name" label="State" name="myselect1" value="ip_address" display="fabric_name" width="200" >
<option value="All">All</option>
</cfselect>
<cfgrid appendkey="yes" name="data" query="NG_Switches" href="telnet://ip_address" hrefkey="ip_address" selectmode="single" >
<cfgridcolumn header="Fabric" name="fabric_name" />
<cfgridcolumn header="IP Address" name="ip_address" />
<cfgridcolumn header="Switch Type" name="switch_type" />
<cfgridcolumn header="id" name="id" display="false" />
</cfgrid>
</cfformgroup>
</cfform>
Problem is when I click on the row or IP address it does not open up a telnet session to the ip address.
I Google cfgrid url and cfgridcolumn url found some basic examples and that's what I tried above.
I hope some one can help Smile or point me in the right direction.
Thanks in advance
Harold
Example:
(1)First name field from drop down list, Text input: A
(2)Last name field from drop down list, Text input: B
(3)State field from drop down list, Text input: C
If anyone can help me with this ASAP it would save my neck.
Harold
Example:
(1)First name field from drop down list, Text input: A
(2)Last name field from drop down list, Text input: B
(3)State field from drop down list, Text input: C
If anyone can help me with this ASAP it would save my neck.
Darkenning
I would like to find a way to search each column of the grid when entering text into the filter box rather than one coulmn at a time - is this possible?
Many thanks for your help in advance!
Royal T
Royal T
Troy
<option value="">
i.e. I would like to be able to use this to search through 3 columns
I have tried several combinations and have been unsuccessful, but my AS3 is very beginner
Troy
<option value="">
i.e. I would like to be able to use this to search through 3 columns
I have tried several combinations and have been unsuccessful, but my AS3 is very beginner
kimosabe
kimosabe
Axel
The CFGRID is a table in itself. You can specify pretty much everything there. As far as I know there is no colspan as an attribute to modify.
Axel
kimosabe
Thanks for your quick response. I used the width attribute. It is in pixcels. It is a little more work in cfgrid than html table but I think I got it.
kimosabe
FlashHack