This article provides instructions on how to install the Contact Lookup saved query in Slate to be used in Ocelot's direct integration.

The article has been broken down into two sections:


Installing the Contact Lookup Query

  1. On the Navigation Bar, under the Database menu, select Database.

  2. On the Queries page, select New Query.
  3. Enter the following information:
    1. Name: Lookup Contacts
    2. User: OcelotAPI
    3. Sharing: Select the checkbox.
    4. Folder: Ocelot Integration
    5. Type: Local
    6. Base: Custom SQL
  4. Select Save.
  5. In the Custom SQL field, enter the following SQL code:
    /* Convert incoming JSON array to a local table */

declare @DeviceCriteria table ([emailCriteria] varchar(64), [phoneCriteria] varchar(32))

 

insert into @DeviceCriteria

select emailCriteria, phoneCriteria

from OpenJson(@criteria)

with (

    emailCriteria varchar(64) '$.Email',

    phoneCriteria varchar(32) '$.Phone'

)

 

 

/* Find the set of phone-numbers that matched to contacts */

declare @PhoneMatch table ([emailCriteria] varchar(64), [phoneCriteria] varchar(32), [record] uniqueidentifier)

 

insert into @PhoneMatch

    select distinct isnull(D1.[emailCriteria], '') as [emailCriteria],

    isnull(D1.[phoneCriteria], '') as [phoneCriteria],

      D2.[record]

    from @DeviceCriteria D1

    inner join [device] D2

    on isnull(D1.[phoneCriteria], '') <> ''

    and D2.[type] in ('business', 'phone', 'mobile')

      and replace(translate(D1.[phoneCriteria], '+ ()', '----'), '-', '')

      = replace(translate(D2.[value], '+ ()', '----'), '-', '')

 

/* Find the set of email that matched to contacts */

declare @EmailMatch table ([emailCriteria] varchar(64), [phoneCriteria] varchar(32), [record] uniqueidentifier)

 

insert into @EmailMatch

    select distinct isnull(D1.[emailCriteria], '') as [emailCriteria],

    isnull(D1.[phoneCriteria],'') as [phoneCriteria],

      D2.[record]

    from @DeviceCriteria D1

    inner join [device] D2

    on isnull(D1.[emailCriteria], '') <> ''

    and D2.[type] in ('email')

      and lower(D2.[value]) like '%' + lower(D1.[emailCriteria]) + '%'

;

 

/*

Distinct union of following sets:

Contacts matched on phone-number because only phone-number critera was specified.

Contacts matched on email because only email criteria was specified

Contacts matched on both phone-number and email because both criteria were were specified

*/

with MatchedContactId as (

    select distinct [emailCriteria], [phoneCriteria], [record]

    from @PhoneMatch

    where [emailCriteria] = ''

    union

    select distinct [emailCriteria], [phoneCriteria], [record]

    from @EmailMatch

    where [phoneCriteria] = ''

    union

    (

    select distinct [emailCriteria], [phoneCriteria], [record]

    from @PhoneMatch

    where [emailCriteria] <> ''

    intersect

    select distinct [emailCriteria], [phoneCriteria], [record]

    from @EmailMatch

    where [phoneCriteria] <> ''

    )

)

select L.[emailCriteria], L.[phoneCriteria],

    P.[ref] as [slateId], P.[last], P.[first], P.[email], P.[business], P.[mobile], P.[phone]

from MatchedContactId L

inner join [person] P

    on P.[id] = L.[record]

  6. Select Save.
  7. On the Edit Queries update the @Criteria field (the data entered here is specific to each individual customer).
  8. Select the Edit Permissions button on the upper right corner of the page.
  9. On the Edit Permissions dialog page, select the Add Grantee link.

  10. On the Edit Grantee dialog page, set the Type to User Token.

  11. On the Edit Grantee dialog page, the Name, Token, and Permissions fields will appear.
    1. In the Name field, enter OcelotAPI.
    2. In the Permissions field, select the Web Service box.
    3. Select Save

  12. On the Edit Permissions dialog page, the Ocelot Grantee will now be listed. Select Close.
  13. Select the Edit Web Services page. 
    1. in the Custom Parameters field, enter the following code:
      <param id="criteria" type="varchar" />
    2. From the Service Type dropdown, select JSON.
    3. Select Save.


    Capture Web Service URL


    1. On the Edit Query page, in the Web Service field, select JSON.
    2. On the Web Services dialog box, from the Service Account dropdown, select User Token - Ocelot.
    3. The URL field will appear. Copy the full URL and paste it into a safe location. The full URL will be used when registering the Contact Lookup query in the Ocelot Admin Portal.
      For information on how to register the Contact Lookup query in the Ocelot Admin Portal, review the Step 4 - Set up Ocelot/Slate Integration: Registering Slate Web Service article. 
    4. Select Close.

Continue on to Step 3 - Set up Ocelot/Slate Integration: Installing Interactions Source Format to set up the interactions source format.