1

I have to write a query where all conditions should match at the same time.

Account has 3 custom fields: Country__c, State__c, and Customer__c (look up to Customer_Account__c object).

The Customer_Account__c object also has a 2 custom fields (Country__c and State__c).

Whenever I create an Account record, it will first check if any Customer_Account__c record exists in our database based on country and state. If yes, then map that record (Customer_Account__c) to Account__c.Customer__c . If not, create a new one.

I can deal with mapping Customer_Account__c to Account. But I am having difficulty with how to query Customer_Account__c record with matching Account field values.

Example:

If an Account has Country__c = 'Australia' and State__c = 'Brisbane', then it will query Customer_Account__c records where Country__c = 'Australia' and State__c = 'Brisbane'.

Both conditions should match together. And of course we are bulkifying the trigger.

3

You sort of have two questions, how to match multiple criteria and how to match against a collection.

The first is quite straightforward, use the AND clause:

SELECT Id FROM Customer_Account__c
WHERE Country__c = 'Australia'
AND State__c = 'Brisbane'

The second is also fairly simple. Use the IN clause. I would recommend using a Set and also make sure it does not contain null so as to avoid a TableScan. You would also get the aforementioned TableScan if the Set is empty, so don't run the query in that case.

Set<String> countries = new Set<String>();
Set<String> states = new Set<String>();
for (Account account : accounts)
{
    if (account.Country__c != null) countries.add(account.Country__c);
    if (account.State__c != null) states.add(account.State__c);
}
if (countries.isEmpty() || states.isEmpty() return new List<Account>();
return [
    SELECT Id FROM Customer_Account__c
    WHERE Country__c IN :countries
    AND State__c IN :states
]

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.