Is it possible to report blanks and nulls on boolean fields?

[omarsanchez]

Hello!

I'm auditing a database at the moment, and I found this issue, I'm trying to report blank and null records on a boolean field using a smart list, but the only option that I found is to filter by TRUE or FALSE. I read that Marketo sees BLANKS and NULLS as FALSE, however I'd like to show the client something like:

Field Boolean 1:

TRUE = %

FALSE = %

BLANKS = %

NULLS = %

Bdw, it could be NULLs as well since a sync that they did recently from their system filled blank fields with the string NULL. So it's important to report that as well.

Is this possible in Marketo?

Thanks!

[SanfordWhiteman · Accepted solution]

There's not a material difference between a Boolean field set to false and a Boolean field set to the null value. Even via API, the current value will be materialized as false in either case. Another way of looking at it is a field update request supports nulls, but field storage does not.

A Boolean can't be blank.

A Smart List on Data Value Changes also treats false and null as interchangeable.

If they really insist on seeing whose value was originally requested to change to false and whose was requested to change to null, and you are/have a developer, you could use the Get Lead Changes API. This will reflect the "New Value" of null when that was requested. Finding it hard to imagine why they would care about this, as the database treats them the same.

[omarsanchez]

Hello, Sandy.

The idea for us to report nulls vs falses is because the client is updating thousands of Marketo leads and creating new ones via API and list imports. We don't have visibility of the data delivered via API or the source CSV files, but we think there is a lot of fields as null or blanks.

We were able to find a lot of blanks and normalization opportunities running lead performance reports with custom columns and this worked perfectly for string fields.

I will try the Get Lead Changes API option.

Thanks for your help!

[SanfordWhiteman]

---

We don't have visibility of the data delivered via API or the source CSV files, but we think there is a lot of fields as null or blanks.

---

That makes sense. To be clear, they can't be sending empty strings for Boolean fields via the REST API, but they may be sending null.

Way better that they don't send any value at all, assuming they mean null in its natural meaning of "unknown." Especially because null will update an existing true to false! Good reason maybe to send everything through an import gateway, where you can remove the fields that shouldn't be sent at all.