You need to sign in to do that
Don't have an account?
Compare dates on child objects
I need to run a query to compare dates on child objects. We have a custom object Job Placements (child of Contacts) with Start Date and End Date fields. I need to find all Job Placements where the Start Date is within 30 days of an End Date from another Job Placement on the same Contact. Is this possible with SOQL or do I need to write a trigger to mark these records when they are created?
If you have not found solution yet, please try this and let me know. I have tested this code and it is working fine
In this code, replace
Job_Placement with your table name
Start_Date with your exact field name start date
End_Date with your exact field name end date.
Dont change __c or __r
List<Contact> contList = [SELECT Id, Name,(SELECT ID, Name, Start_Date__c, End_Date__c FROM Job_Placements__r) FROM Contact];
Integer count ;
Set<Id> JPId = new set<Id>();
List<Job_Placement__c> finalList = new List<Job_Placement__c>();
for(Contact c : contList)
{ for(Job_Placement__c JPOuterLoop : c.Job_Placements__r)
{
for(Job_Placement__c JPInnerLoop : c.Job_Placements__r)
{
If (JPOuterLoop.ID != JPInnerLoop.ID) // To make sure not to check with same end date
{
count = JPInnerLoop.End_Date__c.daysBetween(JPOuterLoop.Start_Date__c);
If ((count <=30 && count > 0) && !(JPId.contains(JPOuterLoop.Id)))
{
JPId.add(JPOuterLoop.Id);
finalList.add(JPOuterLoop);
}
}
}
}
}
All Answers
I am not sure if you can do it in SOQL. But you may create a formula field of Number data type to store the date difference and use that field in your soql to check if it is lesser than 30.
Let me know if you need more details.
Contact 1
Job Placement 1A
Start Jan 1 2017
End March 30 2017
Job Placement 1B
Start Apr 1 2017
End May 1 2017
Job Placement 1C
Start Aug 1 2017
End Dec 1 2017
Contact 2
Job Placement 2A
Start Jan 1 2017
End Jan 30 2017
Job Placement 2B
Start Apr 1 2017
End July 30 2017
Job Placement 2C
Start Aug 15 2017
End Dec 31 2017
Query/logic should return Job Placements 1B and 2C.
//Query to find ID and Start/End Dates of Placements This Year
List<ts2__Placement__c> plList = new List<ts2__Placement__c>([SELECT ID, ts2__Employee__c, ts2__Employee__r.ID, ts2__Start_Date__c, Actual_End_Date__c FROM ts2__Placement__c WHERE ts2__Start_Date__c = THIS_YEAR]);
//Query to find IDs of Employees with Placements This Year
List<ts2__Placement__c> plEmList = new List<ts2__Placement__c>([SELECT ID, ts2__Employee__c, ts2__Employee__r.ID FROM ts2__Placement__c WHERE ts2__Start_Date__c = THIS_YEAR]);
//Set of IDs of Employees with Placements This Year
Set<ID> plSet = new Set<ID>();
for(ts2__Placement__c p :plEmList){
if(!plSet.contains(p.ts2__Employee__r.ID)){
plSet.add(p.ts2__Employee__r.ID);
}
}
Try the below code and let me know
Note: Please check the field name and table names before you proceed.
List<Contact> cont = [SELECT Id,Name,(SELECT ts2__Start_Date__c, Actual_End_Date__c FROM ts2__Placements__c) FROM Contact];
List<ts2__Placement__c> finalList = new List<ts2__Placement__c>;
for(Contact c : cont)
{
for(ts2__Placement__c JPOuterLoop : c.ts2__Placements__c)
{
for(ts2__Placement__c JPInnerLoop : c.ts2__Placements__c)
{
If (JPOuterLoop.ID != JPInnerLoop.ID) // To make sure not to check with same end date
{
If (JPInnerLoop.Actual_End_Date__c.daysBetween(JPOuterLoop.ts2__Start_Date__c) <=30)
finalList.add(JPOuterLoop);
}
}
}
}
Did this work for you?
If you have not found solution yet, please try this and let me know. I have tested this code and it is working fine
In this code, replace
Job_Placement with your table name
Start_Date with your exact field name start date
End_Date with your exact field name end date.
Dont change __c or __r
List<Contact> contList = [SELECT Id, Name,(SELECT ID, Name, Start_Date__c, End_Date__c FROM Job_Placements__r) FROM Contact];
Integer count ;
Set<Id> JPId = new set<Id>();
List<Job_Placement__c> finalList = new List<Job_Placement__c>();
for(Contact c : contList)
{ for(Job_Placement__c JPOuterLoop : c.Job_Placements__r)
{
for(Job_Placement__c JPInnerLoop : c.Job_Placements__r)
{
If (JPOuterLoop.ID != JPInnerLoop.ID) // To make sure not to check with same end date
{
count = JPInnerLoop.End_Date__c.daysBetween(JPOuterLoop.Start_Date__c);
If ((count <=30 && count > 0) && !(JPId.contains(JPOuterLoop.Id)))
{
JPId.add(JPOuterLoop.Id);
finalList.add(JPOuterLoop);
}
}
}
}
}
Didn't understand relationship 'Job_Placement__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
Have you put the custom child object name in plurals? If you make sure to put <object_name>s__r as in my code, this error should not come.
Please share your code if you get this error even after making the object name to plural name whereever applicable.
As a standard, we follow the naming convention of pluralizing the child object_name as the relationship name when querying from the parent object.
Hope this helps!
Line: 1, Column: 1
System.LimitException: Too many query rows: 50001
Any suggestions to avoid this limit?
There are many ways to do it and it completely depends on your requirement and implementation.
You may use batch Apex.
Governor limits count for each Apex transaction. For Batch Apex, these limits are reset for each execution of a batch of records.
You may use Limit in your Query to check the functionality of this code and please mark it as solved.
And create a new question request in forum with your detailed requirement. Many forum members will provide you many suggestions as it is a very common issue that everyone face. Creating new request will put your question in top and will get focus of many forum members.
- Thanks.