1

What happened: someone incorrectly merged two contacts. A host of related objects had lookups populated to these two contacts. When the contacts merged, all those objects were updated to all point to the same single contact.

When we restored the deleted contact, the lookup values on the related objects did not update their former values. So a bunch of records that should be pointing to the recently restored contact, are still pointing to the 'merged' contact.

The problem: we don't know all the objects/records that had their contact lookup updated due to the merge.

The goal: Would like to identify all records that have a lookup field to the contact object, which have a value in that lookup of the 'merged' contact. This way we can review these records to see which should be updated to point to the restored contact instead.

SOSL seemed like a great way to solution this... but it appears there are significant limitations on querying values in lookup fields with SOSL. Is there a way to use SOSL to get these results? Is there an alternative solution?

Improve this question

1 Answer 1

Reset to default
3

Unfortunately, doing that in SOSL isn't going to work very easily. The best alternative for the future would be to have periodic backups for issues like this.

However, doing this in Apex is trivial:

Id recordIdToFind = '0031T00004MIV3n';
sObject[] potentialRecords = new sObject[0];
for(ChildRelationship rel: sObjectType.Contact.getChildRelationships()) {
    sObjectField field = rel.getField();
    sObjectType child = rel.getChildSObject();
    DescribeFieldResult fieldDescribe = field.getDescribe();
    try {
    if(fieldDescribe.isAccessible() && fieldDescribe.isFilterable() && child.getDescribe().isQueryable()) {
        potentialRecords.addAll(
            Database.query('SELECT '+field+' FROM '+child+' WHERE '+field+' = :recordIdToFind')
        );
    }
    } catch(Exception e) {
        
    }
}
System.debug('Here is what we found:');
System.debug(JSON.serializePretty(potentialRecords));

Just run this in Execute Anonymous and you'll get all your results.

Note that SOQL inside a loop is generally considered a Bad Idea. However, in this one case, it may be one of your best options without resorting to far more complicated code.

Improve this answer
3
  • may want to include checking if child object is queryable first rel.getChildSObject().getDescribe().isQueryable() as you may get certain child objects that won't be queryable
    – Kris Goncalves
    Mar 16, 2022 at 22:30
  • 1
    @KrisGoncalves Indeed. Thanks for the feedback. I actually ran into several problems, such as "field is not filterable" and "This query is not supported on the External Object Name object." This new version should just about do it, though at some cost of possibly missing some unqueryable records.
    – sfdcfox
    Mar 16, 2022 at 22:47
  • Thank you @sfdxfox! The method name had me worried that this would only return master-detail type relationships, but looking through the results of your script I see regular lookups are returned as well.
    – smohyee
    Mar 17, 2022 at 16:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .