?
Solved

Having problems with CFIF Input using listcontains

Posted on 2011-02-18
10
Medium Priority
?
474 Views
Last Modified: 2012-08-13
I have a form which grabs the MASTER data dynamically from this query and displays it as check boxes on a form:
<cfquery name="getSCMaster" datasource="csf">
SELECT     checklistID, category, criteria, scholarshipName
FROM         dbo.tblScholarshipChecklist
WHERE            checklistID <> 1
</cfquery>

I then have a query which grabs data from another table.  The "shared" field is checklistID:
<cfquery name="getSCApp" datasource="csf">
SELECT     applicantChecklistID, applicationID, checklistID
FROM         dbo.tblApplicantChecklist
WHERE     (applicationID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#getAppInfo.applicationID#">) AND checklistID <> 1
</cfquery>

These two tables can't be joined because I need the page to display ALL of the data from the MASTER table, and check the checklistID check box ONLY where the data from the 2nd query is valid.  

I have created a list from the 2nd query and it outputs the data correctly.  
<cfset checklistIDlist = ValueList(getSCApp.checklistID)>
<cfloop list="#checklistIDlist#" index="i">
   <cfoutput>#i# </cfoutput>
</cfloop>
For example, it would output 10 12 15.

This is where the problem starts.  The form is displaying ALL of the numbers in the list returned.  For example, if the list returns 10 12 15, it checks the following checkboxes:
2 5 10 12 15. It's not separating the list correctly and looks for any number within the list and checks that check box.  

This is the input field in my form:
<input name="checklistID" <cfif listContains("#checklistIDlist#", #getSCMaster.checklistID#)>checked="checked"</cfif> type="checkbox" id="checklistID" value="#checklistID#" />

Do I need to add a delimiter to the list somehow or do I need to loop through the list?  Help!  This is driving me crazy!!!  

Thank you!
0
Comment
Question by:ssailer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
10 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 34927560
I believe you can join the tables with the LEFT JOIN.

In the code below, the field I added isChecked should be 1 when you need to check the box and 0 when the box would be unchecked.

Give it a test..

<cfquery name="getSCMaster" datasource="csf">
SELECT   sch.checklistID
       , sch.category
       , sch.criteria
       , sch.scholarshipName
       , case when app.applicationID > 0 then 1 else 0 end as IsChecked
FROM     dbo.tblScholarshipChecklist sch
  left join tblApplicantChecklist app on app.checklistID = sch.checklistID
       and app.applicationID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#getAppInfo.applicationID#">) 
       and app.checklistID <> 1
WHERE  sch.checklistID <> 1
</cfquery>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34927632
To address your question more directly, you are saying that the following code woud show you the correct values but the checkboxes are ALL checked.


Please try the modifications - you don't need to loop through a list to show the list.   I've cleaned up the INPUT tag to reduce unneeded code..

<cfquery name="getSCApp" datasource="csf">
  SELECT     applicantChecklistID, applicationID, checklistID
  FROM         dbo.tblApplicantChecklist
  WHERE     (applicationID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#getAppInfo.applicationID#">) AND checklistID <> 1
</cfquery>
<cfset checklistIDlist = ValueList(getSCApp.checklistID)>

Only these should be checked: #checklistIDlist#<br>

<cfloop query="getSCMaster">
<input type="checkbox" name="checklistID" <cfif listContains(checklistIDlist, getSCMaster.checklistID)>checked="checked"</cfif> value="#getSCMaster.checklistID#" />
</cfloop>

Open in new window

0
 

Author Comment

by:ssailer
ID: 34927633
YOU ARE A GENIUS!!!  Absolutely PERFECT :)  
0
Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

 
LVL 39

Expert Comment

by:gdemaria
ID: 34927688
I didn't notice it before, also please change listContains() to ListFind()
<cfloop query="getSCMaster">
<input type="checkbox" name="checklistID" <cfif listFind(checklistIDlist, getSCMaster.checklistID)>checked="checked"</cfif> value="#getSCMaster.checklistID#" />
</cfloop>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34927706
I think you clicked the wrong button, you are attempting to accept your own answer.   Please try again and accept the answer that you're going to use :)

Don't forget listFind() is the right function, listContains does something different
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34928832
@ssailer - did you mean to accept gdemaria's answer?

<cfif listContains(checklistIDlist, getSCMaster.checklistID)>

But I wanted to mention, that's the wrong function for matching whole ID's.  ListContains matches substrings.  So "2" will match "12", "214", "512" - not just the whole value "2".  For an exact match, use ListFind/FindNoCase instead.

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34928880

agx, I suspect you stopped reading all the posts, which if fine - I do the same thing, but I did mention that in the last two.. :)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34928883

Author intended to accept my answer, I believe and checked his own by mistake
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34928909
Yeah, I missed it.  But now they have an explanation of why use one not the other ;-)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34928927
> But now they have an explanation of why use one not the other

Yes!  perfect..
0

Featured Post

Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question