Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

Cannot provide a NULL value for external reference Id

Tyler Beckett

Recommended Posts

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.


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

Link to comment
Share on other sites

  • 4 months later...
@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.
Link to comment
Share on other sites

  • 2 months later...


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.

- 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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Create New...