Integration Configuration - Value Query with Calendar Context – Flow Support

When integrating measure values using calendar context, the following parameters can be used as placeholders in SQL queries to insert or update data.

Properties available to use in the query:

[ModelAttribute(“ModelAttributeName”)]	This will be replaced by the value of the model attribute configured on the measure based on the name of the model attribute. If no value is found, it will be replaced by a blank string (“”).
[measuredescription]	This will be replaced by the value of the description of the measure. If no value is found, it will be replaced by a blank string (“”).
[measureintervaltype]	This will be replaced by the string value of the measure’s interval type (Minutely, Hourly, Shiftly, Daily etc.). If no value is found, it will be replaced by a blank string (“”).
[measureparent]	This will be replaced by the string value of the name of the measure’s parent entity. This can be either a metric or an event. If no value is found, it will be replaced by a blank string (“”).
[measureid]	This will be replaced by the integer value of the measure’s unique ID.
[measurename]	This will be replaced by the string value of the measure’s name. If no value is found, it will be replaced by a blank string (“”).
[measureuom]	This will be replaced by the string value of the measure’s unit of measure that has been configured. If no value is found, it will be replaced by a blank string (“”).
[hierarchicalname]	This will be replaced by the string value of the measure’s hierarchical name up-to metric of event level. If no value is found, it will be replaced by a blank string (“”).
[measurevalue]	This will be replaced by the double representation of the measure’s value per time period interval. If no value is found, it will be replaced by NULL (“NULL”).
[measurevaluedatetime]	This will be replaced by the most recent timestamp the measure value had been changed.
[measurevaluedatetimeutc]	This will be replaced by the most recent timestamp, in UTC, the measure value had been changed.
[periodend]	This will be replaced by the Date Time value of the current time period’s end time being processed.
[periodstart]	This will be replaced by the Date Time value of the current time period’s start time being processed.
[periodendutc]	This will be replaced by the UTC Date Time value of the current time period’s end time being processed.
[periodstartutc]	This will be replaced by the UTC DateTime value of the current time period’s start time being processed.
[quality]	This will be replaced by the integer representation of the measure’s value quality score, based on the OPC quality code as received from the data source for that time period interval.
[duration]	This will be replaced by the integer representation of the duration, in milliseconds, of the particular time period interval.
[version]	This will be replaced by the integer representation of the current version of the value.
[timeperiodid]	This will be replaced by the integer representation of the unique timeperiod id of the specific time period interval.
[measurevalueid]	This will be replaced by the integer representation of the unique measure value ID of the specific time period interval.
[attributevalue]	If event context is added to the measure, each of the attribute values will be replaced for the current time period interval.
[attribute]	If event context is added to the measure, each of the attributes will be replaced for the current time period interval.
[preferred]	All values will be pushed to the consumer. This include all versions, not just the latest value. The preferred flag will be set as a Boolean (true/false) to indicate if the current value in the value set is the preferred value or not.
[usernameid]	If the current version had been changed manually by a specific user, the username ID will be replaced with the unique ID of the user configured in Flow that modified the value. If no user made changes to the value, it will be replaced by a blank string (“”).

Note: For Time Periods and any string values, please remember to add single quotes (') before and after the placeholder parameters.

Microsoft SQL Example

  update ERPStaging setValue=[MeasureValue] where DateTime='[PeriodEnd]'
IF@@ROWCOUNT=0
  insert into ERPStaging (DateTime, Value, tagname, TimePeriodId, MeasureId) values
  ('[PeriodEnd]',[MeasureValue], '[MeasureName]', [TimePeriodId], [MeasureID])

MySQL Example

  update ERPStaging setValue=[MeasureValue] where DateTime='[PeriodEnd]'
IF@@ROWCOUNT=0
  insert into ERPStaging (DateTime, Value, tagname, TimePeriodId, MeasureId) values
  ('[PeriodEnd]',[MeasureValue], '[MeasureName]', [TimePeriodId], [MeasureID])

PostgreSQL Example

do $$
  begin
  update ""schema"".""ERPStaging"" set""Value"" = [MeasureValue] where ""DateTime""='[PeriodEnd]'
  and ""MeasureId"" = [MeasureID]
IF NOT FOUND THEN
  insert into schema.""ERPStaging"" (""DateTime"", ""Value"", ""Tagname"", ""TimePeriodId"",
  ""MeasureId"") values ('[PeriodEnd]', [MeasureValue], '[MeasureName]', [TimePeriodId],
  [MeasureID])
end if;
end
$$