Help with Field Formula

Question

Not sure if this is entirely possible. I have been asked to create a checkbox field on the Account object. Below is the SOQL query that would list all accounts that have Opportunities that are 'Closed Won' with the Product Family 'VMS'. Basically, the Field Formula would do a lookup similar to below query and be marked true if the Account has any Opportunities that 'Closed Won' with the Product Family, 'VMS'.

My second option would be to create a custom apex page maybe have a dropdown to select Product Family to filter the query and then return a list.

Select AccountID, Account.Name 
From Opportunity
WHERE Id IN 
(Select OpportunityId From OpportunityLineItem WHERE Product_Family__c='VMS')
AND StageName='Closed Won'
GROUP BY AccountID, Account.Name 
ORDER BY Account.Name

Not finished and can use some cleanup of the code, but using the wrapper class controller, I was able to accomplish what I needed in the short term. I need to add features later.

public class wrapperClassController {

public List<aAccount> accountList {get; set;}   
public List<Opportunity> oppt {get; set;}
public String selected {get; set;}

public List<aAccount> getFilterAccounts(String option) {

    if(selected == 'VEMS') {       
        Map<Id, Opportunity> opptMap = new Map<Id, Opportunity>([Select ID, AccountID From Opportunity o WHERE Id IN (Select OpportunityId From OpportunityLineItem WHERE Product_Family__c LIKE 'VEMS%') AND StageName='Closed Won']);
        oppt = opptMap.values();
    }
    else if(selected == 'Renewal') {
        Map<Id, Opportunity> opptMap = new Map<Id, Opportunity>([Select ID, AccountID From Opportunity o WHERE Id IN (Select OpportunityId From OpportunityLineItem WHERE Product_Family__C='Renewal') AND StageName='Closed Won']);
        oppt = opptMap.values();
    }

    option = selected;

    if (option == 'None' || option == null) {
        return null;
    }

    else {     
        Set<Id> accountSet = new Set<Id>();
        for(Opportunity o: oppt) {
            accountSet.Add(o.AccountID);
        }

        if(accountList == null) {
            accountList = new List<aAccount>();
            for(Account a: [SELECT Id, Name FROM Account WHERE ID in:accountSet LIMIT 10]) {
                accountList.add(new aAccount(a));
            }   
        }
        return accountList;
    }
}

public List<aAccount> getAccounts() {
    return getFilterAccounts(selected);
}

public PageReference refreshSelected() {
    accountList = null;
    getAccounts();  
    return null;        
}

public PageReference processSelected() {

    List<Account> selectedAccounts = new List<Account>();
    for(aAccount aAcct: getAccounts()) {
        if(aAcct.selected == true) {
            selectedAccounts.add(aAcct.acct);
        }
    }

    System.debug('These are the selected Accounts...');
    for(Account acct: selectedAccounts) {
        system.debug(acct);
    }
    accountList=null;
    return null;
}

public class aAccount {

    public Account acct {get; set;}
    public Boolean selected {get; set;}

    public aAccount(Account a) {
        acct = a;
        selected = false;
    }
}
}

Then for the very basic apex page.

<apex:page controller="wrapperClassController">
<apex:form >
 <apex:selectList id="selected_list" value="{!selected}" required="false" size="1">
        <apex:selectOption itemvalue="None" itemLabel="--None--"/>
        <apex:selectOption itemvalue="VEMS" itemLabel="VEMS"/>
        <apex:selectOption itemvalue="Renewal" itemLabel="Renewal"/>
        <apex:actionSupport event="onchange" action="{!refreshSelected}"/>
    </apex:selectList>
    <apex:pageBlock >
        <apex:pageBlockButtons >
            <apex:commandButton value="Process Selected" action="{!processSelected}" rerender="table"/>
        </apex:pageBlockButtons>
        <apex:pageBlockTable value="{!accounts}" var="a" id="table">
            <apex:column >
                <apex:inputCheckbox value="{!a.selected}"/>
            </apex:column>
            <apex:column value="{!a.acct.Name}" />
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:form>

Answer 1

An object's page layout gives you a choice of what fields to display from related lists, but there's no way to chose which records will show up in the list. I think you'll need to go with your second option.

What you need is a Visualforce page that uses a wrapper class to gather the specific records (and fields) that you want to see. Here's a link to one example of such a page:

http://wiki.developerforce.com/page/Wrapper_Class

I'm skipping some detail here, but you'd pass the picklist info from the page to the controller like so:

public String SitePref1 {get;set;}     //apex code in controller
List <SiteProgram__c> Sitelist;

and getting that list would be something like this:

Sitelist = [Select Name,Id From SiteProgram__c Where Id in :keys];

Visualforce code:

<apex:outputlabel value="Site Preference1:" for="cmbSitePref1"/>           
<apex:selectList id="cmbSitePref1" value="{!SitePref1}" size="1" >
<apex:selectOptions value="{!SiteProgramsList}"/>
</apex:selectList>

Answer 2

You can have a checkbox field (say 'Won VMS') on Opportunity. A workflow on the Opportunity Line Item sets the value to true on the Opportunity if the OpportunityLineItem.PriceBookEntry.Product2.ProductFamily = 'VMS' and Opportunity.isWon

Then a roll-up summary on the Account which has a count of Opportunity records where the checkbox field Opportunity.Won_VMS__c = true

If the count is greater than 0, then the Account has at least one Opportunity which meets the criteria.

The caveat however is that if someone changes the OpportunityLineItem to be non VMS or the Opportunity changes to a non-Won stage, then you will need workflows to unset the checkbox field. A trigger might be more efficient.