Database View that includes a catalog item variables and approval information

[joconnor]

Hello!

I am trying to create a database view to generate a report on a service catalog item with approval information.

I am not able to get the variable information.  Is there something I am missing>

Left join	Order	Table	Variable prefix	View	Where clause
FALSE	100	task	tas	u_approver_req_view
TRUE	200	sc_req_item	req	u_approver_req_view	tas_sys_id=req_sys_id
TRUE	300	sysapproval_approver	app	u_approver_req_view	tas_sys_id=app_sysapproval

Thank you, James

[Swapna Abburi]

Hi @joconnor 

Database view on 'sc_req_item' table will not display the variables. You need to join 'sc_item_option' and 'sc_item_option_mtom' tables as well in DB view along with other tables.

[joconnor]

I tried this but still can not find the variable information to set as columns.  Thoughts? 

Left join	Order	Table	Variable prefix	Where clause
FALSE	100	sc_req_item	req
FALSE	200	sc_item_option_mtom	own	req.sys_id = own.request_item
FALSE	300	sc_item_option	opt	own.sc_item_option = opt.sys_id
FALSE	400	sysapproval_approver	app	app.sysapproval = req.sys_id

[Rakesh18081]

Below is how your query should be it will require joining 3 tables.

SELECT req.sys_id, req.number, opt.variable_name, opt.value
FROM sc_req_item req
LEFT JOIN sc_item_option_mtom mtom ON req.sys_id = mtom.request_item
LEFT JOIN sc_item_option opt ON mtom.sc_item_option = opt.sys_id

Regards

Rakesh Agarwal