Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
Are the question id's that are not in your response table at least in a question table of some sort?
Copy link to clipboard
Copied
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=#session.inspection#&transaction_message=P|QN">
.