Solved: 2 Salesforce ETL Errors

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

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.

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 "{{y8E010101010101010}}."
  • 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.)