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
Sylvie SerpletSylvie Serplet 

Comparing fields in two different objects in a SOQL

Hi All,
I am trying to retrieve a list of records for a custom object (Fact_Finder__c) which has the same Account name as the Opportunity in where the list is displayed (in a Ligthning Component). There is a master detail relationship between the Opportunity (parent) and the Fact Finder (child).
My current code (below) retrieve records for this particular Opportunity but what I want to achieve is retrieving all the Fact Finders with the same Account name in all Opportunities.

public with sharing class FFsamePolicysameClient {
   
    @AuraEnabled     
    public static List<Fact_Finder__c> getallFF(String recordId){ 
       List <Fact_Finder__c> FF = [SELECT Id, Name, RecordType.Name, Date__c, Client_Name__r.Name, Policy__r.Name, Opportunity_Name__r.Name FROM Fact_Finder__c Where (Opportunity_Name__c =:recordId) ORDER BY Date__c DESC];
      return FF;        
    }
}

 I have tried many different ways but nothing seams to work.
Any help will be greatly appreciated.
Thanks in advance.
Sylvie
 
Best Answer chosen by Sylvie Serplet
Waqar Hussain SFWaqar Hussain SF
Hi Sylvie,

See tht updated apex controller.
  
public with sharing class FFsamePolicysameClient {
   
    @AuraEnabled      
    public static List<Fact_Finder__c> getallFF(String recordId){ 
        list<Opportunity> Opps = new list<Opportuniyt>();
        Opps = [Select AccountId from Opportunity where ID= :recordId limit 1];
        
        Id AccountId;
        if(Opps.size() > 0)
        AccountId = Opps[0].AccountId;
        
        
       List <Fact_Finder__c> FF = new List<Fact_Finder__c>();
	   FF = [SELECT Id, Name, RecordType.Name, Date__c, Opportunity_Name__c, Client_Name__r.Name, Policy__r.Name, Opportunity_Name__r.Name FROM Fact_Finder__c Where Client_Name__c  =:AccountId ORDER BY Date__c DESC];
      return FF;         
    }
}

And for showing list of Facts and Finder on Lightning component, please follow the following article, which will help you alot. 

https://developer.salesforce.com/docs/atlas.en-us.lightning.meta/lightning/events_one_demo_load.htm

Thanks 

All Answers

Waqar Hussain SFWaqar Hussain SF
Hi Sylvie,

What I have understood is that you want to retrieve all Fact Finders that are related to opportunity's Account. Correct me if I am wrong.

Please try below code
  
public with sharing class FFsamePolicysameClient {
   
    @AuraEnabled      
    public static List<Fact_Finder__c> getallFF(String recordId){ 
        list<Opportunity> Opps = new list<Opportuniyt>();
        Opps = [Select AccountId from Opportunity where ID= :recordId limit 1];
        
        Id AccountId;
        if(Opps.size() > 0)
        AccountId = Opps[0].AccountId;
        
        set<Id> OppIds = new set<Id>();
        
        list<Opportunity> AccOpps = new list<Opportunity>();
        AccOpps = [Select Id from Opportunity where AccountId = :AccountId];
        for(Opportunity opp : AccOpps){
            OppIds.add(opp.Id);
        }
        
       List <Fact_Finder__c> FF = [SELECT Id, Name, RecordType.Name, Date__c, Client_Name__r.Name, Policy__r.Name, Opportunity_Name__r.Name FROM Fact_Finder__c Where Opportunity_Name__c IN:OppIds ORDER BY Date__c DESC];
      return FF;         
    }
}

Thanks

 
Sylvie SerpletSylvie Serplet
In Fact Finder the Account name field is Client_Name__c and it is a lookup into Account.

And yes, I try to retrieve all Fact Finders that are related to opportunity's Account.

I got the following error message :Unknown controller action 'getallFF'

My Lightning component is as follow:
Component 
<aura:component controller="FFsamePolicysameClient" implements="flexipage:availableForAllPageTypes,flexipage:availableForRecordHome,force:hasRecordId" access="global" >

    <aura:attribute name="factfinder" type="List" />
    <aura:attribute name="factFinderList" type="String" />
    
    <aura:attribute name="opportunity" type="Object" />
    <aura:attribute name="opp" type="Object"/>

        <force:recordData aura:id="oppRecordLoader"
                      recordId="{!v.recordId}"
                      layoutType="FULL"
                      targetRecord="{!v.opportunity}"
                      targetFields="{!v.opp}"    
                      />

       <aura:handler name="init" value="{!this}" action="{!c.doInit}" />
    
        <lightning:card iconName="custom:custom99" title="Fact Finders">  
       
        <div class="slds-card__body">   	
            <div class="slds-scrollable" >
                <table class="slds-table slds-table_bordered slds-table_cell-buffer" >
                    <thead>
                        <tr class="slds-text-heading--label">	
                            <th class="slds-is-sortable" scope="col">
                                <div class="slds-truncate">Fact Finder No.</div>
                            </th>     
                            <th class="slds-is-sortable" scope="col">
                                <div class="slds-truncate">Record Type</div>
                            </th>
                            <th class="slds-is-sortable" scope="col">
                                <div class="slds-truncate">Date</div>
                            </th>
                            <th class="slds-is-sortable" scope="col">
                                <div class="slds-truncate">Client Name</div>
                            </th>
                            <th class="slds-is-sortable" scope="col">
                                <div class="slds-truncate">Policy Name</div>
                            </th>    
                        </tr>
                    </thead>
                    <tbody>
                        <aura:iteration items="{!v.factfinder}" var="FF">
                            <tr class="slds-hint-parent"> 	
                                <td class="slds-truncate" scope="row" data-label="Fact Finder No.">
                                     <a href="{!'/lightning/r/'+ FF.Id + '/view'}"  target="_self">{! FF.Name }</a>                                   
                                </td> 
                                <th class="slds-truncate" scope="row" data-label="RecordType">
                                   {! FF.RecordType.Name }
                                </th> 
                                <td class="slds-truncate" scope="row" data-label="Date">
                                     <ui:outputDate value="{! FF.Date__c }"/>
                                </td>
                                <td class="slds-truncate" scope="row" data-label="Client Name">
                                    {! FF.Client_Name__r.Name }
                                </td>
                                <td class="slds-truncate" scope="row" data-label="Policy Name">
                                   {! FF.Policy__r.Name }
                                </td>  
                            </tr>
                        </aura:iteration>
                    </tbody>
                </table>
                <br/>
            </div>         
        </div>        
    </lightning:card>    
</aura:component>

Controller 
({
    doInit: function(component) {     
      var action = component.get("c.getallFF");
        action.setStorable();  
                action.setParams({
            "recordId" : component.get("v.recordId")
        }); 
        action.setCallback(this, function(actionResult) {
            component.set("v.factfinder", actionResult.getReturnValue());            
        });
        $A.enqueueAction(action); 
	}
})

Thank you for your help.
Sylvie
Waqar Hussain SFWaqar Hussain SF
Hi Sylvie,

See tht updated apex controller.
  
public with sharing class FFsamePolicysameClient {
   
    @AuraEnabled      
    public static List<Fact_Finder__c> getallFF(String recordId){ 
        list<Opportunity> Opps = new list<Opportuniyt>();
        Opps = [Select AccountId from Opportunity where ID= :recordId limit 1];
        
        Id AccountId;
        if(Opps.size() > 0)
        AccountId = Opps[0].AccountId;
        
        
       List <Fact_Finder__c> FF = new List<Fact_Finder__c>();
	   FF = [SELECT Id, Name, RecordType.Name, Date__c, Opportunity_Name__c, Client_Name__r.Name, Policy__r.Name, Opportunity_Name__r.Name FROM Fact_Finder__c Where Client_Name__c  =:AccountId ORDER BY Date__c DESC];
      return FF;         
    }
}

And for showing list of Facts and Finder on Lightning component, please follow the following article, which will help you alot. 

https://developer.salesforce.com/docs/atlas.en-us.lightning.meta/lightning/events_one_demo_load.htm

Thanks 
This was selected as the best answer
Sylvie SerpletSylvie Serplet
Thank you so much Waqar, it works perferctly!