Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

Solved: 2 Salesforce ETL Errors

01 Nov 2019 🔖 salesforce integration tips jitterbit
💬 EN

Table of Contents

I’m blogging about 2 Salesforce errors I ran into today so that your search results will, hopefully, be a little bit more helpful when you, too, see them.

bad value for restricted picklist field & id value of incorrect type

I was trying to design a Jitterbit ETL process loading data into Salesforce.

  1. I got stuck on a “bad value for restricted picklist field” error when trying to load data into a picklist field.
    • Turns out picklist values, like fields, have both display names and “API names.”
  2. I also ran into “id value of incorrect type” loading what I thought was an 18-digit record ID into a lookup field
    • Turns out “{{XYZZY}}” is not the same as “XYZZY” to a computer. Fancy that.

Bad Picklist Value

For Visualforce purposes, our admins had set the values of a picklist to read “Yes, I am” and “No, I am not” in a field responsible for capturing responses to a question on an application form we present to the general public.

Screenshot

It makes for a slight silly feeling as a staff member doing data entry on Salesforce record detail pages, but it’s not a usability show-stopper, and I can definitely see how it helps make things display nicely in our portal without too many workarounds.

The thing is … I kept trying to do this Jitterbit field-mapping transformation from a data source with “Y” and “N” values, because that’s what the picklist drop-downs said and I never thought to question them:

<trans>
Case(
	SOURCE_FIELD=="Y","Yes, I am"
	, SOURCE_FIELD=="N","No, I am not"
	, true,SOURCE_FIELD
);
</trans>

In the end, I needed to data-load against the API names of the picklist values, “Yes” and “No”:

<trans>
Case(
	SOURCE_FIELD=="Y","Yes"
	, SOURCE_FIELD=="N","No"
	, true,SOURCE_FIELD
);
</trans>

Lookup ID Type Error

In other “oops” news, when I tried to do a SFCacheLookup against the following SOQL and data-load it into a lookup field just like this:

<trans>
SFLookupAll($myOrg, "SELECT Id FROM Validation_Table__c WHERE Friendly_Name__c = 'The Friendly Name'");
</trans>

I received this error:

MY_LOOKUP_FIELD_LABEL_HERE: id value of incorrect type: {{y8E010101010101010}}.
  • Q: Can anybody spot why?
  • A: It turns out that making a typo between SFLookupCache and SFLookupAll does matter. 😜
    • SFLookupAll() was, of course, returning some sort of collection (list/dict/etc.) variable, which meant that when Salesforce got it as a piece of text, the text was no longer “y8E010101010101010” but “``.”
    • SFLookupCache() is what I meant to type, and is the operation that returns a single value.

Check for stupid mistakes if you get this error when designing a data-load ETL job and swear you “didn’t do anything wrong.”

(I knew that y8E010101010101010 had to be the correct record ID because if I logged into the Salesforce web interface and edited the field by hand, looking up the record with a search, that was exactly the record Salesforce populated the field with.)

--- ---