Re: Is it possible to NULL a field from a webhook response

Subscribe
Jon_Wright
Level 4

Is it possible to NULL a field from a webhook response

I have a webhook which updates a picklist field in Marketo. Occasionally it will come back with an empty response, in those cases I would like it to NULL the existing value in the picklist field.

I can control what value is returned in the JSON response but when I try using a value of NULL it doesn't remove the value in the field. I guess I can get around it by returning the NULL value in a string field and then use this to NULL the picklist, but wanted to avoid the 'let's create another field' solution if possible. I'm sure Sanford Whiteman​ would be able to let me know the answer to this pretty quickly.

Thanks!

14 REPLIES 14
SanfordWhiteman
Level 10 - Community Moderator

Re: Is it possible to NULL a field from a webhook response

This is a painfully absent feature.

What I do to avoid an auxiliary field is use a Webhook Is Called trigger and scan the literal response JSON (Add Constraint - Response [contains]).

Obviously not great if you want to do this with tons of different fields but should be good for your one picklist.

Jon_Wright
Level 4

Re: Is it possible to NULL a field from a webhook response

Thanks Sanford! Better than an extra field.

SanfordWhiteman
Level 10 - Community Moderator

Re: Is it possible to NULL a field from a webhook response

Ah, one more thing: Marketo trims insignificant whitespace in JSON responses. So when doing your [contains] make sure you don't include spaces around colons.

Paul_Morgan
Level 1

Re: Is it possible to NULL a field from a webhook response

Hi,

Where would you add the Webhook is Called trigger? Would it be in a new Smart Campaign(SC)?

I currently have a SC that detects a Data Value Change under the Smart List subsection.

I call the webhook in the Flow subsection of the SC

...and the Schedule subsection is configured such that any user can be run through the SC at anytime.

The Smart List subsection seems the only place where you can add triggers but I already have the Data Value Changes in there and to add another would break the pattern I'm looking to capture I think.

Flow would be:

1. Data Value is changed on a Person -> pushes person into Smart List of Campaign

2. Webhook is fired to send Data Value to external service and a JSON response is returned

3. A possible value is null so I'd like to capture the response, see if a key has the null value and then update the field on the Person object to be 'empty' accordingly.

I have 3 fields with this and changing the service to return "NULL" is not an option at present so your suggestion sounds a possible replacement. Could you expand on your comment please. I'm comfortable with response and contains part, it's the place where the test is applied that I can't seem to grasp.

TIA

SanfordWhiteman
Level 10 - Community Moderator

Re: Is it possible to NULL a field from a webhook response

Webhook is Called fires after any Response Mappings have been applied (note you don't need to have any Response Mappings).

But in your case it sounds like you already have to return a special "magic" value (not null) which would then have to be translated into Change Data Value → NULL via a Smart Campaign. (That SC itself triggering on Data Value Changes with source = webhook.) 

So while you could search for the same magic value using Webhook is Called, and a contains search on the response, I don't think it's necessary. Webhook is Called would be an alternative to having a Response Mapping.

Paul_Morgan
Level 1

Re: Is it possible to NULL a field from a webhook response

Hi,

Not quite, I'm looking to change the value AFTER receiving the response mappings, hence I thought the Webhook is Called trigger might do the job.

Consider:

A person object exists in Marketo;

A value (in this case Postal Code) is changed, either by an API call, an upload of updated data, or a manual change on the person object;

- This change is picked up by the SC (the SourceList has a trigger set for Data Value Changed set on Postal Code)

- A webhook call is then fired to an external webservice; is supplied with the new Postal Code value and sends a JSON response with several attributes, some of which may be null, or "no"

- The response_mappings set on the Webhook do pick up these values, but the value null causes no change (as discussed above), and the value 'no' does not cause a Boolean checkbox to be unticked (again this is due to a type mismatch - string vs boolean)

You've mentioned that Webhook is Called AFTER the response_mappings have been satisfied, so I was hoping to be able to use it after the response_mappings have been applied to then 'fix' the type mis-matches between the values supplied by the external webservice and those expected in Marketo.

In my SC, from what you've said, I can add the Webhook is Called as a trigger in the SourceList section and after the Data Valid Changed trigger and it will be executed after the change (to Postal Code) has been made, and the response_mappings have been applied (to fields that are type matched and accept the update). This trigger would seek to tidy up/fix the data value mismatches and reach a final state based off the values returned from the external webservice.

That's my theory of how it works based on the above. I'll give it a try and report back.

Thanks for the pointer.

Paul_Morgan
Level 1

Re: Is it possible to NULL a field from a webhook response

No dice with the additional trigger within the same SmartList section, it just caused a more complex matching strategy and thus does not fire on just a Postal Code Data Value Changed.

Also tried creating just a SmartList under Database > Operational Lists and then applying an Activity Filter for Webhook is Called but that can only be applied historically so I don't think the context of the response_mappings will be available for response -> contains processing.

A glue script using the Activity API is starting to look attractive to handle the type mismatches.

Anyone else have any other suggestions?

TIA 

SanfordWhiteman
Level 10 - Community Moderator

Re: Is it possible to NULL a field from a webhook response

I don't think you're understanding the options.

What, exactly, is the magic JSON property value that means "please set me to NULL"?

Paul_Morgan
Level 1

Re: Is it possible to NULL a field from a webhook response

Highly plausible! 

I'm not looking to set something to "NULL", I'm looking to convert a null response to a String "No" on particular returned values.

An example JSON response from a call to the external webservice would be:

{  "features": [    {      "attributes": {        "OBJECTID": 918765,        "Eastings": 484227,        "Northings": 238033,        "Postcode_Original": "MK9 1BB",        "postcode_no_spaces": "MK91BB",        "Operational_Region": "London and South East",        "External_Region": "London and South East",        "NEAREST_NAVIGATION": "Grand Union Canal",        "DISTANCE_TO_NEAREST_NAVIGATIO": "2834.42753952194",        "Nearest_KM_Length": "GU-127",        "Within_KM": null,        "Within_5_KM": "Yes",        "Distance": 2834.42753952194,        "POINT_X": -0.772037314049585,        "POINT_Y": 52.0338776552251      },      "geometry": {        "x": -0.7735707034083765,        "y": 52.03432722810158      }    }  ]}

So, you can see that the Within_KM value is null (but it's not formatted in the way mentioned above which can be parsed by Marketo i.e. a String with the value "NULL".

Therefore, if a Person has previously been at a location elsewhere where there has been a value ("Yes") and then updates their Postal Code such as above, the Webhook is unable to set the value to No (The values for the Within_KM value are either "Yes" or "No" within Marketo) because 'proper' null values are not interpreted by Marketo in the response.

I have the SC set up as in the following screenshots:

sc-smartlist-trigger.png

SmartList is populated via the trigger for Data Value Changed on Postal Code

sc-flow-webhook-call.png

The Flow is set to then push the change to the external webservice

sc-schedule.png

Everyone should be able to move through this SC

sc-results-webhook-fires.png

I can see that it fires off to the webservice without problem.

webhook-config.png

The webhook is configured as above. The response_mappings are configured according to the JSON structure above. If within1km is currently set as "Yes" and the value in the external webservice comes back as null, it will not unset the field i.e. to be "No". What I think I need to do is have this flow run through as above, but that after the response_mappings have fired and updated, I need to be able to inspect the values returned from the webservice and complete a 'secondary' parse of the data to detect the mismatched values and convert the null to "No" on the within1km field as one example.

Hope that has cleared up what my aim is.