Compare two Unique set of records and pull the ID of the Missing Record

Question

On Account I have two objects Monthly and Yearly which are on Master-Detail relationship with Account.

Monthly has records captured for each month. For example USA, GBR, IND ETC

Yearly has Once in a Year records captured USA, GBR, IND ETC

We have identified that Yearly records are which we are pulling out of our DB are mismatching and some additional countries are being added, for example

Monthly has - USA, GBR, IND Yearly should have - USA, GBR, IND But we are having - USA, GBR, IND and AUS which is wrong.

We would want to find out the record which has AUS and then delete or resend the information.

Unique fields in Monthly and Yearly are

distributor__c = Master detail relation ship with Account

AC_Sponsor_Key__c = Country+distributor ID which is Unique in both objects

Please help me with a solution on how to tackle this situation

Answer 1

You didn't specify whether you're looking for solution in Salesforce or Postgres ;)

So "yearly" (probably should be called "annual") related list has more records than "monthly"?

You could have 2 text fields on Account that will contain values like "GBR;IND;USA" and then simply compare the two (sorted for best results). You could be populating them with a trigger or directly from your source system.

Failing that - this should give you some ideas. You'll probably have to put some filter on Accounts to limit the results set. Or make a batch job out of this code snippet?

List<Account> accs = [SELECT Id, Name
    FROM Account
    WHERE Id IN (SELECT Distributor__c FROM Monthly__c)
        AND Id IN (SELECT Distributor__c FROM Yearly__c)
    ORDER BY Name
    LIMIT 1000];

Set<String> keys = new Set<String>();

for(Monthly__c m : [SELECT AC_Sponsor_Key__c
    FROM Monthly__c
    WHERE Distributor__c IN :accs AND AC_Sponsor_Key__c != null]){
    keys.add(m.AC_Sponsor_Key__c);
}

for(Yearly__c y : [SELECT Id, Distributor__c, AC_Sponsor_Key__c
    FROM Yearly__c
    WHERE Distributor__c IN :accs AND NOT IN :keys
    ORDER BY Distributor__c, AC_Sponsor_Key__c]){
    System.debug(y);
}

---

Alternatively you could try something like this (pure SOQL / report, no Apex). You'd have to run it for each country though.

SELECT Id, Name,
    (SELECT Id FROM Yearlys__r WHERE Country__c = 'AUS')
FROM Account
WHERE Id IN (SELECT Distributor__c FROM Yearly__c WHERE Country__c = 'AUS')
AND Id NOT IN (SELECT Distributor__c FROM Monthly__c WHERE Country__c = 'AUS')

SELECT Id, Distributor__c, Distributor__r.Name
FROM Yearly__c
WHERE Country__c = 'AUS'
AND Distributor__c NOT IN (SELECT Distributor__c FROM Monthly__c WHERE Country__c = 'AUS')

---

Going forward you could look into some protection, "you can't insert Yearly with this country because there's no matching Monthly". You could do it with before insert trigger but there's a code-free solution too. Check the VLOOKUP function and how it can be used in validation rules. But it'd require you to have unique matching Name value for Monthly records and I don't know what you have there, might be some useless autonumber...