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
On the Navigation Bar, under the Database menu, select Database.
- On the Queries page, select New Query.
- Enter the following information:
- Name: Lookup Contacts
- User: OcelotAPI
- Sharing: Select the checkbox.
- Folder: Ocelot Integration
- Type: Local
- Base: Custom SQL
- Select Save.
- 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]
- Select Save.
- On the Edit Queries update the @Criteria field (the data entered here is specific to each individual customer).
- Select the Edit Permissions button on the upper right corner of the page.
- On the Edit Permissions dialog page, select the Add Grantee link.
On the Edit Grantee dialog page, set the Type to User Token.
- On the Edit Grantee dialog page, the Name, Token, and Permissions fields will appear.
- In the Name field, enter OcelotAPI.
- In the Permissions field, select the Web Service box.
- Select Save
- On the Edit Permissions dialog page, the Ocelot Grantee will now be listed. Select Close.
- Select the Edit Web Services page.
- in the Custom Parameters field, enter the following code:
<param id="criteria" type="varchar" />
- From the Service Type dropdown, select JSON.
- Select Save.
Capture Web Service URL
- On the Edit Query page, in the Web Service field, select JSON.
- On the Web Services dialog box, from the Service Account dropdown, select User Token - Ocelot.
- 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.
- 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
Feedback sent
We appreciate your effort and will try to fix the article