• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

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!
0
Overthere
Asked:
Overthere
  • 2
  • 2
1 Solution
 
Scott Fell, EE MVEDeveloperCommented:
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 MVEDeveloperCommented:
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
 
OverthereAuthor Commented:
Good so far!  Thanks!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now