View All option in dynamic select list

[tina2]

I am generating a select list from a query and displaying the results on the action page.

I would like to also give the user the option to select "all" from the list...

I have tried putting in an IF statement that correctly identifies that All was selected, but throws an error because I am pulling an ID from the select list, which of course returns the word "all" instead of an ID.

What is the missing link???

Thanks,
Kristine

 

[CFDude]

Kristine,
Can you post your code? What error are you getting?

You could set the value of the select all option with the value of all the record ids: (use valuelist to create a comma separated list of all the record ids)

e.g
<cfquery name=&quot;myQuery&quot; datasource=&quot;foo&quot;>
Select recordId, recordname from mytable
</cfquery>
<form action=&quot;page2.cfm&quot;>

<select name=&quot;recordid&quot;>
Select ID
<option value=&quot;#valuelist(myQuery.recordID)#&quot;>Select All
<cfoutput query=&quot;myQuery&quot;>
<option value=&quot;#recordID#&quot;>#recordName#
</cfoutput>
</select>
</form>

 

[tina2]

CFDude,
Using valuelist (thanks for that), the single list values work, but the All selction does not.

Could it be the action page query??

-------------Form page Query-----------------------

<CFQUERY NAME=&quot;qGetPolicy&quot; DATASOURCE=&quot;ZenithWeb&quot;>
SELECT WebUserLookup.UserID, WebUserLookup.PolicyID, WebUsers.UserID, WebUsers.ClientID, Policy.ClientID, Policy.PolicyID, Policy.Name
FROM WebUserLookup, WebUsers, Policy
WHERE (WebUserLookup.UserID = webusers.userID) AND (WebUserLookup.PolicyID = Policy.PolicyID) AND (WebUsers.UserName = '#form.Username#')
ORDER BY policy.name
</CFQUERY>

---------------------Select List-----------------

<cfselect name=&quot;Policy_List&quot;>
<option value=&quot;#valuelist(qGetPolicy.PolicyID)#&quot;>All</option>
<cfoutput query=&quot;qGetPolicy&quot;>
<option value=&quot;#qGetPolicy.PolicyID#&quot;>#qGetPolicy.Name#</option>
</cfoutput>
</cfselect>

---------------------Action page query-----------------
<CFQUERY NAME=&quot;qGet_Policy_Details&quot; DATASOURCE=&quot;ZenithWeb&quot;>
SELECT ClientID, PolicyNumber, InsurerID, Name, Commencement, Expiry, MaximumLiability, TermsOfPayment, PolicyType, PolicyID, Administrator
FROM Policy
WHERE (PolicyID=#Policy_List#)
</CFQUERY>

--------------------Error message----------------------

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(PolicyID=#valuelist(qGetPolicy.PolicyID)#)'.


SQL = &quot;SELECT ClientID, PolicyNumber, InsurerID, Name, Commencement, Expiry, MaximumLiability, TermsOfPayment, PolicyType, PolicyID, Administrator FROM Policy WHERE (PolicyID=#valuelist(qGetPolicy.PolicyID)#)&quot;

Thanks for your help!

Kristine

 

[CFDude]

Kristine,
Put this line inside a cfoutput tag:
<cfoutput>
<option value=&quot;#valuelist(qGetPolicy.PolicyID)#&quot;>All</option>
</cfoutput>

Also, change the query line:
WHERE (PolicyID=#Policy_List#)

to

WHERE (PolicyID IN (#Policy_List#))

The difference is that the PolicyID= will only take a single value, so that is the reason the single value will work. The IN clause allows a list to be passed in. It will still work on a single value as well.

HTH,
Tim P.

 

[tina2]

Dude!!!

thanks! that worked like a charm.
(duh... cfoutput;-)
Kristine