I have a requirement where I am mapping a date/time field from a DB profile to a datetime field on an Salesforce XML profile. Sometimes the date value, from SQL server is 1900-01-01 (which is considered an null or empty in SQL server). In this case, I need to be able to recognize that value and pass a empty tag in the outgoing Salesforce profile. I cannot pass the 1900-01-01 value as is because Salesforce will take it at face value and put that in the field. So I replace that with an empty string (doing a string compare). This part works, however when I pass the empty string into the mapping, the outgoing Salesforce profile doesn't have that tag. Same thing happens when I query the DB by converting 1900-01-01 into a null value and passing the null straight into the Salesforce profile tag i.e., the outgoing document doesn't have that tag. So I try to use scripting and look for empty string for that field and pass a 'NULL' string. In this case Boomi throws an error since the data field format for the field on the outgoing profile is date/time. How do I make this work?
The reason I need to sometimes send empty value in the Salesforce tag is because users may actually be deleting the date/time value in the source application, which then needs to flow to Salesforce and wipe out that value in the field.
Any suggestions please? Thanks in advance.