Question Importance Rules Setup

Creates the table for dynamic question-importance rules (e.g. when F50 first char is "9", set F51 important=0). Rules are filtered by assessment_reason and include Trigger_Quest_ID / Target_Quest_Ids (ques_id) for future use.

Migration Progress:

SELECT COUNT(*) as table_exists FROM information_schema.tables WHERE table_schema = '#Request.prefix_db_lookup#' AND table_name = 'QuestionImportanceRules' CREATE TABLE #Request.prefix_db_lookup#.QuestionImportanceRules ( Rule_ID INT AUTO_INCREMENT PRIMARY KEY, Trigger_Question VARCHAR(50) NOT NULL COMMENT 'e.g. F50', Trigger_Quest_ID INT NULL COMMENT 'ques_id from assessment_questions_lookup; future use', Value_Match_Type VARCHAR(30) NOT NULL COMMENT 'first_char, first_2_chars, exact, in_list, first_char_in_list, first_2_chars_in_list', Value_Pattern VARCHAR(500) NOT NULL COMMENT 'e.g. 9 or 07,08,09,10,88 or 0-No', Target_Questions VARCHAR(500) NOT NULL COMMENT 'comma-separated e.g. F51 or F129,F130', Target_Quest_Ids VARCHAR(500) NULL COMMENT 'comma-separated ques_id; future use', Action_Value TINYINT NOT NULL DEFAULT 0 COMMENT '0 = set important 0, 1 = set important 1', Assessment_Reason VARCHAR(255) NOT NULL COMMENT 'e.g. 1 - Start of care - further visits planned', Status TINYINT DEFAULT 1, Date_Create DATETIME DEFAULT CURRENT_TIMESTAMP, Date_Modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_trigger (Trigger_Question), INDEX idx_assessment_reason (Assessment_Reason), INDEX idx_status (Status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 INSERT INTO #Request.prefix_db_lookup#.QuestionImportanceRules (Trigger_Question, Trigger_Quest_ID, Value_Match_Type, Value_Pattern, Target_Questions, Target_Quest_Ids, Action_Value, Assessment_Reason) VALUES ('F50', NULL, 'first_char', '9', 'F51', NULL, 0, ), ('F52', NULL, 'first_char', '9', 'F53', NULL, 0, ), ('F54', NULL, 'first_char', '9', 'F55', NULL, 0, ), ('F56', NULL, 'first_char', '9', 'F57', NULL, 0, ), ('F58', NULL, 'first_char', '9', 'F59', NULL, 0, ), ('F60', NULL, 'first_char', '9', 'F61', NULL, 0, ), ('F62', NULL, 'first_char', '9', 'F63', NULL, 0, ), ('F64', NULL, 'first_char', '9', 'F65', NULL, 0, ), ('F66', NULL, 'first_char', '9', 'F67', NULL, 0, ), ('F68', NULL, 'first_char', '9', 'F69', NULL, 0, ), ('F50', NULL, 'exact', '0-No', 'F51', NULL, 0, ), ('F52', NULL, 'exact', '0-No', 'F53', NULL, 0, ), ('F54', NULL, 'exact', '0-No', 'F55', NULL, 0, ), ('F56', NULL, 'exact', '0-No', 'F57', NULL, 0, ), ('F58', NULL, 'exact', '0-No', 'F59', NULL, 0, ), ('F60', NULL, 'exact', '0-No', 'F61', NULL, 0, ), ('F62', NULL, 'exact', '0-No', 'F63', NULL, 0, ), ('F64', NULL, 'exact', '0-No', 'F65', NULL, 0, ), ('F66', NULL, 'exact', '0-No', 'F67', NULL, 0, ), ('F37', NULL, 'first_char', '0', 'F38', NULL, 0, ), ('F34', NULL, 'first_char_in_list', '0,1,2', 'F46,F47,F48,F49', NULL, 0, ), ('F34', NULL, 'first_char_not_in_list', '0,1,2', 'F38,F39,F40,F41,F42,F43,F44,F45', NULL, 0, ), ('F30', NULL, 'first_2_chars', 'NA', 'F181,F31', NULL, 0, ), ('F124', NULL, 'first_2_chars_in_list', '07,08,09,10,88', 'F129,F130', NULL, 0, ), ('F126', NULL, 'first_2_chars_in_list', '07,08,09,10,88', 'F129,F130', NULL, 0, ), ('F157', NULL, 'first_2_chars', 'No', 'F158,F159,F160,F161,F162,F163', NULL, 0, ), ('F166', NULL, 'first_char_in_list', '0,3', 'F168', NULL, 0, ) UPDATE #Request.prefix_db_lookup#.QuestionImportanceRules qir INNER JOIN #Request.prefix_db_lookup#.#Request.assessment_questions_lookup# aql ON aql.answer_label = qir.Trigger_Question AND aql.assessment_reason = qir.Assessment_Reason AND (aql.Deleted = 0 OR aql.Deleted IS NULL) SET qir.Trigger_Quest_ID = aql.ques_id

Created QuestionImportanceRules table, inserted initial rules, and set Trigger_Quest_ID from assessment_questions_lookup (answer_label = Trigger_Question).

QuestionImportanceRules table already exists.

SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = '#Request.prefix_db_lookup#' AND table_name = 'QuestionImportanceRules' ALTER TABLE #Request.prefix_db_lookup#.QuestionImportanceRules ADD COLUMN Rule_Type VARCHAR(20) NOT NULL DEFAULT 'importance' COMMENT 'importance or validation' AFTER Assessment_Reason

Added Rule_Type column.

ALTER TABLE #Request.prefix_db_lookup#.QuestionImportanceRules ADD COLUMN Validation_Message TEXT NULL COMMENT 'For validation rules: message to show when condition fails' AFTER Rule_Type

Added Validation_Message column.

ALTER TABLE #Request.prefix_db_lookup#.QuestionImportanceRules ADD COLUMN Validation_Condition VARCHAR(50) NULL COMMENT 'e.g. one_of_required' AFTER Validation_Message

Added Validation_Condition column.

Alter validation columns warning: #cfcatch.message#

SELECT COUNT(*) AS cnt FROM #Request.prefix_db_lookup#.QuestionImportanceRules WHERE Rule_Type = 'validation' AND Target_Questions = 'F181,F31' AND Assessment_Reason = INSERT INTO #Request.prefix_db_lookup#.QuestionImportanceRules (Trigger_Question, Trigger_Quest_ID, Value_Match_Type, Value_Pattern, Target_Questions, Target_Quest_Ids, Action_Value, Assessment_Reason, Rule_Type, Validation_Message, Validation_Condition, Status) VALUES ('F181', NULL, 'exact', 'n/a', 'F181,F31', NULL, 0, , 'validation', 'M1005 Enter most recent inpatient discharge date or select unknown
', 'one_of_required', 0)

Inserted M1005 validation rule (F181 or F31 required).

SELECT 1 FROM #Request.prefix_db_lookup#.QuestionImportanceRules WHERE Rule_Type = 'validation' AND Validation_Condition = AND Assessment_Reason = LIMIT 1 " /> INSERT INTO #Request.prefix_db_lookup#.QuestionImportanceRules (Trigger_Question, Trigger_Quest_ID, Value_Match_Type, Value_Pattern, Target_Questions, Target_Quest_Ids, Action_Value, Assessment_Reason, Rule_Type, Validation_Message, Validation_Condition, Status) VALUES (, NULL, 'exact', 'n/a', , NULL, 0, , 'validation', , , 0)

Ensured all SOC validation rules from soc_script.cfm are in QuestionImportanceRules.

UPDATE #Request.prefix_db_lookup#.QuestionImportanceRules SET Status = 0 WHERE Assessment_Reason =

Set Status = 0 for all SOC rules (active).

ALTER TABLE #Request.prefix_db_lookup#.QuestionImportanceRules ADD COLUMN Trigger_Quest_ID INT NULL COMMENT 'ques_id; future use' AFTER Trigger_Question, ADD COLUMN Target_Quest_Ids VARCHAR(500) NULL COMMENT 'comma-separated ques_id; future use' AFTER Target_Questions

Added Trigger_Quest_ID and Target_Quest_Ids columns.

ALTER TABLE #Request.prefix_db_lookup#.QuestionImportanceRules ADD COLUMN Assessment_Reason VARCHAR(255) NULL COMMENT 'e.g. 1 - Start of care - further visits planned' AFTER Action_Value, ADD INDEX idx_assessment_reason (Assessment_Reason) UPDATE #Request.prefix_db_lookup#.QuestionImportanceRules SET Assessment_Reason = '1 - Start of care - further visits planned' WHERE Assessment_Reason IS NULL ALTER TABLE #Request.prefix_db_lookup#.QuestionImportanceRules MODIFY COLUMN Assessment_Reason VARCHAR(255) NOT NULL

Added Assessment_Reason column and backfilled.

ALTER TABLE #Request.prefix_db_lookup#.QuestionImportanceRules DROP COLUMN Assessment_Type

Dropped old Assessment_Type column.

Alter table warning: #cfcatch.message# (table may already be up to date)

UPDATE #Request.prefix_db_lookup#.QuestionImportanceRules qir INNER JOIN #Request.prefix_db_lookup#.#Request.assessment_questions_lookup# aql ON aql.answer_label = qir.Trigger_Question AND aql.assessment_reason = qir.Assessment_Reason AND (aql.Deleted = 0 OR aql.Deleted IS NULL) SET qir.Trigger_Quest_ID = aql.ques_id

Updated Trigger_Quest_ID on QuestionImportanceRules from assessment_questions_lookup (answer_label = Trigger_Question, same Assessment_Reason).

Sync Trigger_Quest_ID warning: #cfcatch.message#

Error: #cfcatch.message#

Back