How to Create Database View for Request Item, Survey results in ServiceNow?

[PrasannaKuD]

Hi Team,

I'm trying to create database view for Request Item, SCTASK and Assessment Instance (asmt_assessment_instance), Assessment Metric (asmt_metric), Assessment Metric result (asmt_metric_result)

1. Username who gave the survey
2. RITM and SCTASK Number
3. Assignment group to which this ticket was assigned in SCTASK
4. User rating what he gave out of 5
5. User comments if he put any
6. date of survey triggered
7. date of survey filled

I have configured the setup as per the below screenshot and table. Kindly review the configuration and let me know if there are any corrections required.

The table is taking a significant amount of time to load the data. Additionally, the "Actual Value" and "String Value" fields are appearing empty for all the records.

Kindly review and advise on this issue.

Additionally, please suggest if there are any alternative approaches to achieve the same requirement.

If there are any similar configuration requirements or existing implementations, kindly share those details as well.

Table	Order	Variable Prefix	Where Clause	Left Join	Active
asmt_assessment_instance	100	asmtInst		FALSE	TRUE
sc_req_item	200	ritm	asmtInst_trigger_id = ritm_sys_id	TRUE	TRUE
sys_user	300	usr	asmtInst_user = ritm_requested_for	TRUE	TRUE
asmt_metric_result	400	metricRes	metricRes_instance=asmtInst_sys_id	TRUE	TRUE
asmt_metric	500	metric	metricRes_metric=metric_sys_id	TRUE	TRUE
sc_task	600	sctask	sctask_request_item = ritm_sys_id	TRUE	TRUE
sys_user_group	700	grp	sctask_assignment_group = grp_sys_id	TRUE	TRUE

 Thanks & Regards,

Prasanna Kumar

[Vishal Jaswal]

Hello @PrasannaKuD 

Please avoid using uppercase in the variable prefix as per this KB article here: KB0727206 - A similar community post resolved few days ago see this .

I have submitted an idea to ServiceNow, please vote so that ServiceNow takes care of it and subscribe to get any updates on this idea here: https://support.servicenow.com/ideas?id=view_idea&sysparm_idea_id=b2af31cf935ccbd8e7eef35d6cba1097&s...

[PrasannaKuD]

Hi Vishal,

After changing the prefix, still nothing has been changed. Still the "Actual Value" and "String Value" fields are appearing empty for all the records.

Thanks & Regards,

Prasanna Kumar

[Vishal Jaswal]

Hello @PrasannaKuD 

Working as expected:

[Artur Makowski]

Hi,

I see you miss condition to join the sys_user table.

I suggest either of 2 options:

- use "usr_sys_id=asmtInst_user" in the where clause for sys_user table

- drop the sys_user from the join (you need to move the condition for , if you need any user data, then you may anywhere dot-walk from either of asmtInst_user or ritm_requested_for attribute.

Also, not sure if existing condition asmtInst_user = ritm_requested_for does any good, IMHO it is redundant (if not even harming...)

This should fix the slowness (system tries to process all users...)

Regards,

Artur