This document will go over the logic and reasoning behind the standard set of SQL that Higher Logic provides on any iMIS integration. In addition to that, this document will also guide users to adding and testing modifications to the integration.

All the logic is in iMISStoredProcedures.sql file that comes with the iMIS integration install package. This article is only going over the files that might need modifications to suit your business needs.

These stored procedures should not be modified.

• hlGetMemberAddresses.sp
• hlGetMemberCommunityGroups.sp
• hlGetCommunityGroupMembers.sp
• hlGetSecurityGroupMembers.sp
• hlGetSecurityGroups.sp
• hlGetMemberSecurityGroups.sp
• hlWriteActivity.sp

Member Details

Every user account/profile that gets created/synced between iMIS and Higher Logic Thrive Community (Thrive Community) uses the hlGetMemberDetails stored procedure. This stored procedure captures all the information needed to populate an end user’s information. This info can appear in the user’s profile.

CREATE PROCEDURE dbo.hlGetMemberDetails
 @MemberKey varchar(10)
AS
BEGIN
 SELECT
 n.ID AS MemberKey,
 n.Prefix,
 n.First_Name AS FirstName,
 n.Last_Name AS LastName,
 n.Suffix,
 n.Designation,
 n.Informal AS InformalName,
 n.Company AS CompanyName,
 n.Title,
 n.Email AS Email1,
 '' AS Email2,
 Work_Phone AS Phone1,
 'Work' AS Phone1Type,
 n.Home_Phone AS Phone2,
 'Home' AS Phone2Type,
 n.Fax AS Phone3,
 'Fax' AS Phone3Type,
 n.Toll_Free AS Phone4,
 'TollFree' AS Phone4Type,
 n.Website AS WebsiteUrl,
 mt.COMPANY_RECORD AS IsCompany,
 n.CO_ID AS CompanyID,
 n.Status,
 n.EXCLUDE_DIRECTORY AS DoNotList,
 CAST(0 AS bit) AS DoNotEmail,
 '' AS PictureUrl,
 '' AS Custom01,
 '' AS Custom02,
 '' AS Custom03,
 '' AS Custom04,
 '' AS Custom05,
 '' AS Custom06,
 '' AS Custom07,
 '' AS Custom08,
 '' AS Custom09,
 '' AS Custom10
 FROM Name n
 INNER JOIN Member_Types mt ON n.MEMBER_TYPE = mt.MEMBER_TYPE
 WHERE n.ID = @MemberKey
END
GO

• Higher Logic uses the iMIS Name table.
• Please note that there are hard coded strings coming back for each Phone type.
• The iMIS Member_Types table has a flag to determine if a record is a normal user vs a company/organization. This is used to determine if a profile belongs to a regular user versus a company/organization user.

Possible Modifications

Enabling Profile Picture Integration

To enable profile picture integration, PictureUrl needs to point to a field in the iMIS SQL database with the image URL. This has to be a public-facing URL - Higher Logic does not accept SQL image blobs.

Please add the field and table to the SELECT and FROM blocks in the SQL logic. Any tables added need to be joined to Name so the stored procedure can extract the field. (We recommend using the ID column as the joiner field).

Example below:

CREATE PROCEDURE dbo.hlGetMemberDetails 
SELECT t.PICTURE_URL AS PictureUrl, 
FROM Name n 
INNER JOIN Member_Types mt 
ON n.MEMBER_TYPE = mt.MEMBER_TYPE 
INNER JOIN Table t on n.ID = t.ID
END GO

Higher Logic will need to configure profile picture integration on our end so please make sure that is enabled.

Adding Custom Demographics

To enable custom demographics, Custom01 to Custom10 needs to point to a field with the demographic that is going to be surfaced on the Thrive Community site. Higher Logic will convert the output into a string type so please make sure the value returned is a normal varchar value.

Please add the field and table to the SELECT and FROM blocks in the SQL logic. Any tables added need to be joined to Name so the stored procedure can extract the field. (We recommend using the ID column).

Example below:

CREATE PROCEDURE dbo.hlGetMemberDetails
...
 SELECT
 ...
 t.DEMOGRAPHIC AS Custom01,
 t.DEMOGRAPHIC2 AS Custom02,
 // and so on
 ...
 FROM Name n
 INNER JOIN Member_Types mt ON n.MEMBER_TYPE = mt.MEMBER_TYPE
 INNER JOIN Table t on n.ID = t.ID
 ...
END
GO

Additional configuration needs to happen on the Higher Logic side. Once these modifications are done and tested, please communicate what kind of demographics are coming over to your project manager, support person, analyst, etc. They will need to know what is coming over for each CustomXX demographic.

Community Groups

Higher Logic communities are created from the hlMemberCommunityGroups view. This view handles the mapping between the iMIS SQL tables into the Higher Logic group schema. There are a number of communities that are brought into Higher Logic by type.

SELECT a.ID AS MemberKey, a.CHAPTER AS GroupKey, b.TITLE AS GroupName, 
'Chapter' AS GroupType, 'Member' AS ROLE, NULL AS SinceDate, NULL AS StartDate, 
a.PAID_THRU AS EndDate 
FROM Name a INNER JOIN Product b ON a.CHAPTER = b.PRODUCT_MINOR 
AND b.PRODUCT_MAJOR = 'CHAPT' 
WHERE a.PAID_THRU > GetDate()

SELECT
 a.ID AS MemberKey,
 a.OTHER_CODE AS GroupKey,
 a.DESCRIPTION AS GroupName,
 'Committee' AS GroupType,
 CASE WHEN Len(RTrim(b.TITLE)) > 0 
 THEN b.TITLE
 ELSE 'Member'
 END AS ROLE,
 NULL AS SinceDate,
 a.EFFECTIVE_DATE AS StartDate,
 a.THRU_DATE AS EndDate
FROM Activity a
 LEFT OUTER JOIN Committee_Position b
 ON a.ACTION_CODES = b.POSITION_CODE
WHERE a.ACTIVITY_TYPE = 'COMMITTEE'

 SELECT a.ID AS MemberKey, a.PRODUCT_CODE AS GroupKey, a.DESCRIPTION 
AS GroupName, 'Event' AS GroupType, 'Attendee' AS ROLE, 
NULL AS SinceDate, NULL AS StartDate, NULL AS EndDate 
FROM Activity a LEFT OUTER JOIN Meet_Master b 
ON a.PRODUCT_CODE = b.MEETING WHERE a.ACTIVITY_TYPE = 'MEETING'

SELECT a.ST_ID AS MemberKey, b.MEETING AS GroupKey, 
c.TITLE AS GroupName, 'Event' AS GroupType, 
CASE WHEN c.BEGIN_DATE > GetDate() THEN 'Registrant' ELSE 'Attendee' END 
AS ROLE, NULL AS SinceDate, NULL AS StartDate, NULL AS EndDate 
FROM Orders a INNER JOIN Order_Meet b 
ON a.ORDER_NUMBER = b.ORDER_NUMBER 
LEFT OUTER JOIN Meet_Master c 
ON b.MEETING = c.MEETING WHERE a.STAGE <> 'CANCELED' AND a.Status <> 'C' 

Possible Modifications

Adding new communities

New SQL logic needs to be added via a UNION statement to the existing logic in the view. It must follow the same schema (shown below).

UNION SELECT t.ID AS MemberKey, t.GroupKey AS GroupKey, 
t.GroupName AS GroupName, "Community" AS GroupType, t.Role AS Role, 
NULL AS SinceDate, t.Date1 AS StartDate, t.Date2 AS EndDate FROM ExampleTable t

Security Groups

Higher Logic security groups are created from the hlMemberSecurityGroups view. This view handles the mapping between the iMIS SQL tables into the Higher Logic group schema.

CREATE VIEW dbo.hlMemberSecurityGroups 
AS SELECT a.ID AS MemberKey, a.MEMBER_TYPE AS GroupKey, 
b.DESCRIPTION AS GroupName, 'Member' AS GroupType, 
'Member' AS ROLE, a.JOIN_DATE AS SinceDate, 
NULL AS StartDate, 
a.PAID_THRU AS EndDate 
FROM Name a INNER JOIN Member_Types b ON a.MEMBER_TYPE = b.MEMBER_TYPE GO

Possible Modifications

Adding new security groups

New SQL logic has to be added via a UNION statement to the existing logic in the view. It must follow the same schema (shown below).

UNION 
SELECT t.ID AS MemberKey, t.GroupKey AS GroupKey, t.GroupName AS GroupName, 
NULL AS GroupType, NULL AS Role, NULL AS SinceDate, t.Date1 AS StartDate, 
t.Date2 AS EndDate 
FROM ExampleTable t 

Periodic Refresh

The Higher Logic periodic refresh uses the hlGetChangedMembers stored procedure to retrieve a list of users that have changed/edited some sort of information that needs to reflect in the Thrive Community site. The stored procedure creates a temporary table to group all the users that are being pulled from the various blocks of logic. There are some mandatory modifications that need to be made.

CREATE PROCEDURE [dbo].[hlGetChangedMembers]
 @ChangedSince datetime
AS
BEGIN
 DECLARE @ChangedRecords TABLE 
 (
 MemberKey varchar(10),
 LastUpdated datetime
 )

INSERT INTO @ChangedRecords
 SELECT
 ID AS MemberKey,
 LAST_UPDATED AS LastUpdated
 FROM Name
 WHERE LAST_UPDATED >= @ChangedSince
 
 UNION
---- If there are transaction dates with a time value of 00:00:00.000, 
---- then use the SELECT statement below:
 SELECT
 a.ID AS MemberKey,
 MAX(COALESCE(a.TRANSACTION_DATE, '2000-12-02 00:00:00:000')) AS LastUpdated
 FROM Activity a
 INNER JOIN Name b ON a.ID = b.ID
 WHERE 
 CAST(a.TRANSACTION_DATE AS DATE) >= CAST(@ChangedSince AS DATE)
 AND NOT a.TRANSACTION_DATE > DATEADD(D,1, GetDate())
 GROUP BY a.ID
 
---- If there aren't transaction dates with time value of 00:00:00.000, then 
---- delete the SELECT statement above and use the SELECT statement below:
 --SELECT 
 --a.ID AS MemberKey, 
 --MAX(COALESCE(a.TRANSACTION_DATE, '2000-12-02 00:00:00:000')) AS LastUpdated
 --FROM Activity a
 --INNER JOIN Name b ON a.ID = b.ID
 --WHERE a.TRANSACTION_DATE >= @ChangedSince
 --GROUP BY a.ID

UNION
 SELECT
 a.ST_ID AS MemberKey,
 MAX(COALESCE(a.UPDATED_DATE_TIME, '2000-12-02 00:00:00:000')) AS LastUpdated
 FROM Orders a
 INNER JOIN Name b ON a.ST_ID = b.ID
 WHERE a.UPDATED_DATE_TIME >= @ChangedSince
 GROUP BY a.ST_ID

UNION
 SELECT
 a.BT_ID AS MemberKey,
 MAX(COALESCE(a.UPDATED_DATE_TIME, '2000-12-02 00:00:00:000')) AS LastUpdated
 FROM Orders a
 INNER JOIN Name b ON a.BT_ID = b.ID
 WHERE 
 a.UPDATED_DATE_TIME >= @ChangedSince
 AND a.BT_ID <> a.ST_ID
 GROUP BY a.BT_ID

UNION
 SELECT
 a.ST_ID AS MemberKey,
 MAX(COALESCE(a.ENTERED_DATE_TIME, '2000-12-02 00:00:00:000')) AS LastUpdated
 FROM Orders a
 INNER JOIN Name b ON a.ST_ID = b.ID
 WHERE a.ENTERED_DATE_TIME >= @ChangedSince
 GROUP BY a.ST_ID

UNION
 SELECT
 a.BT_ID AS MemberKey,
 MAX(COALESCE(a.ENTERED_DATE_TIME, '2000-12-02 00:00:00:000')) AS LastUpdated
 FROM Orders a
 INNER JOIN Name b ON a.BT_ID = b.ID
 WHERE 
 a.ENTERED_DATE_TIME >= @ChangedSince
 AND a.BT_ID <> a.ST_ID
 GROUP BY a.BT_ID

SELECT
 a.MemberKey,
 (CASE
 WHEN MAX(a.LastUpdated) > GetDate() 
 THEN GetDate()
 ELSE MAX(a.LastUpdated)
 END) AS LastUpdated
 FROM @ChangedRecords a
 INNER JOIN Name b ON a.MemberKey = b.ID
 WHERE 
 (
 EXISTS
 (
 SELECT 1
 FROM Member_Types c
 WHERE 
 (
 b.MEMBER_TYPE = c.MEMBER_TYPE
 OR b.PREVIOUS_MT = c.MEMBER_TYPE
 )
 )
 OR 
 EXISTS
 (
 SELECT 1
 FROM Orders c
 INNER JOIN ORDER_MEET d ON c.ORDER_NUMBER = d.ORDER_NUMBER
 WHERE 
 (
 b.ID = c.ST_ID
 OR b.ID = c.BT_ID
 )
 AND c.ORDER_DATE > GetDate() - 730
 )
 )
 AND
 (
 a.LastUpdated > GetDate() - 180
 )
 GROUP BY a.MemberKey
 -- If Name_Log.SUB_TYPE = 'delete' is NOT used, then use the ORDER BY below:
 ORDER BY MAX(a.LastUpdated)
-- If Name_Log.SUB_TYPE = 'delete' is used, delete the ORDER BY above and add the SELECT statement below:
-- UNION
-- SELECT 
-- ID AS MemberKey, 
-- MAX(Date_time) AS LastUpdated
-- FROM Name_Log
-- WHERE SUB_TYPE = 'delete'
-- AND DATE_TIME >= @ChangedSince
-- GROUP BY ID
-- ORDER BY LastUpdated
END
GO

Necessary Modifications

• Making the necessary modifications
• There are multiple edits that need to be made based on certain scenarios. Please read the comments in the view to decide which blocks of logic are most applicable to your iMIS database.

Possible Modifications

Adding logic to encompass custom fields to the periodic refresh

For any customized logic added that goes outside of the scope of the basic SQL stored procedures and views, corresponding logic must be added to this view so the periodic refresh can pick up any new user updates. Here is example format for adding customized logic to this stored procedure:

SELECT 
 {iMIS ID} AS MemberKey, 
 {Datetime Field} AS LastUpdated 
FROM {Table with Datetime Field}
WHERE {Datetime Field} >= @ChangedSince 

The logic should follow all the other UNION statements populating members into the temporary table.

UNION
 SELECT
 a.ST_ID AS MemberKey,
 MAX(COALESCE(a.ENTERED_DATE_TIME, '2000-12-02 00:00:00:000')) AS LastUpdated
 FROM Orders a
 INNER JOIN Name b ON a.ST_ID = b.ID
 WHERE a.ENTERED_DATE_TIME >= @ChangedSince
 GROUP BY a.ST_ID

UNION
 SELECT
 a.BT_ID AS MemberKey,
 MAX(COALESCE(a.ENTERED_DATE_TIME, '2000-12-02 00:00:00:000')) AS LastUpdated
 FROM Orders a
 INNER JOIN Name b ON a.BT_ID = b.ID
 WHERE 
 a.ENTERED_DATE_TIME >= @ChangedSince
 AND a.BT_ID <> a.ST_ID
 GROUP BY a.BT_ID
-- EXTRA LOGIC STARTS HERE (with UNION statement)

Testing

Make sure to test out the expected outputs of all the stored procedures and views with their modifications. You can think of these queries as snapshots for how your Thrive Community site will look like. Here are some example queries:

SELECT * FROM hlMemberSecurityGroups

• This query outputs all the existing memberships to any security groups within the Thrive Community.

SELECT * FROM hlMemberSecurityGroups WHERE MemberKey = '10000'

• This query outputs all security group memberships that 10000 is a part of. This should be used when testing out modifications to the hlMemberSecurityGroups view or hlMemberCommunityGroups view.

EXEC hlGetMemberDetails @MemberKey = '10000'

• This query outputs the general user information and custom demographics for user 10000. This should be used to test out modifications made to the hlGetMemberDetails stored procedure.

Any changes and modifications to the stored procedures or views will result in immediate changes to how users are going to be displayed in your Thrive Community site so testing should be done before making the edits to the stored procedures. We recommend creating backups or building out the sub queries in your query explorer.