5️⃣
Cinchy Platform Documentation
Cinchy v5.8
Cinchy v5.8
  • Data Collaboration Overview
  • Release notes
    • Release notes
      • 5.9 release notes
      • 5.8 Release Notes
      • 5.7 Release Notes
      • 5.6 Release Notes
      • 5.5 Release Notes
      • 5.4 Release Notes
      • 5.3 Release Notes
      • 5.2 Release Notes
      • 5.1 Release Notes
      • 5.0 Release Notes
  • Support
  • Glossary
  • FAQ
  • Deployment guide
    • Deploying Cinchy
      • Plan your deployment
        • Deployment architecture
          • Kubernetes architecture
          • IIS architecture
        • Deployment prerequisites
          • Single Sign-On (SSO) integration
            • Enable TLS 1.2
            • Configure ADFS
            • AD Group Integration
      • Kubernetes
        • Disable your Kubernetes applications
        • Change your file storage configuration
        • Configure AWS IAM for Connections
        • Use Self-Signed SSL Certs (Kubernetes)
        • Deploy the CLI (Kubernetes)
      • IIS
  • Upgrade guide
    • Upgrade Cinchy
      • Cinchy Upgrade Utility
      • Kubernetes upgrades
        • v5.1 (Kubernetes)
        • v5.2 (Kubernetes)
        • v5.3 (Kubernetes)
        • v5.4 (Kubernetes)
        • v5.5 (Kubernetes)
        • v5.6 (Kubernetes)
        • v5.7 (Kubernetes)
        • v5.8 (Kubernetes)
        • Upgrade AWS EKS Kubernetes version
        • Update the Kubernetes Image Registry
        • Upgrade Azure Kubernetes Service (AKS)
      • IIS upgrades
        • v4.21 (IIS)
        • v4.x to v5.x (IIS)
        • v5.1 (IIS)
        • v5.2 (IIS)
        • v5.3 (IIS)
        • v5.4 (IIS)
        • v5.5 (IIS)
        • v5.6 (IIS)
        • v5.7 (IIS)
        • v5.8 (IIS)
      • Upgrading from v4 to v5
  • Guides for using Cinchy
    • User Guide
      • Data Browser overview
      • The Admin panel
      • User preferences
        • Personal access tokens
      • Table features
      • Data management
      • Queries
      • Version management
        • Versioning best practices
      • Commentary
    • Builder Guide
      • Best practices
      • Create tables
        • Attach files
        • Columns
        • Data controls
          • Data entitlements
          • Data erasure
          • Data compression
        • Formatting rules
        • Indexing & partitioning
        • Linking data
        • Table and column GUIDs
        • System tables
      • Delete tables
        • Restore tables, columns, and rows
      • Saved queries
      • CinchyDXD
        • Overview
        • DXD workflow
        • Package the data experience
        • Install the data experience
        • Release package
        • Changelog
        • References
          • Cinchy DXD CLI reference
          • Data Experience Definitions table
          • Data Experience Reference table
      • Multilingual support
      • Integration guides
    • Administrator Guide
    • Additional guides
      • Monitor and Log on Kubernetes
        • Grafana
        • OpenSearch dashboards
          • Set up Alerts
        • Monitor via ArgoCD
      • Maintenance
      • Cinchy Secrets Manager
      • GraphQL (Beta)
      • System properties
      • Enable Data At Rest Encryption (DARE)
      • Application experiences
        • Network map
          • Custom node results
          • Custom results in the Network Map
        • Set up experiences
  • API Guide
    • API overview
      • API authentication
      • API saved queries
      • ExecuteCQL
      • Webhook ingestion
  • CQL
    • Overview
      • CQL examples
      • CQL statements overview
        • Cinchy DML statements
        • Cinchy DDL statements
      • Cinchy supported functions
        • Cinchy functions
        • Cinchy system values
        • Cinchy User Defined Functions (UDFs)
          • Table-valued functions
          • Scalar-valued functions
        • Conversion functions
        • Date and Time types and functions
          • Return System Date and Time values
          • Return Date and Time parts
          • Return Date and Time values from their parts
          • Return Date and Time difference values
          • Modify Date and Time values
          • Validate Date and Time values
        • Logical functions
        • Math functions
        • String functions
        • Geometry and Geography data type and functions
          • OGC methods on Geometry & Geography instances
          • Extended methods on Geometry & Geography instances
        • Full Text Search functions
        • Connections functions
        • JSON functions
    • CQL functions reference list
  • Meta-Forms
    • Introduction
    • Install Meta-Forms
      • Deploy Meta-Forms (Kubernetes)
      • Deploy Meta-Forms (IIS)
    • Forms data types
    • Meta-Forms Builder Guide
      • Create a dynamic meta-form with tables
      • Create a dynamic meta-form example with Form Designer
      • Add links to a form
      • Rich text editing in forms
  • Data syncs
    • Get started with data syncs
    • IIS installation
      • Install Connections
      • Install the Worker/Listener
      • Install the Connections CLI
    • Build data syncs
      • Data sync types
      • Design patterns
      • Sync actions
      • Columns and mappings
        • Calculated column examples
      • Advanced settings
        • Filters
        • Variables
        • Auth requests
        • Request headers
        • Post sync scripts
        • Pagination
      • Batch data sync example
      • Real-time sync example
      • Schedule a data sync
      • Connection functions
    • Data sync sources
      • Cinchy Event Broker/CDC
        • Cinchy Event Broker/CDC XML config example
      • Cinchy Table
        • Cinchy Table XML config example
      • Cinchy Query
        • Cinchy Query XML config example
      • Copper
      • DB2 (query and table)
      • Dynamics 2015
      • Dynamics
      • DynamoDB
      • File-based sources
        • Binary file
        • Delimited file
        • Excel
        • Fixed width file
        • Parquet
      • Kafka Topic
        • Kafka Topic example config
        • Apache AVRO data format
      • LDAP
      • MongoDB collection
        • MongoDB collection source example
      • Mongo event
      • MongoDB collection (Cinchy event)
      • MS SQL Server (query and table)
      • ODBC Query
      • Oracle (query and table)
      • Polling event
        • Polling event example config
      • REST API
      • REST API (Cinchy event)
      • SAP SuccessFactors
      • Salesforce Object (Bulk API)
      • Salesforce platform event
      • Salesforce push topic
      • Snowflake
        • Snowflake source example config
      • SOAP 1.2 web service
      • SOAP 1.2 web service (Cinchy Event Triggered)
    • Data sync destinations
      • Cinchy Table
      • DB2 table
      • Dynamics
      • Kafka Topic
      • MongoDB collection
      • MS SQL Server table
      • Oracle table
      • REST API
      • Salesforce
      • Snowflake table
      • SOAP 1.2 web service
    • Real-time sync stream sources
      • The Listener Config table
      • Cinchy Event Broker/CDC
      • Data Polling
      • Kafka Topic
      • MongoDB
      • Salesforce Push Topic
      • Salesforce Platform Event
    • CLI commands list
    • Troubleshooting
  • Other Resources
    • Angular SDK
    • JavaScript SQK
Powered by GitBook
On this page
  • Overview
  • resolveLink()
  • Syntax
  • currentUserID()
  • Syntax
  • currentUsersGroups()
  • Syntax
  • executeSavedQuery()
  • Syntax
  • Optional parameters
  • GetLastModifiedBy()
  • draft([Column Name])
  1. CQL
  2. Overview
  3. Cinchy supported functions

Cinchy functions

PreviousCinchy supported functionsNextCinchy system values

Last updated 1 year ago

Overview

The Cinchy functions covered in this section are:

  • ​

  • ​

  • ​

  • ​

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).

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 1: Querying a table with draft data
Image 2: Query results that include draft data
​resolveLink()​
currentUserID()​
currentUsersGroups()​
executeSavedQuery()​
​GetLastModifiedBy()​
draft([Column Name])​