Step 2 - Set up Ocelot/Slate Integration: Installing Contact Lookup Query

Modified on Wed, 5 Jul, 2023 at 6:11 PM

            
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.


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article