How can I reference the owner of a survey's corresponding incident/record in the assessment instance table?

[Dylan26]

Hi all!

I've created a new field, Incident Owner, on the Assessment Instance table (asmt_assessment_instance), which I'd like to display the technician who was assigned the corresponding incident (INC00090002, in this case). I tried to reverse the behavior of other fields, like Assigned To, which is tied to the user who should complete the survey, but came up woefully short. A more literal interpretation of what's desired is below.

 

Thank you in advance! I've been racking my brain around this mini project for far too long!

Side Note: While attempting to reverse other fields' behavior, I found that this is far more complex than originally anticipated. Is there any way to visualize how the fields reference each other and/or other tables?

[Cuong Phan]

Hi Dylan,

You can achieve it by a Business Rule on table asmt_assessment_instance:

If document_id field "Trigger ID" is not allowed to dot-walk, you will need to query Incident table and get asssigned_to field value.

[JasonS_ · Accepted solution]

Hi Dylan,

I came upon a similar issue when I was building reports to see which survey results went with which Service Desk analyst on our team. 

The table I ended up referencing in my case was [asmt_metric_result] and I dot-walked to the assignee of the Task (or Incident) by going Instance > Task > Assigned to.

I hope that is helpful to you in some way!

Kind Regards,

Jason S.

[Dylan26]

This was the exact reason I had begun this little project - I'm glad I wasn't the only one in the same boat! I was able to circumvent any custom modifications to tables; no references needed.

I dot-walked just as you did on the asmt_metric_result table, which provided the results I was after.

For anyone else wondering the exact process when creating this report...

Data Tab:

Type Tab:

Configure Tab:

Columns (dot-walked):

Group By: 

Conditions:

These will vary, select your own home-grown metric definitions (questions):

Then sort the list view by Instance.

This report, when following the above steps, groups the Assessment Instance (survey) and the corresponding incident/task and its two (or more) metrics. See below.

[Sharjeel Malik]

Hi Dylan,

There are multiple possible ways of achieving this:

- You can dot walked Task>Assigned to field.

- As you had created "Incident owner" field of type String so you can write a Before Insert Business rule on 'asmt_assessment_instance' table and GlideRecord Incident table with the condition "sys_id=<sys_id reference in trigger_id field>" to populate assigned_to user on the incident table into incident owner field on asmt_assessment_instance table.

- You can create Incident Owner of type reference (sys_user) field and populating the user based on same logic I describe above. Also, we can use advance reference qualifier to limit the result to assigned_to user of associated incident record.

I hope this will help. Please let me know if you need further help.

Best Regards,

Sharjeel Malik