[SalesForce] Remove lookup value once selected in a record

If I select a value on a lookup field on a particular record, is there a way that this selected lookup value gets hidden and does not display in another record? Lets say I have an object Object1__c which has a lookup field (Object2__c). Object2__c has 3 records "A, B and C". Now I create a new record on Object1__c and select value "A" on lookup field. Now when I create a 2nd record on Object1__c , the lookup field should only give me the option to select either B or C. not A. Is this poosible?

Best Answer

Lookup fields such as your Object1__c.Object2__c field can have a lookup filter defined. To exclude the already selected Object2__c instances in such a filter, you then need say a checkbox field Object2__c.InUse__c to use in the filter expression. A trigger can populate that checkbox field on insert/update/delete/undelete of Object1__c.

The trigger would look like this in its basic form:

trigger Object2InUse on Object1__c (after insert, after update, after delete, after undelete) {
    Set<Id> o2Ids = new Set<Id>();
    if (Trigger.isInsert || Trigger.isUpdate || Trigger.isUndelete) {
        for(Object1__c o1 : Trigger.new){
            if (o1.Object2__c != null) o2Ids.add(o1.Object2__c);
        }
    }
    if (Trigger.isUpdate || Trigger.isDelete) {
        for(Object1__c o1 : Trigger.old){
            if (o1.Object2__c != null) o2Ids.add(o1.Object2__c);
        }
    }
    if (o2ids.size() > 0) {
        Map<Id, Integer> m = new Map<Id, Integer>();
        for (Id id : o2ids) m.put(id, 0);
        for (AggregateResult ar : [
                select Object2__c i, count(id) c
                from Object1__c
                where Object2__c in :o2ids
                group by Object2__c
                ]) {
            m.put((Id) ar.get('i'), (Integer) ar.get('c'));
        }
        Object2__c[] o2s = new Object2__c[] {};
        for (Id id : m.keySet()) {
            o2s.add(new Object2__c(Id = id, InUse__c = m.get(id) > 0));
        }
        update o2s;
    }
}

and the filter criteria on the Object1__c.Object2__c field would be:

Object 2: In Use EQUALS False
Related Topic