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
Devendra SawantDevendra Sawant 

Retrieve Records from objects which are in Relationship

 

Hi,

 

I have created one object called Job which has lookup relationship with Schedule Template object.

I have another object Schedule Template Item which has master-detail relationship with Schedule Template object.


Job (Master) and Schedule Template (Child) : Lookup Relationship

Schedule Template (Master) and  Schedule Template Item (Child) : Master-Detail Relationship

I want to retrive Schedule Template Items.

How to do it?

Using visualforce,apex code how can i display related Schedule Template Items of particular Schedule Template?

 

Cheers,

Devendra S

Best Answer chosen by Admin (Salesforce Developers) 
Devendra NataniDevendra Natani

You are using List<Schedule_Task__c > lstSTI on vf page. This list should be public if you want to use that on vf page.

Please use like 

 

public List<Schedule_Task__c > lstSTI{get;set;}

 

In constructor - Please instanciate the List<Schedule_Task__c >

 

lstSTI = new List<Schedule_Task__c >();

 

and finally please replace this line 

List<Schedule_Task__c > lstSTI = [select id,name from Schedule_Task__c where Schedule_Template__r.Id in :lstSTIds];

 

by

lstSTI = [select id,name from Schedule_Task__c where Schedule_Template__r.Id in :lstSTIds];

 

Please let me know if there is any issue.

All Answers

bob_buzzardbob_buzzard

There's a few ways to do this.

 

If you have the parent schedule template id, you can retrieve all of its children via SOQL, e.g.

 

Id tempId;    // store the id here

List<Schedule_Template__c> masters=[select id, name, (select id, name from Schedule_Templates__r) from Schedule_Template__c where id=:tempId];

if (masters.size()>0)
{
   Schedule_Template__c master=masters[0];
   // iterate the children
   for (Schedule_Template__c template : master.Schedule_Templates__r)
   {
      // do something with the template here!
   }
}

 
Devendra NataniDevendra Natani

Create a apex class("TestController") and use the following code.

Please replace the ScheduleTemplateItem, ScheduleTemplate  objects with Api names respectively. If you want to use more fields in query please add it.

ScheduleTemplateId is the id of master record.

 

public List<ScheduleTemplateItem> getScheduleTemplateItem(){
List<ScheduleTemplateItem> lstSTI = [select id,name from ScheduleTemplateItem where ScheduleTemplate =: ScheduleTemplateId];
}

 Create a vf page. Set the controller of that page to the apex class (created above).

 

<apex:page controller="testController">
<apex:form>
<apex:pageBlock >
    		<apex:pageBlockSection columns="1">
    			<apex:PageblockTable value="{!ScheduleTemplateItem}" var="item"> 
                        <apex:column value="{!item.id}" />
                        <apex:column value="{!item.name}" /> 	
	                </apex:PageblockTable>  
	                </apex:pageBlockSection>
	                </apex:pageBlock >
	                </apex:form>
	                </apex:page>

 

Please let me know if you have any issues.

 

Thanks,

Devendra Natani

Blog
Devendra SawantDevendra Sawant

 

Hi,

 

I want to retrive ScheduleTemplateItems on a Job page. That means i need to first require id of particular job. After retriving job, i need to find ScheduleTemplate and based on ScheduleTemplate the available ScheduleTemplateItems will be listed.

 

 

Job->ScheduleTemplate->ScheduleTemplateItems

 

How can i find ScheduleTemplateItems based on particular job?

 

Cheers,

Devendra S
bob_buzzardbob_buzzard

You'll need to do that as two queries I think - I don't believe you can traverse two levels of relationship in that way.

 

Given a job, you can get the related schedule templates

Id jobId;  // set job id here

List<Job__c> jobs=[select id, Name, (select id from Schedule_Templates__r) from Job__c where id=:jobId];
if (Jobs.size()>0)
{
   // now get the items
   List<Schedule_Template__c> templateItems=[select id, Name, Schedule_Template__c from Shedule_Template__c where id IN :jobs[0].Schedule_Templates__r];
   
   // do stuff with the items here
}

 

:

 

 
Devendra NataniDevendra Natani

Hi,

 

Is your job page is native/visualforce page? If its a native page then you will have to use a inline vf page on job layout.

 

Create a vf page/controller using following pseudocode steps to fetch ScheduleTemplateItems.

  1. Fetch Job Id
  2. Fetch ScheduleTemplate by jobId and keep ScheduleTemplate in list<ScheduleTemplate >.
  3. Fetch ScheduleTemplateItems by list<ScheduleTemplate >


    Please let me know if there is any issue.
    Thanks,
    Devendra Natani 
Devendra SawantDevendra Sawant

 

Hi,

 

Yes,I am using inline visualforce.

 

I am able to fetch the job id. But how to retrive ScheduleTemplate id?

 

Which statements should i use to retrieve all three steps that u mention above?

 

Cheers,

Devendra S

 

 
Devendra NataniDevendra Natani

string jobId = '' ; // assign job id 

List<ScheduleTemplate> lstScheduleTemplate = [select id,name from ScheduleTemplate where jobId__c=: jobId];

if (lstScheduleTemplate.size() > 0)

{

 List<ScheduleTemplateItem> lstScheduleTemplateItem = [select id, name from ScheduleTemplateItem where ScheduleTemplate__c in : lstScheduleTemplate];

}

 

Please replace the objectname with object name api. Please let me know if there is any issue.

 

 
Devendra SawantDevendra Sawant

 

Hi,

 

List<ScheduleTemplate> lstScheduleTemplate = [select id,name from ScheduleTemplate where jobId__c=: jobId];

 

"   Error: Compile Error: No such column 'jobId__c' on entity 'Schedule_Template__c'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names."

 

I am getting this error.

 

 

Cheers,

Devendra S
Devendra NataniDevendra Natani

The code which i was written is a sample code. You need to use the api names for fields and objects. 

 

In ScheduleTemplate object you have a field for jobId. Please use the api name of that field. Please use the api name for the fields in query.
Devendra SawantDevendra Sawant

 

Hi,

I am extremely sorry.

I have put my question wrongly. Once again sorry.

In first relationship Job is Child and ScheduleTemplate is Master where they have lookup relationship.

In second relationship,

Schedule Template (Master) and  Schedule Template Item (Child) : Master-Detail Relationship

Now, how can i retrieve Schedule template items using those three steps?

Cheers,
Devendra S


Devendra NataniDevendra Natani

That's fine. No issues

 

As per my understanding you have three objects.

 

Job

ScheduleTemplate 

ScheduleTemplateItem 

 

ScheduleTemplate and Job have lookup relationship - so that means Job object has a lookup of ScheduleTemplate .

ScheduleTemplateItem and ScheduleTemplate have master detail relationship.

 

Note - Please replace the following variables with api name of fields/object.

Job - Job object apiname

ScheduleTemplateId - lookup field apiname of ScheduleTemplate in job object

ScheduleTemplateItem - api name of ScheduleTemplateItem object

ScheduleTemplateId2 - masterdetail field apiname of ScheduleTemplate object in ScheduleTemplateItem object

 

// fetch ScheduleTemplate using job

// string jobId = ''; // assign job id

List<Job> lstJob = [select ScheduleTemplateId  from JOb where id=: jobId and ScheduleTemplateId  != null];

List<String> lstSTIds = new LIst<String>();

for (Job j : lstJob){

lstSTIds.add( j.ScheduleTemplateId); 

}

 

List<ScheduleTemplateItem > lstSTI = [select id,name from ScheduleTemplateItem where ScheduleTemplateId2 in :lstSTIds];

 

Please use that lstSTI to show values on vf page. Please let me know if there is any issue.

 

 

 

 
Devendra SawantDevendra Sawant

 

Hi,

 

I am writting the above code in constructor. so that when page gets loaded it will get populated with the ScheduleTemplateItems based on jobid.

 

On visualforce page it is giving me error.

 

To list lstSTI on visualforce page, which statement should i use on visualforce page?

 

DO i making mistake by writting the code in constructor? How to solve this problem?

 

Cheers,

Devendra S

 

 
Devendra SawantDevendra Sawant

Hi,

 

<!-- This page is used to display detail information of each job -->

 

VF Page to display detail and related list on using tab panel

 

<apex:page standardController="Job__c" showHeader="true" tabStyle="Job__c" >
<head>
   <style>
   <!-- Style Code goes here -->
     
       <!-- End Of Style -->
   </style>
</head>
   <!-- Start Tab Panel -->
   <apex:tabPanel switchType="client" selectedTab="Details" id="JobTabPanel" tabClass="activeTab" inactiveTabClass="inactiveTab">
                 
      <!-- Detail Tab-->
      <apex:tab label="Details" name="Details" id="tabdetails" style="background-color:#FFF" styleClass="labelCol">
         <apex:detail relatedList="false" title="true" inlineEdit="true" showChatter="true"/>
      </apex:tab>
      <!-- End of Detail Tab -->
     
      <!-- Start Job Template Items Tab -->
      <apex:tab label="Job Template Items" name="Job Template Items" id="tabtempi">
  <!--       <apex:relatedList subject="{!Job__c}" list="Specification_Templates__r"/>-->
  <apex:include pageName="DisplayJobTempItemsOnJobTab" rendered="true"/>
      </apex:tab>
      <!-- End Job Template Items Tab -->
 
    
   </apex:tabPanel>
   <!-- End Of Tab Panel -->
 </apex:page>
 <!-- End of Page -->

 

 

Visualforce page for Inlice VF: DisplayJobTempItemsOnJobTab

 

<apex:page controller="RetrieveTask">
 
  <apex:form >
  <apex:pageBlock >
  <apex:PageblockTable value="{!lstSTI}" var="item">
    <apex:inputField value="{!item.Name}"/>                 

      </apex:PageblockTable>
  </apex:pageBlock>
  </apex:form>
 
</apex:page>

 

Apex Class:

 

public class RetrieveTask
{ 

  public LIST<Schedule_Task__c> lstSTI{get;set;}
   public RetrieveTask(ApexPages.StandardController st)
    {
 }
   
    public RetrieveTask()
    {
    Id jobId = ApexPages.currentPage().getParameters().get('id');    
      List<Job__c> lstJob = [select Schedule_Template__r.Id  from Job__c where id=: jobId and Schedule_Template__r.Id  != null];
     
    List<String> lstSTIds = new List<String>();
   
    for (Job__c j : lstJob){

    lstSTIds.add( j.Schedule_Template__r.Id);
}
List<Schedule_Task__c > lstSTI = [select id,name from Schedule_Task__c where Schedule_Template__r.Id in :lstSTIds];
    }
   }

 

Hi Devendra,

 

I am not getting expected output.

 

May i know where i am making mistake?

 

I am not able to identify the mistake.

 

Cheers,

Devendra S
Devendra NataniDevendra Natani

You are using List<Schedule_Task__c > lstSTI on vf page. This list should be public if you want to use that on vf page.

Please use like 

 

public List<Schedule_Task__c > lstSTI{get;set;}

 

In constructor - Please instanciate the List<Schedule_Task__c >

 

lstSTI = new List<Schedule_Task__c >();

 

and finally please replace this line 

List<Schedule_Task__c > lstSTI = [select id,name from Schedule_Task__c where Schedule_Template__r.Id in :lstSTIds];

 

by

lstSTI = [select id,name from Schedule_Task__c where Schedule_Template__r.Id in :lstSTIds];

 

Please let me know if there is any issue.

This was selected as the best answer
Devendra SawantDevendra Sawant

 

Hi,

 

Many many thanks.

 

It is giving expected output now.

 

Cheers,

Devendra S