I'm having some difficulty creating a Database View for surveys. Below are the requirements, screenshots of the database view I created, and the challenges I'm facing.
Requirements:
- Each survey result per INC is in one row.
- Each question (from Question Bank) is in one column.
- Only Completed surveys should be in the report. (This is ok if we have to filter them out later.
- Multiple surveys are needed due to trigger conditions.
- 5 questions/response options are same across all surveys.
- 2 questions are the same across all surveys but the response options are different.
Challenges:
We are using Question Bank for all the survey questions and those questions hold all the response options. We created (4) surveys based on trigger conditions. In survey designer, we pulled in the Questions from the Question Banks and for two questions removed some of the response options. However, this created different sys_ids for every question (to be different than the Question Bank question sys_id). Therefore, in the database view I am referencing only one survey sys_id to one question sys_id. I attempted to add another join referencing the sys_id of a 2nd survey and the sys_id of it’s question, but the database view was completely blank. Can I only add joins for one survey in particular? Even that method would result in having 4 different columns for the same questions (for each survey). Is there a different way we can handle this to get the report to meet the requirements?
