Skip to main content

Columns

Overview​

Columns are a core feature of the Cinchy platforms. Data stored in columns can be reused across an unlimited number of use-cases to meet your business goals.

Each table that you build in Cinchy must have at least one column, and can have up to a maximum of 336 columns (plus sixteen system columns).

System Columns​

Cinchy has system columns used to perform various functionality. These columns can't be modified directly by a user.

caution

You can't create a new column with the same name as a system column.

NameDescriptionWith Change Approvals EnabledWithout Change Approvals Enabled
Cinchy IdCinchy ID is a unique identifier assigned automatically to all records within a normal table. The Cinchy Id is associated with the record permanently and is never reassigned even if the record is deleted.
VersionUsed to track changes on your data.Any data approvals will increment the Version number.Any changes made to a record will increment the Version number.
Draft VersionUsed to track changes made on draft data.Any data approvals resets the Draft Version to 0. Any proposed data changes increments the Draft Version.Draft Version will always be 0.
Approval StateThis is a legacy column. It will always remain blank.
Created ByThis is a linked column that references the [Cinchy].[Users] table. It will populate with the name of the user who created the record.Will be based on the first user to make changes on an approved record.Created By will be the same for all records with the same Cinchy ID.
CreatedThis is the time whe the record was created, per the logged-in user's timezone.Is the time based on the first user to make changes on the approved record.The time will be the same for all records with the same Cinchy ID.
Modified ByThis is a linked column that references [Cinchy].[Users] table. It will populate with the name of the user who last modified the record.The last user to either modify or approve the record.Will be the name of the last user to modify the record.
ModifiedThis is the time when the record was last modified, per the logged-in user's timezone.Will be the timestamp for when the record was last modified or approved.The time when the record was last modified.
Deleted ByThis is a linked column that references the [Cinchy].[Users] table. It will populate with the name of the user who deleted the record.The name of the user who created the deletion request.The name of the user who deleted the record.
DeletedThis is the time when the deletion (or deletion request) occurred.The time when the deletion request occurred.The time when the record was deleted.
ReplacedCinchy always has one latest/up to date record at a time. Anytime changes are made to a record, a new version (normal or draft) is created, and the previous version is updated with a Replaced timestamp. Any record where the Replaced value is empty is the current version of that record.

Column Parameters​

The following parameters are used when creating columns in Cinchy.

Column Name​

Each column must have a unique name. They must also not conflict with system columns (even if you aren't using Change Approvals on the table).

Column Description​

Each column can optionally have a description. The description is displayed when you hover on the column header in Data Management.

Enable Text Formatting​

Checking this box will turn on formatting options such as checklists, hyperlinks, code blocks, etc.

Data security classification​

Each column has a data security classification. This defaults to blank, and can be set to one of 4 pre-configured settings (Public, Internal, Restricted, Confidential) or additional options can be created in the [Cinchy].[Data Security Classifications] table by an administrator.

info

Currently there is no functionality tied directly to Data Security Classification - the tagging is just for internal auditing purposes. Future security settings will be tied to Data Security Classifications, rather than simply done at a column level.

  • Public: This type of data is accessible to all employees and company personnel. It can be used, reused, and redistributed without repercussions. An example might be job descriptions, press releases or links to articles.
  • Internal: This type of data is strictly accessible to internal company personnel or employees who are granted access. This might include internal-only memos, business performance, customer surveys or website analytics.
  • Confidential: Often, access to confidential data requires additional authorization and explanation of why access to the data is needed. Examples of confidential data include social security numbers, credit card details, phone numbers or medical records. Depending on the industry, confidential data is protected by laws like GDPR, HIPAA, CASL and others.
  • Restricted: Restricted data is the most sensitive data, so you would have to treat it extra carefully. If compromised or accessed without authorization, it could lead to criminal charges, massive legal fines, or cause irreparable damage to the company. Examples include intellectual property, proprietary information or data protected by state and federal regulations.

GUID​

A GUID is a globally unique identifier, formatted as a 128-bit text string, that represents a unique identification. Every column in Cinchy is automatically assigned one. For more information, see the Table and column GUID page

danger

Be careful when editing a GUID, as you can have unintended consequences.

Add to Default View​

Checked by default. After saving your changes this will add the column to be displayed in the default table (All Data by default). Generally it makes sense to be checked since there should be a view where all columns are displayed.

info

If you need to hide a column from certain users or groups you can do so in table controls. It's usually best to have a view where all table columns are displayed.

Mandatory​

Makes the column a mandatory field. You won't be able to save or alter a record in a state where a mandatory field is blank.

Unique​

Requires all values in the column to be unique. Adding a new record or modifying a previous record into a state where it's a duplicate of another record will cause an error and can't be saved.

info

If you need uniqueness across multiple columns instead, you can create an index in Design Table, add those columns and set the index to unique. If it needs to be more complicated, you can also create a calculated column and set that column to unique. For example, First Name doesn't need to be unique, but First Name + Last Name needs to be unique.

Multi-Select​

Some fields can also be set to multi-select.

For example, the column Players in [Football].[Teams] can be a multi-select field since each team will have multiple players.

Note that once a column is created, you cannot change whether it is multiselect or not.

When you need to update a multi-select link field in Cinchy Query Language (CQL), you'll use a concatenated string format.

Example

In the example below, the query modifies the [Users] field in the [Cinchy].[Groups] table. This string is composed of alternating [Cinchy Id] and [Version] values separated by commas, where 7,7,45,3,51,2 represents CinchyId,Version,CinchyId,Version,... for the [Users] records.

UPDATE g
SET g.[Users] = '7,7,45,3,51,2'
FROM [Cinchy].[Groups] g
WHERE g.[Deleted] IS NULL
AND [Cinchy Id] = 29
-- Where ’7,7,45,3,51,2’ represents 'CinchyId,Version,CinchyId,Version,etc.' for the Users records

Allow Linking​

Checked by default. This allows other tables to use the column as a link/relationship.

See Linking data to get more context on how they're used.

info

You want to pick identifying columns for linking, such as IDs or Name. Generally you want to use unique columns, but in some cases it's a better user experience to pick an almost unique field for readability.

For example, Full name may not be unique, but it's much easier to understand than Employee ID.

Allow Display in Linked Views​

Checked by default. Some columns may not make sense for linking but can be useful to display when someone is choosing an option.

See Linking Data to get more context and tips.

Encrypt​

If Data At Rest Encryption is enabled, you will see the option of Encrypt for columns. If this is checked, the column will be encrypted within the database. This is useful for hiding sensitive information so that people with access to the database directly don't see these fields.

Selecting encryption makes no difference to the user experience within the Cinchy platform. The data is displayed in plain text on the UI or via the query APIs.

Column Data Types​

Regular Columns​

Text​

This column type expects data input as text. Text columns have a maximum length; this is set to 500 by default but can be configured by the builder.

Limits: Text types have a maximum storage size of 2^31-1 bytes (2 GB).

info

These are equivalent to NVARCHAR data type in SQL.

Number​

You can choose from 3 display formats for number - regular, currency, and percentage. You can also decide how many decimal places to display (0 by default). Note that these are both display settings, and won't affect how the number is stored.

Limits: In a TSQL platform, number types have an 8 byte limit with an allowed range of - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

Limits: In a PGSQL platform, number types have an allowed data range of up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.

info

These are equivalent to the FLOAT(53) data type in TSQL platforms and the DECIMAL data type in PGSQL.

Date​

Cinchy has several Date column type display format options available:

  • MMM DD, YYYY (Oct 31, 2016)
  • YY-MM-DD (16-10-31)
  • DD-MM-YYYY (31-10-2016)
  • DD-MMM-YY (31-Oct-16)
  • Custom Format

Limits: There is a 3 byte storage limit with an allowed date range between 0001-01-01 through 9999-12-31.

info

The "Default Value" field isn't mandatory and should be left blank (best practice). However, if populated you won't be able to clear the default date value provided to a "blank" data (no date). You will only be able to overwrite it with another date value.

info

These are equivalent to DATE() data type in SQL.

tip

When updating a date field using a variable, and no value is entered for that variable, the date field will be 1900-01-01. To avoid this, use a case statement to replace the empty string with NULL, as shown in the following example:

UPDATE E
SET E.[Date Hired]=CASE WHEN @dhired<>'' THEN @dhired ELSE NULL END
FROM [HR].[Employees] E
WHERE E.[Deleted] IS NULL AND [Employee ID]=@empid

Yes/No​

You must select a default value of yes (1) or no (0) for yes/no fields.

info

These are equivalent to bitdata type in SQL.

Choice​

You can create a choice column (single or multi-select) in Cinchy. In this scenario, you specify all your choices (1 per newline) in the table design. A user will only be able to select from the options provided.

Calculated Columns​

A calculated column uses values from other fields in the record for its evaluation. These columns also have a specified result type, which dictates the format of the calculated output.

Example:

A [Full Name] column can be calculated as CONCAT([First Name], ' ', [Last Name]).

info

These columns are similar to computed columns in SQL databases.

Live vs Cached Calculated Columns​

When creating a calculated column, you have two types to choose from: cached and live. This feature is accessible via the Advanced Settings and was a part of the 4.0 version update.

Cached Calculated Columns

  • What It Does: Speeds up data retrieval.
  • How It's Stored: As an actual column based on a CQL formula.
  • When It Updates: Updates only if the data in the same row changes.

Example:

Changing a name in a single row only triggers a recalculation for that row's "Label" column.

Limitations

If a cached column relies on a column from another table, changes in the other table's column won't automatically update the cached column. Make sure to account for this when using cached columns that depend on external data.

Live Calculated Columns

  • What It Does: A live calculated column is a non-cached calculated column that provides real-time data.
  • How It's Stored: As a formula executed on-the-fly during read or query.
  • When It Updates: Refreshes automatically upon every query or screen refresh.
  • When to use:
    • Your calculated column depends on a value from a linked table and you need the latest value from the linked table.
    • Your table doesn't contain many records.

Example:

A live "Label" column will update instantly if any referenced data changes, affecting all rows and tables.

Limitations

  • Live columns consume more system resources.
  • Using user-defined functions in live calculated columns can cause errors if they reference other live calculated columns. Only use inbuilt functions in live columns if they reference other live columns.

Geospatial Columns​

If you created a spatial table, you will have access to the geography and geometry column types. These columns also have the option to be indexed via Index in the advanced settings on the column itself.

Check off Index to create an index on a geospatial column

Geometry​

In the UI, this takes a well-known text (WKT) representation of a geometry object. You can modify or paste the WKT representation directly in the editor on the UI. Geometric functions can be performed on this column through CQL and calculated columns.

Geography​

In the UI, this takes a well-known text (WKT) representation of a geography object. You can modify or paste the WKT representation directly in the editor on the UI. Geographic functions can be performed on this column through CQL and calculated columns.

Link columns allow you to establish inherent relationships with other records in other tables. See Linking Data for more details.

Hierarchy Columns​

Hierarchy columns are link columns referencing the current table. For example, the below table contains a list of documentation pages, some of which also have sub-level pages (or even sub-sub-level pages). Using a Hierarchy Column shows the relationships between data.

Example 1: API Overview is the parent page. It had four sub-pages: API Authentication, API Saved Queries, ExecuteCQL, and Webhook Ingestion. Clicking on any of the links within the Sub-Pages column would return you to the row for that specific data set.

Example 2: Builder Guides is the parent page. It has five sub-pages: Best Practices, Creating Tables, Saved Queries, Integration Guides, and CInchy DXD Utility. In this example, we also have another level of hierarchy, wherein Best Practices is also a parent page, and Multilingual Support is its sub-page.

Another common use of Hierarchy columns are to show Manager/Employee relationships