0

I have a picklist in picklist_active_inactive__c with Values and Inactive Values. The problem is given by I have to get by SOQL some values, and the picklist_active_inactive__c records have inactive an active values.

Example:

  • Object: picklist_active_inactive__c

  • Field: picklist__c

  • Values of picklist__c

    • Active:

      • Values: value0

      • API Name: value01

    • Inactive:

      • Values: value0
      • API Name: value0

I need count picklist_active_inactive__c that have the value0 value.

I tried

SELECT toLabel(picklist__c) a, count(ID) b
     FROM picklist_active_inactive__c
     WHERE toLabel(picklist__c) = 'value0'
     GROUP BY picklist__c

And it return AggregateResult:{b=1, a=value0}, AggregateResult:{b=2, a=value0}

I need only a record with AggregateResult:{b=3, a=value0}

GROUP BY toLabel(picklist__c) does not work, it said: Only date aggregate functions are allowed as grouping expressions: toLabel

EDITED: I have code to aggregate the results in a map, that works. I only whant to know if it is posible to do it in only one SOQL query.

2

When you do GROUP BY picklist__c, the grouping that is being applied is on the API value of the picklist entry, rather than the label. That's why you end up with two AggregateResults. The database is actually storing the API values.

As you've observed, the toLabel() function isn't a legal grouping.

You can post-process these AggregateResult objects in Apex to check for duplicated labels. Something like this should work:

Map<String, Integer> valueCounts = new Map<String, Integer>();

for (AggregateResult a : listOfAggregateResults) {
    String picklistValue = a.get('a');
    String recordCount = a.get('b');

    if (valueCounts.containsKey(picklistValue) {
        valueCounts.put(picklistValue, valueCounts.get(picklistValue) + recordCount);
    } else {
        valueCounts.put(picklistValue, recordCount);
    }

Then valueCounts has your global total by picklist value.

  • Hello, i have a similar code that works. I only whant to know if it is posible to do it in only one SOQL query. Thanks – Pestana Apr 19 '18 at 11:20
  • No, I don't believe it is possible in a single SOQL query. This would be helpful context to include in your question. – David Reed Apr 19 '18 at 11:25

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.