Prevent DMLException when lookup's external id is unknown

Question

Imagine a Custom SObject X with a lookup field for Custom SObject Y. Y has an external ID field EID. As described in this answer, I can create a record X like this:

Y y = new Y(EID = 1);
X x = new X(Y__r = y);
insert x;

However, this fails if no Y record with EID = 1 exists.

To prevent this failure we could create a set of all external ids:

Set<Decimal> externalIds = new Set<Decimal>();
List<Y> ys = [SELECT EID FROM Y];
for (Y y : ys) {
    externalIds.add(y.EID);
}
X x = new X();
if (externalIds.contains(1)) {
    Y y = new Y(EID = 1);
    x.Y__r = y;
}
insert x;

However, this won't scale. If you have e.g. 500,000 Y records, you could never create this set.

Also, the solution has to scale for thousands of records of X, so directly querying the given EID won't work either.

Is there a way to directly bind X to Y if and only if a record of Y exists that has the given EID?

score 3 · Accepted Answer · answered Mar 25 '14 at 12:32

3

What about using saveResults to see whats happened? All depends on whether you want to check before insert that the data is valid; in your 'trivial' case looking at EID and whether it exists or not. Or whether you want to try it and see what the results is; this can be useful when validation rules, triggers, etc... may come into play.

An example maybe:-

Y y = new Y(EID = 1);
X x = new X(Y__r = y);

Database.SaveResult[] srList = Database.insert(x,false);
for (Database.SaveResult sr : srList) {
    if (sr.isSuccess()) {
        // Operation was successful, so get the ID of the record that was processed
        System.debug('Successfully inserted x');
    }
    else {
       System.debug('Failed to insert x');
    }

answered Mar 25 '14 at 12:32

Richard Durrant

3,092
1
16
26

Interesting! Can we verify that the unknown external id is the cause for the failure? (In my real scenario I have about 5 different lookup fields, so I'd like to be able to clear only those that have unknown values) – Jolanda Verhoef Mar 25 '14 at 12:46
Have a look here https://www.salesforce.com/us/developer/docs/apexcode/Content/Chunk1705263657.htm#apex_methods_system_database_saveresult and see if the Database.Error objects have enough data for you. There is a getFields() method which may be useful in determining which field caused the error. – Richard Durrant Mar 25 '14 at 12:50

score 1 · Answer 2 · answered Mar 25 '14 at 12:23

1

How about SOQL?

You can always ask if there is Object Y with such Id

//eidSet_FromX  - Set Of EId values from X object

 set<String> eidSet_FromY = new Set<String>();

for(Y someY : [SELECT EID FROM Y WHERE EID IN : eidSet_FromX]){
    eidSet_FromY.add(someY.EID);
}

answered Mar 25 '14 at 12:23

Artur Kępczyński

3,933
21
41

This seems like the only workable solution. It's a pity that we have to loop through the X records twice (1st time to retrieve the eids, 2nd time to set the lookup field). – Jolanda Verhoef Mar 25 '14 at 12:38

Prevent DMLException when lookup's external id is unknown

2 Answers2

Linked