User Custom Criteria Creation

I have an Internet CRM product that holds my client database in SQL Server 2008.  My clients would like to be able to customize their own criteria with a variety of field types - freeform text fields, dropdown listboxes to select one, lists to select as many as necessary.  They ultimately want to be able to create a client registration page that has their own personalized marketing fields and info.  Makes sense, but I'm overthinking the process.

I am trying to figure out the easiest way to configure this so they can build their own fields (type/length/selections/etc.) and store their client's data for the easiest way to query/sort in the future. Do I create a single record for every single criteria that a client would select - then delete if they update and no longer want that criteria?

All suggestions are welcome!
OverthereAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I have a back end crm that has this.   Think of this like you are selling items.  

You will need two tables.  A survey table and a question table where the question table has a foreign key of the survey table.  
Your main table will have at the very least an ID, Question, question type, question choices, answers.  And your survey table will be the survey id, name of survey, perhaps a date range when to display on the site.

Think of the form input types

<input type="text" name="lastname">

<input type="hidden" name="id" value="abc">

<input type="radio" name="status" value="y">
<input type="radio" name="status" value="n">

<input type="checkbox" name="things" value="cars">
<input type="checkbox" name="things" value="balls">
<input type="checkbox" name="things" value="desks">
<input type="checkbox" name="things" value="cups">

Open in new window

When building the questions, you would probably expect input as follows based on each input type:

<input type="text" name="lastname">
ID=1
Question=""
question_type="text"
question_choices="" ' blank because it is open ended
default_value = "" ' because it is open ended
answers=' blank because the form is not submitted

<input type="hidden" name="id">
ID=2
Question="" 'blank because hidden
question_type="hidden"
question_choices="" ' blank because it is hidden
default_value = "2" ' the id
answers=' blank because it is hidden

<input type="radio" name="status" value="y">
<input type="radio" name="status" value="n">

ID=3
Question="What is your status?"
question_type="radio"
question_choices="Yes, No" ' y,n  we will convert this to an array later when outputting the answer.
default_value = "n" ' default to n
answers=' blank because the form is not submitted


<input type="checkbox" name="things" value="cars">
<input type="checkbox" name="things" value="balls">
<input type="checkbox" name="things" value="desks">
<input type="checkbox" name="things" value="cups">

ID=4
Question="What things do you want?"
question_type="checkbox"
question_choices="cars, balls, desks, cups" ' y,n  we will convert this to an array later when outputting the answer.
default_value = "" ' default to nothing
answers=' blank because the form is not submitted

Assuming the they have added all of their questions, we now need to output. We don't know what any question will be so we need to do some type of if then statement. I think, CASE will be best.

I just wrote out the radio button, I think you can figure out the rest.
Do until rs.eof
Select CASE question_type

case "text"

case "hidden"

case "radio"
arrChoices = split(question_choices,",")
if default_value<>"" then
    default =  default_value
    else
    default = ""
end if
response.write question&" "
for each choice in arrChoices
   response.write "<input type='radio' name='"&id&"' value = '"&choice&"'> "&choice&" "
next


case "checkbox"



end if

Open in new window


For your request.form, you are looking to accept data to the "answers" field based on the question id.  I use a hidden field with the survey id.  But when you accept the answer the field name is the question id, so you just need to update the one row of data.  This means you will loop through the form collection and update one row at a time.

When you have a multiple choice, radio button etc.  Your answers field will be a comma delimited thing.   I would recommend offering to stop there on your bid.  They can import all the data to excel or just see the answers.  But the next step would be doing some type of reporting and you can see this is not a small deal.

I went over this pretty quickly from memory.  Let us know what you need help on.
0
OverthereAuthor Commented:
Ah - that makes sense.  I'm going to begin this now.  My biggest issue was with the multiple selections fields, but putting them into the field with commas separating is perfect!

I will just put some limitations on the number of custom fields and then the number of selections within those fields.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Where my sample may have been misleading is the name should have been the id of the question.

<input type="checkbox" name="4" value="cars">
<input type="checkbox" name="4" value="balls">
<input type="checkbox" name="4" value="desks">
<input type="checkbox" name="4" value="cups">

Then to accept the data
For each item in request.form
    theID = Item
    theData = Request.Form(theID)
   sql = "Update MyTable SET Answers = '"&theData&"' WHERE ID ="&theID

next

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OverthereAuthor Commented:
Good so far!  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.