To get the variables and MVRS variables database views.

prudhvisnow
Tera Contributor

Friday

How can I create a Database View to join custom tables with RITM records in ServiceNow, including both normal variables and MVRS (Multi-Row Variable Set) variables?
How can I retrieve normal variables and MVRS variables in the Database View?

0 Helpfuls
  • 350 Views
2 REPLIES 2

Tanushree Maiti
Mega Patron

Friday

Hi @prudhvisnow 

 

Connecting so many tables in a single Database view !  ( link has been shared - DB view for MRVS variable and DB view for normal variable)

Try this..

OrderTable NamePrefixWhere ClauseLeft Join
100[Your Custom Table]cust FALSE
200Requested Item (sc_req_item)ritmritm.sys_id = cust.[ritm_field]FALSE
300Variable Ownership (sc_item_option_mtom)mtommtom.request_item = ritm.sys_idTRUE
400Options (sc_item_option)optopt.sys_id = mtom.sc_item_optionTRUE
500Multi-Row Question Answer (sc_multi_row_question_answer)mrvsmrvs.document_key = ritm.sys_idTRUE

 

Refer: 

https://www.servicenow.com/community/developer-forum/data-visualization-how-to-make-reports-based-of...

To see normal Variable report : Two ways (Database View and Variable config) to create Catalog Item variable report

 

 

Please mark this response as Helpful & Accept it as solution if it assisted you with your question.
Regards
Tanushree Maiti
ServiceNow Technical Architect
Linkedin:
0 Helpfuls

21121A3359
Tera Expert

Friday

hai @prudhvisnow  

You can create a Database View to include both normal catalog variables and MVRS variables for RITM records, but it requires joining multiple tables since the data is stored differently.

1. Tables to include

For normal variables:

  • sc_req_item
  • sc_item_option_mtom
  • sc_item_option
  • item_option_new

For MVRS variables:

  • sc_req_item
  • sc_multi_row_question_answer
  • item_option_new

2. Join conditions

Normal variables:

sc_req_item.sys_id = sc_item_option_mtom.request_item
sc_item_option_mtom.sc_item_option = sc_item_option.sys_id
sc_item_option.item_option_new = item_option_new.sys_id

MVRS variables:

sc_req_item.sys_id = sc_multi_row_question_answer.parent_id
sc_multi_row_question_answer.question = item_option_new.sys_id

3. Fields to select

For normal variables:

  • sc_req_item.number
  • item_option_new.name
  • sc_item_option.value

For MVRS:

  • sc_multi_row_question_answer.row_index
  • item_option_new.name
  • sc_multi_row_question_answer.value

4. Important points

  • MVRS data is stored as multiple rows, so one RITM will produce multiple records
  • Database Views do not support aggregation or pivoting
  • You may see duplicate RITM numbers due to multiple rows

5. Recommended approach

If you need cleaner reporting:

  • Create separate views for normal variables and MVRS, or
  • Use scripts/reports to flatten MVRS data after retrieval
0 Helpfuls