Cannot provide a NULL value for external reference Id

[Tyler Beckett]

How do you map an External ID for a Lookup field that might contain some nullsThe MS SQL View returns nulls for some of the lookups where there's no value in the source data (normal logic for an RDBMS and for SF), but it returns an error saying I cannot use NULL for an external Reference ID (actual error has a typo "referehce"). I tried Coalescing these to a blank string, no luck. I tried using the IF(ISNULLOREMPTY( logic in the success article linked below, no luck. Two other fields that have lookups but are never NULL don't fail (when I remove the two offending fields from the mapping). I'm running out of ideas of how to handle this normal situation.

 

https://success.scribesoft.com/s/question/0D50e00004aOszYCAS/how-to-repl...

 

IF(ISNULLOREMPTY(v_CC_Opportunity.Account_Title_Company__r_External_ID__c), "", v_CC_Opportunity.Account_Title_Company__r_External_ID__c)

[Manoj Chaurasia]

Hello Tyler,

I will create a case on your behalf about this. You will get an email from support shortly.

 

With regards,

Marzieh

[Ramy Zahran]

Hi,

 

What was the solution for this issue I'm having the same error and I'm trying to find some alternatives other then making the map more complex by adding separates update block and separate create blocks.

 

Thank you

Ramy

[Tyler Beckett]

@Ramy ZahranI believe they reported it as a bug, but I've not been notified if it has been resolved. My work around was to add a step to sync (SF Query) the Id and External ID for all records I need to do the lookup for, then do a join in SQL to provide the Salesforce ID in my output.

[Ash Arendsz]

Hi,

External ids are usually unique (not null), null values are present when the record hasn't been linked to an external record yet, so you need to add a validation step in your map to check for this condition(IF Block) to avoid mapping errors.

Also keep the following in mind about Lookup blocks VS Fetch blocks usage:

- A Scribe Online Lookup Block operation can match 0,1, or more records.

How to deal with these different outcomes, is up to you and your logic map design.

- A lookup block returns all fields as Null values when no records are found or matched. So it would be a good idea to add an IF block to check for the lookup result records matched=1

- When a lookup matches more than one record, only one record is returned, not necessarily the one record intended.

https://help.scribesoft.com/scribe/en/#advsys/maps/blocks/adv_lookup.htm...

- Alternatively you can use Fetch block(this does not set the results to Null but skips if no results found.

- A Fetch block returns all records found/matched one by one to be processed.

[Tyler Beckett]

The issue is not setting an external ID on a record, but attempting to link a relationship/lookup field USING an external ID on a LOOKUP (not Master Detail, not Scribe Lookup() function) field. These are NOT required, so NULL is acceptable for Salesforce. There's a "feature" in Scribe that is preventing this from happening when using External IDs for the lookup, while it has no issue allowing NULLs in the non-External ID version of the same field (AccountId vs Account_Account_External_ID__c).

 

I use this practice of linking records using their external ID rather than doing essentially a VLOOKUP and getting the Salesforce ID for every operation I can using Jitterbit Cloud Data Loader or Salesforce Data Loader without issue thousands of times because it is a time saver.