Editable Tables provide a way to configure certain SQL tables to be editable by end users without the need to import files and/or write stored procedures to insert, update or delete records. This becomes beneficial for tables which are not necessarily in a source system or are fairly small eg lookup tables and lists.
It is possible to configure who can edit the table, what actions they can perform on the table and what controls are available to users when editing the data.
Note: Only CALUMO Administrators can configure what tables are available via the Table Editor interface.
Editable Table Administration¶
From the CALUMO Administration menu in the CALUMO Home screen, select Editable Tables and you will be taken to the administration screen.
On the left side, it shows the list of data sources against which have already configured an Editable Table and on selecting an item
On the right side, it shows the Editable Tables already configured.
Adding/Editing Editable Table Definitions¶
To Add a new editable table definition, click the Add button and an Edit Details window appears. The table below describes all the sections of the window.
Give a friendly name to the editable table.
Select your database.
Choose the table you wish to be editable.
Select the primary key of that table.
Use a Where clause to restrict what users can see and therefore amend - this is a SQL where clause. The where clause can use the users login id if you need to restrict rows by user permissions. The where clause is executed when the table is queried.
Use an Order By clause to always order the results in a certain way.
Popup Mode, allows the table to appear as a pop up window.
Set permissions on the table - create, update & delete. These permissions can be restricted by one or more user groups.
Wire up custom stored procedures to run Before Save to perform tasks like custom validation or extra calculations or After Save to perform tasks once the action has been performed.
Save information about the created and modified date and user to table fields.
This list will automatically be populated based on the table definition.
The alias column specified a friendly name for when users are editing the table in the Table Editor.
The control column specifies what data entry control your users will see for that field.
The width column specifies the width in pixels for a given fields column in the Table Editor grid.
Note: By default, when users are using the Table Editor, the columns will automatically size. Setting a column width will fix that size of that specific column and all other columns will resize accordingly.
The format column specifies the format for a given field, please see Date Formatting and Number Formatting for more detail on the options available. For more information, see the section below on Field Mappings
To finish creating your Editable Table, click Save and the Editable Table now appears on the right and you will see three icons: edit, delete and open table editor.
Field Mapping Details¶
The Field Mapping section reads the definition of the table and makes certain assumptions and set certain defaults based on this definition.
If the table definition ever changes, just click the small refresh icon next to the Field Mapping heading.
Notice that certain defaults are preset based on the definition that is read from the table. The preset can be changed at any time.
The rules below are applied to set these defaults:
- The primary key of the table is configured to not be updatable.
- If a column is flagged read-only, it is configured to not be updatable.
- If a column is flagged as hidden, it is configured to not be visible.
- Default control types are selected based on the data type of the column in SQL.
- Any fields that are in a foreign key relationship with another table will have their control type set to Lookup and will automatically wire themselves up to provide a drop down of values from the foreign table to users
The control column specifies one of the following control types:
Note: If the control type is not right for your underlying field, please aim to select the right control type for your fields data type so as to avoid unnecessary errors for users.
|Date||The user will be presented with a date picker.|
|Date and Time||The user will be presented with a date and time combination picker.|
|Decimal||The user will be presented with an input box that accepts decimal numbers.|
|HTML||The field data will be treated as raw HTML and rendered in the column as such. You can use this method to return hyperlinks from your table data that users can click on.|
|Lookup||The user will be presented with a drop down combo-box that shows the list of values from the foreign table lookup you have|
|Number||The user will be presented with an input box that accepts whole numbers.|
|Switch||The user will be presented with a toggle switch that returns a True or False result to the underlying data.|
|Text||The user will be presented with a simple text box.|
|Time||The user will be presented with a time picker.|
This type of field allows the targetting of another table from which to retrieve a list of values for the field that will be shown in a combo-box style, with the key of the selected item being the value that is written into the field.
|Table||The other table that contains the data to display in the lookup.|
|Key Field||The column/field from that table that holds the key for the lookup.|
|Description Field||The column/field from that table that holds the description for the lookup.|
|Sort By Field||Whether to sort the list by the Description field or the Key field.|
|Sort Direction||Whether to sort ascending or descending.|
|Server Filtering||Toggle on/off the controls ability to filter server side where it will seed the list with a subset of all values and then perform lookups against the table on the server for other items as the user types in a search term.|
|Initial List Size||When using Server Filtering, this is the number of items that are retrieved initially.|
|Where Clause||A partial where clause to be used when searching for the values in the list on the server side. See below for a broarder description.|
Server Side Filtering Using a Where Clause¶
When using server side filtering, a partial where clause can be used to restrict the list of foreign lookup items.
To use the value from another field on the same record as the filter, use ||FieldName|| in the text.
Example: [Another Foriegn Field] = ‘||PersonKey||’
Current field filtering is only available with Server Side Filtering turned on.