0

I would like to display data based on a picklist. The picklist display a list of Region and the user can select a Region from the picklist and we display its related data. Easy.

However I would like to add in the picklist a ALL parameter that will display the data of ALL the Regions without filtering.

Here I get the data:

@AuraEnabled(cacheable=true)
    public static List<AggregateResult>OwnerList(){
        List<AggregateResult> OwnerListMap = [SELECT Owner.Name from Opportunity GROUP BY Owner.Name];
        //List<String> OwnerListMap = [SELECT Owner.Name from Opportunity GROUP BY Owner.Name];
        return OwnerListMap;
    }

Based on the data I organise my picklist from the data and add a "All" for all data:

@wire(OwnerList)
    OwnerListData({error, data}){
        let tempArray = []
        let tempDefaultValueList = []
        if(data){  
            for(let key in data){
                tempArray.push({label:data[key], value:key}); 
                tempDefaultValueList.push(key);
            } 
            tempDefaultValueList.push({label:'All', value:'All'})
        }
        this.OwnerList = tempArray;
        console.log('Owner', tempArray);
        this.selectedOwner = tempDefaultValueList[0];
        }

And based on the data I would like to query my data but I do not know how to do it properly. I tried that way:

if(RegionId != 'All'){
            OpportunityWithApproval = [SELECT Name, id, StageName, Region__c,   Country__c, Owner.LastName, Owner.FirstName 
                                                   FROM Opportunity 
                                                   WHERE Id IN(SELECT Opportunity__c FROM Approval__c WHERE CALENDAR_YEAR(Date__c) = 2021 AND (Type__c = 'Investment_Committee' or Type__c = 'Pre_IC') AND Status__c != Null)
                                                   AND Region__c = : RegionId
                                                  ];
        } else{
            OpportunityWithApproval = [SELECT Name, id, StageName, Region__c,   Country__c, Owner.LastName, Owner.FirstName 
                                                   FROM Opportunity 
                                                   WHERE Id IN(SELECT Opportunity__c FROM Approval__c WHERE CALENDAR_YEAR(Date__c) = 2021 AND (Type__c = 'Investment_Committee' or Type__c = 'Pre_IC') AND Status__c != Null)
                                                  ];
        }   

Any idea ? thx you

1

Your attempt looks reasonable. Basically, you are using the "AND Region__c = : RegionId" if RegionId <> 'All'. Otherwise, you are not using that condition. What is the problem or error you are seeing?

If 'RegionId' is really declared as an 'id' type, it will not accept the string 'All'. My habit would be to use a string on the method signature, and then cast it to an id if it is not blank and not 'All'. The casting helps ensure the data is really a valid id.

3
  • no everything is working .. but I want to add other pick list .. if I go with if statement .. it become impossible, I want to be able to add a pick list for region .. one for market and one owner.. imagine if the user want all region for one specific market and owner .. it is already 9 if ..
    – Ben2pop
    Jun 22 '21 at 14:27
  • how do you add string to the query ?
    – Ben2pop
    Jun 22 '21 at 14:43
  • for that many conditions, you should consider using a string for the query. See: developer.salesforce.com/docs/atlas.en-us.apexcode.meta/… For example: ``` string basicQuery = "select ...fields...FROM Opportunity " string ownerCondition; //code for owner string regionCondition //code for region string marketCondition; //code for market string fullQuery = basicQuery + ownerCondition + regionCondition + marketCondition; List<opportunity> OpportunityWithApproval = Database.query(fullQuery); ```
    – SeanW
    Jun 22 '21 at 14:44
0

Best Solution fits for this is Dynamic soql query.

String queryStr =   'SELECT Name, id, StageName, Region__c,   Country__c, Owner.LastName, Owner.FirstName'+ 
                                               ' FROM Opportunity'+ 
                                              ' WHERE Id IN (SELECT Opportunity__c FROM Approval__c WHERE CALENDAR_YEAR(Date__c) = 2021 AND (Type__c = \'Investment_Committee\' or Type__c = \'Pre_IC\') AND Status__c != Null)';

if(RegionId != 'All'){
     queryStr += ' AND Region__c = : RegionId';
}

List<Opportunity> sobjList = Database.query(queryStr);  

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.