Issue joining Story table to Defect and/or Enhancement table via Database View.

[ryangordon]

Currently our process flow is to create a Defect or Enhancement and then it would become a Story.   Once it's a story, it will then be part of our planning sessions for development.

I'm trying to create a report that identifies those defects or enhancements that do NOT have a corresponding story created for them.   That way I can ensure all defects or enhancements are accounted for in our planning sessions as stories.

I'm trying to use a Database View to join the Story and Defect table but I'm not certain how to join them properly.   The Defects and Enhancements are listed on the story page and are joined already but I'm unable to do it as part of the view.   Based on the screenshot, it will show all the fields from the Defect table (prefix of d) and Story table (prefix of s).   As you'll see, you'll also see the Defect and Enhancement field as options but they don't contain a prefix so I'm not sure how to create this view.

Any help would be greatly appreciated!

[Arnoud Kooi · Accepted solution]

The Defect and Story table both extend the Featuture table, so they have the most columns in common.

All the columns that are in both tables show the tablename between brackets. The fields in only one table dont contain the tablename in the column list

You are missing a where clause in the rm_story table

It should be s_defect = d_sys_id Also this table should be Left join: true

Then after you hit Try It, set a filter to Sys ID (s_sys_id) Is Null

This way you filter only the Defects that dont have a Story linked.

Also if you have list v3 enabled (Helsinki or newer) you could use a related list condition and dont need a DB join:

[ryangordon]

Thank you so much arnoud!   This was very helpful.   I couldn't seem to find that in the documentation anywhere so thank you so much for the support.