Form Generator - Help with Query

[JethroTull]

I am seeking help with a form generator app that I am working on...

There are 2 tables: Campaign & Campaign Forms

Each campaign will have one or more forms. The table Campaign Forms consists of all the fields that the form may have with yes/no. (fname, lname, email, address, etc etc...)

For example: to generate a short form of only Fname, Lname and Email, then i can do so by checking those fields in the DB.

The question is, I am struggling with the query. How do I get ONLY those fields which are checked true?

Thank you in advance everyone.

JT
PS: please send a private msg and i can send a test DB. thx

[paross1]

quote:

---

For example: to generate a short form of only Fname, Lname and Email, then i can do so by checking those fields in the DB.

---

What do you mean by ..checking those fields in the DB?

Phil

[JethroTull]

All the fields in the DB are Yes/No

Fname, Lname, Email etc... so basically i set them true to generate the form.

On the front end, i want to run a query on the forms table to return only those fields which are true and then display the form...

thanks

[paross1]

OK, I am assuming now that you are using an Access database. (It is a good idea to post that kind of information up front when asking for help, since it helps narrow things down.)

I haven't used Access with ColdFusion for a few years now, but I believe that the Yes/No datatype equates to a type bit (CF_SQL_BIT ?) where yes=1 and no=0. I suppose that you can query the Campaign Forms table and use CFIF tags to drive display of the field(s) in your output HTML form anywhere that the queried field = 1. In other words, you would have to select all of the fields in your query to be able to check which ones were "Yes" and "No", then use the results of that query to drive what is displayed in your display.

Phil

[JethroTull]

Thank you Phil,
You are right, i have created a test form generator exactly the way you suggested. The only problem with that method would be any change requested by the client would involve to update the code for the form too...

What I was trying to do is to query only those fields which are set to true and then create a list of the column list using:

colList = #ArrayToList(getForms.getColumnList())#

then loop through that list to display the form. So if any field is added or deleted does not involve any code change for the form...

What you are suggesting will work. Selecting all the fields by: Select * from Forms where campaignID = 'CMP1' and then on the front end do the following:

<cfif FName>
First Name: <input type="text" name="Fname">
</cfif>

What I am trying to do is to loop through column names:

<cfloop list="#colList# index="i">

<input type="text" name="#i#">

</cfloop>

I am anticipating that different clients will want customized form for different campaigns and that is the reason why I want to make this a dynamic form. Do let me know if I am on the wrong track...

Thank you in advance

JT