Sign in

Integration Configuration - Comment Query with Event Context

Any comments added to data points will be available to be consumed.  This includes comments added to historical data points that might have already been consumed.  All measure value data will also be available with the comments data.

In addition to the measure value parameters, the following placeholders can be used:

  • [measurecomment]
 This will be replaced by the comment entered against the measure time period.
  • [measurecommentdatetime]
 This will be replaced by the timestamp of the comment had been entered.
  • [measurecommentdatetimeutc]
 This will be replaced by the UTC timestamp that the comment had been entered.
  • [measurecommentmeasurevalueid]
 This will be replaced by the measure value ID that the comment had been captured against.
  • [measurecommentmeasureeventperiodvaluecommentid]
 This will be replaced by the unique event time period ID that the comment had been captured against.
  • [measurecommentusername]
 This will be replaced by the username of the user who entered the comment. If no value is found, it will be replaced by a blank string (“”).
   

Microsoft SQL Example

update Comments set Comment ='[MeasureComment]' where DateTime='[MeasureCommentDateTime]'
IF @@ROWCOUNT=0
insert into Comments (Comment, DateTime, TimePeriodId, UserName, MeasureId) values
('[MeasureComment]', '[MeasureCommentDateTime]',[MeasureCommentMeasureTimePeriodID],
'[MeasureCommentUsername]', [MeasureID])

MySQL Example

update Comments set Comment ='[MeasureComment]' where DateTime='[MeasureCommentDateTime]'
IF @@ROWCOUNT=0
insert into Comments (Comment, DateTime, TimePeriodId, UserName, MeasureId) values
('[MeasureComment]', '[MeasureCommentDateTime]',[MeasureCommentMeasureTimePeriodID],
'[MeasureCommentUsername]', [MeasureID])

PostgreSQL Example

do $$
begin
update schema.""Comments"" set ""Comment""= '[MeasureComment]'
where ""DateTime""='[MeasureCommentDateTime]'
IF NOT FOUND THEN
insert into schema."" Comments "" (""Comment "", ""DateTime"", "" TimePeriodId"",
"" UserName"","" MeasureId "") values ('[MeasureComment]', '[MeasureCommentDateTime]',[MeasureCommentMeasureTimePeriodID],
'[MeasureCommentUsername]', [MeasureID])
end if;
end
$$