Reporting on question_answer table

[daveyk72]

Hi

I'm running a report on the question_answer table to get results from a service catalog form. One of the questions is employee's name and it seems to only return the sys_id value as apposed to the get display value (see below)

Is there any way of returning the get display value? These are my selected column headers:

Thanks in advance

Dave

[Chuck Tomasi]

Hi David,

The value of a reference field is a sys_id. If there is a display value for the table being referenced, then it will display that. This is the systems way of saying "sys_ids are nice for computers, but I need one of the fields in this table to show the humans so they can read it."

Check what table is being used by the field/variable in your report (typically users are referenced from sys_user, but you may be referring to a table that refers to sys_user). Make sure that table has one of the fields set to display=true in the dictionary.

http://wiki.servicenow.com/?title=Reference_Fields

[Andras Kisgyorg · Accepted solution]

Hi Dave,

I think this could be achieved by a Database View table and then running report on that table instead.

You can try following the below to create a Database View (details about creating them can be found on Wiki or Docs),

and adding two View Tables, the question_answer and sys_user.

Then, in your sys_user View Table you could join these 2 tables where the "value" field of question_answer table = to the "sys_id" field of sys_user table.

In the same sys_user View Table then add the desired user fields e.g. sys_id, First name, Last name as View Fields which you want to appear in your report.

This should result in a Database View table, containing the question_answer records with the employee sys_id value as well as the matching records with the columns you identified from the sys_user table.

At the end make sure to verify the view returns all the question_answer records required.

You can check the screenshot of this drafted as a starting point:

Best regards,

Andras

[daveyk72]

Perfect...thanks Andras

[Carl Fransen1]

Thanks for this Andras - I was struggling to translate the 'value' field from sys_id to user name - as some fields were sys_ids and others were the string data entered.

Adding the 'view fields' did the trick, so thanks for your reply above it worked great!

Cheers

Carl.