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
Sylvio AvillaSylvio Avilla 

Compare Records

Hello Everyone,

I kindly ask for some help from the experts, regarding the following problem:

I need is somehow get all records with the same date (DATE) and subtract the field B from field A of the next record.
After that, create at least 2 variables, the first with the biggest value ( VAR 1 = Record 3.ID ) and so on (VAR 2 = Record 1.ID , VAR 3 = Record 2. ID).
I've made an example that I think will be easier to understand!
 
 DateABOppResultSort
Record 11/1/101020=B1-A220Var 2
Record 21/1/004050=B2-A310Var 3
Record 31/1/006070=B3-A160Var 1
Record 42/1/001020   
Record 52/1/003040   
Record 62/1/006070   

Does anyone can help whit that?

Thanks
Best Answer chosen by Sylvio Avilla
LBKLBK
Here is the updated code for fetching IDs as well.
  
public class myCalc {
    Public doMyCalc(Date myDate){
       List<MyObject__c> lstMyObjects =  [Select ID, Name, A__c, B__c from MyObject__c WHERE Date__c :=myDate ORDER BY MyField__c DESC];
        
        for(int i=0; i<lstMyObjects.size();i++){
            int myValue = 0;
           String Record1 = '';
           String Record2 = '';
           
            if (i+1 < lstMyObjects.size()){
                myValue = (Integer)lstMyObject[i+1].A__c - (Integer)lstMyObject[i].B__c; 
                Record1 = (String)lstMyObject[i].Id;
                Record2 = (String)lstMyObject[i+1].Id;
            }
            else{
                myValue = (Integer)lstMyObject[0].A__c - (Integer)lstMyObject[i].B__c;
                Record1 = (String)lstMyObject[i].Id;
                Record2 = (String)lstMyObject[0].Id;

            }
        }
    }
   
}
Let me know if this helps.
 

All Answers

LBKLBK
Normal SOQL won't help. But you can do it in APEX class.

You need something like this. 
public class myCalc {
    Public doMyCalc(Date myDate){
       List<MyObject__c> lstMyObjects =  [Select ID, Name, A__c, B__c from MyObject__c WHERE Date__c :=myDate ORDER BY MyField__c DESC];
        
        for(int i=0; i<lstMyObjects.size();i++){
            int myValue = 0;
            if (i+1 < lstMyObjects.size()){
                myValue = (Integer)lstMyObject[i+1].A__c - (Integer)lstMyObject[i].B__c; 
            }
            else{
                myValue = (Integer)lstMyObject[0].A__c - (Integer)lstMyObject[i].B__c;
            }
        }
    }
   
}

 
DixitDixit
1) Query with a "where" statement with the date and include the fields you need to substract. 
2) loop your list with a while, and substract VarA[i+1] - VarB[i]  and add the result to a new list.
3)  sort that new list (newlist.sort() ) and create a loop to change the order of the list (the method always returns ascending order), you can see an example here: https://developer.salesforce.com/forums/?id=906F000000091wIIAQ

I will try doing that.
 
Sylvio AvillaSylvio Avilla
Hello @LBK,

It worked perfect, thanks! Just one more thing: How could I get the ID from the 2 values?
Based on the example:
 myValue = 60  Id1= Record3 and Id2= Record2

@Dixit, your answer also helped me, thank you too. Unfortunately, I can only choose one!
 
LBKLBK
Here is the updated code for fetching IDs as well.
  
public class myCalc {
    Public doMyCalc(Date myDate){
       List<MyObject__c> lstMyObjects =  [Select ID, Name, A__c, B__c from MyObject__c WHERE Date__c :=myDate ORDER BY MyField__c DESC];
        
        for(int i=0; i<lstMyObjects.size();i++){
            int myValue = 0;
           String Record1 = '';
           String Record2 = '';
           
            if (i+1 < lstMyObjects.size()){
                myValue = (Integer)lstMyObject[i+1].A__c - (Integer)lstMyObject[i].B__c; 
                Record1 = (String)lstMyObject[i].Id;
                Record2 = (String)lstMyObject[i+1].Id;
            }
            else{
                myValue = (Integer)lstMyObject[0].A__c - (Integer)lstMyObject[i].B__c;
                Record1 = (String)lstMyObject[i].Id;
                Record2 = (String)lstMyObject[0].Id;

            }
        }
    }
   
}
Let me know if this helps.
 
This was selected as the best answer
Sylvio AvillaSylvio Avilla
Perfect!

Thank you again LBK!

 
Synkronizer Excel Compare ToolSynkronizer Excel Compare Tool
Hi, I Think you can Export Data From Salesforce and Then Compare Records Using Synkronizer Excel Compare Tool without writing any formula's. Thanks