Looping over query 1

[Helen267]

I having trouble populating checkboxes dynamically, and I'm hoping someone can help me.

I have three tables. This first is the parent:

PROJECTS
Project_ID
Name
History
Directorate_ID

The second is a list of values, grouped by Directorate_ID:

MEASURES
ID
Directorate_ID
Measure

The third is a child to the first (an intersection table), using the second list:

PROJECT_MEASURES
ID
Project_ID
Directorate_ID

In other words, a project can have one or more measures based on the directorate, and these are displayed on the form with checkboxes against each measure.

I have written the code to insert values successfully, but I'm having trouble populating the checkboxes for an update form.

This is what I have currently:

<CFQUERY NAME="qryEProjects"
DATASOURCE="Source">
SELECT e.e_proj_id e_proj_id
, e.proj_name proj_name
, e.new_sap_proj_no proj_no
, e.proj_desc proj_desc
FROM e_projects e
WHERE e.e_proj_id = '#URL.e_proj_id#'
ORDER BY e.e_proj_id
</CFQUERY>

<CFQUERY NAME="qryMeasures"
DATASOURCE="Source">
SELECT b.id b_id
, b.d_id d_id
, b.measure measure
FROM balanced_measures b
WHERE b.d_id = '#URL.d_id#'
ORDER BY b.measure
</CFQUERY>

<CFQUERY NAME="qryProjectMeasures"
DATASOURCE="Source">
SELECT pm.b_id b_id
FROM e_project_measures pm
WHERE pm.e_proj_id = '#URL.e_proj_id#'
</CFQUERY>

Here's my current code to populate the list of available measures, with a checkbox against each option (which should be checked if a value exists in the PROJECT_MEASURES table):

<TD COLSPAN="3" ALIGN="left" VALIGN="top">
<INPUT TYPE="checkbox"
NAME="SEL_#b_id#"
VALUE="Y"
<CFLOOP QUERY="qryProjectMeasures">
<CFIF #qryProjectMeasures.b_id# EQ #qryMeasures.b_id#>
CHECKED
</CFIF>
</CFLOOP>>
&nbsp;#qryMeasures.measure#
</TD>

This is displaying the list of options correctly (12 items), but all the checkboxes are checked, rather than just three (as per the data currently in the PROJECT_MEASURES table).

I've been working on this for some time, and I'm probably missing something really obvious - hopefully someone can help me!

Thanks in advance....

Helen

 

[Helen267]

Sorry, should have included the cfoutput tags in the last piece of code:

<CFOUTPUT QUERY = "qryMeasures">
<TR>
<TD COLSPAN="3" ALIGN="left" VALIGN="top">
<INPUT TYPE="checkbox"
NAME="SEL_#b_id#"
VALUE="Y"
<CFLOOP QUERY="qryProjectMeasures">
<CFIF #qryProjectMeasures.b_id# EQ #qryMeasures.b_id#>
CHECKED
</CFIF>
</CFLOOP>>
&nbsp;#qryMeasures.measure#
</TD>
</TR>
</CFOUTPUT>

 

[webmigit]

<CFOUTPUT QUERY = "qryMeasures">
<TR>
<TD COLSPAN="3" ALIGN="left" VALIGN="top">
  <INPUT TYPE="checkbox" 
     NAME="SEL_#b_id#" 
     VALUE="Y"
     <CFIF #qryProjectMeasures.b_id# EQ #qryMeasures.b_id#>
       CHECKED
     </CFIF>>
     &nbsp;#qryMeasures.measure#
</TD>
</TR>
</CFOUTPUT>

Try that

and then try this...

<CFOUTPUT QUERY = "qryMeasures">
<TR>
<TD COLSPAN="3" ALIGN="left" VALIGN="top">
  <INPUT TYPE="checkbox" 
     NAME="SEL_#b_id#" 
     VALUE="Y"
     <CFIF [b]listfind(valuelist(qryProjectMeasures.b_id),qryMeasures.b_id)>
       CHECKED
     </CFIF>>
     &nbsp;#qryMeasures.measure#
</TD>
</TR>
</CFOUTPUT>

or this...

<CFOUTPUT QUERY = "qryMeasures">
<TR>
<TD COLSPAN="3" ALIGN="left" VALIGN="top">
  <INPUT TYPE="checkbox" 
     NAME="SEL_#b_id#" 
     VALUE="Y"
     <CFIF [b]listfind(valuelist(qryMeasures.b_id),qryProjectMeasures.b_id)>
       CHECKED
     </CFIF>>
     &nbsp;#qryMeasures.measure#
</TD>
</TR>
</CFOUTPUT>

I can say the cfloop is in the wrong place... before you run any of this code, take a look at the source code, you'll probably have something like... <input type="checkbox"...CHECKED CHECKED CHECKED>.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[Helen267]

Thank you! That second piece of code solved the problem, and everything's working perfectly. I suspected it was the CFLOOP tag, and I tried various combinations with the listfind function, but not in combination with the valuelist function.

Thanks again - you're a life-saver!

Helen