Tables and views

Both tables and views have a common header menu on the right side of the table widget, that contains a number of actions (listed below) that you can perform based on the contents of the table.

Graph

When clicking the Graph , the right sidebar graph menu will be open. For more information about the sidebar, please click here.

If you are interested in creating an aggregated report of the table or view, without writing any SQL code, you can do that by clicking the Measurements . Then, simply start adding the columns based on the report by clicking the button +.

Scai detects automatically based on the column data type whether it should be:

group by - the aggregation will group the results on the values of this column (typically chosen for non-numerical columns)
pivot - the distinct values of the column will be used to create a pivot table. Each distinct value will then be represented as a separate column in the aggregated results.
measurement - an aggregate function that will be applied to the values of this column, e.g. sum, average, count, min, max, etc. For a full list, check the list of Supported aggregate functions.

If you want to change the names of the columns that will be displayed in the aggregated table, simply fill in the alias input after the name of the column.

When satisfied with the selected columns for the aggregated report, click the Save button.

For every table/view you have some helpers in this menu that enable you to remember a set of filters that you commonly use. The following options are available:

clear - clears all filters/orderings you have applied on the current table
remember - remembers the current set of applied filters for this table. This set can be automatically applied at a later time by clicking the reload option. Use this if you would like to have the contents of a table ordered/filtered in a certain way each time you access it.
forget - forgets any remembered filters. Any filters that you might have saved using the remember button will now be lost.
reload - applies the set of filters that were most recently remember-ed

Options

For every table/view and depending on user permissions, an options menu can be found for the main SQL operations if you click the widget header Options . These are the following:

create - insert a new record into the table (only for non read-only tables)
delete - delete one or more records from table (only for non read-only tables)
insert into - insert the content of the table or view into another table
create a new table - create a new table based on the contents of the table or view
merge into - merge the contents of this table or view into another table
load into - load data from file (only for non read-only tables)

Data management

Add new row

Go to table you want to add a new row to, click Options and then create. It will open a window where the data can be filled in.

Edit data

You can edit a record in Scai by clicking their row button on the left side of the row. After clicking the edit button, a modal will open with the content of the row. Scai automatically detects data types and makes appropriate fields for each (a text box for text, a date picker for dates, etc.). You can edit your data and click the Save button.

This functionality is present only for tables that contain a primary key so that the changes are modified in place and there are no conflicts in case of duplicate data.
If this functionality is not present, we recommend adding a primary key to your tables to be able to edit them.

Delete data

Data can be easily deleted from a table by going to that particular table, then clicking Options , then delete. The table will enter in delete mode. The data can be filtered and only those records that match those filters will be deleted from it.

Pay attention that if you CLICK the "Delete" button without any filters, you will delete the entire data permanently!It is recommended that you first apply the filters and, then, click the delete button

Once you are done with removing records, simply click the Go to table to exit the delete mode.

Insert records into another table

If want to insert some rows of the (input) table (or view) into another table (destination table), click Options , then insert into. On the left side, a menu to select the table into which the data should be inserted will appear. Here, you can search for a given table. Click the name of the table into which you want to insert the data. Once that is done, the names of the columns from the destination table will appear.

For each column of the destination table, there are 3 methods of inserting into them:

by column (checkbox Column selected) - insert the values of a field from the table (or file loaded) into that given column. In this case you would check the Column checkbox and then select the field from the input table (or file) that you would like to map to the given column.
by value (checkbox Value selected) - insert a static value into that given column for each row inserted. In this case you would check the Value checkbox and then enter the static value in the input that appears.
by using the sql default values (no selected checkboxes) - insert the database default value into that given column for each row inserted. In this case do not check any of the checkboxes.

Examples
You can set a null value for a given column for each row you insert by clicking the Value checkbox and leaving the input field empty.
To insert the values of the field 'A' into the column 'name' of a given table, you would check the Column checkbox next to the 'name' label and then select from the list field 'A'.

You can click the Save button, once you have found the table you are interested in and configured how to match the input fields to its columns and limited the rows inserted using filters. If inserting into an existing table was successful (i.e. no errors occurred due to mismatch of data types or the format of the data), you can click Go to table to be directed to the destination table.

Create new table based on content

This can be achieved by clicking Options , then create a new table. It will open a new table with the following menu. On the left side, you can select the name of the schema and the name of the table that will be created. Then, click the Save button.

Merge into

If you want to update another table with the content of the current table, you can click Options , then click merge into. The widget will be split into two parts:

left side - a search box with all tables from the database which is used for selecting the table name that will be updated
right side - the current table, from which rows can be selected based on filters

For ease of use, you can search for tables by using the searchbox and filtering tables following the schema.table_name format.

After a table is selected, you will notice that for each column of the selected table there appear more options, possibly auto-filled, that can be configured:

Key columns are columns that will behave as joining keys when performing the merge. These columns will not have their values updated, but will only help match rows of the table you see on the right side with the rows of the selected table.
Please make sure that the combination of Key columns uniquely identifies the rows of each table. This means that there should be no two rows that have the same values on the Key columns in any tables, both the one you see the content of in the right side, but also in the selected table.
New value columns are columns that will have their values updated during the merge operation.
all other columns will not participate in the merge operation

After deciding which columns will be Keys or New values, for each such column of the selected table you need to choose whether you want to match a column of the current table (that you can see on the right side of the widget) or a constant value.

Example

This complex operation is better explained by means of example. Let's suppose that you have two tables: Customer(id, name, address, credit_amount) and OnlineCustomer(id, name, email). You would then like to update the contents of the Customer table with the data coming from OnlineCustomer. Let's check how you can do that using the merge operation.

Suppose you would like to set as address (column of Customer) the email address of all online customers and also set their credit_amount to zero. You would then go to the OnlineCustomer table and click the merge into option. After the new page is loaded, you can select the table you would like to merge the data into, in our case Customer.

You would then make sure that the id column of the Customer table is a Key, that it is mapped to a Column of the OnlineCustomer table and, more specifically to the id column of the OnlineCustomer. Thus you would check the Key and Column checkboxes and select the id column from the drop-down select.
Next, the address column should be checked as a New value, since we want to have its values updated and mapped to the email Column of the OnlineCustomer table. Thus, here we check New value and Column and select the email column.
Finally, the credit_amount column should be mapped to a constant Value of zero. Thus check New value and Value boxes and enter 0 in the input that appears.
the name column should not have any checked boxes

Load into

This represents a quick menu to load data into a table. The same steps apply as here.

Show definition

In case of views, another option will appear which is Show definition. If it is clicked then a console displaying the definition code of the view will be shown and Show definition will be replaced by Hide definition. The code can be modified for a quick modification of the view.

Note: the view definition can be modified only if you are the owner of the view's data source

In the widget containing the view definition, two buttons are present:

Preview - quick preview of the results of the new modified code
Save - save the new (modified) definition of the view into the database

If you wish to exit the view mode, simply click the Hide definition button located in the original widget menu.

Dropping a table or a view

Version: 3.3.0+

If you are the owner of the source you are connected to, you can drop a table or a view by going to the General table configuration page (can be found in the main sidebar). Select the table/view you wish to drop by applying some filters on the list of available tables and then click the (drop table/view) button.