Webservice Lookup

This page provides two samples for the WebserviceLookup.

Simple / Shopify Lookup

In this example Shopify is queried using the email address to return the id (and email address) of a customer record. This is a common integration scenario, where the record's id is needed to update the record, but the id isn't part of the source data.

Lookup Setup

Webservice Behaviour

Select the Shopify behaviour setup in a previous step.

Content Type

The service will return json, so pick 'application/json' from the drop down.

Query URL

/admin/api/2019-04/customers/search.json?query=email:%[1]&fields=id,email

The lookup uses Shopify's Search Syntax, which is quite limited as it can only be used on Customer and Giftcard endpoints. If querying other endpoints in Shopify, other syntax must be used.

Luckily Shopify have an example, but it's quite limited.

https://help.shopify.com/en/api/reference/customers/customer#search

The first parameter is the query parameter "query=email:%[1]" allow the email address to be used as the search criteria.

The search itself is parameterised, meaning we can pass an argument from WebserviceLookup function into the %[1] placeholder.

The second parameter "fields=id, email" allow us to to return only those specified fields thus reducing the amount of data being returned and improving performance too.

Return Path

/customers[]/id

See following Resulting Query Url for discussion.

WebserviceLookup Function

To call the lookup, in a map transform add the following function to a new field.

WebserviceLookup("SHOPCUST", %email, True)

The %email field will be passed as the first parameter (%[1]) to the lookup.

Resulting Query & Service

Using the first record as an example.

Request URL

The resulting query URL combines the base URL from the Webservice Behaviour, with the substituted Query URL.

https://realisable-test.myshopify.com/admin/api/2019-04/customers/search.json?query=email:[email protected]&fields=id,email

Webservice Result

Shopify returns the following JSON. The Return Path "/customers[]/id" will return the value of the "id" property in the "customers" array (highlighted in bold).

{
  "customers": [{ 
    "id": 659878576193,
    "email": "[email protected]"
  }]
}

Parameterised Return / Mailchimp Lookup

This example shows you how to use a parameterised return path.

https://developer.mailchimp.com/documentation/mailchimp/reference/lists/#

The lookup makes a call to the Mailchimp API to return the id of a mail list. In a real scenario this may be needed if we wish to add new subscribers to a list.

Unfortunately, the /lists endpoint returns all the mail lists for a particular account i.e. it cannot searched. Therefore, to find the id of the required list we need to search through the results. The parameterised Return Path will search the list based on a value of a specific property.

Lookup Setup

Webservice Behaviour

Select the Mailchimp behaviour setup in a previous step.

Content Type

The service will return json, so pick 'application/json' from the drop down.

Query URL

/lists?fields=lists.id,lists.name

The fields parameter allows you to return the specified fields (again for performance). However, the syntax is a little strange and very different to most webservices so could easily be omitted (to return the entire document using the '/lists').

The reason for each field being prefixed with 'lists.' is note in the API documentation stating 'Reference parameters of sub-objects with dot notation.'.

Return Path

/lists[name='%[1]']/id

See following Resulting Query Url for discussion.

WebserviceLookup Function

To call the lookup, in a map transform add the following function to a new field.

WebserviceLookup("MCLISTID", "Sage300 Customers", True)

In this instance a static value of 'Sage300 Customers' being passed a parameter.

Resulting Query & Service

Request URL

The resulting query URL combines the base URL from the Webservice Behaviour, with the Query URL.

https://us2.api.mailchimp.com/3.0/lists?fields=lists.id,lists.name

Webservice Result

Mailchimp returns the following JSON.

{
  "lists":[{
    "id":"3a71ab629a",
    "name":"Realisable Software Ltd"
  },{
    "id":"543a16ac1f",
    "name":"Sage300 Customers"
  },{
    "id":"17623ac4ea",
    "name":"Magento Customers"
  }]
}

The Return Path will be substituted with the value passed from the WebserviceLookup function to result in the following JSON Path:

/lists[name='Sage300 Customers']/id

The value of the "id" property in the "lists" array (bold highlight) where the name property is 'Sage300 Customers' (highlighted in red) is returned.