2000000000 f762e3a9-e4c5-4e4a-9a9e-28a3320930b4 *Zoom Registrants - Same Day Reminder Text This recipient group can be used to send automated registration reminder text to Zoom registrants of ANY events, the day of the event. Schedule texts with this recipient group to run between 4am and 6am every day. Connect4 Support true false <Query QueryName="" QueryGUID="ec2837e0-898d-4619-b051-afd7f0877e20" SchemaGUID="f762e3a9-e4c5-4e4a-9a9e-28a3320930b4" SchemaVer="133"> <Properties /> <Top>All</Top> <ResultGrouping>1</ResultGrouping> <Select> <ReturnField> <FieldName>dbo.tbl104_EVRegCatJoin.Registration_Category</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>0</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.EPrimaryEmailAddress</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>1</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PPrefName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>2</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PLastName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>3</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>4</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.OnlineMeetingPassword</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>5</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.OnlineMeetingNumber</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>6</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.EVIStartEventZone_fixed</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>7</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Events.EVName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>8</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Registrations.REGID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>9</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.EVIID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>10</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Events.EVID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>11</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> </Select> <Where> <ConditionGroup GroupName="baseGroup" Behavior="0"> <Condition> <ComparisonFieldName>EventInstance.HasOnlineMeeting</ComparisonFieldName> <ComparisonTypeName>is true</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVIStartEventZone_fixed</ComparisonFieldName> <ComparisonTypeName>is greater than</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues> <ConditionValue>00:01:00</ConditionValue> </ConditionValues> <ValueSourceType>2</ValueSourceType> <ValueSourceTitle>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueSourceTitle> <ValueProviderGUID>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueProviderGUID> <DisplayConditionValues> <DisplayConditionValue>Run Time Plus 00:01</DisplayConditionValue> </DisplayConditionValues> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVIStartEventZone_fixed</ComparisonFieldName> <ComparisonTypeName>is less than or equal to</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues> <ConditionValue>18:00:00</ConditionValue> </ConditionValues> <ValueSourceType>2</ValueSourceType> <ValueSourceTitle>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueSourceTitle> <ValueProviderGUID>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueProviderGUID> <DisplayConditionValues> <DisplayConditionValue>Run Time Plus 18:00</DisplayConditionValue> </DisplayConditionValues> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVICanceled</ComparisonFieldName> <ComparisonTypeName>is false</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>Person.TPhoneMobile</ComparisonFieldName> <ComparisonTypeName>is not null</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>dbo.tbl104_EVRegCatJoin.Digital_Registration</ComparisonFieldName> <ComparisonTypeName>is true</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> </ConditionGroup> </Where> </Query> SELECT DISTINCT Events.EVID AS Event_ID, EventInstance.EVIID AS Event_Instance_ID, Registrations.REGID AS Registration_ID, Events.EVName AS Event_Name, EventInstance.EVIStartEventZone_fixed AS Event_Start_Local_Time, EventInstance.OnlineMeetingNumber AS Online_Meeting_Number, EventInstance.OnlineMeetingPassword AS Online_Meeting_Password, Person.PID AS Person_ID, Person.PLastName AS Last_Name, (CASE WHEN LEN(ISNull(PPrefName,''))=0 THEN PFirstName ELSE PPrefName END) AS Preferred_Name, Person.EPrimaryEmailAddress AS Primary_Email_Address, dbo.tbl104_EVRegCatJoin.Registration_Category FROM ( SELECT [EVID] ,[REIDRegCategory] ,CASE WHEN Len ( RTrim ( IsNull ( EVRECategoryText , '' ) ) ) = 0 THEN IsNull( REName,'Deleted') ELSE tbl104_EVRegCatJoin.EVRECategoryText END AS Registration_Category ,CAST(CASE WHEN REF.REFlagValue & RegInPersonFlagVal.FlagValue > 0 THEN 1 ELSE 0 END as bit) AS InPersonRegistration ,CAST(CASE WHEN REF.REFlagValue & RegDigitalOnlyFlagVal.FlagValue > 0 THEN 1 ELSE 0 END as bit) AS DigitalRegistration FROM [dbo].[tbl104_EVRegCatJoin] LEFT OUTER JOIN tbl_RefEntity REF ON [dbo].[tbl104_EVRegCatJoin].REIDRegCategory = REF.REID CROSS APPLY (SELECT FlagValue FROM tbl_FlagLookup WHERE FlagKey='104RegInPerson' and FlagArea = '104EventRegCat') RegInPersonFlagVal CROSS APPLY (SELECT FlagValue FROM tbl_FlagLookup WHERE FlagKey='104RegDigitalOnly' and FlagArea = '104EventRegCat') RegDigitalOnlyFlagVal ) dbo.tbl104_EVRegCatJoin RIGHT OUTER JOIN dbo.vs104_RegistrationsPlusMembers Registrations ON Registrations.REIDRegCategory = dbo.tbl104_EVRegCatJoin.REIDRegCategory AND Registrations.EVID = dbo.tbl104_EVRegCatJoin.EVID LEFT OUTER JOIN dbo.v1_Personv2 Person ON Registrations.PID = Person.PID LEFT OUTER JOIN ( SELECT [EVIID] ,[tbl104_EventInstance].[EVID] ,[EVIStart_fixed] ,[EVIStartEventZone_fixed] ,[EVIOriginalStart] ,[EVICanceled] ,[EVINotes] ,[EVIMediaBaseUrl] ,[EVIUserModified] ,IsNull(EVIOnlineMeetingNumber, tbl2_OnlineMeeting.OMNumber) AS OnlineMeetingNumber ,IsNull(EVIOnlineMeetingPassword, tbl2_OnlineMeeting.OMPassword) AS OnlineMeetingPassword ,OMAccountName AS OnlineMeetingAccount ,OMKeepID AS SameOnlineIDForAll ,Cast(Case when tbl2_OnlineMeeting.OMID is null then 0 else 1 end as bit) as HasOnlineMeeting FROM [tbl104_EventInstance] INNER JOIN tbl104_Events ON [tbl104_EventInstance].EVID = tbl104_Events.EVID LEFT OUTER JOIN tbl2_OnlineMeeting ON tbl104_Events.EVID = tbl2_OnlineMeeting.EVID ) EventInstance ON Registrations.EVIID = EventInstance.EVIID RIGHT OUTER JOIN dbo.tbl104_Events Events ON Events.EVID = EventInstance.EVID WHERE EventInstance.HasOnlineMeeting = 1 AND EventInstance.EVIStartEventZone_fixed > cast('2021-06-07 13:42:54' as datetime) AND EventInstance.EVIStartEventZone_fixed <= cast('2021-06-08 07:41:54' as datetime) AND EventInstance.EVICanceled = 0 AND Person.TPhoneMobile is not Null AND dbo.tbl104_EVRegCatJoin.Digital_Registration = 1 OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); false 2021-05-25T08:10:03 2021-05-25T08:10:03 1000 Connect4 Support 2602 Event Registration Administration false 2000000001 f762e3a9-e4c5-4e4a-9a9e-28a3320930b4 *Hybrid In-Person Regs - Same Day Reminder Text This recipient group can be used to send automated registration reminder text to the In-Person registrants of hybrid events, the day of the event. Schedule texts with this recipient group to run between 4am and 6am every day. Connect4 Support true false <Query QueryName="" QueryGUID="ec2837e0-898d-4619-b051-afd7f0877e20" SchemaGUID="f762e3a9-e4c5-4e4a-9a9e-28a3320930b4" SchemaVer="133"> <Properties /> <Top>All</Top> <ResultGrouping>1</ResultGrouping> <Select> <ReturnField> <FieldName>dbo.tbl104_EVRegCatJoin.Registration_Category</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>0</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.EPrimaryEmailAddress</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>1</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PPrefName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>2</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PLastName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>3</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>4</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.OnlineMeetingPassword</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>5</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.OnlineMeetingNumber</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>6</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.EVIStartEventZone_fixed</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>7</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Events.EVName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>8</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Registrations.REGID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>9</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.EVIID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>10</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Events.EVID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>11</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> </Select> <Where> <ConditionGroup GroupName="baseGroup" Behavior="0"> <Condition> <ComparisonFieldName>EventInstance.HasOnlineMeeting</ComparisonFieldName> <ComparisonTypeName>is true</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVIStartEventZone_fixed</ComparisonFieldName> <ComparisonTypeName>is greater than</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues> <ConditionValue>00:01:00</ConditionValue> </ConditionValues> <ValueSourceType>2</ValueSourceType> <ValueSourceTitle>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueSourceTitle> <ValueProviderGUID>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueProviderGUID> <DisplayConditionValues> <DisplayConditionValue>Run Time Plus 00:01</DisplayConditionValue> </DisplayConditionValues> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVIStartEventZone_fixed</ComparisonFieldName> <ComparisonTypeName>is less than or equal to</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues> <ConditionValue>18:00:00</ConditionValue> </ConditionValues> <ValueSourceType>2</ValueSourceType> <ValueSourceTitle>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueSourceTitle> <ValueProviderGUID>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueProviderGUID> <DisplayConditionValues> <DisplayConditionValue>Run Time Plus 18:00</DisplayConditionValue> </DisplayConditionValues> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVICanceled</ComparisonFieldName> <ComparisonTypeName>is false</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>Person.TPhoneMobile</ComparisonFieldName> <ComparisonTypeName>is not null</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>dbo.tbl104_EVRegCatJoin.In_Person_Registration</ComparisonFieldName> <ComparisonTypeName>is true</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> </ConditionGroup> </Where> </Query> SELECT DISTINCT Events.EVID AS Event_ID, EventInstance.EVIID AS Event_Instance_ID, Registrations.REGID AS Registration_ID, Events.EVName AS Event_Name, EventInstance.EVIStartEventZone_fixed AS Event_Start_Local_Time, EventInstance.OnlineMeetingNumber AS Online_Meeting_Number, EventInstance.OnlineMeetingPassword AS Online_Meeting_Password, Person.PID AS Person_ID, Person.PLastName AS Last_Name, (CASE WHEN LEN(ISNull(PPrefName,''))=0 THEN PFirstName ELSE PPrefName END) AS Preferred_Name, Person.EPrimaryEmailAddress AS Primary_Email_Address, dbo.tbl104_EVRegCatJoin.Registration_Category FROM ( SELECT [EVID] ,[REIDRegCategory] ,CASE WHEN Len ( RTrim ( IsNull ( EVRECategoryText , '' ) ) ) = 0 THEN IsNull( REName,'Deleted') ELSE tbl104_EVRegCatJoin.EVRECategoryText END AS Registration_Category ,CAST(CASE WHEN REF.REFlagValue & RegInPersonFlagVal.FlagValue > 0 THEN 1 ELSE 0 END as bit) AS InPersonRegistration ,CAST(CASE WHEN REF.REFlagValue & RegDigitalOnlyFlagVal.FlagValue > 0 THEN 1 ELSE 0 END as bit) AS DigitalRegistration FROM [dbo].[tbl104_EVRegCatJoin] LEFT OUTER JOIN tbl_RefEntity REF ON [dbo].[tbl104_EVRegCatJoin].REIDRegCategory = REF.REID CROSS APPLY (SELECT FlagValue FROM tbl_FlagLookup WHERE FlagKey='104RegInPerson' and FlagArea = '104EventRegCat') RegInPersonFlagVal CROSS APPLY (SELECT FlagValue FROM tbl_FlagLookup WHERE FlagKey='104RegDigitalOnly' and FlagArea = '104EventRegCat') RegDigitalOnlyFlagVal ) dbo.tbl104_EVRegCatJoin RIGHT OUTER JOIN dbo.vs104_RegistrationsPlusMembers Registrations ON Registrations.REIDRegCategory = dbo.tbl104_EVRegCatJoin.REIDRegCategory AND Registrations.EVID = dbo.tbl104_EVRegCatJoin.EVID LEFT OUTER JOIN dbo.v1_Personv2 Person ON Registrations.PID = Person.PID LEFT OUTER JOIN ( SELECT [EVIID] ,[tbl104_EventInstance].[EVID] ,[EVIStart_fixed] ,[EVIStartEventZone_fixed] ,[EVIOriginalStart] ,[EVICanceled] ,[EVINotes] ,[EVIMediaBaseUrl] ,[EVIUserModified] ,IsNull(EVIOnlineMeetingNumber, tbl2_OnlineMeeting.OMNumber) AS OnlineMeetingNumber ,IsNull(EVIOnlineMeetingPassword, tbl2_OnlineMeeting.OMPassword) AS OnlineMeetingPassword ,OMAccountName AS OnlineMeetingAccount ,OMKeepID AS SameOnlineIDForAll ,Cast(Case when tbl2_OnlineMeeting.OMID is null then 0 else 1 end as bit) as HasOnlineMeeting FROM [tbl104_EventInstance] INNER JOIN tbl104_Events ON [tbl104_EventInstance].EVID = tbl104_Events.EVID LEFT OUTER JOIN tbl2_OnlineMeeting ON tbl104_Events.EVID = tbl2_OnlineMeeting.EVID ) EventInstance ON Registrations.EVIID = EventInstance.EVIID RIGHT OUTER JOIN dbo.tbl104_Events Events ON Events.EVID = EventInstance.EVID WHERE EventInstance.HasOnlineMeeting = 1 AND EventInstance.EVIStartEventZone_fixed > cast('2021-06-07 13:34:09' as datetime) AND EventInstance.EVIStartEventZone_fixed <= cast('2021-06-08 07:33:09' as datetime) AND EventInstance.EVICanceled = 0 AND Person.TPhoneMobile is not Null AND dbo.tbl104_EVRegCatJoin.In_Person_Registration = 1 OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); false 2021-05-25T08:07:45 2021-05-25T08:07:45 1000 Connect4 Support 2602 Event Registration Administration false 2000000002 f762e3a9-e4c5-4e4a-9a9e-28a3320930b4 *In-Person Registrants - Same Day Reminder Text This recipient group can be used to send automated registration reminder texts to In-Person registrants of In-Person-only events, the day of the event. Schedule texts with this recipient group to run between 4am and 6am every day Connect4 Support true false <Query QueryName="" QueryGUID="ec2837e0-898d-4619-b051-afd7f0877e20" SchemaGUID="f762e3a9-e4c5-4e4a-9a9e-28a3320930b4" SchemaVer="133"> <Properties /> <Top>All</Top> <ResultGrouping>1</ResultGrouping> <Select> <ReturnField> <FieldName>tbl104_EVRegCatJoin.Registration_Category</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>0</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.EPrimaryEmailAddress</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>1</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PPrefName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>2</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PLastName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>3</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Person.PID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>4</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.OnlineMeetingPassword</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>5</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.OnlineMeetingNumber</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>6</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.EVIStartEventZone_fixed</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>7</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Events.EVName</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>8</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Registrations.REGID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>9</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>EventInstance.EVIID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>10</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> <ReturnField> <FieldName>Events.EVID</FieldName> <AggregationType>0</AggregationType> <ReturnSetOrder>11</ReturnSetOrder> <ReturnSetSortOrder>-1</ReturnSetSortOrder> <ReturnSortDirection>0</ReturnSortDirection> </ReturnField> </Select> <Where> <ConditionGroup GroupName="baseGroup" Behavior="0"> <Condition> <ComparisonFieldName>EventInstance.HasOnlineMeeting</ComparisonFieldName> <ComparisonTypeName>is false</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVIStartEventZone_fixed</ComparisonFieldName> <ComparisonTypeName>is greater than</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues> <ConditionValue>00:01:00</ConditionValue> </ConditionValues> <ValueSourceType>2</ValueSourceType> <ValueSourceTitle>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueSourceTitle> <ValueProviderGUID>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueProviderGUID> <DisplayConditionValues> <DisplayConditionValue>Run Time Plus 00:01</DisplayConditionValue> </DisplayConditionValues> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVIStartEventZone_fixed</ComparisonFieldName> <ComparisonTypeName>is less than or equal to</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues> <ConditionValue>18:00:00</ConditionValue> </ConditionValues> <ValueSourceType>2</ValueSourceType> <ValueSourceTitle>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueSourceTitle> <ValueProviderGUID>9a9c9c8b-1b96-4c4d-a15c-33e110fccb05</ValueProviderGUID> <DisplayConditionValues> <DisplayConditionValue>Run Time Plus 18:00</DisplayConditionValue> </DisplayConditionValues> </Condition> <Condition> <ComparisonFieldName>EventInstance.EVICanceled</ComparisonFieldName> <ComparisonTypeName>is false</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>Person.TPhoneMobile</ComparisonFieldName> <ComparisonTypeName>is not null</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> <Condition> <ComparisonFieldName>tbl104_EVRegCatJoin.InPersonRegistration</ComparisonFieldName> <ComparisonTypeName>is true</ComparisonTypeName> <AggregationType>0</AggregationType> <ConditionValues /> <ValueSourceType>0</ValueSourceType> <ValueSourceTitle /> <DisplayConditionValues /> </Condition> </ConditionGroup> </Where> </Query> SELECT DISTINCT Events.EVID AS Event_ID, EventInstance.EVIID AS Event_Instance_ID, Registrations.REGID AS Registration_ID, Events.EVName AS Event_Name, EventInstance.EVIStartEventZone_fixed AS Event_Start_Local_Time, EventInstance.OnlineMeetingNumber AS Online_Meeting_Number, EventInstance.OnlineMeetingPassword AS Online_Meeting_Password, Person.PID AS Person_ID, Person.PLastName AS Last_Name, (CASE WHEN LEN(ISNull(PPrefName,''))=0 THEN PFirstName ELSE PPrefName END) AS Preferred_Name, Person.EPrimaryEmailAddress AS Primary_Email_Address, tbl104_EVRegCatJoin.Registration_Category FROM ( SELECT [EVID] ,[REIDRegCategory] ,CASE WHEN Len ( RTrim ( IsNull ( EVRECategoryText , '' ) ) ) = 0 THEN IsNull( REName,'Deleted') ELSE tbl104_EVRegCatJoin.EVRECategoryText END AS Registration_Category ,CAST(CASE WHEN REF.REFlagValue & RegInPersonFlagVal.FlagValue > 0 THEN 1 ELSE 0 END as bit) AS InPersonRegistration ,CAST(CASE WHEN REF.REFlagValue & RegDigitalOnlyFlagVal.FlagValue > 0 THEN 1 ELSE 0 END as bit) AS DigitalRegistration FROM [dbo].[tbl104_EVRegCatJoin] LEFT OUTER JOIN tbl_RefEntity REF ON [dbo].[tbl104_EVRegCatJoin].REIDRegCategory = REF.REID CROSS APPLY (SELECT FlagValue FROM tbl_FlagLookup WHERE FlagKey='104RegInPerson' and FlagArea = '104EventRegCat') RegInPersonFlagVal CROSS APPLY (SELECT FlagValue FROM tbl_FlagLookup WHERE FlagKey='104RegDigitalOnly' and FlagArea = '104EventRegCat') RegDigitalOnlyFlagVal ) tbl104_EVRegCatJoin RIGHT OUTER JOIN dbo.vs104_RegistrationsPlusMembers Registrations ON Registrations.REIDRegCategory = tbl104_EVRegCatJoin.REIDRegCategory AND Registrations.EVID = tbl104_EVRegCatJoin.EVID LEFT OUTER JOIN dbo.v1_Personv2 Person ON Registrations.PID = Person.PID LEFT OUTER JOIN ( SELECT [EVIID] ,[tbl104_EventInstance].[EVID] ,[EVIStart_fixed] ,[EVIStartEventZone_fixed] ,[EVIOriginalStart] ,[EVICanceled] ,[EVINotes] ,[EVIMediaBaseUrl] ,[EVIUserModified] ,IsNull(EVIOnlineMeetingNumber, tbl2_OnlineMeeting.OMNumber) AS OnlineMeetingNumber ,IsNull(EVIOnlineMeetingPassword, tbl2_OnlineMeeting.OMPassword) AS OnlineMeetingPassword ,OMAccountName AS OnlineMeetingAccount ,OMKeepID AS SameOnlineIDForAll ,Cast(Case when tbl2_OnlineMeeting.OMID is null then 0 else 1 end as bit) as HasOnlineMeeting FROM [tbl104_EventInstance] INNER JOIN tbl104_Events ON [tbl104_EventInstance].EVID = tbl104_Events.EVID LEFT OUTER JOIN tbl2_OnlineMeeting ON tbl104_Events.EVID = tbl2_OnlineMeeting.EVID ) EventInstance ON Registrations.EVIID = EventInstance.EVIID RIGHT OUTER JOIN dbo.tbl104_Events Events ON Events.EVID = EventInstance.EVID WHERE EventInstance.HasOnlineMeeting = 0 AND EventInstance.EVIStartEventZone_fixed > cast('2021-10-06 14:44:53' as datetime) AND EventInstance.EVIStartEventZone_fixed <= cast('2021-10-07 08:43:53' as datetime) AND EventInstance.EVICanceled = 0 AND Person.TPhoneMobile is not Null AND tbl104_EVRegCatJoin.InPersonRegistration = 1 false 2021-05-25T08:02:10 2021-05-25T08:02:10 1000 Connect4 Support 2602 Event Registration Administration false 2000000000 1017 Same Day Reminder Text - Zoom Registrants Test Text Message 2 EventInstance.EVIStartEventZone_fixed,Events.EVName,Person.TPhoneMobile,Person.PPrefName,EventInstance.OnlineMeetingPassword DESKTOP-A3CPLGV Connect4 Support 1 false 2021-06-16T22:30:23 false false 2021-05-25T13:02:10 2021-06-16T22:30:28 1000 Connect4 Support 2 true true 2000000001 1017 Same Day Reminder Text - In-Person Registrants to Hyrbid Event Test Text Message 2 EventInstance.EVIStartEventZone_fixed,Events.EVName,Person.TPhoneMobile,Person.PPrefName,EventInstance.OnlineMeetingPassword,Events.EVLocation DESKTOP-A3CPLGV Connect4 Support 1 false 2021-06-16T22:29:40 false false 2021-05-25T13:20:07 2021-06-16T22:29:47 1000 Connect4 Support 2 true true 2000000002 1017 Same Day Reminder Text - In-Person Registrants to In-Person Event Test Text Message 2 EventInstance.EVIStartEventZone_fixed,Events.EVName,Person.TPhoneMobile,Person.PPrefName,EventInstance.OnlineMeetingPassword,Events.EVLocation DESKTOP-A3CPLGV Connect4 Support 1 false 2021-06-16T22:28:10 false false 2021-05-25T13:17:21 2021-06-16T22:28:16 1000 Connect4 Support 2 true true 2000000000 1169 _______ Event Reminder: «EventInstance.Event_Start_Local_Time»: «Events.Event_Name» Zoom Link: «@ShortenURL("«ZoomMeetingURL»",autoauth)» Password: «EventInstance.Online_Meeting_Password» From a send-only number. STOP to opt-out. 1016 1016 false false false false 2021-05-25T13:02:11 2021-05-25T13:02:11 1000 Connect4 Support 2000000001 1169 _______ Event Reminder: «EventInstance.Event_Start_Local_Time»: «Events.Event_Name» «Events.EVLocation» If are not well or no longer comfortable attending in person, or have been in contact with someone who has tested positive for COVID, you may still attend via Zoom! Zoom Link: «@ShortenURL("«ZoomMeetingURL»",autoauth)» Password: «EventInstance.Online_Meeting_Password» From a send-only number. STOP to opt-out. 1016 1016 false false false false 2021-05-25T13:20:07 2021-05-25T13:20:07 1000 Connect4 Support 2000000002 1169 _______ Event Reminder: «EventInstance.Event_Start_Local_Time»: «Events.Event_Name», «Events.EVLocation» We'll see you soon! From a send-only number. STOP to opt-out. 1016 1016 false false false false 2021-05-25T13:17:21 2021-05-25T13:17:21 1000 Connect4 Support 2000000000 2000000000 2000000001 2000000001 2000000002 2000000002 <__SourceCommsTypes> 1015 E-Mail Message EmailMergeEngine 3.1 Connect4.Framework.Email.dll,Connect4.Framework.Email.cEmailEngine Email 1016 1018 0 1 2 EPrimaryEmailAddress,PID,EIDPrimary,PFirstName|First_Name,PLastName|Last_Name false true true true false false false false true 30 <__SourceCommsTypes> 1020 MSWord Mail-Merge MSWordMerge 3.3 CommsModule.dll,Connect4.Framework.Plugins.CommsModule.cMSWordEngine Hardcopy 0 0 1021 2 3 AIDPrimary,HID true true false true false true false false false 30 <__SourceCommsTypes> 1168 Mail File Generation MailFileGeneration 3.9 CommsModule.dll,Connect4.Framework.Plugins.CommsModule.cExportFileEngine Postal Mail 0 2 AIDPrimary,HID false true false true false false false false true 30 <__SourceCommsTypes> 1169 SMS Text Message SMSTextMessage 3.11 Connect4.Framework.Plugins.CommsEngines.AdditionalCommsEngines.dll,Connect4.Framework.Plugins.CommsEngines.AdditionalCommsEngines.cSMSTextEngine SMS Text Msg. 1016 3 PID,TIDPhoneMobile,TPhoneMobile,THasSMS,TSMSDenied false true true true false false false false false 30 <__SourceCommsTypes> 1174 Gnosis Report Design GREPMergeEngine 3.13 Connect4.Framework.Plugins.CommsEngines.AdditionalCommsEngines.dll,Connect4.Framework.Plugins.CommsEngines.AdditionalCommsEngines.cGREPMergeEngine 1173 0 0 2 AIDPrimary,HID false true true true false false false false false 30 <__SourceCommsTypes> 1265 RTF Mail-Merge RTFMerge 3.15 CommsModule.dll,Connect4.Framework.Plugins.CommsModule.cRTFMergeEngine HardCopy 1263 0 0 2 AIDPrimary,HID false true false true false false false false false 30 <__SourceCommsTypes> 1272 Envelopes (Gnosis Rep Design) GREPEnvelopes 3.13 Connect4.Framework.Plugins.CommsEngines.AdditionalCommsEngines.dll,Connect4.Framework.Plugins.CommsEngines.AdditionalCommsEngines.cGREPMergeEngine N/A 1173 0 0 2 AIDPrimary,HID false true true true false false false false false 30 <__SourceCommsTypes> 1276 Envelopes (for RTF Mail-Merge) RTFEnvelopes 3.15 CommsModule.dll,Connect4.Framework.Plugins.CommsModule.cRTFMergeEngine N/A 1263 0 0 2 AIDPrimary,HID false true false true false false false false false 30 <__SourceCommsDataTypes> 1016 8 Plain Text txt 3 <__SourceCommsDataTypes> 1018 8 HTML htm txt 3 <__SourceCommsDataTypes> 1021 8 MS Word Document doc 3 <__SourceCommsDataTypes> 1173 8 Gnosis Report Format repx 3 <__SourceCommsDataTypes> 1263 8 RTF rtf txt 3 <__SourceCommsDataTypes> 1454 8 Beefree The beefree/pro/plugin json format (template) that the html is built from beeJSON