How to create a DB view between sc_request and sc_req_item?

[alfredrodriguez]

Newbie question here, and frankly have tried the entire day to figure this out...

As we know that in ServiceNow, a REQUEST can have multiple REQUEST ITEMS. In our instance, these tables would be sc_request and sc_req_items.

Checking the Show List for each table, I created this view:

But nothing is returned.

This is what I've been struggling for so long. I'm a fairly experienced DB developer, but, for some reason, can't find the best way to JOIN at least TWO tables in ServiceNow. Am I missing something here?

For the above, I've tried checking the table and column definitions and I'm fairly sure that sc_request.number = sc_req_item.order.

Thanks in advanced.

[sergiu_panaite]

Hi Alfred,

1. The "request" column in sc_req_item is showing to which Request it actually belongs to. So, not "order" column. You can actually see in System Definition -> Dictionary -> table: sc_req_item that column "request" is a reference to "sc_request".

2. Did you choose in your view which fields to be displayed per table?   For the Where clause this is mandatory as explained on our wiki page:

http://wiki.servicenow.com/index.php?title=Database_Views#Task_3:_Specify_Fields_to_Return

Let me know if this helped.

Regards,

Sergiu

[alfredrodriguez]

Thanks Sergiu -

That is how it would be in a default instance; but, unfortunately, in our instance, there is no "Request" label, but an "Order" label that references an "Order" table (not a "Request" table), though having name as "sc_request."

But though, your reply made me thinking. Let me try something else...

As for your second point, if we haven't set anything, shouldn't it show all fields?

[gflewis]

If you are new to ServiceNow, but an experienced DB developer, then I would advise you to avoid ServiceNow views until you are more experienced.   ServiceNow views are not like database views.   They can be tricky to create, and the performance is not great.   If general they are useful only if you need to do an outer join, or if you need to join multiple times to the same table within a single report.

The trick to ServiceNow reports is to always start at the bottom of the hierarchy and work your way up.   If you want to report on Requests and Requested Items, then simply create a report on the Requested Item table (because it is at the bottom) and drill through the Request to pick up fields from the parent table.

In your example, the only reason for a view would be if you needed a report of Requests which have no Requested Items.   In SQL this would require either a NOT EXISTS clause or an outer join. In ServiceNow it can only be done using a database view.

[alfredrodriguez]

Nice insight on that trick. Let me incorporate that in my current assignment.

Thanks Giles.