Adrian, Thanks, I agree would have been better to not have built the tables this way, but you have to play the hand dealt.
Mike Sumner Nations Best Sports 817-788-0034 ext 244 817-788-8542 Fax -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Adrian J. Moreno Sent: Monday, October 02, 2006 3:12 PM To: Dallas/Fort Worth ColdFusion User Group Mailing List Subject: Re: [DFW CFUG] Many Many Checkboxes Re: the horizontal scroll - Is CFGRID automatically displaying the data horizontally because of the query (i.e. one record, multiple columns)? I haven't used CFGRID, but if so, you could do what's called a pivot query to transform the columns to rows and vice-versa. This should then display the data vertically. I think there's a UDF for this on http://cflib.org/ . If possible, I'd change the DB table design. Having all those profile Categories defined as columns limits the scalability of the database. You'll have to add a new column every time you add an category. Table: Profile (individual profiles) ProfileID [PK] Table: Categories (list of all available categories) CategoryID [PK] CategoryActive [0/1: This would allow you to turn categories on or off as needed.] Table: Profile_Categories (categories selected per profile) ProfileID [FK: Profile.ProfileID] CategoryID [FK: Categories.CategoryID] The Primary key for Profile_Categories could be either a separate column called ProfileCategoryID or you could create a Primary Key from the combination of the ProfileID and CategoryID. The 2nd would also ensure a unique constraint on combinations of ProfileID and CategoryID. All the checkboxes would have the same name with the CategoryID as a value: <input type="checkbox" name="category" value="#query.CategoryID#" /> When the form is submitted, you'd just loop over the list created by the selected checkboxes and create a new record in Profile_Categories for each list item. When a user changes their profile categories, just DELETE FROM Profile_Categories WHERE ProfileID = #form.ProfileID# then loop over the selected checkboxes to store the changed list. If you use this database layout, it may also be easier to use <cfinput /> as well. You'll have one query that returns the full list of categories. You'd use this to display the category checkboxes vertically. You'd also have the profile query, which would contain all the selected categories for that profile. Create a list of the selected categories: <cfset categoryList = valueList(profileQuery.CategoryID) /> As you loop over the categoryQuery, check the current CategoryID against the list to see if the checkbox should be marked: <cfoutput query="profileQuery"> Profile: #profileQuery.ProfileName# <cfloop query="categoryQuery"> <cfif listFind(categoryList, categoryQuery.CategoryID)> <cfinput type="checkbox" name="categoryID" value="#categoryQuery.CategoryID#" checked="checked" /> <cfelse> <cfinput type="checkbox" name="categoryID" value="#categoryQuery.CategoryID#" checked="" /> </cfif> </cfloop> </cfoutput> This will allow you to add infinitely more Categories without affecting your Profile table. All you have to do is add a new record to the Categories table and it will be automatically displayed in the form's checkbox list. HTH, Adrian Mike Sumner wrote: > I have a table with 49 Yes/No fields - a member profile. I would > appreciate any suggestions for handling this mess - best way to > display it - table, flash form, etc. and how to handle updating the > DB. I wanted to use a Flash form and am having trouble displaying the > fact that an item should be checked according to the table <cfinput > type="checkbox"...> <cfgrid> works well, I just need to think about > how to make that look good and easy to navigate - I do not like the > thought of a horizontal scroll that wide. If anyone has an thought I am all ears. > > Thanks, > > Mike Sumner > Nations Best Sports > 817-788-0034 ext 244 > 817-788-8542 Fax _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/ _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
