Follow

Integration Configuration - User Query (all Contexts)

When integrating measure values using either calendar or event context, the user details of the person that made changes to a particular data point can be used in the User Query. The following parameters can be used as placeholders in SQL queries to insert or update data.

Properties available to use in the query are:

  • [name]
This will be replaced by the name of the user that edited the measure value or that created a comment for the time period and measure value version. If no value is found, it will be replaced by a blank string (“”)
  • [usernameid]
This will be replaced by the unique username ID of the user that edited the measure value or that created a comment for the time period and measure value version.
  • [email]
This will be replaced by the email address of the user that edited the measure value or that created a comment for the time period and measure value version. If no value is found, it will be replaced by a blank string (“”).
  • [mobile]
This will be replaced by the mobile number of the user that edited the measure value or that created a comment for the time period and measure value version. If no value is found, it will be replaced by a blank string (“”).
   
Note: For Time Periods and any string values, please remember to add single quotes to the placeholder parameters.

The example queries that are provided are shown below:

Microsoft SQL Example

update Users set name='[Name]', mobile ='[Mobile]', email ='[Email]' where UserID=[userID]
IF@@ROWCOUNT=0
insert into Users (Name, UserID, mobile, email) values ('[Name]', [userID], '[Mobile]',
'[Email]')

MySQL Example

update Users set name='[Name]', mobile ='[Mobile]', email ='[Email]' where UserID=[userID]
IF@@ROWCOUNT=0
insert into Users (Name, UserID, mobile, email) values ('[Name]', [userID], '[Mobile]',
  '[Email]')

PostgreSQL Example

do $$
begin
update schema.""Users"" set ""Name""= '[Name]', ""Mobile""='[Mobile]', ""Email""=
'[Email]'where ""UserID""=[userID]
IF NOT FOUND THEN
insert into schema.""Users"" (""Name"", ""UserID"", ""Mobile"", ""Email"") values
('[Name]', [userID], '[Mobile]', '[Email]')
end if;
end
$$
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments