1

I have a SQL query that is always returning true for my boolean field even though the data in SF is showing false. Incomplete_Runtime_Data__c is the boolean field, all other fields return correctly.

Select 
d.WelcomeEmailAddress__c as [EmailAddress],
d.Id as [DeviceId],
d.Name as [DeviceName],
p.Name as [ProductName],
c.FirstName as [FirstName],
d.ContactID__c as [ContactID],
r.Id as [RuntimeID],
r.Online__c as [Online],
r.Date__c as [Date],
r.Incomplete_Runtime_Data__c as [Incomplete_Runtime_Data]
From Runtime__c_Salesforce r with(nolock)
Left Join Device__c_Salesforce d with(nolock) on r.Device__c = d.id
Left Join Product2_Salesforce p with(nolock) on d.Product__c = p.id
Left Join Contact_Salesforce c with(nolock) on d.ContactID__c = c.Id

In the Data Extension, I have the field Incomplete_Runtime_Data marked as boolean and nullable.

Why would the data only show True when it should be False?

2

In your data extension, do you have Incomplete_Runtime_Data set as a Boolean with a default value?

I would suggest doing some tests by creating Incomplete_Runtime_Data as a text field in the Data Extension and check what value gets passed across.

There can be discrepancies in the way SF stores a Boolean value and how a data extension handles a Boolean.

If you find that a Text value is returning differing text result e.g. ON, then you can properly convert it to Boolean with a Case statement

Select 
d.WelcomeEmailAddress__c as [EmailAddress], 
d.Id as [DeviceId], 
d.Name as [DeviceName], 
p.Name as [ProductName], 
c.FirstName as [FirstName], 
d.ContactID__c as [ContactID], 
r.Id as [RuntimeID], 
r.Online__c as [Online], 
r.Date__c as [Date], 
case when r.Incomplete_Runtime_Data__c in ('yes','OK','on','True','TRUE') then 1 else 0 end As Incomplete_Runtime_Data
From Runtime__c_Salesforce r with(nolock) 
Left Join Device__c_Salesforce d with(nolock) on r.Device__c = d.id
Left Join Product2_Salesforce p with(nolock) on d.Product__c = p.id 
Left Join Contact_Salesforce c with(nolock) on d.ContactID__c = c.Id
  • I was able to change the data type to Text and see what values were being passed. Each record was recorded as 1 even though it should be either True or False. It never recorded a 0 value either. I decided instead to just move forward with the Text values instead as those were coming over correctly. – DMelsh Nov 12 '18 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.