Experiencing Brain Freeze: comparing 2 sets of values

Report · Apr 23, 2009

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

Report · Apr 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)

Report · Apr 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.

Report · Apr 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>

</cfcatch>

</cftry>

Report · Apr 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

Report · Apr 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?

Report · Apr 30, 2009

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

Report · May 07, 2009

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.

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

<cfif response IS 'FIELDNAMES'>
    



    <cfelseif response IS 'QUESTIONNAIRE_RECCOMENDATIONS'>



    


        <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'>

<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#">
            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>


    

    </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>

.

Adobe Community

Experiencing Brain Freeze: comparing 2 sets of values