Skip to main content
jbird5k
jbird5k
Inspiring
April 23, 2009
Question

Experiencing Brain Freeze: comparing 2 sets of values

  • April 23, 2009
  • 2 replies
  • 1356 views

Hi here is my issue,  my app uses questionnaires to capture interview data, the administrator can add questions to the questionnaires at any time.  This issue comes in when new questions are added to questionnairs that are opened in edit mode. The new questions appear but when the edited form is saved the data from the new questions is not saved, this because the action page, updates the response based on question id, and since the new questions have never been saved they are not in the response table and dont get updated.

What I need to do is compare the question id being passed by the form with question ids in the response table for the specified questionnaire Then take the form question ids that do not match [this is where my brain quits]question ids in the response table and instert them into the response table.

How do i isolate the new question id's?

thanks

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
April 26, 2009

Just use the database setting that makes the question_id values in the response table unique. For example, make question_id the primary key. Then the database server will automatically refuse to duplicate the question ID. I would also place the code within a try-catch, to trap the exception, thus

<cftry>

<!--- the code --->

<cfcatch type="database">

<!--- handle the error, for example, by logging it --->

</cfcatch>

</cftry>

jbird5k
jbird5kAuthor
Inspiring
April 30, 2009

uniqueness of the question Id is not the issue.  This issue is the forms are built dynamically and may  have question_ids that are not in the response table,  I want to isolate those "new" question ids so that I can run them through an insert record function that will generate a new response id and insert the new response id and question id to the response table so that it can be updated and properly assoiciated with the questionnaire in the response table.

I have two sticking points, isolating the new questions,  and generating the new id,  had the response id been auto generated I would have run a sql merge and been done with this long ago.

Thanks

J

ilssac
ilssac
Inspiring
April 30, 2009

How do these 'question_id's used by the forms relate to the question id's in the database?

How is the form dynamically generating new ids?

How much of that can you change if necessary or desired?

I
insuractive
Inspiring
April 23, 2009

Without being familiar with your application structure, its a little hard to understand what you are asking.  What does "edit mode" mean?  Does that mean the user is desigin the form (adding new questions, etc) or filling out the form? How are your question IDs assigned?  Where are they stored?

Are you just trying to figure out what fields are NOT in your database?  You should be able to do a query of your existing question IDs in your table and compare it against your form lists to identify which ones are missing (maybe combine your question ID values into a list using ValueList(Query.columnName) and then use the ListFind() function to identify which ones aren't present in the DB)

jbird5k
jbird5kAuthor
Inspiring
April 24, 2009

Thank you for your reply,

"Are you just trying to figure out what fields are NOT in your database? "  this is exactly what I'm trying to do.  I will take a look at the valuelist() and listfind() functions and let you know how it works out,

Thanks J.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices