Bind a grid to select list

Posted At : April 2, 2005 5:26 AM | Posted By : Mike Nimer
Related Categories: CFForm

There are a number of examples of binding a grid column to a text field, but binding a grid column to a select list is a little trickier. The problem is that a number of components contain the bind attribute, however, the select list does not. To select an item in a select list you need to find the item you want and tell the component to select that row.

To bind a select list to a grid, you need to loop over all of the items in the select list comparing them to the value you want selected. We will do this in the onChange="" attribute of cfgrid and a for loop in actionscript.

<cfscript>
    qNames = queryNew("id,firstname,lastname,city,state,zip");
    queryAddRow(qNames);
    querySetCell(qNames, "id", "1");
    querySetCell(qNames, "firstname", "Mike");
    querySetCell(qNames, "lastname", "Nimer");
    querySetCell(qNames, "city", "Boston");
    querySetCell(qNames, "state", "MA");
    querySetCell(qNames, "zip", "O2127");
    queryAddRow(qNames);
    querySetCell(qNames, "id", "1");
    querySetCell(qNames, "firstname", "Bob");
    querySetCell(qNames, "lastname", "Jones");
    querySetCell(qNames, "city", "Salt Lake City");
    querySetCell(qNames, "state", "UT");
    querySetCell(qNames, "zip", "84111");
    queryAddRow(qNames);
    querySetCell(qNames, "id", "3");
    querySetCell(qNames, "firstname", "Chris");
    querySetCell(qNames, "lastname", "Smith");
    querySetCell(qNames, "city", "Las Vegas");
    querySetCell(qNames, "state", "NV");
    querySetCell(qNames, "zip", "55555");
    queryAddRow(qNames);
    querySetCell(qNames, "id", "4");
    querySetCell(qNames, "firstname", "Susan");
    querySetCell(qNames, "lastname", "McFoo");
    querySetCell(qNames, "city", "Boston");
    querySetCell(qNames, "state", "MA");
    querySetCell(qNames, "zip", "02466");
</cfscript>

<cfform format="Flash">
    <cfgrid name="UsersGrid" format="Flash"
            query="qNames" maxRows="5" rowheaders="No"
            onchange="for (var i:Number = 0; i<state.length; i++) {if (state.getItemAt([i]).data == UsersGrid.selectedItem.state) state.selectedIndex = i}">

            <cfgridcolumn name="id">
            <cfgridcolumn name="firstname" header="First Name">
            <cfgridcolumn name="lastname" header="Last Name">
            <cfgridcolumn name="city" header="city">
            <cfgridcolumn name="state" header="state">
    </cfgrid>

    <cfinput type="text" name="firstname" label="First Name"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['firstname']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'firstname', firstname.text);">

    <cfinput type="text" name="lastname" label="Last Name"
        bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['lastname']}"
        onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'lastname', lastname.text);">

    <cfselect name="state" width="200" size="1" label="State" onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'state', state.selectedItem.data);">
        <option value="MA">Boston</option>
        <option value="CA">California</option>
        <option value="NV">Las Vegas</option>
        <option value="UT">Salt Lake City</option>
    </cfselect>

    <cfformgroup type="horizontal">
    <cfinput type="Submit" name="submitBtn" value="Save">
    </cfformgroup>
</cfform>

Comments (23) |

Print |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 7651 Views

Comments

[Add Comment] [Subscribe to Comments]

How can I bind this using a query from a table rather than fixed variables?

# Posted By Rob Girellini | 4/5/05 11:49 PM

My code is below...It's not working right...

DATASOURCE="provisions"
TABLENAME="products"
GRID="artistGrid"
KEYONLY="Yes">

SELECT    product_category, Product_category_id
FROM ProductCategories
ORDER BY Product_category

SELECT    products.product_id, productcategories.product_category_id, productbrands.product_brand_id,
      producttypes.product_type_id, vendors.vendor_id,
      products.productname,
      productcategories.product_category,
      productbrands.product_brand,
      producttypes.product_type,
      products.price, products.sku, vendors.CompanyName
FROM products, productcategories, productbrands, producttypes, vendors
WHERE products.product_category_id = productcategories.product_category_id
AND products.product_brand_id = productbrands.product_brand_id
AND products.product_type_id = producttypes.product_type_id
AND products.vendor_id = vendors.vendor_id

      onchange="for (var i:Number = 0; i

    NAME="product_id"
DISPLAY="No">

    NAME="product_category_id"
DISPLAY="No">

    NAME="product_brand_id"
DISPLAY="No">

    NAME="product_type_id"
DISPLAY="No">

    NAME="vendor_id"
DISPLAY="No">

NAME="productname"
HEADER="Product"
WIDTH="100">

    NAME="price"
HEADER="Price"
WIDTH="100">

    NAME="sku"
HEADER="SKU"
WIDTH="100">


NAME="product_category"
HEADER="Category"
WIDTH="80">

NAME="product_brand"
HEADER="Brand"
WIDTH="100">

NAME="product_type"
HEADER="Type"
WIDTH="80">

    NAME="CompanyName"
HEADER="Vendor"
WIDTH="80">

bind="{artistGrid.selectedItem.productname}"
    onChange="artistGrid.dataProvider.editField(artistGrid.selectedIndex, 'productname', productname.text);">

    bind="{artistGrid.selectedItem.price}"
    onChange="artistGrid.dataProvider.editField(artistGrid.selectedIndex, 'price', price.text);">

    bind="{artistGrid.selectedItem.sku}"
    onChange="artistGrid.dataProvider.editField(artistGrid.selectedIndex, 'sku', sku.text);">




            onchange="artistGrid.dataProvider.editField(artistGrid.selectedIndex, 'product_category', product_category.selectedItem.data);">

# Posted By Rob Girellini | 4/5/05 11:49 PM

I am trying to add up on multiple currency value rows in a datagrid and bind the result. Any ideas how that would work?

# Posted By Brad Fetter | 4/6/05 6:21 PM

First, thanks for this example. BIG HELP and exactly what I needed. However, when trying to use this with an actual database query note that the db typically returns CAPITALIZED column names. Code that pertains to the query results such as 'firstname' now needs to be 'FIRSTNAME'. This, of course, does not pertain to cf formfield names, those remain lowercase.

# Posted By Brian Huxtable | 4/19/05 7:18 PM

First of all thanks!
How can i do to bind the grid to two or more selects?
I´ve got to bind it to a category and a subcategory selects, is there a way?

# Posted By felipe fernandes | 5/3/05 7:02 PM

I tried the above code and it works great. But its not showing me quite what im looking for. Wouldnt the States come from another query. Im using the cfinvoke. So you can't really use the cfselect for this? I have the WACK book and there is a good example too, but it also does not show how this is done. I have a grid with names when you select one; cfinput fields are filled in with the details. I have 1 cfselect that I want to show a list of departments to select from, but it needs to reflect what the selection from the grid is and allow for changes; so people could change departments. Can I put multiple query calls in the cfgrid? Can I Dynamicaly list the options?

# Posted By George Smith | 6/7/05 10:46 AM

I would also like to bind multiple cfselects on an "Edit" form to a CFGRID. I use a TabNavigator form with perhaps a dozen query-driven select boxes and it seems like this looping method would create a monster in code view and take forever to process especially if the select box query result is long (like in the case of ContactNames or Cost Center Codes) Is there any hope?

# Posted By Michael White | 8/7/05 3:56 PM

As per a few othewr people, how do you bind a cfgrid to multile selct boxes?

# Posted By Darren Bennett | 8/7/05 5:05 PM

How would one do the example with query based select drop down?

Here is the code I am working with...

# Posted By Brad Fetter | 9/29/05 3:29 PM

Great example!

However, it is really annoying that IE makes short clicking sounds when you type into the input boxes. It sounds kind of like a typewriter. Is there a way to prevent this?

# Posted By Jim Wells | 10/13/05 9:37 AM

I want to bind input fields with a cfselect field.

I tried to set the field with as but I keep getting undefined.
Here is quick part of my code.

<CFQUERY NAME="getinv" DATASOURCE="ds_inv">
SELECT Tag_num, Type
FROM inventory
ORDER BY Tag_num
</CFQUERY>

<cfsavecontent variable="setinv">
tagnum.text = assettag.selectedItem.Tag_num;
type.text = assettag.selectedItem.Type;
</cfsavecontent>

<cfSelect query="getinv" name="assettag" label="assettag" onChange="#setinv#"
value="Tag_num" display="Tag_num" queryPosition="below" width="100">
<option value=""> </option>
</cfSelect>

<cfformgroup type="horizontal">
<cfinput type="text" name="tagnum" label="TagNo" size="8"/>
<cfinput type="text" name="type" label="Type" size="8"/>
</cfformgroup>

Any help would be very appreciated.

# Posted By thomary | 8/24/06 2:54 PM

Check the case of the query columns. use ServiceCapture or some alert() calls. But I bet it's all uppercase. So use this instead.

assettag.selectedItem.TAG_NUM;

hope that helps.

# Posted By Mike Nimer | 8/29/06 5:09 PM

No the database name for the field is Tag_num.
It seems to work with
tagnum.text = assettag.selectedItem.Tag_num;
But it comes in as undefined like there is no data in the other fields or it can't match.

Thanks anyway.

# Posted By thomary | 9/6/06 11:16 AM

BTW... There is something wrong with this blog. The latest comments are so small you can't read it. They seem to be inside the last comments.

# Posted By thomary | 9/6/06 11:17 AM

The same question as everyone else. Is it possible to it change multiple selects, meaning if I select a record from the CFgrid it will change more that one select form field.

# Posted By Rey | 9/13/06 3:16 PM

Hi Mike,
Thank you for your reply and the link to this post. What I am trying to do is bind a select list to a grid, and having problems looping over all of the items in the select list comparing them to the value I want selected. For example if the db entry is "Call" then that is selected in the cfselect.

I have a a cfgrid which is outputting the cfquery fine in the cfgrid for the cfinputs but not the cfselect, and the cfselect doesn´t change when I select a new record to display:
<cfquery name="getcompany" datasource="cfcodeexplorer">
select * from COMPANY
</cfquery>

The values are:
send fax, send email, call and email, call, send letter presentation, no, meeting

Following your link that you send me I get the following code for the cfselect but I don´t understand how to loop over the values with cfscript and don´t know what to do if the value is "null".

<cfselect name="NEXTSTEP" width="200" size="1" label="NEXT STEP" onchange="companyGrid.dataProvider.editField(company.selectedIndex, 'NEXTSTEP', NEXTSTEP.selectedItem.data);">
<option value="SEND FAX">SEND FAX</OPTION>
<option value="SEND EMAIL">SEND EMAIL</OPTION>
<option value="CALL AND EMAIL">CALL AND EMAIL</OPTION>
<option value="CALL">CALL</OPTION>
<option value="SEND LETTER PRESENTATION">SEND LETTER</OPTION>
<option value="NO">NO</OPTION>
<option value="MEETING">MEETING</OPTION>
</cfselect>

Thank you very much for any pointers, Best Regards, Edward

# Posted By Ed | 10/11/06 12:36 PM

My best guess, you have a case problem. Make sure you aren't comparing upper to lower case values in your loop. CF isn't case sensitive, however flex is. So that could be it.

# Posted By Mike Nimer | 10/11/06 11:44 PM

Hi Mike,
Thanks a lot for your answer worked great: for those reading the blog: watch out for upper and lowercase problems in the formfields, db fields etc as you will get errors!

The cfgrid tag works fine for one field the "state" field output with cfselect with the following onchange actionscript:
<cfform format="Flash" skin="haloblue">
<cfgrid name="UsersGrid" format="Flash"
query="qNames" rowheaders="No"
onchange="for (var i:Number = 0; i<state.length; i++) {if (state.getItemAt().data == UsersGrid.selectedItem.state) state.selectedIndex = i}">

What I want to get is
onchange="for (var i:Number = 0; i<state.length; i++) {if (state.getItemAt().data == UsersGrid.selectedItem.state) state.selectedIndex = i}"
AND
onchange="for (var i:Number = 0; i<meetingtime.length; i++) {if (meetingtime.getItemAt().data == UsersGrid.selectedItem.meetingtime) meetingtime.selectedIndex = i}">

so I can connect to my meetingtime field and db field. I just don´t know how , thank you for any help.
<cfgridcolumn name="FIRSTNAME" header="Contact Name">
<cfgridcolumn name="ENTID" display="FALSE">
<cfgridcolumn name="state" header="Next Step">
</cfgrid>

<cfformgroup type="tabnavigator">
<cfformgroup type="page" label="Details: #dateFormat(now(), 'mmm dd, yyyy')#">
<cfformgroup type="horizontal">
<cfinput type="text" name="FIRSTNAME" label="First Name"
bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['FIRSTNAME']}"
onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'FIRSTNAME', FIRSTNAME.text);">

<cfselect name="state" width="200" size="1" label="Next Step" onchange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'state', state.selectedItem.data);">
<option value="nulo">None</OPTION>
<option value="SEND STUDY">SEND STUDY</OPTION>
<option value="SEND PROPOSAL">SEND PROPOSAL</OPTION>
<option value="CALL AND EMAIL">CALL AND EMAIL</OPTION>
<option value="CALL">CALL</OPTION>
<option value="SEND PRESENTATION">SEND PRESENTATION</OPTION>
<option value="MEETING">MEETING</OPTION>
</cfselect>

<cfinput type="hidden" name="ENTID" label="ENTID"
bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ENTID']}"
onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ENTID', ENTID.text);">

</cfformgroup>
</cfform>

# Posted By Ed | 10/24/06 10:58 AM

Everytime I put onchange into my cfselect or cfgrid, I get nothing on the grid / form displaying. Any suggestions?

# Posted By Lois | 3/22/07 8:11 PM

Sounds like you have an actionscript error in your code. Make sure you turn on "Show flash form errors" in the ColdFUsion administrator so you can see the error message. You can also run CF from a dos/cmd window to see the error messages.

# Posted By mike nimer | 3/23/07 3:10 AM

Thanks Mike! I work on an intranet site and my IT team will not let me have access to teh CF Administrator. I have asked them to turn this on and found the error in the cmd window.

# Posted By Lois | 3/23/07 1:56 PM

How do I get into coldfusion from cmd? Start run coldfusion? Thanks

# Posted By Ed | 3/26/07 4:35 PM

Hi,

this works perfectly, thank you so much!

now, i have also checkboxes and radio buttons ...
how could i do that because it doens't have to loop for checkboxes, no?

cheers,
kat

# Posted By kat | 6/8/07 9:27 PM

[Add Comment]