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.
ResolveLink('value(s)','column in target table')
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'))
SET [Watchers] = '152,0,187,0,347,0'
WHERE [Summary] = 'You can use resolveLink to update link values in DML.'
The currentUserID() function returns the current logged in user's Cinchy Id. This is useful for setting up views as well as permissions.
[Linked_Column].[Cinchy Id] = currentUserID()
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.
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 Productgroup and the
Cinchy Productgroup is under
Cinchy Employees, then both will be returned).
[Cinchy Id] IN (currentUsersGroups())
SELECT [Name] FROM [Cinchy].[Groups]
WHERE [Cinchy Id] IN (currentUsersGroups())
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.
[Column] = executeSavedQuery('Domain','Saved Query Name')
[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]
WHERE [Cinchy User Account].[Cinchy Id] = currentUserID())
[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)
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)
This example will return the CinchyID of the user who last modified the Name column in the Employees table.
This function allows you to query for draft values on tables where Change Approval is enabled.
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]
Image 2: Query results that include draft data