Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Eager-2-LearnEager-2-Learn 

Opportunities without Opportunity Partner related records?

Hi,

 

I have been charged with creating a report that displays only opportunities that do not have opportunity partner related records; however, I do not see the Opportunity Partner related table as an option when creating custom report types and I do not see how custom report types allow you to create a type that returns only records in the parent that have no children?

 

Is there a way to do this in a simple report?  If it is not then how can I create a SOQL that can do it?  If I have a SOQL that can do it I can use it in code to populate a custom table then use that custom table in a report?

 

Any ideas?

Best Answer chosen by Admin (Salesforce Developers) 
SurekaSureka

Hi,

 

You can create a checkbox feild in the Opportunity and make it checked(with the help of Apex), if the Opportunity has partners.

 

Then you can run the report based on this checkbox ( your criteria will be checkbox is unchecked)

 

Hope this solves. If so, Please mark the solution as solved.

 

Thanks

All Answers

SurekaSureka

Hi,

 

You can create a checkbox feild in the Opportunity and make it checked(with the help of Apex), if the Opportunity has partners.

 

Then you can run the report based on this checkbox ( your criteria will be checkbox is unchecked)

 

Hope this solves. If so, Please mark the solution as solved.

 

Thanks

This was selected as the best answer
QuiqueprQuiquepr

you can also use a partner opportunity report with a select criteria of partner = null and that should do the trick without apex....
Eager-2-LearnEager-2-Learn

Hi Quiquepr,


Your solution is not clear to me!  Can you please elaborate on how you would use an Opportunity with Partner Repor type  (Standard) to do this?  Or do you mean create custom Report Type of Opportunity with or without Partner and use that in my report and set the partner to null?  I believe I tried this months ago and the results were unexpected!

 

If you could give more detail on your approach I would be grateful.

 
mtbclimbermtbclimber

The reporting engine doesn't support exception reports (currently) and thus the standard "Opportunities WITH partners" report can not be coerced into "Opportunities withOUT partners".

 

Here's a jump start on some Apex with a Visualforce page:

 

CONTROLLER:

 

 

public with sharing class OpportunitiesWithoutPartners {

    public List<Opportunity> getOpportunities() {
        return new List<Opportunity>([SELECT Name 
                                        FROM Opportunity
                                       WHERE Id NOT IN (SELECT OpportunityId 
                                                          FROM OpportunityPartner)]);
    }
}

 

 

PAGE:

 

<apex:page controller="OpportunitiesWithoutPartners" tabStyle="Opportunity">
    <apex:sectionHeader title="Opportunities Without Partners"/>
    <apex:pageBlock>
        <apex:pageBlockTable value="{!opportunities}" var="opp">
            <apex:column headerValue="{!$ObjectType.Opportunity.Label}">
                <apex:outputLink value="{!URLFOR($Action.Opportunity.View, opp.id)}">{!opp.name}</apex:outputLink>
            </apex:column>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

 

 

 

 
Eager-2-LearnEager-2-Learn

This may be another great way to go; however, I tried the code including a critera to only show the last 365 days of opportunities and I received the following error message when trying to access the page.

 

Collection size 1,201 exceeds maximum size of 1,000.

Any ideas how to work around this limitation?
Eager-2-LearnEager-2-Learn

I wanted to say thank you for this solution.  It is ironic that you sent it on 08/24/10 and I just put the solution that was previously suggested in to production that day.  I never liked the solution of putting a custom checkbox on the opportunity and the process checking that field if partners do not exist for that opportunity because it modifies the record with my name (I am the job submitter) and the date too.  I had to create another custom text field and combine the oringal last modified by person and date/time so we know who last changed the record from the screen.

 

Long story short, I am keeping the code you submitted for future use and as starter snippet; however, it would be nice to know how to get passed the limitation of 1200 rows. Like you haven't helped enough. LOL
mtbclimbermtbclimber

You're welcome.

 

If you need to present users with this information in a usable manner, you'll need to present them with a paginated interface to the result set. Fortunately we have support for this with the StandardSetController class. You can read more about that in the Visualforce developer guide but to get you started I've carved out the following modified example:

 

CONTROLLER:

 

public with sharing class OpportunitiesWithoutPartners {

    ApexPages.StandardSetController setCon;
   
    public OpportunitiesWithoutPartners() {
        Database.QueryLocator ql = Database.getQueryLocator([SELECT Name 
                                                               FROM Opportunity
                                                              WHERE Id NOT IN (SELECT OpportunityId 
                                                               FROM OpportunityPartner)]);
        setCon = new ApexPages.StandardSetController(ql);
    }
    
    public void next() {
        setCon.next();
    }
    public List<Opportunity> getOpportunities() {
        return (List<Opportunity>)setCon.getRecords();
    }
}

PAGE:

 

<apex:page controller="OpportunitiesWithoutPartners" tabStyle="Opportunity">
    <apex:sectionHeader title="Opportunities Without Partners"/>
    <apex:form>
        <apex:pageBlock >
            <apex:pageBlockTable value="{!opportunities}" var="opp">
                <apex:column headerValue="{!$ObjectType.Opportunity.Label}">
                    <apex:outputLink value="{!URLFOR($Action.Opportunity.View, opp.id)}">{!opp.name}</apex:outputLink>
                </apex:column>
                <apex:facet name="footer">
                    <apex:commandLink action="{!next}" value="Next"/>
                </apex:facet>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

 

 

I leave it to you to add previous, page numbers, etc. :smileyhappy:

 

Hope that helps.

 
Eager-2-LearnEager-2-Learn

I appreciate the hints and the nudges but this grasshopper needs another nudge! :smileyvery-happy:

 

I am new to SFDC and steadily learning but VF pages are the newest ground.

 

This is what I have so far but I am thinking I need code behind those actions?  Not how to get that going.  I will keep looking but how is it looking so far?

 

<apex:page controller="OppWithoutPartners_controller" tabStyle="Opportunity" >
    <apex:sectionHeader title="Opportunities" subtitle="Without Partners"
                        description="working on showing opportunities without partners"/>
    <apex:form >
    <apex:pageBlock >
        <apex:pageBlockTable value="{!opportunities}" var="opp">
            <apex:column headerValue="Owner">
                <apex:outputField value="{!opp.Owner.Name}"/>
            </apex:column>
            <apex:column headerValue="{!$ObjectType.Opportunity.Label}">                
                <apex:outputLink value="{!URLFOR($Action.Opportunity.View, opp.id)}">{!opp.name}</apex:outputLink>
            </apex:column>            
            <apex:column headerValue="Effective Date">
                <apex:outputField value="{!opp.Effective_Date__c}"/>
            </apex:column>
        </apex:pageBlockTable>
            <apex:panelGrid columns="2"> 
            <apex:commandLink action="{!previous}">Previous</apex:commandlink> 
        <apex:commandLink action="{!next}">Next</apex:commandlink> 
        </apex:panelGrid> 
    </apex:pageBlock>
    </apex:form>
</apex:page>

 

 

 

 
mtbclimbermtbclimber

Trying to teach you how to fish here, your username being "Eager-2-Learn" tells me you are up for it :smileywink:

 

I've changed the sample a bit to make things easier (and demonstrate better ways of doing a couple things). I leave the rest to you. My suggestion for where to start is to look at the documentation for the ApexPages.StandardSetController class in the Visualforce developer guide.

 

CONTROLLER:

 

public with sharing class OpportunitiesWithoutPartners {

    public ApexPages.StandardSetController setCon { get; set; } 
   
    public OpportunitiesWithoutPartners() {
        Database.QueryLocator ql = Database.getQueryLocator([SELECT Name, Amount 
                                                               FROM Opportunity
                                                              WHERE Id NOT IN (SELECT OpportunityId 
                                                               FROM OpportunityPartner)]);
        setCon = new ApexPages.StandardSetController(ql);
    }
    
    public List<Opportunity> getOpportunities() {
        return (List<Opportunity>)setCon.getRecords();
    }
}

 

 

PAGE:

 

 

<apex:page controller="OpportunitiesWithoutPartners" tabStyle="Opportunity">
    <apex:sectionHeader title="Opportunities Without Partners"/>
    <apex:form >
        <apex:pageBlock >
            <apex:pageBlockTable value="{!opportunities}" var="opp">
                <apex:column headerValue="{!$ObjectType.Opportunity.Fields.Name.Label}">
                    <apex:outputLink value="{!URLFOR($Action.Opportunity.View, opp.id)}">{!opp.name}</apex:outputLink>
                </apex:column>
                <apex:column value="{!opp.amount}"/>
                <apex:facet name="footer">
                    <apex:commandLink action="{!setcon.next}" value="Next"/>
                </apex:facet>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

 

 
Eager-2-LearnEager-2-Learn

I have it working but I am better that there is a better option at the footer than using the panelGrid?  In addition, it would be a great if I could get the View combo box to appear at the header area so the user can view all opp, my opps, etc.  Is this possible with Visual Force?  I have not found anything in the header element that would do this!

 

<apex:page controller="OpportunityWithoutPartners_controller" tabStyle="Opportunity">
    <apex:sectionHeader title="Opportunities" subtitle="Opportunities Without Partners"/>
    <apex:form >
        <apex:pageBlock >
            <apex:pageBlockTable value="{!opportunities}" var="opp">
                <apex:column headerValue="Owner">
                    <apex:outputField value="{!opp.Owner.Name}"/>
                </apex:column>
                <apex:column headerValue="{!$ObjectType.Opportunity.Label}">                
                    <apex:outputLink value="{!URLFOR($Action.Opportunity.View, opp.id)}">{!opp.name}</apex:outputLink>
                </apex:column>            
                <apex:column headerValue="Effective Date">
                    <apex:outputField value="{!opp.Effective_Date__c}"/>
                </apex:column>  
                <apex:facet name="footer">
                    <apex:panelGrid columns="2">  
                        <apex:commandLink action="{!setcon.next}" value="Next"/>
                        <apex:commandLink action="{!setcon.previous}" value="Previous"/>
                    </apex:panelGrid>
                </apex:facet>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

 

 

I truly appreciate your help.
Andreas GerogiannisAndreas Gerogiannis
I know this is an old post, but if anyone else look at it ... if you add 

<apex:page controller="OpportunityWithoutPartners_controller" tabStyle="Opportunity" readonly="true">
it goes up to 10000 records on a page.