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
Test to make sure any changes/modifications are producing the expected output. Look at the Testing section in this document for more information.
If a modification that you are making is not on this document, it is not supported. This may create errors with the data or break the integration.
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
Keep in mind that the schema cannot change for this stored procedure. Doing so will break the integration.
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).
The custom demographic fields may be modified to sync as many fields as desired, up to a maximum of ten. Syncing some (for example, the first three) and leaving the rest empty is also acceptable.
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()
- Higher Logic looks at both iMIS Name table and iMIS’s PRODUCT table to retrieve user’s membership to a valid chapter community.
- The PRODUCT table contains the chapter name while the membership duration is determined by the PAID_THRU date.
- To be considered having a valid membership, a user’s PAID_THRU date must be in the future.
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'
- Higher Logic looks at both the Activity table and Committee_Position table to retrieve a user’s membership to a valid committee community.
- The Activity table contains the committee name, membership start date, and membership end date.
The Committee_Position table contains the role name (if there is one).
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'
- Higher Logic looks at both the Activity table and the Meet_Master table to retrieve a user’s membership to a valid event based community.
- The logic here is follows the same schema as the Committee logic (above) but instead, Meet_Master houses all the event information such as the event name.
The Activity_Type field is used to only target activities/memberships with the MEETING type.
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'
- On top of the logic above, Higher Logic looks at the Orders table, Meet_Master and Order_Meet to retrieve a user’s membership to a valid event based community.
- The logic here utilizes all the tables to populate a membership to the event based community.
- The Orders.STAGE and Orders.STATUS field are checked to make sure they are considered active memberships.
Possible Modifications
Keep in mind that edits to the schema could alter/remove memberships that will affect your Thrive Community site (on the next periodic/member refresh).
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
SQL Field | Notes |
---|---|
MemberKey | [REQUIRED] The primary identifier used to map an integrated user to an integrated community. This should always be the iMIS ID for the user. |
SinceDate | Deprecated; leave NULL |
BeginDate | The starting date for the membership. The integration has to make sure the date range being returned is valid. If the begin date is set in the future, the membership will not count and the user will not be added. If the begin date is empty, the user will be added to the community as the integration will treat it as the earliest date in SQL time (January 1st, 1753) |
EndDate | The ending date for the membership. The integration has to make sure the date range being returned is valid. If the end date is set in the past, the user will be removed from (or not added to) the community. If the end date is empty, the user will not be removed from (or will be added to) the community as the integration will treat it as having the latest date. |
GroupKey | [REQUIRED] The unique identifier that Higher Logic will use to link the community back in iMIS. |
GroupName | [REQUIRED] The long name that Higher Logic will display for the community name. |
GroupType | The community type that will be associated with this community. By default, any community will come over as a "Hidden Community" type. |
Role | The identifier for an integrated role in an integrated community. |
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
- Higher Logic will convert the iMIS Member Types stored in the Member_Types table into Higher Logic security groups.
-
This table is joined with the Name table to utilize the shared MEMBER_TYPE field - this is so the ID field can be used in tandem with the other information available within the Member_Types table.
Possible Modifications
Keep in mind that edits to the schema could alter/remove memberships that will affect your Thrive Community site (on the next periodic/member refresh).
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
SQL Field | Notes |
---|---|
MemberKey | [REQUIRED] The primary identifier used to map an integrated user to an integrated security group. |
SinceDate | Deprecated; leave NULL |
BeginDate | The starting date for the membership. The integration has to make sure the date range being returned is valid. If the begin date is set in the future, the membership will not count and the user will not be added. If the begin date is empty, the user will be added to the security group as the integration will treat it as the earliest date in SQL time (January 1st, 1753). |
EndDate | The ending date for the membership. The integration has to make sure the date range being returned is valid. If the end date is set in the past, the user will be removed from (or not added to) the security group. If the end date is empty, the user will not be removed from (or will be added to) the security group as the integration will treat it as having the latest date. |
GroupKey | [REQUIRED] The unique identifier that Higher Logic will use to link the community back in iMIS. |
GroupName | [REQUIRED] The long name that Higher Logic will display for the community name. |
GroupType | Not applicable; leave NULL |
Role | Not applicable; leave NULL |
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
The idea for this logic is to target all the main iMIS tables used in the default logic:
-
The Activity table stores committee memberships and event community memberships. If users add any memberships, Higher Logic needs to look through this table using the TRANSACTION_DATE to account for those changes.
-
The Orders table stores chapter memberships. If a user adds a new chapter community or edits an existing chapter community membership, Higher Logic needs to look through this table using the UPDATED_DATE_TIME and ENTERED_DATE_TIME.
-
The Name table stores basic user information and a user’s current member type. If a user changes their member type, Higher Logic needs to compare using the PREVIOUS_MT and MEMBER_TYPE field.
-
The ORDER_MEET table stores event community memberships. Higher Logic uses a join on the Orders table and uses the ORDER_DATE field to see any new event community memberships.
-
The Name_Log table contains certain activities that aren’t recorded in the other tables. This table is important for older iMIS versions that used to have a “delete” subscription type. Higher Logic has added logic to accommodate this scenario
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 SELECT statement must return the iMIS ID and the date time field for when a change has happened.
- The FROM statement needs to target the database table that houses the iMIS ID and the date time field.
- The WHERE statement must use that datetime field relative to the @ChangedSince variable. That variable is the datetime that Higher Logic is sending over with each periodic refresh.
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.