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

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.