+ Start a Discussion
DBManagerDBManager 

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*******
		}
	}
}

}

}

 

Best Answer chosen by Admin (Salesforce Developers) 
DBManagerDBManager

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

Abhinav GuptaAbhinav Gupta

Can you please share you sobject xml and few records in data loader format, I can try it on my end.

DBManagerDBManager

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.

DBManagerDBManager

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;
    }
}

 

This was selected as the best answer