1. Overview

Data erasure allows you to permanently delete data in Cinchy. As the data owner, you can set an erasure policy on your table if you need to delete data for compliance reasons (Image 1).

The actual erasing of data happens during the maintenance window. Please check with your system administrators to confirm when maintenance is scheduled.

Once data is erased, any links pointing to erased data will look like this (Image 2):

2. Change Approval Enabled Tables

The time is counted based on the record's modified time stamp, not the deleted time stamp. This means for change approval records it is the time when the pending delete request was approved and moved to the Recycle Bin, not when the delete request was made.

You can find the Data Controls menu on the left-hand navigation bar of a table.

From here you may select to:

• Change your Data Entitlements

• Erase your Data

• Compress your Data

Table of Contents

1. Overview

You are able to attach files and images to any row in a Cinchy table by creating a linked column that is linked to the 'Files' system table of your platform.

If you have access to view the 'Files' table, you can also view every attachment on your system.

2. How to Attach a File

1. Navigate to the table where you want to attach your file.

2. Click Design Table > Columns

3. Add a new column with the following parameters (Image 1):

   1. Column Name: We recommend you name this something easy to recognize, like "Images and Files"

   2. Data Type: Link

   3. Linked Table: You will link this to the "Cinchy\Files" table

   4. Linked Column: File Name

   5. Advanced Settings: Make sure to select the "Multi-select" checkbox if you want the capability to add more than one file to a row.

4. Click Save

5. Navigate back to your table and locate your newly created files column.

6. To attach a file, click on the upload button (located in the top right hand corner of any cell in the column) (Image 2).

7. From the pop-up window, select Choose Files to pick your file from your machine, then click Submit (Image 3).

8. Once uploaded, your cell should look like this (Image 4):

3. Deleting an Attached File

To fully delete your attachment from the platform, you will need to navigate to the Files system table and delete the row associated with your attachment.

Your file will appear crossed out in your original table and cannot be opened or downloaded (Image 5).

Table of Contents

1. Overview and Example

1. When viewing a table, click on Data Controls > Entitlements from the left navigation menu (Image 1).

2. Currently both the table creator and anyone in the Cinchy Administrators group has access to perform any action on any objects. You can give granular entitlements at a Group or a User level, for both viewing and editing access (Image 2).

3. In the above scenario, John Smith is part of the Developers group. He is able to view all columns via the entitlement to the Developers group, and he is able to edit both the First Name and Last Name column through different entitlements.

2. Table Level Entitlements

There are certain entitlements in the Data Controls menu that apply to the entire table.

2.1 Marketplace

Approving this entitlement enables users to see and serarch for the table in the Marketplace/Homepage.

2.2 Bulk Export

Approving this entitlement enables users to export data from the table via the Manage Data screen (Image 3).

2.3 Direct Query

Approving this entitlement enables users to query the data from the table directly in the Query Builder (Image 4).

2.4 Design Table

Approving this entitlement enables users to alter the structure of the table.

2.5 Design Controls

Approving this entitlement enables users to change the permissions on a table.

3. Column Level Entitlements

There are certain entitlements in the Data Controls menu that apply only to columns.

3.1 View All Columns

Approving this entitlement enables users to view all columns within the table.

3.2 View Specific Columns

This is a drop down where you can select the specific columns you want to grant view access to for users.

3.3 Edit All Columns

Approving this entitlement enables users to edit all columns within the table.

3.4 Edit Specific Columns

This is a drop down where you can select the specific columns you want to grant edit access to for users.

3.5 Approve All Columns

Approving this entitlement enables users to approve all columns within the table. This also allows users to approve Create and Delete requests.

Approve permissions only apply when Change Approvals are enabled.

3.6 Approve Specific Columns

This is a drop down where you can select the specific columns you want to grant approve access to for users.

Approve permissions only apply when Change Approvals are enabled.

3.7 Link Columns

Link columns require both permission to the column within this table, as well as the column in the link column itself.

4. Row Level Entitlements

These are entitlements that apply to specific rows. Used in conjunction with Column Level entitlements this allows for granular cell level entitlements.

4.1 Insert Row

Approving this entitlement enables users to create new rows in the table.

4.2 Delete Row

Approving this entitlement enables users to delete rows in the table.

4.3 Viewable & Editable Row Filter

This is a CQL fragment that applies a filter to which rows will be viewable or editable. Think of the column entitlements and the fragment as a SQL statement applied to the table.SELECT {Edit Selected Columns} WHERE {Editable Row Filter}

4.4 Examples for Row Filter

Most of these examples will be with the editable row filter so it is easy to see the underlying data for comparison. However this can be done for viewable row data as well.

Sample Data

(Image 5)

Simple Example

With the following entitlements (Image 6):

• Edit Specific Columns: Age

• Editable Row Filter: [Age] > 30

Example with Viewable Data

(Image 7)

• View Specific Columns: First Name, Last Name

• Viewable Row Filter: [End Date] IS NULL OR [End Date] > GetDate()

Layer On Another Entitlement

(Image 8)

• View Specific Columns: All

• Edit Specific Columns: First Name, Last Name, Age

• Viewable Row Filter: [First Name] = 'John'

• Editable Row Filter: [First Name] = 'John'

Example for Current User

(Image 9)

For the All Users group:

(Image 10)

• View All Columns: Check

• Edit Selected Columns: First Name, Last Name

• Editable Row Filter: [User Account].[Cinchy Id] = CurrentUserId()

To allow a user to edit certain fields of their own data, you will need an association from a user to the [Cinchy].[Users] table. You can then use the following function to allow edit for that user, where [...] is the chain of link columns to get to the Users table.

[...].[Cinchy Id] = CurrentUserId()

Table of Contents

1. Creating a Table from Scratch

1. Navigate to the Cinchy homepage. In the upper left-hand corner, click on Create to get started. (Image 1)

2. Select either a Standard or a Spatial Table (Image 2), per the descriptions below.

• Spatial Table: A spatial table allows you to create geography and geometry column types, as well as geospatial indexes. You will not be able to create partitions on a spatial table.

• Standard Table: You cannot create geography or geometry columns in a standard table.

3. Select From Scratch (Image 3).

4. A new page will open with the Table Info tab (Image 4). Input the following information:

• Table Name: This is a mandatory field, and must be unique to the domain you select.

• Icon: You can pick an icon and colour to differentiate your table on thee home screen.

• Domain: This is a mandatory field. Select the domain that this table will reside under. If you are have administrative privileges, you can also create new domains from this screen.

• Description: You can give your table a description, which will be displayed on the homepage.

5. When you are finished with the Info page, select Columns from the left-hand navigation bar (Image 5). See here for additional information about column types.

6. A new page will open with the Columns tab (Image 6). Every table in Cinchy must have at least one column. Input the following information:

• Column Name: Input a unique column name.

• Data Type: You can select from the following:

  • Text: All data in this column must be input as text

  • Number: All data in this column must be input numerically

  • Date: All data in this column must be in date format. The default is yyyy-mm-dd however you can change that.

  • Yes/No: All data in this column must be in Yes/No format

  • Calculated: Data is this column is calculated using a CQL expression

  • Choice: Data entered in this column must be selected from a set of choice answers that you provide

  • Link: Data in a link column is pulled from elsewhere on Cinchy

• Description: Enter a description of your column

• Data Security Classification: You can select from Public, Internal, Confidential, or Restricted. Additional options can be created in the [Cinchy].[Data Security Classifications] table by an Administrator.

  • Restricted: Restricted data is what we can call "high stakes". If compromised or accessed without authorization, it could lead to criminal charges, massive legal fines, or cause irreparable damage to the company. This is the most sensitive data, so you would have to treat it extra carefully. Examples include intellectual property, proprietary information or data protected by state and federal regulations.

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

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

  • Public: This type of data is freely accessible to all employees and company personnel. It can be freely used, reused, and redistributed without repercussions. An example might be job descriptions, press releases or links to articles.

• Advanced Settings: Select any checkboxes that pertain to your column. See here for more information about these parameters.

7. Click on Design Controls > Entitlements in the left navigation pane to set your permissions (Image 7). You may set these as granular as you choose. You may also set permissions on a view by view basis. See here for more about data controls and entitlements.

8. Click Save to finalize your table.

9. You may return to change the structure of the existing table (i.e. Rename columns, add new columns, change data type) by clicking on the Design Table button on the left-hand navigation (Image 8).

2. Creating a Table by Importing a CSV

1. Navigate to the Cinchy homepage. In the upper left-hand corner, click on Create to get started (Image 9).

2. Select either a Standard or a Spatial Table (Image 10), per the descriptions below.

• Spatial Table: A spatial table allows you to create geography and geometry column types, as well as geospatial indexes. You will not be able to create partitions on a spatial table.

• Standard Table: You cannot create geography or geometry columns in a standard table.

3. Select Import a CSV (Image 11).

4. Enter the following information:

• Domain: Select the domain that you table will reside under. If you are have administrative privileges, you can also create new domains from this screen.

• File: In order to create the table, you must upload a .csv file.

5. When creating a table via Import a CSV, a few settings will be set by default:

• Default Table Name: The name of the file will be used as the name of the table (a number will be appended if there is a duplicate - ex. uploading Teams.csv will create a table named Teams 1, then Team 2 if uploaded again). You can always rename the table after it has been created.

• Default Table Icon: The icon defaults to a green paintbrush.

• Default Column Types: Columns by default will be created as a text field, with a maximum length of the longest value in the column. If a column has only numeric values in it, it will be created as a numeric column.

6. To update these settings, navigate to the Design Table tab on the left navigation bar (Image 12).

3. Table Views

1. When you first create a table, a default view called All Data will be created for you, which you can find on the left navigation bar under Manage Data (Image 13).

2. You can create additional views by clicking on "+Create View" (Image 14).

3. You may chose to create a view From Scratch or by Copying an Existing view (Image 15).

From Scratch:

1. Select "From Scratch".

2. The Columns tab will open. Create a Name for your View (Image 16).

3. If you'd like this to become the default view, toggle the default setting to On (Image 16).

4. Select the column(s) that you want to be visible in this view (Image 17). You may rearrange the column order using drag and drop.

5. Click on the Sort tab in the left navigation bar (Image 18).

6. Use this screen to select which columns you'd like to sort your data by, and in which order. You may rearrange the columns using drag and drop (Image 19).

7. Click on the Filter tab in the left navigation bar. Here, you may use query language to focus your view (Image 20).

8. Click on the Permission tab in the left navigation bar. Here, you may set permissions for who can use this view. By default, it is set to All Users (Image 21).

9. Select Save to finalize your view.

From Existing:

1. Select "From Existing".

2. Select which view you would like to copy (Image 22).

Updating a View

1. To update any view, including the Add Data view, click on the pencil icon next to the view's name under Manage Data (Image 23).

4. Bookmarks and the Homepage

Once you create a table, it will be added to your bookmarks by default. Other users (or if you un-star the table from your bookmarks) will see it in the Homepage if they have permissions to.

Table of Contents

1. System Columns

Cinchy contains system columns used to perform various functionality. These columns cannot be modified directly by a user.

1.1 Cinchy Id

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

1.2 Version & Draft Version

Version and Draft Version are used to track changes and versions.

Without Change Approvals Enabled

Any changes made to a record increments the Version. Draft Version is always 0.

With Change Approvals Enabled

Any data approval increments Version and resets Draft Version to 0. Any proposed changes increments the Draft Version.

1.3 Approval State

This is a legacy column. It is always blank.

1.4 Created By & Created

Created By is a linked column to the [Cinchy].[Users] table, of the user who created the record.

Created is the time when the record was created, per the logged-in user's timezone.

Without Change Approvals Enabled

Created By and Created will be the same for all records with the same Cinchy Id.

With Change Approvals Enabled

Created By and Created is based on the first user to make changes on an approved record. So the user where Draft Version = 1.

1.5 Modified By & Modified

Modified By is a linked column to the [Cinchy].[Users] table, of the user who last modified the record.

Without Change Approvals Enabled

The last user to modify the record, and when it happened, per the logged-in user's timezone.

With Change Approvals Enabled

The last user to either modify the record (Draft Version != 0) or approve the record (Draft Version = 0). The timestamp for when that version was generated.

1.6 Deleted By & Deleted

If a record is deleted, it will show up in the Recycle Bin.

Without Change Approvals Enabled

A deleted record will have Deleted By and Deleted filled in, with the timezone set to the logged-in user's.

With Change Approvals Enabled

Deleted By and Deleted are based on the user/time when the Delete Request was created, per the logged-in user's timezone., not when it was approved.

1.7 Replaced

• There is always only 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 Replaced is empty is the current version of that record.

2. Common Fields

2.1 Name

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

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

• Public: This type of data is freely accessible to all employees and company personnel. It can be freely 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 what we can call "high stakes". If compromised or accessed without authorization, it could lead to criminal charges, massive legal fines, or cause irreparable damage to the company. This is the most sensitive data, so you would have to treat it extra carefully. Examples include intellectual property, proprietary information or data protected by state and federal regulations.

2.3 Description

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

2.4 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 on GUIDs, you can review the documentation here.

2.5 Common Parameters

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.

Mandatory

Makes the column a mandatory field. You will not 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 is a duplicate of another record will cause an error and cannot be saved.

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.

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 are used.

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 do not see these fields.

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

3. Regular Columns

3.1 Text

Text columns have a maximum length, set to 500 by default.

These are equivalent to VARCHAR(n) data type in SQL.

3.2 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 will not affect how the number is stored.

These are equivalent to FLOAT(53) data type in SQL (default, 8-byte field).

3.3 Date

There are several Date column type display format options available in Cinchy:

• MMM DD, YYYY (e.g. Oct 31, 2016)

• YY-MM-DD (e.g. 16-10-31)

• DD-MM-YYYY (e.g. 31-10-2016)

• DD-MMM-YY (e.g. 31-Oct-16)

• Custom Format

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

3.4 Yes/No

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

These are equivalent to bitdata type in SQL.

3.5 Calculated

A calculated column is evaluated using other fields on the record. It will also have a result type - which is the form in which the calculated results will be stored.

For example, you can have a column [Full Name] that is CONCAT([First Name], ' ', [Last Name]).

These are equivalent to computed columns in SQL.

3.5.1 Cached vs Live Calculated Columns

When creating a calculated column, you will notice the option to have it cached or not, located under Advanced Settings. This was an option introduced in version 4.0 of the platform.

A cached calculated column stores your data for fast retrieval and querying. Calculated columns are defaulted to cached. This is an actual column in your table that is based on a defined CQL formula. This column will recalculate when data is changed in the same row.

In the below example, the Label column is a calculated column that connects various name columns together. You can see that "Connect your data" appears in each label. If you then wanted to change the name in row one from "Connect your data" to "Connect all your data", only that specific row would recalculate automatically to update the label. To update any other row (within this table or another) with a calculated column that references that data, you would need to manually make changes to each in order to prompt a recalculation.

The other option is to uncheck the cached button, and to have a live calculated column instead. A live column runs the query in real time, on the fly: it is essentially a stored formula that is only executed when you read or query the record, and is executed each time you do so.

In the above example, if the Label column was a live calculation, the formula would run every time you refresh the screen or requery the data. If changes have been made (in this example, changing "Connect your data" to "Connect all your data"), the calculated column will automatically reflect them, regardless of if it references another row or even another table.

While live calculated columns can be very powerful this way, they also have their drawbacks, including possibly throwing errors and being very resource intensive. In some cases, live calculated columns can even break a table (for example if you have a live calculated column linking to another live calculated column, you may run into errors).

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

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

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

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

5. Link Columns

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

6. Hierarchy Columns

Hierarchy columns are simply 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 allows you to quickly view 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

Overview

This page documents the method of resstoring deleted taables, columns, and rows in your Cinchy table.

1. Restoring a Deleted Row

If you wish to restore a row that has been deleted, you can do so through the following steps:

1. In the table where you want to restore the row, navigate to the Recycle Bin.

2. Locate the deleted row.

3. Right click anywhere in the row > Restore Row (Image 1).

4. You should see a "Restore Successful" pop-up.

2. Restoring a Deleted or Changed Column

If you wish to restore a column that has been deleted or changed (such as a name change), you can do so through the following steps:

1. Navigate to the [Cinchy].[Tables] table.

2. Find the row with the table that has the column you want to restore > right click anywhere in the row > Collaboration Log > Revert to a previous version (Image 2).

3. You should see a "Revert Successful" pop-up.

3. Restoring a Deleted Table

If you wish to restore a table that has been deleted, you can do so through the following steps:

1. Navigate to the [Cinchy].[Tables] table.

2. Navigate to the Recycle Bin.

3. Find the row with the table that you want to restore > right click > "Restore Row" (Image 3)

4. You should see a "Restore Successful" pop-up.

If you need to manage space within your database, you can set a data compression policy. Currently we allow you to permanently delete versions in the collaboration log. Be aware that the current version of compression is a LOSSY process (data will be permanently deleted). Take that into consideration when configuring a policy.

The actual compression of data happens during the maintenance window. Please check with your system administrators to confirm when maintenance is scheduled.

Change Approval Enabled Tables

The number of versions is based on the major version and not the minor version. This means for a record on version 35.63 with a keep most recent 10 versions, it will keep all versions 26.0 +, rather than all versions 35.44+.

Table of Contents

1. Indexing

Indexing is used to improve query performance on frequently searched columns within large data sets. Without an index, Cinchy begins a data search with the first row of a table and then follows through the entire table sequentially to find all relevant rows. The larger the table(s), the slower the search.

If the table you are searching for has an index for its column(s), however, Cinchy is able to search much quicker.

In the below example, we will set up a query for a Full Name field. When you create an index for that field, an indexed version of your table is created that is sorted sequentially/alphabetically.

When you run your query on this index, that table will be searched using a binary search.

A binary search will not start from the top record. It will check the middle record with your search criteria for a match. If a match it not found, it will check whether the found value is larger or smaller than the desired value. If smaller, it reruns the data check with the top half of the data, finding the median record. If larger, it reruns the data check with the bottom half of the data, finding the median record. It will repeat until your data is found.

1.1 Setting up an Index

In this example, we have a table with employee names (Image 1). We want to search for "John Smith", using the Full Name column.

1. To set up your index, select Design Table from the left navigation tab.

2. Click Indexes (Image 2).

3. Select "Click Here to Add" and fill out the following information for a new index. Click save when done (Image 3):

• Index Name.

• Select the column(s) to add to your index. For our example we have selected the Full Name column to be indexed.

  • You can select more than one column per index.

• Select the Included column(s) to add to your index, if applicable.

  • The difference between regular columns and Included columns is that indexes with included columns provide the greatest benefit when covering your query because you can include all columns your query may reference, such as columns with data types, numbers, or sizes not allowed as index key columns.

4. We can now query our full name column for John Smith and receive our results quicker than if we hadn't set up our index (Image 4).

2. Full Text Indexing

A full-text index is a special type of index that provides index access for full-text queries against character or binary column data. A full-text index breaks the column into tokens and these tokens make up the index data.

2.1 Setting up a Full Text Index

1. Click on Design Table > Full-text Index

2. Add in the desired column(s) and click save when done (Image 5).

3. Columnar Indexing

You may want to use a columnar index when:

• Your table has over 1 million records.

• Your data is rarely modified. Having large numbers of deletes can cause fragmentation, which adversely affect compression rates. Updates are also processed as deletes followed by inserts, which will adversely affect the performance of your loading process.

3.1 Setting up Columnar Indexing

1. Click on Design Table > Columnar Index

2. Add in the desired column(s) and click save when done (Image 6).

4. Partitioning

Partitioning data in a table is essentially organizing and dividing it into units that can then be spread across more than one file in a database. The benefits of this are:

• Improved efficiency of accessing and transferring data while maintaining its integrity.

• Maintenance operations can be performed on one or more partitions more efficiently.

• Query performance is improved based on the types of queries most frequently run.

When creating a partition in Cinchy, you use the values of a specified column to map the rows of a table into partitions.

4.1 Setting up a Partition

In this example we want to set up a partition that divides our employees based on a Years Active column (Image 7). We want to divide the data into two groups: those who have been active for two years or more, and those who have only been active for one year.

1. Click on Design Table > Partition

1. Fill in the following information and click save when done (Image 8):

• Partitioning Column: this is the column value that will be used to map your rows. In this example we are using the Years Active column.

• Type: Select either Range Left (which means that your boundary will be <=) or Range Right (where you boundary is only <). In this example we want our boundary to be Range Left.

• Add Boundary: Add in your boundary value(s). Click the + key to add it to your boundary list. In this example we want to set our boundary to 2.

Once set up, this partition will organize our data into two groups, based on our boundary of those who have a Years Active value of two or above.

2. You can now run a query on your partitioned table (Image 9).

1. Overview

A GUID is a globally unique identifier, formatted as a 128-bit text string, that represents a unique identification. Both Cinchy Tables and Columns have a GUID.

This feature is particularly useful when deploying between Cinchy instances.

For example, in a model deployment, you must have matching GUIDs on your columns in order for them to properly load between environment A and environment B. There might be times when these GUIDs don’t automatically match, however, such as if you manually added a new column to environment B and also manually added it to environment A.

In this case, the two columns would have different GUIDs, and the model deployment would fail. With this new feature, however, you can match up conflicting GUIDs to properly load your model.

1. Viewing and Editing GUIDs

You have the ability to display and edit these table and column GUIDs within the Design Table screen.

Table GUIDs musts be unique to your specified environment.

Column GUIDs must be unique to the table.

1. Table GUIDs can be found under Design Table > Info > GUID (Image 1).

2. Click on the pencil icon to edit the GUID.

3. Column GUIDs can be found under Design Table > Columns > GUID (Image 2).

4. Click on the pencil icon to edit the GUID.

1. Columns

There are formatting rules for columns:

1.1 Row Condition

This follows the same syntax as a view filter query.

1.2 Ordinal

Order in which the formatting rules will apply on the same table. Ordinal 1 will show up above ordinal 2.

1.3 Highlight Color

Color to highlight the cell. If you want to add your own colors you can do so within System Colours and check off highlight under usage.

1.4 Table

Table in which to apply the conditional formatting.

1.5 Highlight Columns

Columns to apply the conditional formatting rules to. You do not need to include any row condition columns within the highlight columns.

2. Example

Table of Contents

1. About Linking

Linking is done by the Cinchy ID, which is unique. When you create a link column, you select a column to link to. This is simply a decision on which field to show from the linked record. You should pick a unique field to link on to avoid confusion if possible.

Once a record is created, its Cinchy ID never changes. This means that modifying the row of data in the linked table will not change the relationship in your table to that row. This also means that if you did not use a unique column, even though the UI looks the same, you are actually linking to different rows.‌

2. Choosing a Linked Column

‌In general, you should only use unique columns as the linked column. This needs to be balanced with readability in other tables.

For example, Full Name might not be unique to every employee, but it is a lot more readable and understandable than Employee ID. In other cases, it makes sense to link via an ID and simply add a display column to show relevant information.‌

2.1 Allow Linking

‌To help other builders follow best practices of only linking to unique (or close to unique, such as Full Name) columns, you should un-check the Allow Linking checkbox for non-unique columns so they will not be able to use it for linking.‌

2.2 Allow Display in Linked View

‌If this option is unchecked, it prevents users from showing this column in another table.

For example, if you have an ID card # within an employees table, you may not want to display it to the rest of the company because it simply would not be relevant when they are linking to employees and want to see additional information (such as department, title, location). Arguably, a lot of these columns are also taken care of by access controls (since most people will not have access to view that column).‌

Generally unchecking this box should be done sparingly, as it does not impact the security of your data, only how convenient it is to see it.‌

2.3 Display Columns

‌When you select a record to link to on the Manage Data screen, it can be useful to see additional information about the records to ensure that it is the record you want to link to (Image 1). You can add additional display columns in the advanced options for link columns (Image 2).

When you type in the cell, all displayed columns will be searched through, not just the Linked Column (Image 3). (Green does not have a B in it, but #00B050 does so the Green record shows up)

‌

2.4 Link Filter

‌‌The link filter can be used to filter records from the table being linked. This is useful for reducing the options to only the typical use case. Commonly used for filtering the drop down to a list of active users or other resources, while not preventing someone from entering missing records with inactive resources.‌

Note that this is simply a display filter, it does not prevent other values from being entered as long as they are valid records in the linked table.‌

3. Relationships

‌You can define 1 to 1, 1 to many, and many to many relationships.‌

3.1 1:1 Relationship

‌Generally it is rare to link 1:1 relationships since they should usually be in the same table. For example, you would not have a separate table for Employee Phone Number and Employee Address, they would simply be two columns within the Employees table. However there are cases nonetheless where it makes sense, for example, a Keycard tracking table where each keycard has 1 assigned employee.‌

To enforce a 1:1 relationship within Cinchy, you set the unique constraint and leave it as single-select when creating a link column.‌

3.2 1:Many Relationship

‌A common relationship to have is a one to many relationship. For example, one customer can have multiple invoices.‌

To enforce a 1:many relationship within Cinchy, you want to create a link column in the table on the “many” side of the relationship (in the above example, in the invoices table) and leave the link column as single select.‌

3.3 Many:Many Relationship

‌You can also have a many to many relationship. For example, you can have multiple customers, and multiple products. Each customer can order multiple products, and each product can be ordered by multiple customers. Another example is books and authors. An author can write multiple books, but a book can also have multiple authors. There are two ways to express many to many relationships.‌

In the case of multiple customers and multiple products, we want to use orders as an intermediary table to create indirect relationships between customers and products. Each order has 1 customer, and each order has multiple products in it. We can derive the relationship between customers and products through the orders table.‌

To create a many:many relationship through a different entity, you want to create a table for orders. Within orders, you want to create a single-select link to customers and a multi-select link to products.‌

In the case of books and authors, it makes sense to create a multi-select link column in the Books table where multiple authors can be selected.‌

To create a multi-select link column in Cinchy, you simply check off the Multi-Select option when you create a new link column.

1. System Tables

System tables are included out-of-the-box with your Cinchy platform, and can be used to track and manage a variety of data.

You can easily query for a list of your system tables using the below:

The system tables included are:

• Applets: This system table manages a list of all your integrated applications

• Data Experience Definitions: This is a system table for managing data experience definitions

• Data Experience References: This is a system table for managing reference data for data experiences

• Data Experience Release Artifacts: This is a system table for maintaining data experience release artifacts

• Data Experience Releases: This is a system table for maintaining data experience releases

• Data Security Classifications: This is a system table for maintaining data security classifications

• Data Sync Configurations: This system table manages a list of all your data sync configurations.

• Domains: This system table manages a list of all the domains in your instance.

• Execution Log: This system table tracks the execution logs of data syncs

• Formatting Rules: This system table manages your formatting rules

• Groups: System table for managing all groups

• Literal Groups: This system table maintains a list of groups

• Literal Translations: This system table maintains a list of literal translations

• Literals: This system table maintains a list of literals

• Regions: This system table maintains a list of regions

• Saved Queries: This system table manages a list of all user saved queries that can be exposed via the REST API

• System Colours: System table for maintaining colours

• Table Access Control: This system table maintains a list of all table access controls in your instance

• Table Columns: This system table manages a list of all the system column definitions

• Tables: This system table manages a list of all the tables in your instance.

• User Defined Functions: This system table manages a list of all your user defined functions

• Users: System table for managing all user information including enabling/disabling the ability to create tables, queries, etc.

• Views: This system table manages a list of all the views in your instance.