Imitate Excel Index & Match Formula to Retrieve Value From Matrix

Question

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

=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

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?

Answer 1

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.