How can I filter through object using 2 separate fields as unique identifiers?

Question

I have a custom object (we'll call it RS__c), and it theoretically has a 1 to 1 relationship to OpportunityLineItems (it is not possible to create a reference field directly to create this relationship). So for the time being I have a lookup reference to the parent Opportunity object AND I have a simple text field that stores the OpportunityLineItem.Id

The goal is to keep this custom object records in sync with their respective OpportunityLineItem.

At the moment I can easily INSERT a new RS__c line item when I INSERT an OpportunityLineItem, and even UPDATING is not difficult, BUT the problem occurs when we have an orphan line item. In order to sync Opportunities with QuickBooks we often need to delete the Opportunity and have it reinserted through 3rd party software. Naturally because of the lookup relationship between the RS__c and Opportunity, when this happens the relationship is severed with the Opportunity field gets cleared.

I have a another custom field that is unique to the quickbooks invoice, so when the Opportunity is reinserted I can determine the new Opportunity ID just fine, but if that Opportunity has 3 OpportunityLineItems then I cannot distinguish which RS__c line item each one belongs to. Once I've narrowed it down to these 3 orphan line items I would then need to use the Product.ID to determine which 'OpportunityLineItembelongs to its respectiveRS__c` line item.

I've considered complex mapping solutions but I'm not sure how I would create such an collection. For example something with this structure seems like something I could iterate through to maintain the relationships I need to reconcile my problem.

Map<QB.ID,Map<Product.ID,RS__c>> 

Meanwhile, I've been attempting something like this, but am not sure how I would write the conditional statement to determine an orphan item based on QB.ID and Product.ID

/* If OLI exist, then update RS Line Item */
if(rsLineItemByOpportunityLineItem.get(oli.Id) != NULL){
    rsLineItemByOpportunityLineItem.get(oli.Id).Opportunity__c = opp.Id;
    rsLineItemByOpportunityLineItem.get(oli.Id).Quickbooks_Id__c = opp.QB_Id__c;
    rsLineItemByOpportunityLineItem.get(oli.Id).OpportunityLineItem_Id__c = oli.Id;
    rsLineItemByOpportunityLineItem.get(oli.Id).Product__c = oli.Product2Id;
    rs_items.add(rsLineItemByOpportunityLineItem.get(oli.Id));
    System.debug('::ADD EXISTING LINE ITEM:: '+rsLineItemByOpportunityLineItem.get(oli.Id));
} 
/* If OLI does NOT exist, then check for orphan line item */
else if (){
} 
/* If OLI exist, then update RS Line Item */
else {
    Refrigerant_Sale__c rs_item = new Refrigerant_Sale__c();
    rs_item.Opportunity__c = opp.Id;
    rs_item.Quickbooks_Id__c = opp.QB_Id__c; // Required field
    rs_item.OpportunityLineItem_Id__c = oli.Id;
    rs_item.Product__c = oli.Product2Id;
    rs_items.add(rs_item);
    System.debug('::ADD NEW LINE ITEM:: '+rs_item);
}

Just had a new thought towards a solution as I explained this... what if I create a formula or custom text field that uniquely joins the QB.ID and Product.ID as a means for having 1 unique identifier to filter by? ...

Well that's where I'm at with this, appreciate any advice that could help resolve this.

Answer 1

I DID IT!

As suspected towards the end of my OP, I created a separate formula field, (i.e. RS__c.Sync_Id__c), which concatenated the QB Id and the Product Id (18 character version). Once I had that it was easy to create a map that I could actually use to index through my logic like so:

/**
 * Map all active Refrigerant Sale line items based on unique Sync ID
 * @return Map<Sync_Id__c, Refrigerant_Sale__c>
 */
Map<String, RS__c> rsLineItemBySyncId = new Map<String, RS__c>();
for(RS__c rs : rsLineItemMap.values()){
    rsLineItemBySyncId.put(rs.Sync_Id__c, rs);
}

Now when I iterate through my Opportunity and OpportunityLineItems I can filter through existing RS line items like so:

for(Opportunity opp : invoices){
    for(OpportunityLineItem oli : opportunityLineItemsByOpportunityId.get(opp.Id)){
        /* Populate Sync Id to verify orphan line items */
        String sync_id = opp.QB_Id__c+'-'+oli.Product2Id;

        /* Check for existing RS line items */
        if(rsLineItemBySyncId.get(sync_id) != NULL){
            // Update existing RS line item
        } else {
            // Add new RS line item
        }
    }
}

Answer 2

Also, Possibly with External Id and Salesforce ID we can achieve your requirement. Check once...