2

I have a formula in Excel which I'd like to replicate in an Apex Class. The below formula is in cell E3.

enter image description here

=INDEX(Sheet2!$B$2:$F$4,MATCH('Sheet1 (2)'!B3,Sheet2!$B$3:$B$4,0)+1,MATCH('Sheet1 (2)'!D3,Sheet2!$C$2:$F$2,0)+1)

which retrieves values from the below matrix in Sheet2

enter image description here

The first MATCH() retrieves the location of the matching Country (row) and the second retrieves the location of the matching Region (column). Index then returns the value in the cell based on the combination of the row & column locations.

I'll retrieve the Country & Settlement Region values earlier in my Class which I can obviously use as my keys.

I know that I could store a list of Countries in one Map, along with an string / integer, do the same for Settlement Regions and then us If statements to return a different value for each possible combination of Countries and Settlement Regions.

The countryRef & regionRef values will actually be stored in a Custom Setting but I've populated the Maps below, to keep the example as simple as possible

String country;
String region;

Map<String,String> countryRef = new Map<String,String>();
Map<String,String> regionRef = new Map<String,String>();
Map<Id,Integer> records = new Map<Id,Integer>();

country.put('UK','a');
country.put('US','b');
region.put('UK','a');
region.put('US','b');
region.put('EU','c');
region.put('Outside EU','d');

Integer rtndCountry = country.get('UK');
Integer rtndRegion = region.get('Outside EU');

if (rtndCountry = 'a' && rtndRegion = 'd'){
    records.put(Id,4);
} else if (//etc.){

}

But using the If statements to manage the different country & region combinations seems inefficient and awkward to maintain, is there an alternative Apex Class (if that's the correct term) which I could use in place of Excel's INDEX( instead?

Is there an alternative approach that I could adopt?

  • To me, this seems a bit convoluted. Wouldn't it make sense to have a reference table that has three columns, like your first excel spreadsheet? You can easily set up all the potential values, then select the 'return' based on the two input values. I mean, why do you need 'Sheet2'? – Nick Cook Dec 16 '15 at 14:10
  • @NickCook thanks for the suggestion, if I created the three columns, I'd need to check the value in each column and then use an If statement to return a particular result. That's basically what I've had to do in code in my question and it's the If statement that I'm trying to get away from. Index allows you to retrieve a value from the matrix without having the use the Ifs.. – Alex S Dec 16 '15 at 14:13
  • nah. Custom Metadata is your friend.... I'll post an answer that I think might help. – Nick Cook Dec 16 '15 at 14:14
3

This is a great example of using the new Custom Metadata feature.

I'd set this up with a new Custom Metadata object: Country_Setting__mdt

Add three fields:

  1. Country
  2. Settlement Region
  3. Return

You then create data for each Country/Settlement Region combo.
i.e. as per your first sheet in the question.

In your code, you do a basic SOQL query to find the correct Return value. Remember that SOQL queries against Custom Metadata don't count against your limit.

i.e.

Integer return = [SELECT Return__c FROM Country_Setting__mdt WHERE Country__c = :var1 AND Settlement_Region__c = :var2][0].Return__c;

where var1 and var2 are the relevant values you want.

I always forget the exact syntax with these queries. You may need to add it to a list, check that it has exactly one record returned, then use that record to get the Return value from. You'd probably want to add some error handling around this query.

I tend to create a Custom Metadata Service layer that abstracts this out a bit, such as the following:

public class CustomMetadataService {

    public class CustomMetadataServiceException extends Exception {}

    public static Country_Setting__mdt getCountrySetting(String country, String settlementRegion)
    {
        List<Country_Setting__mdt> countrySettings = [SELECT Return__c FROM Country_Setting__mdt WHERE Country__c = :country AND Settlement_Region__c = :settlementRegion];

        if(countrySettings.isEmpty())
        {
            throw new CustomMetadataServiceException('No country setting values matches the provided values: ' + country + ', ' + settlementRegion);
        }
        else if(countrySettings.size() > 1)
        {
            throw new CustomMetadataServiceException('More than one country setting values matches the provided values: ' + country + ', ' + settlementRegion);
        }

        return countrySettings[0];
    }
}

Then you just need to call CustomMetadataService.getCountrySetting(country, settlementRegion) to get the setting.

By doing it this way, you just add extra Country/Settlement Region combos to the Custom Metadata when there are new values to work with, all without touching your code.

Not 100% sure if this is what you're after, so please comment either way.

  • Thanks for this, that does sound good, a lot flexible than If statements. It's probably going to take me a while to implement this solution (& check that it works for me), as this will be my first time working with Custom Metadata. If I can implement this to create a point & click interface to work with, it'll obviously be a lot easier. But either way, I will come get back to you, to let you know how I got on & accept the answer once I'm sorted. – Alex S Dec 16 '15 at 14:34
  • Nice, N.B. ` if(countrySettings == null` will never happen; SOQL will always return a list as coded here. – cropredy Dec 16 '15 at 21:22
  • True. I think the code I took this from could have had a null value but I didn't remove it. – Nick Cook Dec 16 '15 at 22:38
  • I'm just working my way through your solution, it's looking good. But I have a question (it's possibly not one that can be answered in the limited space here) - what's the purpose of extending the method with CustomMetadataServiceException? I've had a look at the documentation & it doesn't seem to describe an application for extensions that's relevant in this scenario. I'm not doubting that there is one, I'm just hoping that you can share some insight. – Alex S Dec 24 '15 at 0:54

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.