CQL
Search…
⌃K

Cinchy Functions

The Cinchy functions covered in this section are:
resolveLink is a function that needs to be used to insert or update values for link columns. resolveLink can be used either with values in the target table or using the Cinchy Id.
Using resolveLink against non-unique data values may not return the same data response each time. We recommend that you resolve using the CinchyID instead.

Syntax

ResolveLink('value(s)','column in target table')
'CinchyId,0,CinchyId2,0'

Examples

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 current logged in user's Cinchy Id. This is useful for setting up views as well as permissions.

Syntax

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

Example

Create a view where [Assignee].[Cinchy Id] = currentUserID() is the filter to show My Tasks. I could also set a row level filter such that I can only edit rows where I am the assignee.

currentUsersGroups()

The currentUsersGroups() function returns a list of the Cinchy Ids of groups that the current user belongs to, including any parent groups (ex. 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.
Value
Definiton
Function Name
GetLastModifiedBy
Function Description
This function function will return the CinchyID of the user who last modified the specified column.
Function Type
Scalar
Return Type
Numeric. It returns the CinchyID

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.
When querying for draft data, the query result type needs to be set to "Query Results (including Draft Data)"

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).
Image 1: Querying a table with draft data
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]
Image 2: Query results that include draft data