CQL
Search…
Cinchy Functions
The Cinchy functions covered in this section are:

resolveLink()

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

1
ResolveLink('value(s)','column in target table')
2
3
'CinchyId,0,CinchyId2,0'
Copied!

Examples

1
INSERT INTO [Cinchy].[Domain] (Name, Colour)
2
VALUES ('Test', ResolveLink('Purple','Name'))
3
4
INSERT INTO [Corporate].[Projects] (Name, Teams)
5
VALUES ('CQL Documentation', ResolveLink('Customer Success, Product, Development','Name'))
6
7
8
UPDATE [Product].[Backlog]
9
SET [Watchers] = '152,0,187,0,347,0'
10
WHERE [Summary] = 'You can use resolveLink to update link values in DML.'
11
Copied!

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

1
[Linked_Column].[Cinchy Id] = currentUserID()
Copied!

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

1
[Cinchy Id] IN (currentUsersGroups())
Copied!

Example

1
SELECT [Name] FROM [Cinchy].[Groups]
2
WHERE [Cinchy Id] IN (currentUsersGroups())
Copied!

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

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

Example (Single Value)

1
[Department] = executeSavedQuery('HR','Get Department')
2
3
-- Equivalent to below if you create a Saved Query in the HR domain called
4
-- Get Department with the following subquery.
5
6
[Department] = (SELECT [Team]
7
FROM [HR].[Employees]
8
WHERE [Cinchy User Account].[Cinchy Id] = currentUserID())
Copied!

Example (Multi Value)

1
[Assignee] IN (executeSavedQuery('HR','Get My Direct Reports')
Copied!
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.
1
[Department] = executeSavedQuery('HR','Get Department',30)
Copied!

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.
1
[Department] = executeSavedQuery('HR','Get Department',30, 5)
Copied!