Editable Tables

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.

Administrators can configure which users can edit the table, what actions users 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 Editable Tables administration screen.

On the left side, it shows the list of data sources against which an Editable Table has already been configured.

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.

Edit Details
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.

Permissions

Set permissions on the table - create, update & delete. These permissions can be restricted by one or more user groups.

On Save

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.

See Stored Procedures Return to learn more about what you get sent by CALUMO in these stored procedures and how you can send custom errors back out of them.

Audit

Save information about the created and modified date and user to table fields.

Field Mapping

This list will automatically be populated based on the table definition.

The Alias column specifies 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. 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 sets 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:

Control Type	Description
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.

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 to avoid unnecessary errors for users.

Lookup Fields

Lookup fields allow you to target another table from which to retrieve a list of values. The field will be displayed to users as a combo-box, with the key of the selected item being the value that is written into the field.

Field	Description
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 control’s 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||’

Note

Current field filtering is only available with Server Side Filtering turned on.