 ShowAll Questionssorted byDate Posted Sylvio 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!

 Date A B Opp Result Sort Record 1 1/1/10 10 20 =B1-A2 20 Var 2 Record 2 1/1/00 40 50 =B2-A3 10 Var 3 Record 3 1/1/00 60 70 =B3-A1 60 Var 1 Record 4 2/1/00 10 20 Record 5 2/1/00 30 40 Record 6 2/1/00 60 70

Does anyone can help whit that?

Thanks Best Answer chosen by Sylvio Avilla LBK
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.A__c - (Integer)lstMyObject[i].B__c;
Record1 = (String)lstMyObject[i].Id;
Record2 = (String)lstMyObject.Id;

}
}
}

}```
Let me know if this helps. LBK
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.A__c - (Integer)lstMyObject[i].B__c;
}
}
}

}``` Dixit
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 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! LBK
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.A__c - (Integer)lstMyObject[i].B__c;
Record1 = (String)lstMyObject[i].Id;
Record2 = (String)lstMyObject.Id;

}
}
}

}```
Let me know if this helps.

This was selected as the best answer Sylvio Avilla
Perfect!

Thank you again LBK! Synkronizer 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