We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Coldfusion checkbox values from query or value list

Medium Priority
1,295 Views
Last Modified: 2013-11-19
I'm trying to make sure checkboxes are checked if a corresponding value exists in another query.  I have all of the add/remove stuff working, but getting the checkboxes to display as checked on an information screen is killing me.  Have tried ListGetAt, but couldn't get it to work.  I'll post my code below.

My checkboxes are created using the 'get_animals' query.

I have a second query to check to see if an existing store has that animal selected - 'cust_animals'.

What I want to do is check the box if the current checkbox's value exists in the cust_animals query.
<!--- GET ALL ANIMALS --->
<cfquery name="get_animals" datasource="#DSN#">
       	select * from lookup_animal order by animal_name asc
</cfquery>
 
<!--- GET CUSTOMER ANIMALS --->
<cfquery name="cust_animals" datasource="#DSN#">
       	select * from customer_animals where animal_customer_id = '#this_cust.id#'
</cfquery>
 
<!--- THE CHECKBOXES --->
<cfoutput query="get_animals">
    <input name="animal_id" type="checkbox" value="#animal_id#" /> #animal_name#<br />
</cfoutput>
 
<!--- Now how do it check the box if the animal_id exists in cust_animals? --->

Open in new window

Comment
Watch Question


<cfoutput query="get_animals">
	<cfif ListFindNoCase(ValueList(cust_animals.animal_id), get_animals.animal_id)>
	<!--- this ID is in the other query --->
		<input name="animal_id" type="checkbox" value="#animal_id#" checked /> #animal_name#<br />
	<cfelse>
	<!--- not found --->
		<input name="animal_id" type="checkbox" value="#animal_id#" /> #animal_name#<br />
	</cfif>
</cfoutput>

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
Another option is to use a single query and determine that value using a CASE statement in your query. Most databases like ms sql, mysql, etc... support CASE.  If you are using Access, try IIF instead.
<!--- GET ALL ANIMALS --->
<cfquery name="get_animals" datasource="#DSN#">
   select la.animal_id, la.animal_name,
	  CASE  WHEN ca.animal_id IS NULL THEN 0 
                ELSE 1 
          END AS IsSelected	 
 
   from   lookup_animal la 
	    LEFT JOIN customer_animals ca ON la.animal_id = ca.animal_id
   order by animal_name asc
</cfquery>
 
<cfoutput query="get_animals">
    <input name="animal_id" 
		type="checkbox" 
		value="#animal_id#" <cfif IsSelected>checked</cfif/> #animal_name#<br />
</cfoutput>

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Very good!  Thank you for your quick response - works perfect.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.