WD_ResponseQuestionAuditTrail has inaccurate data

[TeriStevenson]

We are on 2021 R2 and we have catalog items requiring approval with requester question templates and approver question templates on the approval workflow.  What we are seeing is when an approver pulls up the request in AppPortal and goes to the Answered Questions tab, they see answers by users who do not have any access to the request and the user with no access has placed their own request - it seems like the audit trail is updated incorrectly.  If I look at WD_Response (which I understand is no longer used) it does show the user who correctly answered the requester questions and the user who answered the approver questions.

Has anyone experienced this?  I do have an open support ticket but wondering if the community has seen anything like this.

[jdempsey]

I can't say that I've seen the specific problem you mention, but I don't spend a lot of time in a running system with such use cases.  I do know that for one project I worked on, I needed to be able to see the current answers to questions for various requests, so I created a custom view as a legacy report using the following query...

SELECT r.RequestID_FK,
       q.Question,
       ISNULL(a.Answer, r.Response) AS Response,
       r.Response AS ResponseValue,
       q.QuestionTarget,
       r.IsAnswered,
       r.AnsweredBy,
       q.QuestionID,
       r.SurveyID_FK,
       a.Message,
       r.IsMasked
FROM   dbo.WD_ResponseQuestionAuditTrail AS r INNER JOIN
       dbo.WD_Question AS q ON r.QuestionID_FK = q.QuestionID AND r.QuestionVersion =
           (SELECT MAX(QuestionVersion) AS Expr1
            FROM   dbo.WD_ResponseQuestionAuditTrail
            WHERE (RequestID_FK = r.RequestID_FK) AND (QuestionID_FK = r.QuestionID_FK)) LEFT OUTER JOIN
       dbo.WD_Answer AS a ON r.Response = a.Value AND r.QuestionID_FK = a.QuestionID_FK

Does this query return the correct values for you?

[TeriStevenson]

Interesting... some of the requests are returned using that query but the ones having the issue are not found.

When I look at the wd_responseaudittrail for the some of the ones that are not accurate, it's almost like the user changed one answer or answered an unanswered question and the audit trail shows that user changed every single question but I've watched them and they aren't doing that.  I'm also seeing rows for some of the requests where the answered by column is null, but the is changed is 1 with a timestamp, or it will have a user id who has no access to that request at all.

[CharlesW]

I need to find some time to test this scenario. I typically don't see multiple question templates used (one attached to the catalog item and one attached to the approval workflow), so I don't know if there are any issues or not. About all I can say at this point is that if one answer is changed, or an unanswered question is answered, then all the answers are updated in WD_ResponseQuestionAuditTrail, even if the answers for most of the questions do not change. Can you open a case for this and email me the support case number? That way I won't lose track of this.

Thanks,
Charles