question Re: DB View for variable of type reference in Community Central forum

DB View for variable of type reference

krishnakumar2 — Mon, 15 Jul 2024 14:02:49 GMT

Hi Team,

I am using DB view for showing combined value in variable of type reference. It works as expected and I am able to select appropriate value for the reference field.

But, the issue I am facing is, selected value is not being displayed while viewing the RITM variables. Variable being displayed has one of the sys_id from the DB view (either table1 or table 2 sys_id) but there is not display value being shown like normal reference field. Any suggestions will be appreciated.

As you see in the screenshot below, you can see DOB and Personal email address being fetched and shown properly but not cost code.

Thanks in advance!

Krishna

Re: DB View for variable of type reference

Slava Savitsky — Mon, 15 Jul 2024 16:09:37 GMT

A database view combines data from two or more tables, but it is not a table itself. It does not store any records and doesn't have any attributes, not even sys_id. Consequently, there is no way to have reference fields or variables to point to "records" in a database view.

Your reference variable has to reference actual records from the actual table that is the source of data for the database view. However, your variable can only reference one table, not both at the same time.

Re: DB View for variable of type reference

krishnakumar2 — Tue, 16 Jul 2024 12:21:45 GMT

I found solution. I am using Display Business rules to query the vie and send the Display value of the view to client side and then use the catalog client script (applicable only to Request Item) to set the value of the variable. It's twisted approach a bit, but works.

Re: DB View for variable of type reference

krishnakumar2 — Tue, 16 Jul 2024 12:28:22 GMT

Found a solution. Since DB view part is working just fine while submitting the request from catalog item, I created the Display Business rule on sc_req_item table to fetch the display value from the DB view and then created the catalog client script (applicable only to Requested Item) to set the value on load to the variable. Solution is bit twisted, but works.