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
Jeanette Shown 5Jeanette Shown 5 

Desparate for a solution to relating to unrelated objects to each other

Hello All,
I have two unrelated objects, Enrollment History and Test that are connected to Contact.  However when creating a custom report,
I can choose either contact and EH or Test but not both, trying to use either of these with contact and use a third object only leads me to the Activities object which is a dead end.  I have tried using the Applications object as the main object, and again I can have EH or TESTS but these again lead to the Activities object dead end.

If I start with EH or TEST, my only choice after this is Activities, which is a dead end.

I have tried a junction box between these two objects but to no avail.  Or, I'm creating the box incorrectly.    Can I use a SOQL formula to bring the information I need over into the report.  Or if anyone could shed light on creating a junction box for this dilemma, I would appreciate it.

Thank you.

 
Jeanette Shown 5Jeanette Shown 5
I have created the following junction box...   any comments would be appreciated!
User-added image
rivereridanusrivereridanus
Hi Jeanette,  from your schema diagram it looks like you might be having trouble because of the many to many relationships between your three objects. Is it correct to assume that 1 contact can have multiple tests and multiple enrollment histories? If that is the case then you may not be able to get all that info in one report with the native SF reporting tool.   I've had a similar issue recently where I had to create a report that related a contact to an account to an opportunity and include all opportunity line items, as well as dependents (a related object to Contact) in the same report with many to many relationships. I ended up writing a custom class and controller and had to do the report in visualforce, but it gave me what I needed. If that's the path you need to go, then let me know if you want some code to work with. Good luck!
Jeanette Shown 5Jeanette Shown 5
I think you are right Rivereridanus.  Here is another screen shot and I am not pulling any data for the report at all.   If you don't mind sharing your code, it would be much appreciated. Enrollment History and Test Report with junction
rivereridanusrivereridanus
Sure thing. I was also helped a lot by reading this blog post: http://appirio.com/category/tech-blog/2008/11/learning-apex-display-multi-object-data-in-tables-easily-with-apex-dummy-classes/
  
public class DiaperReportController 
{ 
   public List<DiaperReportDummy> results {get;set;}

   public DiaperReportController()
   {
       results = getResults();
   }

   public DiaperReportDummy[] getResults() 
   { 
      results = new List<DiaperReportDummy>();
      
      Date d = System.today() - 60;
      
      //query for all the data 
      Contact[] allContacts = [select Name, Phone, OtherPhone, Contact_Info_Notes__c from Contact where (Diapers_Needed__c = True or Num_Diapers_Dependents__c > 0) order by MailingPostalCode];
      Dependent__c[] allDependents =  [select Contact__c, Name, Diapers__c from Dependent__c where (Contact__r.Diapers_Needed__c = True or Contact__r.Num_Diapers_Dependents__c > 0)]; 
      Opportunity[] allOpptys = [select Account.Id, CloseDate, Amount, Description, Name, RecordTypeId from Opportunity where Is_Last_Donation__c = True and Contact_Is_Ongoing_Diapers__c = True and RecordTypeId = '012i0000000RGFh'];
      OpportunityLineItem[] allOLIs = [select Opportunity.Id, Description, Product2.Name, Quantity, TotalPrice from OpportunityLineItem where Opportunity.Is_Last_Donation__c = True and Opportunity.Contact_Is_Ongoing_Diapers__c = True and Opportunity.RecordTypeId = '012i0000000RGFh'];
      
      //find all the related data and put into dummy class 
      //loop through each contact in the query results
      for(Contact c: allContacts) 
      {
      Dependent__c[] lstDeps = new List<Dependent__c>();
      OpportunityLineItem[] lstOLIs = new List<OpportunityLineItem>();
      Opportunity lastopty;
      
         //get the related Dependents 
         for(Dependent__c dep: allDependents) 
         { 
           if(dep.Contact__c == c.id) 
            { 
               
               lstDeps.add(dep);    
            } 
         }
         //get the most recent sent donation
         for(Opportunity opp: allOpptys)
         {
             if (opp.AccountId == c.AccountId)
             {
                 lastopty = opp;
                 break;
             }
             else
             {
                 lastopty = null;
             }
         }
         //get the products of the most recent sent donation- if there is one
         if (!(lastopty == null))
         {
             for(OpportunityLineItem oli: allOLIs)
             {
                 if (oli.OpportunityId == lastopty.Id)
                 {
                     lstOLIs.add(oli);
                 }
             }
         }
         
         //create the Dummy class and add to the result list 
         results.add(new DiaperReportDummy(c, lstDeps, lastopty, lstOLIs)); 
         
      } 
      return results; 
   }
   
   //Inner class to hold relevant details for each Contact
   class DiaperReportDummy 
   { 
       public Contact contact { get; set; }  
       public Dependent__c[] dependents { get; set;} 
       public Opportunity opportunity {get;set;}
       public OpportunityLineItem[] opptylineitems {get;set;}
       
       public DiaperReportDummy(Contact con1, Dependent__c[] dep1, Opportunity opp1, OpportunityLineItem[] oli1) 
       { 
          this.contact = con1; 
          this.dependents = dep1;
          this.opportunity = opp1; 
          this.opptylineitems = oli1;
       } 
   }
}



And a visualforce page "report" that looks like this:
  
<apex:page controller="DiaperReportController" title="Diaper Report" showHeader="false">
<style>
.page-break {
        page-break-before:always;
    }
.page-break:first-child {
         page-break-before: avoid;
    }

@page {
        size: A4;
        @bottom-center {
            content: "Page " counter(page) " of " counter(pages);
        }
    }
</style>
  <apex:pageBlock title="Diaper Report">
      <apex:repeat value="{!results}" var="result">
      <div class="page-break" >
          <apex:pageBlockSection title="Contact" columns="1">
              <apex:pageBlockSection columns="2">
              <apex:outputText escape="false" value="<b>Contact Information</b>" style="sample"/>
              <apex:outputText escape="false" style="sample"/> 
              <apex:outputField title="Name" value="{!result.contact.Name}"/>
              <apex:outputField title="Primary Phone" value="{!result.contact.Phone}"/>
              <apex:outputField title="Diapers" value="{!result.contact.Diapers_Needed__c}"/>
              <apex:outputField title="Other Phone" value="{!result.contact.OtherPhone}"/>
              <apex:outputField title="Diaper Size" value="{!result.contact.Diaper_Size__c}"/>
              <apex:outputField title="Description" value="{!result.contact.Description}"/>
              <apex:outputField title="Diaper Date" value="{!result.contact.Diaper_Size_Date__c}"/>
              <apex:outputField title="Contact Info Notes" value="{!result.contact.Contact_Info_Notes__c}"/>
              </apex:pageBlockSection>
              <apex:outputText escape="false" value="<b>Mailing Address</b>" />
              <apex:pageBlockTable value="{!result.contact}" var="contact" title="Mailing Address">
                  <apex:column headerValue="Mailing Address Type" value="{!contact.npe01__Primary_Address_Type__c}" />
                  <apex:column headerValue="Mailing Street" value="{!contact.MailingStreet}" />
                  <apex:column headerValue="Mailing City" value="{!contact.MailingCity}" />
                  <apex:column headerValue="State" value="{!contact.MailingState}" />
                  <apex:column headerValue="ZIP" value="{!contact.MailingPostalCode}" />
              </apex:pageBlockTable>
              <apex:pageBlockTable value="{!result.contact}" var="contact" title="Other Address" rendered="{!result.contact.OtherStreet != null}">
                  <apex:column headerValue="Other Address Type" value="{!contact.npe01__Secondary_Address_Type__c}" />
                  <apex:column headerValue="Other Street" value="{!contact.OtherStreet}" />
                  <apex:column headerValue="Other City" value="{!contact.OtherCity}" />
                  <apex:column headerValue="State" value="{!contact.OtherState}" />
                  <apex:column headerValue="ZIP" value="{!contact.OtherPostalCode}" />
              </apex:pageBlockTable>
              <apex:outputText escape="false" value="<b>Dependent Information</b>" rendered="{!result.dependents.size>0}" />
              <apex:outputText escape="false" value="<b>No Dependents Exist</b>" rendered="{!result.dependents.size=0}" />
                  <apex:pageBlockTable value="{!result.dependents}" var="dep" title="Dependents" rendered="{!result.dependents.size>0}">
                      <apex:column headerValue="Name" value="{!dep.Name}" />
                      <apex:column headerValue="Gender" value="{!dep.Gender__c}" />
                      <apex:column headerValue="Age" value="{!dep.Age__c}" />
                      <apex:column headerValue="Diapers" value="{!dep.Diapers__c}" />
                      <apex:column headerValue="Diaper Size" value="{!dep.Diaper_Size__c}" />
                      <apex:column headerValue="Diaper Date" value="{!dep.Diaper_Size_Date__c}" />
                  </apex:pageBlockTable>
              <apex:outputText escape="false" value="<b>Last Donation Sent</b>" rendered="{!result.opportunity.Id != null}"/>
              <apex:outputText escape="false" value="<b>No Donations Have Been Sent</b>" rendered="{!result.opportunity.Id == null}"/>
              <apex:outputField title="Donation Name" value="{!result.opportunity.Name}" rendered="{!result.opportunity.Id != null}"/>
              <apex:outputField title="Amount" value="{!result.opportunity.Amount}" rendered="{!result.opportunity.Id != null}"/>
              <apex:outputField title="Close Date" value="{!result.opportunity.CloseDate}" rendered="{!result.opportunity.Id != null}"/>
              <apex:outputField title="Description" value="{!result.opportunity.Description}" rendered="{!result.opportunity.Id != null}"/>
              <apex:outputText value="Donation Products" rendered="{!result.opportunity.Id != null}"/>
              <apex:pageBlockTable value="{!result.opptylineitems}" var="oli" title="Donation Products" rendered="{!result.opportunity.Id != null}">
                  <apex:column headerValue="Product Name" value="{!oli.Product2.Name}" />
                  <apex:column headerValue="Description" value="{!oli.Description}" />
                  <apex:column headerValue="Quantity" value="{!oli.Quantity}" />
                  <apex:column headerValue="TotalPrice" value="{!oli.TotalPrice}" />
              </apex:pageBlockTable>
          </apex:pageBlockSection>
      </div>
      </apex:repeat>
  </apex:pageBlock>
</apex:page>


Let me know if you have trouble implementing it. Best of luck!
rivereridanusrivereridanus
What you get out of all that is a report that looks like this for each contact: User-added image