Database view of 2 tables joined

[AbdulRahim Shai]

Hello Developers,

Need your expertise in achieving this requirement.

I am trying to join Variables ownership (sc_item_option_mtom) table and Options(sc_item_option) table

Need this view for migrating RITMs variables as per requirement

I want the view to have parent item attribute  from (sc_item_option_mtom)  table and  option, value  attribute from (sc_item_option) table
Eg :

I am not able see records in view since not sure about where clause to join this table.

Requesting your assistance.
Thanks,

AbdulRahim

 

[Brad Bowman · Accepted solution]

You almost had it.  Try this:

With the Where clause on the second table

vo_sc_item_option=op_sys_id

Where clauses always follow the format prefix_field_name=prefix_field_name

[Brad Bowman · Accepted solution]

That's going to be tricky.  To aid in this, right-click the column headers and choose Configure > List Layout to add the Question.Reference and Question.List table (for List Collector variables) to the view.  So now you can at least see the table the sys_id (or list of sys_ids) belongs to.

To see this all in the view, you will have to add a table for each unique reference table.  All of these tables will be joined with op with Left join = true so that it shows all of the values whether they match that table or not.

Then you would add each Display column to the view so that you can see the display value (along with the sys_id...) but these will each be in their own column.

This will also cause the List Collector variables with only one record/sys_id to appear in the same display value columns.  To get the List Collectors with a comma-separated list of sys_ids to appear and/or to replace the Value with the display value, you'd probably have to do something like export the view to Excel, then have an ODBC SQL connection that can run a GlideRecord query on the Reference table with the Value to return the Name (or Number, ...)

[Brad Bowman · Accepted solution]

You almost had it.  Try this:

With the Where clause on the second table

vo_sc_item_option=op_sys_id

Where clauses always follow the format prefix_field_name=prefix_field_name

[AbdulRahim Shai]

Thanks Brad for your help it worked...

Can you assist for below challenge i am facing...

Now i am realizing that Options table (sc_item_option) for reference variable it stores the sys_id of record .

Eg: 

I want display value of reference variables data instead of sys_id..  

For eg i know its storing sys_id of user but how can i make it show its username??

Thanks,

AbdulRahim

[Brad Bowman · Accepted solution]

That's going to be tricky.  To aid in this, right-click the column headers and choose Configure > List Layout to add the Question.Reference and Question.List table (for List Collector variables) to the view.  So now you can at least see the table the sys_id (or list of sys_ids) belongs to.

To see this all in the view, you will have to add a table for each unique reference table.  All of these tables will be joined with op with Left join = true so that it shows all of the values whether they match that table or not.

Then you would add each Display column to the view so that you can see the display value (along with the sys_id...) but these will each be in their own column.

This will also cause the List Collector variables with only one record/sys_id to appear in the same display value columns.  To get the List Collectors with a comma-separated list of sys_ids to appear and/or to replace the Value with the display value, you'd probably have to do something like export the view to Excel, then have an ODBC SQL connection that can run a GlideRecord query on the Reference table with the Value to return the Name (or Number, ...)

[AbdulRahim Shai]

Thanks a bunch brad this is exactly what I wanted... 

Now is there a way to make this table data visible as related list?

I have my custom table for RITM in target instance where we are migrating these data.

in related list it should look for parent item as filter and show data ... 

Thanks,
AbdulRahim