Cinchy functions

Overview

The Cinchy functions covered in this section are:

• ​resolveLink()​

• ​currentUserID()​

• ​currentUsersGroups()​

• ​executeSavedQuery()​

• ​GetLastModifiedBy()​

• ​draft([Column Name])​

resolveLink()

Use the resolveLink function to insert or update values for link columns. Use it with values in the target table or the Cinchy Id.

Using resolveLink against non-unique data values may not return the same data response each time; in that scenario we recommend that you resolve using the CinchyID instead.

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. This function has two optional parameters: 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 parameters

Timeout

To add a CacheTimeout, enter the number of seconds as a 3rd parameter to the function.

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

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's currently only supported in SELECT statements.​

Value	Definiton
Function Name	GetLastModifiedBy
Function Description	This 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 queries 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's 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, set your 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