7 Replies Latest reply on May 7, 2009 6:37 AM by jbird5k

    Experiencing Brain Freeze: comparing 2 sets of values

    jbird5k Level 1

      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

        • 1. Re: Experiencing Brain Freeze: comparing 2 sets of values
          insuractive Level 3

          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)

          • 2. Re: Experiencing Brain Freeze: comparing 2 sets of values
            jbird5k Level 1

            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.

            • 3. Re: Experiencing Brain Freeze: comparing 2 sets of values
              BKBK Adobe Community Professional & MVP

              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>

              • 4. Re: Experiencing Brain Freeze: comparing 2 sets of values
                jbird5k Level 1

                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

                • 5. Re: Experiencing Brain Freeze: comparing 2 sets of values
                  ilssac Level 5

                  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?

                  • 6. Re: Experiencing Brain Freeze: comparing 2 sets of values
                    Dan Bracuk Level 5

                    Are the question id's that are not in your response table at least in a question table of some sort?

                    • 7. Re: Experiencing Brain Freeze: comparing 2 sets of values
                      jbird5k Level 1

                      Dan / Ian

                       

                      I have the following tables  [abreviate versions not all fields are included for each table]

                       

                      Questionnaire table

                           questionaire_id

                           questionaire_tite

                           ......

                       

                       

                      Question table

                           question_id

                           questionaire_id

                       

                           .......

                       

                      response table

                       

                      response_id

                      questionnaire_id

                      question_id

                      inspection_id

                      response

                      question_comment

                      ............

                       

                      So the form [questionaire] is built dynamically  by calling a questionnaire_id, which pulls all associated questions, when presented on screen ; the question is printed then the appropriate form element presented  the element name is the question_id. [ the form element type is determined by other fields in questionnaire table and question table]

                       

                      The first time a questionnaire is opened, before any questions are answered, a record is created in the response table for each question in the questionaire.

                       

                      The questionaire can be saved at any point at which time the response table is updated with any answers provided.

                       

                      the administrator can add questions to a questionnaire at any point. [ this is the problem area ]

                       

                           If the questionnaire has been saved and the users edits the questionaire, lets say to complete it.  the new questions are  available to be answered but when the questionaire is saved the new questions ARE NOT in the response table, there for they ARE NOT UPDATED.

                       

                      So I am trying to catch any new questions in a questionnaire and insert a record in the response table if the question id is not there already.

                       

                      you will notice below, when I tried the merge,  I tried to use an include for not matched statement , thats because I need to create a response id for each new record. the response id field is not auto incremented.  {useing oracle 10g backend}

                       

                      here is the current code for updating the response table.

                      ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ -------------


                      <cfloop collection="#form#" item="response">

                       

                      <cfoutput>

                       

                          <cfif response IS 'FIELDNAMES'>
                          <!--- no action taken --->
                         
                         
                         
                          <cfelseif response IS 'QUESTIONNAIRE_RECCOMENDATIONS'>
                         
                         
                         
                          <!--- updates reccomendation_tbl --->
                         
                         
                              <cfquery name="UpdateReccomend" datasource="#application.insp#">
                              UPDATE reccomendation_tbl
                              SET reccomendation = '#form[response]#'
                              WHERE  inspection_id = #session.inspection# AND questionaire_id = #session.questionnaire# <cfif session.people GT 0>AND people_id = #session.people# </cfif><cfif session.item GT 0>AND item_id = #session.item# </cfif><cfif session.case GT 0>AND case_id = #session.case#</cfif>
                              </cfquery>
                         
                          <cfelseif response IS 'QUESTIONNAIRE_COMMENTS'>
                          <!---  updtaes reccomendation_tbl --->

                       

                              <cfquery name="UpdateOverAllComment" datasource="#application.insp#">
                              UPDATE reccomendation_tbl
                              SET overall_comments = '#form[response]#'
                              WHERE  inspection_id = #session.inspection# AND questionaire_id = #session.questionnaire# <cfif session.people GT 0>AND people_id = #session.people# </cfif><cfif session.item GT 0>AND item_id = #session.item# </cfif><cfif session.case GT 0>AND case_id = #session.case#</cfif>
                              </cfquery>
                         
                         
                          <cfelse>
                              <cfif Find('COMMENT', response)>
                             
                              <cfset UpdateID = ListLast(response, '|')>
                             
                             
                                  <cfquery name="UpdateQuestionComment" datasource="#application.insp#">
                                  UPDATE response_tbl
                                  SET question_comment = '#form[response]#'
                                  WHERE question_id = #UpdateID# AND inspection_id = #session.inspection# AND questionaire_id = #session.questionnaire# <cfif session.people GT 0>AND people_id = #session.people# </cfif><cfif session.item GT 0>AND item_id = #session.item# </cfif><cfif session.case GT 0>AND case_id = #session.case#</cfif>
                                  </cfquery>
                         
                              <cfelse>
                             
                                  <!---<cfquery name="UpdateResponse" datasource="#application.insp#">
                                  MERGE INTO response_tbl
                                  USING '#form[response]#'
                                  ON (question_id = #response# AND inspection_id = #session.inspection# AND questionaire_id = #session.questionnaire# <cfif session.people GT 0>AND people_id = #session.people# </cfif><cfif session.item GT 0>AND item_id = #session.item# </cfif><cfif session.case GT 0>AND case_id = #session.case#</cfif>)
                                  When matched then update SET response = '#form[response]#'
                                  When NOT matched then <cfinclude template="insert_response_record.cfm">
                                  </cfquery>--->

                       

                       


                                   <cfquery name="UpdateResponse" datasource="#application.insp#">
                                  UPDATE response_tbl
                                  SET response = '#form[response]#'
                                  WHERE <cfif question_id NEQ #response#><cfinclude template="insert_response_record.cfm"><cfelse> question_id = #response# </cfif>AND inspection_id = #session.inspection# AND questionaire_id = #session.questionnaire# <cfif session.people GT 0>AND people_id = #session.people# </cfif><cfif session.item GT 0>AND item_id = #session.item# </cfif><cfif session.case GT 0>AND case_id = #session.case#</cfif>
                                  </cfquery>
                              </cfif>
                         
                         
                          <!---<cfdump var="#session#">
                          <cfabort>
                           updtaes response to question in response_tbl --->
                         
                          </cfif>

                       

                      </cfoutput>

                       

                      </cfloop>

                       

                       

                       

                      <cfquery name="PublishQuestionnaire" datasource="#application.insp#">
                      UPDATE lock_tbl
                      SET complete = 1
                      WHERE inspection_id = #session.inspection# AND questionaire_id = #session.questionnaire# <cfif session.people GT 0>AND people_id = #session.people# </cfif><cfif session.item GT 0>AND item = #session.item# </cfif><cfif session.case GT 0>AND case_id = #session.case#</cfif>
                      </cfquery>

                       

                       

                       

                      <cflocation url="index.cfm?section=inspections&function=inspection_menu&inspection_session_id=#sessio n.inspection#&transaction_message=P|QN">

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                      .