Help with Visualforce Options List

Question

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>

Answer 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

Answer 2

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; 
    }
    }