Help get this topic noticed by sharing it on Twitter, Facebook, or email.
I’m Confused

Required custom Salesforce fields replicated as nullable columns?

Hi ForceAmp team. When comparing some column metadata differences between our local SQL Server database tables and the corresponding SFDC objects, we noticed that custom fields defined as not-null in Salesforce were being replicated over to our database as nullable. Digging through the change history notes at http://www.forceamp.com/hats/DBAmp%20..., it appears that this behavior was introduced in v 2.12.7.

We were wondering: What is the reason this behavior was introduced? It's confusing to find these differences, and we've likely written ETL queries that are somewhat less efficient as a result.

thanks,
Matt Coles
Business Systems Administrator / Developer
Tableau Software
1 person has
this question
+1
Reply
  • The main reason for this is because the salesforce server can say that a field is not nillable and yet still have nulls in the field. In other words, salesforce allows the metadata to not match the data.

    Previous versions of DBAmp created the local tables according to the metadata only to fail when salesforce passed a record with a null for a field we were told was non-nillable. This happened so often that we made the decision to have all custom fields locally be nillable regardless of the salesforce metadata .

    Obviously, this has an impact on your operations. Can you expand on the impact ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • I would describe the impact to our operations as minor. We have done (possibly) unnecessary NULL testing for custom fields, we may have used LEFT rather than INNER joins, etc. If the Salesforce servers do allow null values for fields they also describe as "required" (https://login.salesforce.com/help/doc...), then I suppose the logic we've added is actually necessary anyway. I haven't done any analysis as of yet to determine whether we actually have any NULL values for "required" fields. Definitely seems like a bug on their end, though.

    Thank you for the quick response, Bill.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • It general happens when fields are initially created as not required and then at some later point made into a required field. Salesforce does not go back and "fixup" any existing nulls values, thus causing the behavior described.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated