+ Start a Discussion
Sourav PSourav P 

To Refer a field value from a child object under certain criteria

Dear All,
I have the below fields on Quote object ( parent) , Named Driver ( Child object). I have set up certain fields ( through formula and apex) which will take the minimum age driver from the list of driver records.
The extreme below field , takes the min driver number ID, and the first one is the hyperlink formula on that.
The first one is set as the formula,
HYPERLINK( Youngest_DriverT__c , Name_of_Driver__c )

User-added image

Now i want that, which ever record number being set here, The account name * 2nd field) and the birthday from the NamedDriver will show here. I tried to create a PB , but its not working. Can you plz suggest how to goahead, thanks

Below is my PB i tried,

Criteria :
User-added image

Action :
User-added image

 
Best Answer chosen by Sourav P
Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

Please use below line in the class:

Replace Accountname and DOB by API name on  Quatation Driver(Named Driver) object.

 

oppList.add(new Quote(Id = objNamedDriver.Quote__c, Youngest_DriverT__c = URL.getSalesforceBaseUrl().toExternalForm() + '/' + objNamedDriver.Id,Name_of_Driver__c=objNamedDriver.Name,Young_Driver_AccountName__c =objNamedDriver.Accountname,Young_Driver_DOB__c=objNamedDriver.DOB));

 

 

All Answers

Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

 

Please confirm "Young Driver Account Name" is look up field to Account.

Also, Birthday field is on which object?
 

Thanks

Deepak

Sourav PSourav P
Hi Deepak,

Young Driver Number, we got the same way that you have suggested earlier through the Apex and the formula field. But we too need the Account name & the DOB from the Named Driver object , i tried to change the apex code, but it didnt worked.
The two marked fields are on the Named Driver object (Young_Driver_AccountName__c & Young_Driver_DOB__c). The Young Driver aaccount name is just a text field.


User-added image

But i have also created a lookup to Named Driiver field for try, as below

User-added image

The earlier code suggested by you link :
https://developer.salesforce.com/forums/ForumsMain?id=9060G000000ICaeQAG

Thanks for your help .
Deepak Maheshwari 7Deepak Maheshwari 7

The API name of objects which you have shared are on the quote object.

Please give the API name of same  fields on Quatation Driver(Named Driver) object.

Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

Please use below line in the class:

Replace Accountname and DOB by API name on  Quatation Driver(Named Driver) object.

 

oppList.add(new Quote(Id = objNamedDriver.Quote__c, Youngest_DriverT__c = URL.getSalesforceBaseUrl().toExternalForm() + '/' + objNamedDriver.Id,Name_of_Driver__c=objNamedDriver.Name,Young_Driver_AccountName__c =objNamedDriver.Accountname,Young_Driver_DOB__c=objNamedDriver.DOB));

 

 

This was selected as the best answer
Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

 

Did you try above solution?

 

Thanks

Sourav PSourav P
Hi Deepak, Thanks a lot. Let me try this. and let you know .
Sourav PSourav P
Hi Deepak,
I tried as per below, but its showing me  some issues. plz suggest.
oppList.add(new Quote(Id = objNamedDriver.Quote__c, Youngest_DriverT__c = URL.getSalesforceBaseUrl().toExternalForm() + '/' + objNamedDriver.Id,Name_of_Driver__c=objNamedDriver.Name,Young_Driver_AccountName__c =objNamedDriver.Account__c,Young_Driver_DOB__c=objNamedDriver.DOB__c));

Account__c , is the look up relationship on teh Named Driver object,

User-added image

But while adding a named driver record, its showing the below error,

User-added image

Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger DealTrigger caused an unexpected exception, contact your administrator: DealTrigger: execution of AfterInsert caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Quotation_Driver__c.Account__c: Class.OpportunityHandlers.aggregateResults: line 71, column 1
Sourav PSourav P
Hi Deepak,
I just changes in few places ( or only in aggregate ? ) as below, If these OK ?

1. AggregateResult[] groupedResults = [SELECT MIN(AgeUpdated__c), Quote__c,Name,Account__c,DOB__c FROM Quotation_Driver__c where Quote__c IN :oppIDs GROUP BY Quote__c];
2. for (Quotation_Driver__c objNamedDriver : [SELECT Id,Name, AgeUpdated__c, Quote__c,Account__c,DOB__c FROM Quotation_Driver__c WHERE Quote__c IN :oppIDs ])
3. oppList.add(new Quote(Id = objNamedDriver.Quote__c, Youngest_DriverT__c = URL.getSalesforceBaseUrl().toExternalForm() + '/' + objNamedDriver.Id,Name_of_Driver__c=objNamedDriver.Name,Young_Driver_AccountName__c =objNamedDriver.Account__c,Young_Driver_DOB__c=objNamedDriver.DOB__c));

Its showing me the below error while saving the record,


Error: Compile Error: Field must be grouped or aggregated: Name at line 56 column 36
line 56 is, the No 1. in aggregate no.
Thanks
Deepak Maheshwari 7Deepak Maheshwari 7
Please do not do any changes in Aggregate Query and rest all is fine.
Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

 

Is the issue resolved?

Sourav PSourav P
Hi Deepak,

I kept aggregate as same,
AggregateResult[] groupedResults = [SELECT MIN(AgeUpdated__c), Quote__c FROM Quotation_Driver__c where Quote__c IN :oppIDs GROUP BY Quote__c];

and changed in teh last two points,
for (Quotation_Driver__c objNamedDriver : [SELECT Id,Name, AgeUpdated__c, Quote__c,Account__c,DOB__c FROM Quotation_Driver__c WHERE Quote__c IN :oppIDs ])
oppList.add(new Quote(Id = objNamedDriver.Quote__c, Youngest_DriverT__c = URL.getSalesforceBaseUrl().toExternalForm() + '/' + objNamedDriver.Id,Name_of_Driver__c=objNamedDriver.Name,Young_Driver_AccountName__c =objNamedDriver.Account__c,Young_Driver_DOB__c=objNamedDriver.DOB__c));
Able to save it now, but the values are not coming , plz suggest


User-added image

 
Deepak Maheshwari 7Deepak Maheshwari 7

Please check DOB and Account Name on Quotation Driver record is filled or not.

Maybe they are blank.

Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

 

Were you able to find the real cause?

If not please let me know

Sourav PSourav P
Hi Deepak , Thanks. Its filled. Also, if DOB or Acc no. not filled, cant it show as blank ?
Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

 

 

Please elaborate. I didn't get you.

Sourav PSourav P
Hi Deepak, If suppose only the account name is filled, and the DOB is not filled. Can't it show only the acc name and DOB filed as empty ?
also till , i am not able to get the values, i refreshed the page here,

User-added image
and , all are maintained in the Named driver object. shall we need to change somthing in the code? DOB is a fomula field here which is coming from account. and below i have created another account name field which is as well a formula field. but we are taking the first look up account field one. I think it shd work,isnt it.

User-added image

 
Sourav PSourav P
Hi Deepak, i think we are very close but may be somwhere missing somthing in the code i guess, As per the code , it seems the values should fetch, plz suggest.thnx
Deepak Maheshwari 7Deepak Maheshwari 7
Please let me know the data type for Young Driver Account Name and Young Driver DOB on Quote Object?
Sourav PSourav P
Hi deepak,

Young Driver Account Name        Young_Driver_AccountName__c          Text(255)
Young Driver DOB                          Young_Driver_DOB__c                          Date
Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

 

I have tried in my org. Its working fine.

When I used Account__c it gave account id.

Then I use Account__r.Name then it gave me Account name easily.

 

Did you try to insert, update or delete Quotation driver records?

Sourav PSourav P
Hi Deepak, I tried. But let me try once more and i will use the account__r.Name. Then i post the whole code for evaluation once else, if it wont work. Thnx
Sourav PSourav P
Hi Deepak, Finally managed to come the DOB, But the acc name is till not coming, i tried both account__r .Name as well. Can you see any issue in the code,
public class OpportunityHandlers
{
list<Quote> oppList = new list<Quote>();
set<id> oppIDs = new set<id>();
Map<string, Double> mapMinAge = new Map<string, Double>();
public void oppInsert(List<Quotation_Driver__c> oppLists)
{
for(Quotation_Driver__c opp : oppLists)
{
if(opp.Quote__c !=null)
{
oppIDs.add(opp.Quote__c);
}
}
aggregateResults();
}
public void oppUpdate(List<Quotation_Driver__c> oppLists,Map<Id,Quotation_Driver__c> oppOldmap)
{
for(Quotation_Driver__c opp : oppLists)
{
if(opp.Quote__c!=null)
{
oppIDs.add(opp.Quote__c);
if(oppOldmap.get(opp.id).Quote__c!=opp.Quote__c)
{
oppIDs.add(oppOldmap.get(opp.id).Quote__c);
}
}
}
aggregateResults();
}
public void oppDelete(List<Quotation_Driver__c> oppLists)
{
for(Quotation_Driver__c opp : oppLists)
{
if(opp.Quote__c!=null)
{
oppIDs.add(opp.Quote__c);
}
}
aggregateResults();
}
public void oppundelete(List<Quotation_Driver__c> oppLists)
{
for(Quotation_Driver__c opp : oppLists)
{
if(opp.Quote__c!=null)
{
oppIDs.add(opp.Quote__c);
}
}
aggregateResults();
}
public void aggregateResults()
{
AggregateResult[] groupedResults = [SELECT MIN(AgeUpdated__c), Quote__c FROM Quotation_Driver__c where Quote__c IN :oppIDs GROUP BY Quote__c];
for(AggregateResult ar:groupedResults)
{
mapMinAge.put((String)ar.get('Quote__c'),(Double)ar.get('expr0'));
/*Id oppId = (ID)ar.get('Quote__c');
            Double min = (DOUBLE)ar.get('expr0');
            Quote opp1 = new Quote(Id=oppId);
            opp1.Youngest_DriverT__c = min;
            oppList.add(opp1);*/
}
for (Quotation_Driver__c objNamedDriver : [SELECT Id,Name, AgeUpdated__c, Quote__c,Account__r.Name,DOB__c FROM Quotation_Driver__c WHERE Quote__c IN :oppIDs ]) 
{
Quote objQuote;
if(mapMinAge.containsKey(objNamedDriver.Quote__c)){
if (mapMinAge.get(objNamedDriver.Quote__c) == objNamedDriver.AgeUpdated__c){
oppList.add(new Quote(Id = objNamedDriver.Quote__c, Youngest_DriverT__c = URL.getSalesforceBaseUrl().toExternalForm() + '/' + objNamedDriver.Id,Name_of_Driver__c=objNamedDriver.Name,Young_Driver_AccountName__c =objNamedDriver.Account__r.Name,Young_Driver_DOB__c=objNamedDriver.DOB__c));
}}}
if(oppList.size()>0 && oppList!=null)
{
update oppList;
}
}
}


 
Sourav PSourav P
User-added image
Deepak Maheshwari 7Deepak Maheshwari 7
It should work. Can you please try formula field which you created for Account name.
Sourav PSourav P
Ya sure, Let me try that
Deepak Maheshwari 7Deepak Maheshwari 7

Hi Sourav,

 

Did you try?

Sourav PSourav P
Hi Dipak, I am not getting luck :(
The name is not coming,
i just tried my formula field ( But i think account__r.name would have been better) as below
" Account_name__c"
for (Quotation_Driver__c objNamedDriver : [SELECT Id,Name, AgeUpdated__c, Quote__c,Account_name__c,DOB__c FROM Quotation_Driver__c WHERE Quote__c IN :oppIDs ])

and,
oppList.add(new Quote(Id = objNamedDriver.Quote__c, Youngest_DriverT__c = URL.getSalesforceBaseUrl().toExternalForm() + '/' + objNamedDriver.Id,Name_of_Driver__c=objNamedDriver.Name,Young_Driver_AccountName__c =objNamedDriver.Account_name__c,Young_Driver_DOB__c=objNamedDriver.DOB__c));


 
Deepak Maheshwari 7Deepak Maheshwari 7
Is the issue resolved?
Sourav PSourav P
Hi Deepak, Finally :). It worked for account__r.Name. Thanks a lot for the help.