Apex Code Development You need to sign in to do that
Don't have an account?
DBManager Find latest record by date (grouped by type) and mark as current
I have created a custom object, called Magazine_Subscription__c. The object includes Contact (lookup), Subscription_Date__c (date-time), Magazine__c (picklist) and Status__c (picklist) fields.
What I want for each Contact is the most recent record (by Subscription_Date__c) for each Magazine__c marked as "Current" in the Status__c field, and for the rest to be marked as "Old".
But no matter my research into Group By, Order By and Aggregate functions, I can't seem to work out how to do it.
This is what I've got so far, I hope someone can help:
trigger UpdateOldSubs on Magazine_Subscription__c (after update, after insert) {
Set<Id> ConIds = new Set<Id>();
if (Trigger.isUpdate){
for (integer i=0; i < Trigger.new.size(); i++){
if (Trigger.new[i].Contact__c != Trigger.old[i].Contact__c){
ConIds.add(Trigger.old[i].Contact__c);
}
}
}
if (ConIds.size() > 0){
Contact[] ConList =
[SELECT Id FROM Contact WHERE Id IN :ConIds];
Magazine_Subscription__c[] ConSubList =
[SELECT Id, Contact__c, Subscription_Date__c, Status__c, Magazine__c FROM Magazine_Subscription__c WHERE Contact__c IN :ConIds];
for (Contact cons :ConList){
for (Magazine_Subscription__c subs :ConSubList){
if (subs.Contact__c == cons.Id){
//*******New Code Here*******
}
}
}
}
}
I solved this by looking at it from another perspective.
I looked at all the Magazine Subscriptions that are under the same Contact and check them against each other one by one.
If it helps anyone, the comparison code is below:
if (conIds.size()>0){ Contact[] conList = [SELECT Id FROM Contact WHERE Id IN :conIds]; Magazine_Subscription__c[] subList = [SELECT Id, Contact__c, Subscription_Date__c, Status__c, Magazine__c FROM Magazine_Subscription__c WHERE Contact__c IN :conIds AND Status__c = 'Current']; for (Contact conUp :conList){ for (Magazine_Subscription__c subUp :subList){ for (Magazine_Subscription__c otherSubUp :[SELECT Id, Contact__c, Subscription_Date__c, Status__c, Magazine__c FROM Magazine_Subscription__c WHERE Contact__c = :subUp.Contact__c AND Magazine__c = :subUp.Magazine__c AND Subscription_Date__c > :subUp.Subscription_Date__c AND Status__c = 'Current']){ otherSubUp.Status__c = 'Current'; subUp.Status__c = 'Old'; } } update subList; } }All Answers
Can you please share you sobject xml and few records in data loader format, I can try it on my end.
How do I get hold of the XML for this object?
If it's through the IDE, I'm not that experienced with it - can you guide me through?
Thanks.
I solved this by looking at it from another perspective.
I looked at all the Magazine Subscriptions that are under the same Contact and check them against each other one by one.
If it helps anyone, the comparison code is below:
if (conIds.size()>0){ Contact[] conList = [SELECT Id FROM Contact WHERE Id IN :conIds]; Magazine_Subscription__c[] subList = [SELECT Id, Contact__c, Subscription_Date__c, Status__c, Magazine__c FROM Magazine_Subscription__c WHERE Contact__c IN :conIds AND Status__c = 'Current']; for (Contact conUp :conList){ for (Magazine_Subscription__c subUp :subList){ for (Magazine_Subscription__c otherSubUp :[SELECT Id, Contact__c, Subscription_Date__c, Status__c, Magazine__c FROM Magazine_Subscription__c WHERE Contact__c = :subUp.Contact__c AND Magazine__c = :subUp.Magazine__c AND Subscription_Date__c > :subUp.Subscription_Date__c AND Status__c = 'Current']){ otherSubUp.Status__c = 'Current'; subUp.Status__c = 'Old'; } } update subList; } }