database view

[anvitha ash]

Hi,

I am creating database view between 3 tables, reason is we have some custom fields in "cmdb_ci_printer" and cmdb_ci_lb" table. we want to create reports

• cmdb_ci
• cmdb_ci_lb
• cmdb_ci_printer

It is only displaying the records of the table which has "higher order value"

ex: in screenshot cmdb_ci_printer has order 160, it is displaying only that table records

And if i change the order of cmdb_ci_lb table to  170, then it is only displaying that table records

I want all the tables records combined together in the database table view. how to achieve this functionality

Thanks in advance😊

[Brad Bowman]

This is not a valid use of a database view - to join one or more tables together.  When there are three tables in a view, you need to join the second (order) to the first, and the third to the second - otherwise there's no commonality in records (between lp and printer in this case) so it can't display one row for every joined record.  These are two separate tables with nothing in common other than they both are extended from the cmdb_ci table.  The other odd thing about this approach is that since lb and printer are extended from cmdb_ci, they already have all of the fields on the cmdb_ci table, so joining the extended table to the table it is extended from is not valid. 

I'll have a think on a way to report on 2 extended cmdb_ci tables that will allow you to include fields specific to those tables, but nothing is coming to mind right away. 

[Brad Bowman]

This is crazy enough that it looks like it works.  First configure your database view more like this:

Next, create a Business Rule using the db view name as the Table, and When to run = before Query.  Use this in the Script field on the Advanced tab:

(function executeRule(current, previous /*null when async*/) {
	current.addEncodedQuery('ci_sys_class_name=cmdb_ci_lb^ORci_sys_class_name=cmdb_ci_printer');
})(current, previous);

Now a list view of your db view results, and I believe a report on the 'table' as well, will only show the LBs and Printers, with access to every field on both tables.

[abrouf]

You need to create a relationship between the 1st and 2nd table and then the 2nd table with the 3rd table, I see your first table's relationship is missing. For further info, please follow this community link:

https://www.servicenow.com/community/developer-forum/how-to-create-a-database-view-for-three-tables/...