function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
rrubin128rrubin128 

Custom object and missing records

How do I create a report to show missing records?  Here's the situation:

I have a custom object with a Master-Detail relation to Accounts.  The object also has a Lookup relationship to Opportunities.

 

I want to find all Closed Opportunities that do not have at least one of the custom objects associated with it.

 

I have created a Custom Report Type for "Accounts with Opportunities with or without related custom object."  So how to I report on Opportunities "without" the custom object?

 

Thanks.

Robert 

JimRaeJimRae
What about putting a filter on your report that is where the name = "" or posibly the custom object id field if you exposed that in your CRT?
rrubin128rrubin128

Thanks.  Tried that already.

 

When creating a custom Report Type, I thought that "with or without related records" translated to "outer join" in SQL.  In that case, your suggestion should have worked.  But when I add such a filter, I still get all of the Accounts and Opportunities (just as the report definition says, literally) "without" any custom objects...because none of the custom objects have IDs = "".  Drat.
JimRaeJimRae

Did you actually put the quotes into the field?

I just tried this again, and it works for me.

I created an Accounts with Opportunities with/without custom object report

 

select the account name, the opportunity name and the custom object name

and set a filter of custom object id equals blank

What this means is I left the criteria field blank.

What I got as a result was all of the accounts with Opportunities (one or more) that did not have my custom object related.

 

Works for me, sorry you are having difficulty.

rrubin128rrubin128

Are you able to display *only* those Opportunities without custom objects?  I can get all Opportunities with or without custom objects, but not just the ones without.

 

This is frustrating: Salesforce support says that what I want to do, "Exception Reporting", is not supported.  Then again the initial response was "Custom Report Types are not supported in Professional Edition."  And that is wrong.