Find record based on two (or more) variables

Question

I have a custom object Target__c (we can't use Quota's to track targets in our org) which I will link to Opportunities through a junction object TargetxOpp__c. There are two Master Detail fields on the TargetxOpp records; Target__c & Opportunity__c.

I need to create a trigger which inserts a new TargetxOpp record whenever an Opportunity is created and populate the Target & Opportunity fields.

In order to find the correct Target record to relate to the Opportunity, my trigger needs to find the Target record where the month of the date in the Month_End_Date__c matches the month of the Opportunity Close Date and the User Id in the Salesperson__c lookup field matches the Opportunity Owner Id.

This would be simple if I could create a list of Target records and then run an SOQL query for each Opportunity, to find the matching record. But obviously I'd hit governor limits, if there was a mass insert of Opportunities.

I know how to fetch a value (i.e. the Id) from a map, when using one value as the criteria but I can't work out how to use two values, as maps can only have two arguments.
Then, having found the matching target records, I need to know how to link the correct Target to the matching Opportunity, in the code.

I'd also need to check that the year of the Month_End_Date__c field matches the year of the Opportunity Close Date field but I'm assuming that once I know how to use two values to look up the record, I can use the same process to factor in the third variable.

Here's what I have so far..

trigger OpportunityTarget on Opportunity (after insert) {

    List<WEID__c> weids = WEID__c.getall().values();
    Set<Id> validRecordTypeIds = new Set<Id>();

    // add Opportunity Record Type Ids from Custom Setting to list of valid Ids      
    for (WEID__c weid: weids) {
        try {
            validRecordTypeIds.add(weid.WEOppId__c);
        } catch (System.StringException e) {
            System.debug(
                System.LoggingLevel.ERROR,
                'Invalid Record Type Id ' + weid.WEOppId__c
            );
        }
    }

    // create a list of Opportunities with valid Record Types
    List<Opportunity> oppIds = new List<Opportunity>();
    for (Opportunity o : Trigger.new){

        if(validRecordTypeIds.contains(o.RecordTypeId))
        {
            oppIds.add(o);
            system.debug('oppIds size is ' + oppIds.size());
        }      
    }
    if(oppIds.isEmpty() == true){
        system.debug('No Opportunities with a valid Record Type Id were found');
        return;
    }
    else{

        // find Targets & link to Opportunities

        List<TargetxOpp__c> newTargets = new List <TargetxOpp__c>();
        for(Opportunity opp : oppIds){

            TargetxOpp__c t = new TargetxOpp__c();
            Opportunity__c = 
            Target__c = 

        }
    }
}

EDIT

Adding a Key field, as suggested by Keith below, isn't an ideal fix because we've reached the 800 custom field limit on Opportunities in our org.

Answer 1

One solution is to use the technique illustrated in this answer. It amounts to adding a formula field to the objects that you are relating to each other that combines the various fields. You can then use that key in maps or in SOQL where Key__c in :keys and so limit the work you do to the actual permutations of those fields.

On Opportunity, the Key__c formula would be:

OwnerId + '-' + TEXT(MONTH(CloseDate)) + '-' + TEXT(YEAR(CloseDate))

and on Target__c the Key__c formula would be:

Salesperson__c + '-' + TEXT(MONTH(Month_End_Date__c)) + '-' + TEXT(YEAR(Month_End_Date__c))

If the data volumes are large, you can make a support request to have indexes added to these formula fields - see Force.com Formula Fields, Indexes, and Performance Gotchas.