I am trying to figure out how to write this as a better query.

Current thinking: (each field has a count of records for that State)

Select AL__c,AK__c, etc FROM Account WHERE...;

and then somehow transform that into a list where each field (label or api name) becomes a value.

Want I want in the end is a two dimensional set with all my state values in it: Example:

State, Account
AL, 200
AK, 15

This will be shown on a visualforce page as a table.

Edit : Idea! How about something like this? I am getting a "Initial term of field expression must be a concreaet sObject: list" error.

 selectedAccount = '001S000000qTmDxIAK';
    map <string,string>     mapStates   = new map<string,string>();
    list <Account> accStates = new list<Account>(
    [Select AL__c,AK__c FROM Account WHERE ID = :selectedAccount]);
    mapStates.add('AL', accStates.AL__c);
mapStates.add('AK', accStates.AK__c);
  • 1
    Like a Map<String, List<Account>>()?
    – EricSSH
    Feb 13, 2017 at 18:05
  • I am just showing the state counts for one particular account. So I don't need the state name as part of it. I just need the state name (abbreviation) and the count from the field. I could write some really bad looping soql and get there but I don't want to do that.
    – Matt M
    Feb 13, 2017 at 18:08
  • I keep getting this thought in my head "Instead of doing a ForEach on the Account, I want to do a ForEach for the 50 state fields". Is there a way to do something like that?
    – Matt M
    Feb 13, 2017 at 18:16
  • Sure, but that needs to be put, not add
    – EricSSH
    Feb 13, 2017 at 18:27

1 Answer 1


SOQL isn't the right tool for the job. In fact, if you use a **Field Set, you don't need to write any Apex at all!

<apex:page standardController="Account">
        <apex:pageBlockTable value="{!$ObjectType.Account.FieldSets.State_Fields}" var="field">
            <apex:column headerValue="State" value="{!$ObjectType.Account.fields[field].label}" />
            <apex:column headerValue="Account" value="{!Account[field]}" />

If you really want this structure in Apex, you can still use it in Apex:

Map<String, Decimal> states = new Map<String, Decimal>();
for (FieldSetMember member : SObjectType.Account.FieldSets.State_Fields)
    states.put(member.getLabel(), accountRecord.get(member.getFieldPath());

The simplest way to get these variables is through the StandardController, whether you're currently doing so or not:

public Account record { get; private set; }
public MyExtension(ApexPages.StandardController controller)
    List<String> fields = new List<String>();
    for (FieldSetMember member : SObjectType.Account.FieldSets.State_Fields)
    if (!Test.isRunningTest()) controller.addFields(fields);
    record = (Account)controller.getRecord();

Of course, you can query for it yourself if you wish. You would need to pull the Id in through page parameters most likely:

public static final String ID_PARAM = 'Id';
public Account record { get; private set; }
public MyController()
    Id recordId = ApexPages.currentPage().parameters.get(ID_PARAM);
    if (recordId == null) return;

    // same fields collection
    record = (Account)Database.query(
        'SELECT ' + String.join(fields, ',') + ' FROM Account WHERE Id = :recordId'
  • I am leaving this answer marked as best but I still don't understand this code and I get errors with the FOR statement. I am going to use the other method I found using the suggestion by @EricSSH to accomplish my goal.
    – Matt M
    Feb 14, 2017 at 14:48
  • Are you trying to display a single Account or a collection? Also feel free to ask additional questions to clarify. I can't really help you when all you tell me is "I get errors".
    – Adrian Larson
    Feb 14, 2017 at 14:53
  • I am not trying to display the account at all. Here is the overall goal explained as best as I can. We have Enrollers (aka salespeople) who are licensed for individual states. I am creating an email report that conveys this statement in table form. "Enroller Bob was added to account ABC. This account has employees in states: KS, KY, GA. Enroller Bob is not licensed for GA. "
    – Matt M
    Feb 14, 2017 at 15:03
  • Object Structure: Account > Enroller > License. | The Account page has states fields that are updated daily by batch code. The Enroller has license records for each state.
    – Matt M
    Feb 14, 2017 at 15:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .