rest api / filter by null custom field

Highlighted
Level 1

rest api / filter by null custom field

Hi All,

We've created a custom field on our leads.  We have values syncing in.

The only thing I can't figure out is this: one requirement is that we be able to filter leads that have not yet had a value inserted into the custom field.  Does anyone have any idea?  I've tried every way I can think of to encode null in a request ala

curl -XGET /rest/v1/leads.json?filterType=dgid&filterValues=&access_token=[...]

I've tried: filterValues=null, =&, ="" url encoded, ='', =, etc.  Nothing seems to work.  Does anyone have any suggestions?

Thanks very much for your help.

3 REPLIES 3
Highlighted
Level 10 - Community Moderator

Re: rest api / filter by null custom field

Excellent question. Can't say I've ever expected such a filter to work due to cardinality restrictions. (If the filter encounters > 1000 matching rows it will throw an error.)

Because of the way Marketo trims whitespace... because the string "NULL" is allowed to represent the keyword NULL in certain contexts... and because empty-ness and null-ness are blended together... I don't know how you'd format the filter, either!

Highlighted
Marketo Employee

Re: rest api / filter by null custom field

You could specify the custom field name in the fields parameter.  If the custom field has no value, you will get null back in the result[] array in response body.  Not a filter per se, but a way to tell if the custom field is empty or not.

Highlighted
Level 10 - Community Moderator

Re: rest api / filter by null custom field

Yeah, but that doesn't change the result set (I'm sure Earl has seen this result already!).