Frequently Asked Questions
A list of Frequently Asked Questions. Please use the search function or the Table of Contents to find specific queries.
Last updated
A list of Frequently Asked Questions. Please use the search function or the Table of Contents to find specific queries.
Last updated
You can use PowerShell to count the lines in a delimited file and based on the result decide if you will run the CLI.
There is currently no way for you to find out how many records will be inserted/updated/deleted if you run a CLI without performing the sync.
If the record is still in the table, but has been overwritten by mistake, access your Collaboration Log for the row, and restore back to the correct version.
If your row has been deleted by mistake, access your Recycling Bin, locate the row and restore it.
The only way to truly delete data on the platform is through Data Erasure and Data Compression.
For example: 4,10,15 to be used in WHERE [Id] IN (@param)
This can be done by using parameters in {}, such as {0},{1},...
These will be replaced with the exact text when running the query.
For example: SELECT * FROM [HR].[Employees] WHERE [Deleted] IS NULL AND [Employee ID] IN ({0}) (Image 1).
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:
Currently Cinchy Administrators have access to view/edit/approve all data in the platform. There is no way currently to restrict access for Cinchy administrators.
A workaround is to create a separate "administrators" group which has edit access to all Cinchy system tables, and just leave the "admin" user account or superadmins as "Cinchy administrators."
You can check if a data sync was successful by its exit code. Below is sample code in Powershell to check for the exit code and what they mean.
From the command prompt the following will also return the error code:
In Version 4:
First check <base URL>/elmah, which stores web-related induced errors.
Then check the logs, which can be accessed from <base URL>/admin/index.
Cinchy logs will contain all exceptions thrown by the Cinchy Web application. This includes failed queries, stack overflows and much more.
CinchySSO logs will contain IDP errors.
In Version 5: Errors and Logs can be found through the Opensearch Dashboard.
You can add line breaks in a cell on the UI, the same way as in Excel, by typing Alt+Enter. If you use the expanded row heights option, or manually expand the row, it will show the line breaks.
The best way to load data from external sources into Cinchy, is by using Cinchy's data sync.
You can do the following to preview your changes:
Create staging tables to validate the data first.
Use formatting rules in Cinchy, to highlight data that is not valid.
Configure a CLI using a Cinchy Query source to move the data from the staging tables to the permanent tables.
Right click on the row you want additional information and select the Collaboration Log.
You can also add the "Modified By" and "Modified" columns into the current view/to your query if you want to see it for multiple rows at once.
One Time setup:
Open the Users table
For the password of this user, copy the admin user's password and paste it into the Password field of "defaultuser".
Set the Password Expiration Timestamp to today
In an Incognito browser, navigate to the Cinchy website
Sign in as defaultuser with the admin user password
Cinchy will ask you to change the password for defaultuser, change it to a default password you will give out every time you create an account.
In the original session window, refresh the Users table and remove the Password Expiration Timestamp for defaultuser.
Each time, for new users:
Open the Users table
Create the new user, for example "sandip"
For the password of this user, copy the "defaultuser" password and paste it into the Password field of "sandip".
Set the Password Expiration Timestamp to today
Give the user their username and friendly password created in step 7 above. They will be asked to change their password on first sign in.
You write the query for the records for which you want the change history, including system columns like [Version], [Created], ... and the columns for which you like to see the changes.
You can add an ORDER BY [Version] (either ASC or DESC)
Then you change the query return type to "Query Results (Including Version History )"
The following query will show when the Cinchy instances were upgraded.
Removing and updating a multi-select a link, is the same as setting the link field. The field needs to be updated with the list of values.
The value is a concatenated string of '[Cinchy Id],[Version],[Cinchy Id],[Version],[Cinchy Id],[Version]' from the lookup values
In this example it would set [Multi-Link Field Name] to values with [Cinchy Id] 1, 2, and 3. The version after each Cinchy Id should be 1.
You must provide the full list of multi-select values. If your field was '1,2' and you update it with '3,1' it will end up as '3', not '1,2,3'.
Navigate to <baseURL>/healthcheck
(ex. if your current URL is https://cinchy.mycompany.com/Tables/123?viewId=0 then you would navigate to https://cinchy.mycompany.com/healthcheck)
The response looks this:
In this case your Cinchy version is 4.14.0.0
If you would like to use the health check link for monitoring of the Cinchy application you can add "return503OnFailure=true" to the URL
Use the model loader to load it back in the system (/apps/modelloader).
You create a calculated column in the source and give it the value of the parameter.
For each table, export and import the data via the UI.
Then map the calculated source column to the target. The order of the columns in the source is important. If your source is a file, put the calculated columns at the end in the source, after all the actual columns in the file.
Set the delimiter to "|".
This can be done by using Transformations in the sync configuration of a column. Here is an example:
The pattern contains a regular expression:
^ - anchor for the beginning of the string
0 - the string to replace
* - quantifier to be applied to 0 or more occurrences
Export the Model to XML from the Design Table info tab
Open the exported model in an editor and change the name of the model
Change the name of the table
remove the guids from the table in the model and save the file
Use the modelloader at <cinchy base URL>/apps/modelloader to upload the modified model
Export the data from the Manage Data screen of the initial table and import it in the new table
If you just have a group of tables, see the instructions below. If you have tables, queries, you want to port the permissions, etc. you can use this: Data Experience Deployment - Cinchy Platform Documentation
Table only instructions:
1. Create a dummy Data Experience and add all your tables from your domain to it (Image 2).
2. Hit the following endpoint with the GUID in your row:
<CinchyURL>/api/createdxversion?guid=<GUID>
If [Person 1] and [Person 2] are Link columns and [Member] is a Text column, a calculated column with the following expression will fail to save:
COALESCE([Person 1],[Person 2],[Member])
Please cast the link columns to VARCHAR:
COALESCE(CAST([Person 1] AS VARCHAR(50)),CAST([Person 2] AS VARCHAR(50)),[Member])
This is caused by records in Draft status. To retrieve these records, run a query with return type Query Results (Including Draft Data).
After approving these records, you will be able to disable change approval.
You may have to restore cancelled records, approve them, and delete them so that everything is approved.
The [Cinchy].[Table Access Control] table does not show in the Market Place, but you can query for the data in the table.
In this example it would set [Multi-Link Field Name] to values with [Cinchy Id] 1, 2, and 3. The version after each Cinchy Id should be 1."
Columns do not "Allow Linking" by default. Check the properties of the column in the original table and make sure that in “Show Advanced” the “Allow Linking” checkmark box is selected. If you do not have Design Table access to that table, you will need to ask someone who does to do it.
There are a couple reasons why you may not be able to see any rows:
View Filter
Data access controls
Error with the View or Table
Check the All Data view and see if there is data there, if that is the case but a particular view has no rows, there could be a filter on the view. For example, if there is a "Due Soon" or "My Actions" view, it could just be that there are no records assigned to you that require actioning.
Access controls set on the table could cause you to have access to 0 records. Since you are able to set row level filters in Cinchy, it may be the case that the permissions of the table has not changed, but the data has changed such that you no longer have permission or vice versa.
Error
There may be an error on the view. If the bottom of the page does not show 0 records then there may be an error on the page (Image 3).
It can be done. It is very unlikely that the GUID you want to change to is already allocated, but you should still check. Filter the [Cinchy].[Table Columns] for the new GUID. You should not find it. Then replace it in two places:
the json field in [Cinchy].[Tables] - replace it in the column definition
the GUID field in [Cinchy].[Table Columns]
To replace the table GUID, replace it in the json in [Cinchy].[Tables] and in the GUID field in [Cinchy].[Tables].
When you are done, restart the Cinchy UI.
A query like the following will cause multiple inserts instead of one if your result type is set to Query Results instead of # of Rows Affected.
The same applies to UPDATE statements.
If you need to perform inserts and updates in a query and want to return data at the end, another option is to use the "Single value (First Column of First Row)" return type, which will only be able to return a single value.
When I pass a value to the following query, the result is empty.
The query works without the DECLARE statement. When the DECLARE statement is present, the input variable is ignored, and needs to be SET. In order to still get the variable from the input, a second variable is needed.
When performing a data sync with a Salesforce target, you need to replace nulls with '#N/A' in the source. You can use ISNULL([Column],'#N/A') in the source query. The following is a link to the Salesforce documentation related to this topic:
https://help.salesforce.com/articleView?id=000328822&language=en_US&type=1&mode=1
Declare and set the parameters before invoking the query:
[HR].[Employees and Departments] is:
The default All Data view displays the columns in the same order as in Design Table. But you can create a view and change the columns displayed and their order.
Once link column is added to a table and saved, the multi-select checkbox should be disabled. If you need to change the option, you need to rename the column and create a new link column with the correct option.
The user needs to have "Design Table" permissions granted for the table where he/she will create or edit views and also needs to have the "Can Design Tables" checked in the [Cinchy].[Users] table.
