0

This is my first intro with custom metadata and also dynamic soql.

I am trying to set it up so that users can set up certain fields that they would like to use for the apex class calculation via Custom Metadata.

My question is around the formatting of the SOQL query which uses aggregate functions specifically the following piece: 'SELECT OpportunityId, Product2.Family, Sum(salesPriceProxy) salesPrice, Sum(listPriceProxy) listPrice ' -> I'm getting a bit lost with the + and \ here.

Please see the larger snippet below:

List<Product_Family_Discount_Setting__mdt> defaultSetting =[
            SELECT Sales_Price_Field__c, List_Price_Field__c
            FROM Product_Family_Discount_Setting__mdt
            LIMIT 1
        ];

        System.debug('These are the Default Settings:::::::::'+defaultSetting); 
        System.debug('This is the first element of the list::::::'+defaultSetting[0]);
        
        String salesPriceProxy = (String)defaultSetting[0].get('Sales_Price_Field__c');
        String listPriceProxy = (String)defaultSetting[0].get('List_Price_Field__c');

        System.debug('salesPrice field ==========='+ salesPriceProxy);
        System.debug('listPrice field ============'+ listPriceProxy);

        String query = 
        'SELECT OpportunityId, Product2.Family, Sum(salesPriceProxy) salesPrice, Sum(listPriceProxy) listPrice '+
        'FROM OpportunityLineItem '+
        'WHERE OpportunityId IN: opportunityIdFromTriggerMap '+
        'GROUP BY OpportunityId, Product2.Family';

        List<AggregateResult> oppProdGroupedByFam = Database.query(query);

All feedback is very much appreciated!

1 Answer 1

2

You can make your life easier (in my opinion, of course; perceptions of readability may differ) by using string formatting to inject the dynamic elements of your query, rather than adding more concatenation.

Note that you cannot use an Apex variable name (or a bind, for that matter) in the SELECT clause of your query; you must construct it yourself.

String query = String.format(
    'SELECT OpportunityId, Product2.Family, SUM({0}) salesPrice, SUM({1}) listPrice ' +
    'FROM OpportunityLineItem ' +
    'WHERE OpportunityId IN: opportunityIdFromTriggerMap ' +
    'GROUP BY OpportunityId, Product2.Family',
    new List<String>{salesPriceProxy, listPriceProxy}
);

3
  • Thank you for the response. The static query was my original solution but when I try to deploy this code to sandbox, it's saying that the salesPriceProxy is an invalid field. Within this scenario, the field being retrieved from the relevant Custom Metadata is the UnitPrice field on Opportunity Products
    – Tom G
    Mar 28, 2021 at 14:44
  • @TomG I apologize, I misread your question. I've revised my answer accordingly.
    – David Reed
    Mar 28, 2021 at 14:54
  • Thank you for the feedback, solution and intro to the string formatting method!
    – Tom G
    Mar 28, 2021 at 15:10

You must log in to answer this question.

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