0

So I have a Visualforce Component that is using a List of Options. I would like to have that list only show the values that would have records get displayed.

A little more info:

This page is displayed on Account layout and is used to show all related Sales_History__c records based on the year of their Sale_Date__c. My options need to display all of the potential values for the year. For example, if the Account has sales in 2017 and 2016 then it should show those two values. If the Account only has sales in 2016 then it should only show 2016. Additionally, I would like to add an option for all. Any help would be greatly appreciated. Current code is listed below.

Controller

public class SalesHistoryOverviewController {
public Id AccountId { get; set; }

public String SelectedYear { get; set; }
public List<SelectOption> Options { get; set; }

public SalesHistoryOverviewController() {
    SelectedYear = '2017';

    Options = new List<SelectOption>();

    Options.add(new SelectOption('2017', '2017'));
    Options.add(new SelectOption('2016', '2016'));
    Options.add(new SelectOption('2015', '2015'));
    Options.add(new SelectOption('2014', '2014'));
    Options.add(new SelectOption('2013', '2013'));
    Options.add(new SelectOption('2012', '2012'));
    Options.add(new SelectOption('2011', '2011'));
}

public List<Sales_History__c> Sales_Histories {
    get{
        return [SELECT Id, Name, Account_Name__c, Invoice_Number__c, Sale_Date__c, Grand_Total__c, Account_Name_Text__c, Serial_Number__c FROM Sales_History__c WHERE Account_Name__c = :AccountId AND CALENDAR_YEAR(Sale_Date__c) = :Integer.valueOf(SelectedYear)];
    }
}

public PageReference Rerender() {
    return null; 
}
}

Component

<apex:component controller="SalesHistoryOverviewController">
<apex:attribute name="id_param" assignTo="{!AccountId}" description="This is my description" type="String" required="true"/>

<apex:outputPanel id="componentPanel">
    <apex:form>
        <apex:selectList value="{!SelectedYear}" multiselect="false" size="1">
            <apex:selectOptions value="{!Options}"/>
        </apex:selectList>
        &nbsp;&nbsp;
        <apex:commandButton value="Filter Records" action="{!Rerender}" rerender="componentPanel"/>
        &nbsp;&nbsp;
        <apex:outputLink value="{!URLFOR($Action.Sales_History__c.New)}" target="_blank" styleClass="btn" style="text-decoration:none;padding:4px;">New Sales History</apex:outputLink>

    </apex:form>

    <apex:form>
        <apex:pageBlock>
            <apex:pageBlockTable value="{!Sales_Histories}" var="s">
                <apex:column headerValue="Sales History Number">
                    <apex:outputLink value="/{!s.Id}" target="_blank">
                        <apex:outputText value="{!s.Name}">
                        </apex:outputText>
                    </apex:outputLink>
                </apex:column>
                <apex:column value="{!s.Serial_Number__c}"/>
                <apex:column value="{!s.Invoice_Number__c}"/>
                <apex:column value="{!s.Sale_Date__c}"/>
                <apex:column value="{!s.Grand_Total__c}"/>
                <apex:column headerValue="Account Name">
                    <apex:outputLink value="/{!s.Account_Name__c}" target="_blank">
                        <apex:outputText value="{!s.Account_Name_Text__c}">
                        </apex:outputText>
                    </apex:outputLink>
                </apex:column>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>

</apex:outputPanel>

  • Account has sales in 2017/2016, how do you decide that by sales history created date (or) Sale_Date__c ? – Rao Mar 8 '17 at 21:29
  • Sale Date being within one of those years – cmmoutes13 Mar 8 '17 at 21:30
  • Is sale date a picklist of year or just a date field? – Rao Mar 8 '17 at 21:31
  • It is just a date field. – cmmoutes13 Mar 8 '17 at 21:39
1

You should use aggregate queues here.

years = new List<SelectOption>();
for (AggregateResult aggregate : [
    SELECT CALENDAR_YEAR(Sale_Date__c) year
    FROM Service_History__c
    WHERE ...
]){
    String year = (String)aggregate.get ('year');
    years.add(new SelectOption(year, year));
}

You might also consider adding an Integer year property that does the conversion back to a year for you so you can merge the selected value into your SOQL. In this way, you can avoid running into a NullPointerException if you try to run the query when no year has been selected.

public String selectedYear { get; set; }
public Integer selectedYearFilter
{
    get
    {
        return (selectedYear == null) ? null : Integer.valueOf(selectedYear);
    }
}

Then in your WHERE clause:

WHERE Acccount_Name__c = :accountId AND CALENDAR_YEAR(Sale_Date__c) = :selectedYear
0

Update your Controller code to selectively populate the options with just the year from the sales date ( just the concept, not tested this code so there may be some code errors):

  public class SalesHistoryOverviewController {
    public Id AccountId { get; set; }

    public String SelectedYear { get; set; }
    public List<SelectOption> Options { get; set; }

    public SalesHistoryOverviewController() {
        SelectedYear = '2017';
//If you are trying here to get current year, better approach would be 
//String currentYear = String.valueof(System.Today().year());
        Options = new List<SelectOption>();
        List<Sales_History__c> sales = [SELECT Id, Name, Account_Name__c, Invoice_Number__c, Sale_Date__c, Grand_Total__c, Account_Name_Text__c, Serial_Number__c FROM Sales_History__c WHERE Account_Name__c = :AccountId AND CALENDAR_YEAR(Sale_Date__c) = :Integer.valueOf(SelectedYear)];
        for(Sales_history__c sale: sales) {
        Options.add(new SelectOption(sale.Sale_Date__c.year(), sale.Sale_Date__c.year()));
        }

    }

    public List<Sales_History__c> Sales_Histories {
        get{
            return sales;
        }
    }

    public PageReference Rerender() {
        return null; 
    }
    }
  • First error is on the return sales; line. It says that the variable does not exist? – cmmoutes13 Mar 8 '17 at 21:49
  • Nevermind I resolved that. It is saying now: Constructor not defined: [System.SelectOption].<Constructor>(Integer, Integer) on the line: Options.add(new SelectOption(sale.Sale_Date__c.year(), sale.Sale_Date__c.year())); – cmmoutes13 Mar 8 '17 at 22:00
  • You'll get dupes here. – Adrian Larson Mar 8 '17 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.