Sign in

What Placeholders can I use when configuring a database-type Data Consumer for Events?

For all the database-type Data Consumers (currently Microsoft SQL, MySQL, and PostgreSQL), queries need to be configured to push information from Flow to the consumer.

As has been mentioned when discussing each of these Data Consumers, the queries may be configured against any configured Event Integration section. 

 

Screenshot 2024-07-24 at 22.58.19.png

There are 4 types of queries that may be configured for Event Integration:

  1. Value Query - This is to push and update Event Period related data
  2. Attribute Query - This is to push and update Attribute Values configured on the event
  3. Comment Query - This is to push comments added by users from Event Forms via the Flow Server Front End for a specific event period
  4. User Query - This is to push and update user related information for users changing attributes values or adding comments.

For each of these queries, there are different placeholder available to use in your queries.  When the Integration Engine prepares to run one of these queries against your Data Consumer, it will first replace the values in the placeholders with the actual values associated with each of these placeholders.

Value Query

Event Scheme Details - Details around the specific event that can be utilised in your queries.

  • [eventschemedescription]
  • [eventschemeid]
  • [eventschemename]
  • [ModelAttribute(“Model Attribute Name”)]

 

Event Period Values - Details around the Event Period that can be utilised in your queries.

  • [periodend]
  • [periodendutc]
  • [periodstart]
  • [periodstartutc]
  • [periodstartquality]
  • [periodendquality]
  • [preferred]
  • [usernameid]
  • [preferredevent] – same as [preferred]
  • [eventperiodid]

Example - The following Stored Procedure is an example of how these place holders can be used in a script (Based om MSSSQL):

-- Build Up Varaibles to Use in Value Query - This is the Query Flow will execute
EXEC EventPeriodIntegration 
@EventSchemeID = [EventSchemeID], 
@EventSchemeName = '[EventSchemeName]', 
@EventSchemeDescription = '', 
@EventPeriodID = [EventPeriodID], 
@PeriodStart = '[PeriodStart]', 
@PeriodEnd = '[PeriodEnd]', 
@EventPeriodPreferred = [PreferredEvent].  

--Actual Stored Procedure that needs to be created in SQL against the source database
CREATE PROCEDURE [dbo].[EventPeriodIntegration]

 -- Add the parameters for the stored procedure here

 @EventSchemeID int,
 @EventSchemeName nvarchar(200), 
 @EventSchemeDescription nvarchar(400),
 @EventPeriodID bigint,
 @PeriodStart datetime2,
 @PeriodEnd datetime2 = null,
 @EventPeriodPreferred bit
AS

-- Start Query

-- Update the EventScheme table to ensure the details are up to date with the latest in Flow
 merge into EventScheme as target

using (values (@EventSchemeID, @EventSchemeName, @EventSchemeDescription)) 
as source ([EventSchemeID], [Name], [Description])
on target.[EventSchemeID] = source.[EventSchemeID]

when matched then

 update set 
target.[Name] = coalesce(source.[Name], target.[Name]),  
target.[Description] = coalesce(source.[Description], target.[Description])

when not matched by target then
insert ([EventSchemeID], [Name], [Description]) 
values (@EventSchemeID, @EventSchemeName, @EventSchemeDescription);

-- End EventScheme Update

-- Update the EventPeriod with the latest information

merge into EventPeriod as target
using (values (@EventPeriodID, @EventSchemeID, @PeriodStart, @PeriodEnd, @EventPeriodPreferred)) 
as source ([EventPeriodID], [EventSchemeID], [PeriodStart], [PeriodEnd], [Preferred])
on target.[EventPeriodID] = source.[EventPeriodID] and target.[EventSchemeID] = source.[EventSchemeID]

when matched then
update set 
target.[PeriodStart] = coalesce(source.[PeriodStart], target.[PeriodStart]),  
target.[PeriodEnd] = coalesce(source.[PeriodEnd], target.[PeriodEnd]), 
target.[Preferred] = coalesce(source.[Preferred], target.[Preferred])

when not matched by target then
insert ([EventPeriodID], [EventSchemeID], [PeriodStart], [PeriodEnd], [Preferred]) 
values (@EventPeriodID, @EventSchemeID, @PeriodStart, @PeriodEnd, @EventPeriodPreferred);

-- End EventPeriodUpdate

GO

 

Attribute Query

Event Attribute Values - Details around Event Attribute Values that can be utilised in your queries.

  • [eventattributevalueid]
  • [preferred]
  • [usernameid]
  • [eventperiodid]
  • [eventattributename]
  • [eventattributevalue]
  • [datetime]
  • [datetimeutc]

Example - The following Stored Procedure is an example of how these place holders can be used in a script (Based om MSSSQL):

-- Build Up Varaibles to Use in Attribute Query - This is the Query Flow will execute
EXEC EventAttributeIntegration
@EventAttributeValueID = [EventAttributeValueID],
@EventPeriodID = [EventPeriodID], 
@EventAttributeName = '[EventAttributeName]', 
@EventAttributeValue = '[EventAttributeValue]', 
@EventEventAttributePreferred = [Preferred], 
@UsernameID = [UsernameID]



--Actual Stored Procedure that needs to be created in SQL against the source database

CREATE PROCEDURE [dbo].[EventAttributeIntegration]

 -- Add the parameters for the stored procedure here 

@EventAttributeValueID bigint,
@EventPeriodID bigint,
@EventAttributeName nvarchar(200), 
@EventAttributeValue nvarchar(200),
@EventEventAttributePreferred bit,
@UsernameID int

AS

-- Start Query

merge Attribute as target
using (select @EventAttributeName as [Name], 1 as [Type]) as source
on target.[Name] = source.[Name]
when not matched by target then
insert ([Name], [Type])
values(source.[Name], source.[Type]);

-- Update the EventScheme table to ensure the details are up to date with the latest in Flow

merge AttributeValue as target
using (select a.[AttributeID], @EventAttributeValue as [Value] 
from Attribute a where a.[Name] = @EventAttributeName) source
on (target.[Value] = source.[Value] and target.[AttributeID] = source.[AttributeID])
when not matched by target then
insert ([Value], [AttributeID])
values(source.[Value], source.[AttributeID]);
-- End EventScheme Update


-- Update the EventPeriod with the latest information

merge EventAttributeValue as target
using (select @EventAttributeValueID as [EventAttributeValueID], AttributeValueID, @EventPeriodID as [EventPeriodID], @EventEventAttributePreferred as [Preferred], @UsernameID as [UsernameID]
from AttributeValue av 
join Attribute a on av.AttributeID = a.AttributeID
where av.[Value] = @EventAttributeValue and
a.[Name] = @EventAttributeName) as source
on target.[EventAttributeValueID] = source.[EventAttributeValueID]

when matched then

update set
target.[Preferred] =  coalesce(source.[Preferred], target.[Preferred]),
target.[UsernameID] =  coalesce(source.[UsernameID], target.[UsernameID])

when not matched by target then

insert ([EventAttributeValueID], [EventPeriodID], [AttributeValueID], [Preferred], [UsernameID])
values(source.[EventAttributeValueID], source.[EventPeriodID], source.[AttributeValueID], source.[Preferred], source.[UsernameID]);

-- End EventPeriodUpdate

GO

 

Comment Query

Event Period Comment Values - Details around the Event Period Comments added that can be utilized in your queries.

  • [eventperiodcommentid]
  • [usernameid]
  • [eventperiodid]
  • [comment]
  • [datetime]
  • [datetimeutc]

Example - The following Stored Procedure is an example of how these place holders can be used in a script (Based om MSSSQL):

 

-- Build Up Varaibles to Use in Comment Query - This is the Query Flow will execute
EXEC EventPeriodCommentIntegration
@EventPeriodCommentID = [EventPeriodCommentID], 
@EventPeriodID = [EventPeriodID], 
@UsernameID = [UsernameID], 
@DateTime = '[DateTime]', 
@Comment = '[Comment]'

--Actual Stored Procedure that needs to be created in SQL against the source database

CREATE PROCEDURE [dbo].[EventPeriodCommentIntegration]

 -- Add the parameters for the stored procedure here 
@EventPeriodCommentID int,
@EventPeriodID int, 
@UsernameID int,
@DateTime datetime2,
@Comment nvarchar(MAX)

AS

-- Start Query

merge EventPeriodComment as target
using (select @EventPeriodCommentID as [EventPeriodCommentID], @EventPeriodID as [EventPeriodID], 
@UsernameID as [UsernameID], @DateTime as [DateTime], @Comment as [Comment]) as source
on target.[EventPeriodCommentID] = source.[EventPeriodCommentID]
when not matched by target then

insert ([EventPeriodCommentID], [EventPeriodID], [UsernameID], [DateTime], [Comment])
values(source.[EventPeriodCommentID], source.[EventPeriodID], source.[UsernameID], source.[DateTime], source.[Comment]);

GO

 

User Query 

User Details - Details around User Details that can be utilized in your queries.

  • [usernameid]
  • [name]
  • [email]
  • [mobile]

Example - The following Stored Procedure is an example of how these place holders can be used in a script (Based om MSSSQL):

 

-- Build Up Varaibles to Use in User Query - This is the Query Flow will execute

EXEC UsernameIntegration

@UsernameID = [UsernameID],
@Name = '[Name]', 
@Email = '[Email]',
@Mobile = '[Mobile]'

--Actual Stored Procedure that needs to be created in SQL against the source database
CREATE PROCEDURE [dbo].[UsernameIntegration]
 -- Add the parameters for the stored procedure here 
@UsernameID int,
@Name nvarchar(50), 
@Email nvarchar(50),
@Mobile nvarchar(50)
AS
-- Start Query
merge Username as target
using (select @UsernameID as [UsernameID], @Name as [Name], @Email as [Email], @Mobile as [Mobile]) as source
on target.[UsernameID] = source.[UsernameID]
when matched then
update set
target.[Name] = coalesce(source.[Name], target.[Name]),
target.[Email] = coalesce(source.[Email], target.[Email]),
target.[Mobile] = coalesce(source.[Mobile], target.[Mobile])
when not matched by target then
insert ([UsernameID], [Name], [Email], [Mobile])
values(source.[UsernameID], source.[Name], source.[Email], source.[Mobile]);
GO