Sign Up ›

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

Joining Accounts, Opportunities and Contact data together

Hi,

 

Can someone help me figure out how to get account, contact and opportunity data on a single line record per related group (3)?  To be clear I am not asking for Opportunity Contact Role I am asking for the data at the contact level that is related to the account.  For example if I had the folliwing situation:

 

Account Data:

      Account Name =  A1

            Related Contact Data:          

                First Name: = C1

                First Name = C2

            Related Opportunity Data:

                Opp Name = O1

                Opp Name = O2

                Opp Name = 03

 

Then the record in the flat file would look like this:

Account Name      First Name      Opp Name

A1                C1              O1

A1                C1              O2

A1                C1              03

A1                C2              O1

A1                C2              O2

A1                C2              O3      

 

 

 

 

I started a for loop as shown below but am not sure if this is the correct approach and if it is now I can manage to build the structure to provide the results as shown above.

for ( Account a: [ SELECT Name, (SELECT FirstName, LastName, Email 
                                 FROM Contacts ), (SELECT Name, Field2 
                                                   FROM Opportunities
                                                   WHERE Effective_Date__c >= LAST_N_MONTHS:12 AND Effective_Date__c <= LAST_MONTH) 
                   FROM Account 
                   WHERE Type = 'Customer' LIMIT 10 ] ) {
	
	for ( Contact c: a.Contacts ) {
                           
    }
    // Loop and get the opportunites                   
    for ( Opportunity o: a.Opportunities ) {
                           
    }
                     
                     
}

 

 

 

              

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

The problem then, is that you never defined what would appear when there are no matching rows. The behavior I coded previously would be a full inner join; only accounts with opportunities and contacts would be displayed. To get a full outer join, you need code closer to the following:

 

Account[] accounts = [select id,name,(select id,name,phone from contacts),(select id,name,closedate,amount from opportunities) from account];
string s = '';
for(account ar:accounts) {
    contact[] contacts = ar.contacts.deepclone(true,true,true);
    opportunity[] opportunities = ar.opportunities.deepclone(true,true,true);
	if(ar.contacts.isEmpty()) {
        contacts.add(new Contact(id=null, Firstname=null, Lastname=null, phone=null));
	}
    if(ar.opportunities.isempty()) {
        opportunities.add(new opportunity(id=null, name=null,closedate=null,amount=null));
    }
    for(contact ac:contacts) {
    	for(opportunity ao:opportunities) {
    		s = s + string.join(
                new List<String>{ ar.id, ar.name, ac.id, ac.name, ac.phone, ao.id, ao.name, ao.closedate!=null?ao.closedate.format():null, string.valueof(ao.amount) }, '\t')+'\n';
		}
	}
}
system.debug(logginglevel.error, s);

 

All Answers

sfdcfoxsfdcfox
If you want each opportunity to appear with each contact (note: this is a multiplicative operation, so five contacts with five opportunities is twenty-five rows of data), simply place the opportunity loop inside the contact loop. Note that this has a very high probability for failure with even modest amounts of data.
Dhaval PanchalDhaval Panchal

try below

 

Page:

<apex:page controller="AccountList">
    <apex:form>
        <apex:pageBlock>
            <table cellspacing="0" cellpadding="0" width="100%" height="100%" class="list">
                <tr class="headerRow">
                    <th>
                        Account Name
                    </th>
                    <th>
                        Contact Name
                    </th>
                    <th>
                        Opportunity
                    </th>
                </tr>
                
                <apex:repeat value="{!lstList}" var="lst">
                    <apex:repeat value="{!lst.contacts}" var="con">
                        <apex:repeat value="{!lst.opps}" var="opp">
                            <tr class="dataRow" onmouseover="if (window.hiOn){hiOn(this);}" onmouseout="if (window.hiOff){hiOff(this);}" onfocus="if (window.hiOn){hiOn(this);}" onblur="if (window.hiOff){hiOff(this);}">
                                <td class="dataCell">
                                    {!lst.acc.Name}
                                </td>
                                <td class="dataCell">
                                    {!con.Name}
                                </td>
                                <td class="dataCell">
                                    {!opp.Name}
                                </td>
                            </tr>
                        </apex:repeat>
                    </apex:repeat>
                </apex:repeat>
            </table>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Controller:

public class AccountList {
    Public Class Lists{
        public Account acc{get;set;}
        public List<Contact> contacts{get;set;}
        public List<Opportunity> opps{get;set;}
        public Lists(){
            this.acc = new Account();
            this.contacts = new List<Contact>();
            this.opps = new List<Opportunity>();
        }
    }
    Public List<Lists> lstList{get;set;}
    Public AccountList(){
        lstList = new List<Lists>();
        List<Account> lstAccount = [Select Id, Name, (Select Id, Name from Contacts), 
            (Select Id, Name from Opportunities) From Account];
        if(lstAccount.size()>0){
            for(Account acc:lstAccount){
                Lists lst = new Lists();
                lst.acc = acc;
                if(acc.Contacts.size()>0){
                    lst.contacts = acc.Contacts;
                }
                if(acc.Opportunities.size()>0){
                    lst.opps = acc.Opportunities;
                }
                lstList.add(lst);
            }
        }
    }
}

 
Eager-2-LearnEager-2-Learn

Wow that is pretty slick!

 

I am trying to add several additional fields for each data set (account, contacts, and opportunities) but am having issues with that.  The visualforce page is a cool visual but I really need to just build the results in a variable that I would build as a comma seperated file.

 

If I understand the repeater, it seems to be handling the looping for me but if I was to not use a visualforce page and instead did it all in Apex building a comma delimited file structure how would I do that without hitting govern-limits?

 

 
Eager-2-LearnEager-2-Learn

I am struggling on how to find a pattern that I can code using the FOR LOOP SOQL below so that at the end of the loop process I have a variable that is holding all the fields and data in a comma seperated format.  I took out a lot of the code that I had to try and get that to work but it never showed the same results that I see when I run this SOQL in the Query Editor or Force.com Explorer.

 

Any help would be appreciated.

 

for ( Account a: [ SELECT id, Name, (SELECT FirstName, LastName, Email
                                     FROM Contacts ), (SELECT Field1, Field2
                                                       FROM Opportunities
                                                       WHERE Effective_Date__c >= LAST_N_DAYS:365
                                                       AND Effective_Date__c <= TODAY)
                   FROM Account
                   WHERE Type = 'Customer' LIMIT 10 ] ) {
    // Loop through the Conacts           
    for ( Contact c: a.Contacts ) { 


        // Loop through the Opportunities
        for ( Opportunity o: a.Opportunities ) {
    
        }                           
    }                    
    
}

 

 
sfdcfoxsfdcfox

See this simple example:

 

Account[] accounts = [select id,name,(select id,name,phone from contacts),(select id,name,closedate,amount from opportunities) from account];
string s = '';
for(account ar:accounts)
    for(contact ac:ar.contacts)
    	for(opportunity ao:ar.opportunities)
    		s = s + string.join(
                new List<String>{ ar.id, ar.name, ac.id, ac.name, ac.phone, ao.id, ao.name, ao.closedate.format(), string.valueof(ao.amount) }, '\t')+'\n';
system.debug(s);

This code works in my dev org, except with some filters, of course. Please feel free to steal this code and use it.
Eager-2-LearnEager-2-Learn

It still doesn't seem to give the results of all the records -- it only returns 1 record! 

 

I just discovered and thought I saw something like this with my previous code but I never thought much of it...


When I remove the '\n' at the end of the building of the string then I get a long string int he debug display which looks like all the records or at least many many more.  It seems like the line feed is not working or something.   Any ideas?

 

String recs = '';
for ( Account a: [ SELECT id, Name, (SELECT FirstName, LastName, Email
                                     FROM Contacts ), (SELECT Field1, Field2
                                                       FROM Opportunities
                                                       WHERE Effective_Date__c >= LAST_N_DAYS:365 
                                                       AND Effective_Date__c <= TODAY                                                       
                                                       AND Account.Type = 'Customer'
                                                       ))
                   FROM Account 
                   WHERE Type = 'Customer' LIMIT 50 ] ) {
    // Loop through the Contacts           
    for ( Contact c: a.Contacts ) {
        for ( Opportunity o: a.Opportunities ) {
            recs = recs + String.join(new List<String>{ a.id, a.Name, c.FirstName, c.LastName, c.Email, 
                                                        Field1, Field2 }, '\t') + '\n';
        }                           
    }    
}
System.debug('zz: ' + recs);

 

 

 
sfdcfoxsfdcfox
The log ends up placing each record on a separate line. Look at the raw log to make sure you're seeing the full list.
Eager-2-LearnEager-2-Learn

That is a stragne behavior because I have always used 'zz: ' in my debug statement and then type 'zz:' in the filter to reveal x-number of lines of System.debug statements that occured in a loop.  However, I did check the debug checbox on the developer console and was able to see the list.  Thank you.

 

With that said, I realize now that this still is not the solution that I need.  The probelm with putting the opportunity loop inside the contact loop is that it relize on an opportunity to exist in order to build the list!  The opportunity and contact are not hierarchical to each other but rather siblings!

 

I tried putting the contact for loop and opp for loop inside the account for loop as I originally had it and then used various boolean variables to detect if the contact loop occured or the opp loop occured and then build the list based on that but I am not having much luck.

 

The bottom line is that I need a list that shows all contacts and opportunities (the chosen fields on each of those objects) that match a given account to be as a single record were the data is duplicating itself and seperate lines where it is not as shown below.

 

My Curernt work around is that i am exporting the data out into two separate files (account/contact and account/opportunity) then joining the data in an MS Access database; however, that is not a final solution as it needs to be a single file sent to the user without the manual effort! :(

 

 

 

For example:

Account Data&colon;

      Account Name =  A1

            Related Contact Data&colon;          

                First Name: = C1

                First Name = C2

            Related Opportunity Data&colon;

                Opp Name = O1

                Opp Name = O2

                Opp Name = 03

 

Then the record in the flat file would look like this:

Account Name      First Name      Opp Name

A1                C1              O1

A1                C1              O2

A1                C1              03

A1                C2              O1

A1                C2              O2

A1                C2              O3      
sfdcfoxsfdcfox

The problem then, is that you never defined what would appear when there are no matching rows. The behavior I coded previously would be a full inner join; only accounts with opportunities and contacts would be displayed. To get a full outer join, you need code closer to the following:

 

Account[] accounts = [select id,name,(select id,name,phone from contacts),(select id,name,closedate,amount from opportunities) from account];
string s = '';
for(account ar:accounts) {
    contact[] contacts = ar.contacts.deepclone(true,true,true);
    opportunity[] opportunities = ar.opportunities.deepclone(true,true,true);
	if(ar.contacts.isEmpty()) {
        contacts.add(new Contact(id=null, Firstname=null, Lastname=null, phone=null));
	}
    if(ar.opportunities.isempty()) {
        opportunities.add(new opportunity(id=null, name=null,closedate=null,amount=null));
    }
    for(contact ac:contacts) {
    	for(opportunity ao:opportunities) {
    		s = s + string.join(
                new List<String>{ ar.id, ar.name, ac.id, ac.name, ac.phone, ao.id, ao.name, ao.closedate!=null?ao.closedate.format():null, string.valueof(ao.amount) }, '\t')+'\n';
		}
	}
}
system.debug(logginglevel.error, s);

 

This was selected as the best answer
Eager-2-LearnEager-2-Learn

Thank you very much for your assistance.