I have an existing page for a force.com site created to output a list of items from a custom object (Carrier_Product_Commission_Level__c). It works perfectly except that I want to filter the items it returns by a field on a lookup object.

So Carrier_Product_Commission_Level__c has Carrier_Product__c as a lookup object. Carrier_Product__c has a checkbox field called Inactive__c. And I want to only return those Carrier_Product_Commission_Level__c that have a Carrier_Product__c which in turn has a false value for Inactive__c.

I suspect I need an additional SOQL statement and If statement. I've been playing around with it but failing.

I appreciate some help in figuring this out. Thanks.

public with sharing class Carrier_Product_Commission {
    
    public list<Carrier_Product_Commission_Level__c> CPCL {get;set;}
    
    public string Product {get;set;}
        
    public decimal Excess_Commission_Rate {get;set;}
    
    public decimal Commission_Rate {get;set;}
    
    public String CurrentUserId {get;set;}
    
    public list<Data_Carrier_Product> LDCP {get;set;}
        
    public Carrier_Product_Commission() {
            
        if(System.currentPageReference().getParameters().get('aid') != null){
            CurrentUserId = System.currentPageReference().getParameters().get('aid');
        }

        System.debug('CurrentUserId :'+CurrentUserId);

        // Get all Carrier_Product_Commission_Level list  related to this Commission_Level

        CPCL = [Select c.Product__c, c.Excess_Commission_Rate__c, c.Product__r.Name,
                       c.Commission_Rate__c, c.Carrier_Commission_Level__r.Id 
                From Carrier_Product_Commission_Level__c c 
                where c.Carrier_Commission_Level__r.Id =: CurrentUserId
                order by CreatedDate desc limit 30];
        
        System.debug('CPCL :'+CPCL);    
            
        if (LDCP == null)  LDCP = new List<Data_Carrier_Product> {};
        
        for(Carrier_Product_Commission_Level__c cp : CPCL) {
        
            Data_Carrier_Product DC = new Data_Carrier_Product();
            DC.Product = cp.Product__r.Name;
            DC.Excess_Commission_Rate = cp.Excess_Commission_Rate__c;
            DC.Commission_Rate = cp.Commission_Rate__c;
            LDCP.add(DC);
            
        }  
            
    }
    
}