+ Start a Discussion
Anu-SFDCAnu-SFDC 

getting unique records in pageblock table

Hi,

 

I have created a pageblock table and listed all transactions under a contact. In that transactions, For example there are 4 transactions for a single credit card number fora single contact.I need only the last transaction for that  credit card number.

 

For that, I want to remove the duplicates of credit card number. 

 

Here is my code:

 

Page:

 

 

<apex:page standardController="Contact" extensions="CreditTransactions">
<apex:form >
 <apex:pageBlock >
<apex:pageBlockTable value="{!trans}" var="transaction">
  <apex:column headerValue="Date Last Used" value="{!transaction.TransactionDate__c}"/> 
    <apex:column headerValue="Last 4 digits" value="{!transaction.CreditCard4x4__c}"/> 
    <apex:column headerValue="Expiration Date" value="{!transaction.CardExpiration__c}"/> 
    <apex:column headerValue="Card Type" value="{!transaction.Credit_Card_Name__c}"/> 
    <apex:column >
    <apex:commandButton action="{!VTerminal}" value="Virtual Terminal"/>
    </apex:column>
</apex:pageBlockTable>
  </apex:pageBlock>
  </apex:form>
 <apex:detail />
</apex:page>
Controller:
public with sharing class CreditTransactions {
public List<CnP_Transaction__c> trans{get;set;}
public List<CnP_Transaction__c> duplicates{get;set;}
 set<string> CreditOrderNumber=new set<string>();
 public String ContactId{get;set;}
    public CreditTransactions(ApexPages.StandardController controller) {
     
     ContactId = ApexPages.currentPage().getParameters().get('Id');
     List<CnP_Transaction__c> ToCreateCredits=[select Id, Name,CreditCard4x4__c, Contact__c from CnP_Transaction__c where Contact__c = :contactId];
 
         for(Integer i=0;i<ToCreateCredits.size();i++){
             CreditOrderNumber.add(ToCreateCredits[i].CreditCard4x4__c);
         }
         
  
         
     System.debug('Credit Order numberrrr'+CreditOrderNumber);
     trans = [select Id,Name,TransactionDate__c,Contact__c,Credit_Card_Name__c,CreditCard4x4__c,CardExpiration__c from CnP_Transaction__c where CreditCard4x4__c IN :CreditOrderNumber and Contact__c= :ContactId and CreditCard4x4__c!=null];
    }
    public CreditTransactions() {
     }
     public PageReference Vterminal() 
    {
     return Page.VirtualTerminal;
    }
}
I want  a list of records in the table which does not repeat any credit card number and if more transactions with the same credit card is there need to display the last transaction..
Can any one of you help me regarding this.
Anu

 

Best Answer chosen by Admin (Salesforce Developers) 
ColinKenworthyColinKenworthy

Select the Transactions in Transaction Date order, then loop through the results and use a MAP to store the CardNumber and the TransactionObject. When all the transactions have been added to the map it will contain the most recent transaction for each CardNumber.

 

 

Map<String, CnP_Transaction__c> = new Map<String, CnP_Transaction__c>();
List<CnP_Transaction__c> ToCreateCredits=[select Id, Name,CreditCard4x4__c, Contact__c, TransactionDate__c from CnP_Transaction__c where Contact__c = :contactId ORDER BY TransactionDate__c];

for(Integer i=0;i<ToCreateCredits.size();i++){
CreditOrderNumberMap.put(ToCreateCredits[i].CreditCard4x4__c , ToCreateCredits[i]);
}
// the map now contains just the highest tran date transaction for each credit card number

 

 

 

 

All Answers

ColinKenworthyColinKenworthy

Why not select the transactions in transaction date order and use the last one ?

shruthishruthi

Using Aggregate Result can help in grouping:

AggregateResult[] transactions  = [select Id,Name, MAX(TransactionDate__c),Contact__c,Credit_Card_Name__c,CreditCard4x4__c,CardExpiration__c from CnP_Transaction__c where CreditCard4x4__c IN :CreditOrderNumber and Contact__c= :ContactId and CreditCard4x4__c!=null GROUP BY CreditCard4x4__c];

 

The above groups by the CreditCard4x4 (assuming it to be the credit card number) and uses MAX(TransactionDate__c) to get the latest transaction.

 

Hope this helps.

 

Cheers!

shruthishruthi

Using Aggregate Result can help in grouping:

AggregateResult[] transactions  = [select Id,Name, MAX(TransactionDate__c),Contact__c,Credit_Card_Name__c,CreditCard4x4__c,CardExpiration__c from CnP_Transaction__c where CreditCard4x4__c IN :CreditOrderNumber and Contact__c= :ContactId and CreditCard4x4__c!=null GROUP BY CreditCard4x4__c];

 

The above groups by the CreditCard4x4 (assuming it to be the credit card number) and uses MAX(TransactionDate__c) to get the latest transaction.

 

Hope this helps.

 

Cheers!

Anu-SFDCAnu-SFDC

Hi

 

Thanks for all your replies..

yes I have used aggregate results and grouped with credit card number. But I want to display the Id of that particular transaction...

 

Id cannot be aggregated and grouped.. but I need Id of that transaction and I want to pass that Id value in my button code..

 

Please advice me.

 

My code is

 

 

public with sharing class CreditTransactions {
public List<CnP_Transaction__c> transactvalue;
public String para{get;set{para=value;}}
public String ContactId{get;set;}
PageReference p;
list<AggregateResult> ResultSet;
public list<AggregateResult> getResultSet() {return ResultSet;}
public void setResultSet(list<AggregateResult> ResultSet) {this.ResultSet= ResultSet; } 
    public CreditTransactions(ApexPages.StandardController controller) {
    
         ContactId = ApexPages.currentPage().getParameters().get('Id');
         ResultSet= [select Max(TransactionDate__c) tdate,CreditCard4x4__c cardnum,CardExpiration__c cardexp,Credit_Card_Name__c cardname from CnP_Transaction__c where Id!=null and Contact__c= :ContactId  and CreditCard4x4__c!=null  GROUP BY  CreditCard4x4__c,CardExpiration__c, Credit_Card_Name__c];
    
    }
    public CreditTransactions() {
     }
     public PageReference Vterminal() 
    {
        transactvalue=new List<CnP_Transaction__c>();
        transactvalue  =[SELECT Id,Name FROM CnP_Transaction__c WHERE Id =:para];
            p=new PageReference('/apex/VirtualTerminal');
     return p;
    }
public list<OppClass> getResults()  
{  
    list<OppClass> lstResult = new list<OppClass>();  
    
     
    
    for (AggregateResult ar: ResultSet){  
        oppClass objOppClass = new oppClass(ar);  
        lstResult.add(objOppClass);  
    }  
    return lstResult;  
}
public class oppClass  
{  
    public DateTime tDate{ get;set; }
    
    public String tDate2{ get;set; }
    
    public String cardnum{ get;set; }  
      
    public String cardexp{ get;set; }
    
    public String cardname{ get;set; }  
  
  public String tranId{ get;set; }  
  
public oppClass(AggregateResult ar)  
{  
//Note that ar returns objects as results, so you need type conversion here  
        tDate=(DateTime)ar.get('tdate');
        tDate2 = tDate.format('dd/MM/yyyy hh:mm');
        cardnum = (String)ar.get('cardnum');
        cardexp = (String)ar.get('cardexp');
        cardname = (String)ar.get('cardname');
  
 //      tranId ='a055000000AKc3UAAT';
       
}  
}  
}
Anuradha
Certified developer salesforce

 

ColinKenworthyColinKenworthy

Select the Transactions in Transaction Date order, then loop through the results and use a MAP to store the CardNumber and the TransactionObject. When all the transactions have been added to the map it will contain the most recent transaction for each CardNumber.

 

 

Map<String, CnP_Transaction__c> = new Map<String, CnP_Transaction__c>();
List<CnP_Transaction__c> ToCreateCredits=[select Id, Name,CreditCard4x4__c, Contact__c, TransactionDate__c from CnP_Transaction__c where Contact__c = :contactId ORDER BY TransactionDate__c];

for(Integer i=0;i<ToCreateCredits.size();i++){
CreditOrderNumberMap.put(ToCreateCredits[i].CreditCard4x4__c , ToCreateCredits[i]);
}
// the map now contains just the highest tran date transaction for each credit card number

 

 

 

 

This was selected as the best answer