Cinchy Functions

1. Overview

The Cinchy functions covered in this section are:

• ​resolveLink()​

• ​currentUserID()​

• ​currentUsersGroups()​

• ​executeSavedQuery()​

• ​GetLastModifiedBy()​

• ​draft([Column Name])​

resolveLink()

The resolveLink function is used to insert or update values for link columns. It can be used either with values in the target table or by using the Cinchy Id.

Syntax

ResolveLink('value(s)','column in target table')

'CinchyId,0,CinchyId2,0'

Example

INSERT INTO [Cinchy].[Domain] (Name, Colour)
VALUES ('Test', ResolveLink('Purple','Name'))

INSERT INTO [Corporate].[Projects] (Name, Teams)
VALUES ('CQL Documentation', ResolveLink('Customer Success, Product, Development','Name'))


UPDATE [Product].[Backlog]
SET [Watchers] = '152,0,187,0,347,0'
WHERE [Summary] = 'You can use resolveLink to update link values in DML.'

currentUserID()

The currentUserID() function returns the currently logged in user's Cinchy Id. This is useful for setting up views as well as permissions.

Syntax

[Linked_Column].[Cinchy Id] = currentUserID()

Example

You can use the below example in a view filter so that only the currently logged in user's tasks will appear.

[Assignee].[Cinchy Id] = currentUserID()

currentUsersGroups()

The currentUsersGroups() function returns a list of the Cinchy Ids of groups that the current user belongs to, including any parent groups. For example, if a user is in the Cinchy Product group and the Cinchy Product group is under Cinchy Employees, then both will be returned.

Syntax

[Cinchy Id] IN (currentUsersGroups())

Example

SELECT [Name] FROM [Cinchy].[Groups] 
WHERE [Cinchy Id] IN (currentUsersGroups())

executeSavedQuery()

The executeSavedQuery function returns a scalar or list of scalar values from the saved query specified as the parameters of the function. There are two optional parameters for this function: CacheTimeout and RecordLimitForReturn.

Syntax

[Column] = executeSavedQuery('Domain','Saved Query Name')

Example (Single Value)

[Department] = executeSavedQuery('HR','Get Department')

-- Equivalent to below if you create a Saved Query in the HR domain called
-- Get Department with the following subquery.

[Department] = (SELECT [Team]
                FROM [HR].[Employees] 
                WHERE [Cinchy User Account].[Cinchy Id] = currentUserID())

Example (Multi Value)

[Assignee] IN (executeSavedQuery('HR','Get My Direct Reports')

Optional Parameter: Timeout

A cache expiry timeout for executeSavedQuery is an additional option that can be added. Simply add the number of seconds as a 3rd parameter to the function.

[Department] = executeSavedQuery('HR','Get Department',30)

Optional Parameter: Record Limit

The RecordLimitForReturn parameter limits the amount of records returned. For example: If the query returns 10 records, but you set the parameter to 5, then you will get the first five records back.

[Department] = executeSavedQuery('HR','Get Department',30, 5)

GetLastModifiedBy()

The GetLastModifiedBy([Column]) function will return the CinchyID of the user who last modified the specified column. It is currently only supported in SELECT statements.​

Syntax

GetLastModifiedBy([Column])

Example

This example will return the CinchyID of the user who last modified the Name column in the Employees table.

SELECT getLastModifiedBy([Name])
FROM [HR].[Employees]

draft([Column Name])

This function allows you to query for draft values on tables where Change Approval is enabled.

Syntax

SELECT draft([Column])
FROM [domain].[table]

Example

In this example, we want to query all data in the Employees table, including the data that is pending a change request (Image 1).

To return results that include the draft changes in the First Name column, we would set our query results to Include Draft Data, and use the following syntax (Image 2):

SELECT [Employee ID], draft([First Name]), [Full Name], [Salary], [Date Hired]
FROM [HR].[Employees]