Using Tables to Store Frequently Used Values
Tables are used to store data to be used in processes; for example, you could map customer ID numbers to end-user friendly customer names in tables and have your processes use this to present customer names instead of customer ID's to operators.
Also, many complex processes consist of processes that work with the same parameter values, for instance:
- The name of an external system
- The processing day (or business day) which may not be the same as today
- The name of an SAP instance or client to use for a particular task
These values tend to be used by a large number of processes, and change rarely. They might change:
- On a daily basis (for the business day)
- Between the Development, Quality Assurance and Production environments (for external system names).
In some cases there may be a predefined set of values from which the user can choose:
- A list of countries, provinces or cities
- A list of departments or cost codes
In these cases it is also useful to be able to validate the value a user has entered to ensure it is correct, and to prompt them with a list of valid values. This means that validation can occur at process submission time, rather than at runtime.
- Tables - Tables allow you to store frequently used values and have these form a list in the submit wizard.
- Table definitions - Table definitions define tables, listing valid columns and data types.
note
Unlike most objects, you cannot create a Table in the object's overview page. Tables are created in the Table Definitions overview via the context-menu of the table definition you want to use for the table.
Importing Tables
When you import tables, you have the option to overwrite all values in the table or append only new rows to the table.
In-Memory Tables
You store table data in memory to speed up the retrieval of values. This option is useful for tables that have 1000 or more rows. Note that you should use the option with care, very large tables will use a lot of memory.
Tabs & Fields
The following table illustrates the fields of each tab of Credentials editor dialogs.
| Tab | Field | Description |
|---|---|---|
| Table | Partition | The Partition of the table. |
| Table | Name | The name of the table can contain any combination of US ASCII letters, digits, and underscores; limited to 80 characters. |
| Table | Description | A free text description of the table; can contain any combination of printable UTF-8 characters, limited to 255 characters. |
| Table | Application | The Application of the table. |
| Table | Import Mode | When you re-import this table from a CAR file, you overwrite the table data (Import Table) or append rows defined in the CAR file (Import Rows) |
| Table | In Memory | Tables that have In Memory checked will be cached in memory; this drastically improves the performance of value retrieval, however, in memory tables consume more memory. This feature should only be used for big tables, over 1000 rows. |
| Table | Definition | The table definition of the table. |
| Documentation | Documentation | Free text field for documentation purposes. |
| Values | The table data, the number of columns and column type depend on the table definition. | |
| Security | This is where you can specify who can access/change/remove the table. |
Accessing Tables from REL
You access tables using the REL table functions.
Context-Menu
Tables and table definitions support the following context-menu actions:
| Action | Description |
|---|---|
| Edit Security | Edit the security of the table or table definition |
| Duplicate | Make a copy of the table or table definition to create a similar one |
| New Table | Create a new table from the current table definition |
| Delete | Delete the table or table definition |
| Export > Export | Export the table or table definition into a CAR file |
| Export > Export with related objects | Export the table or table definition into a CAR file including referenced objects |
| Promote > Promote to system | Promote the object to a remote system |
| Promote > Edit further then promote | Edit the export rule set prior to promoting |
| Promote | Promote the table or table definition to another Redwood Server instance |
| Edit | Edit the table or table definition |
| Show permalinks | Show links that can be used from third party applications to link to the object |
| Add to navigation bar | Add the current object to the navigation bar |
| New table definition | Create a new table definition |
| Filter > New Filter | Create a new table or table definition filter |
| Filter > Edit Filter | Edit current table or table definition filter |
| Filter > Delete | Delete current table or table definition filter |
| Filter > Duplicate Filter | Create a copy of the filter |
| Filter > Export Filter | Export the filter into a CAR file |
| Filter > Add to navigation bar | Add the filter to a navigation bar |
| Filter > Create filter from search | Create a filter from the current IntelliSearch query |
Finding Tables and Table Definitions
You can search for tables and table definitions using filters and the Search Tables or Search Table Definitions box on the Tables or Table Definitions tabs, respectively. These boxes are known as the IntelliSearch boxes and located under your username on the top right-hand side of the user interface. Filters allow you to specify a list of objects with static criteria. IntelliSearch allows you to specify complex queries in a simple way using prefixes. Prefixes are used to specify which property you are searching in and have short as well as long syntaxes. For example, if you want to display all tables with the term users in the comment, you would use the search criteria as follows:
c:users
You can search more than one property, as follows:
c:users n:ORA
note
No spaces should be entered before or after the colon (: ).
See the Advanced Object Search for more information.
The following table illustrates the available prefixes for tables and table definitions:
| Prefixes | Description |
|---|---|
| n, name | searches the name property |
| c, comm, comment | searches the documentation property |
| d, desc, description | searches the description property |
| a, application | searches the application property |
| cb, changedbefore | (internal) search for tables and table definitions that changed before a certain ISO-8601 period |
Deleting Table Definitions and Tables
You can only delete table definitions and tables when no other objects relate to them. For example, if there are process definitions that use the table, the table cannot be deleted until all process definitions that use it have been modified. You can see process definitions that relate to the table in Related Objects in the lower detail pane and on the show page. You cannot delete table definitions if there is a table using it.
The table in related objects contains three columns:
- Type - the type of object with a link to it
- Related Object - the name of the object with a link to it
- Used As - objects can sometimes be used in different roles
Security
| Privilege | Description |
|---|---|
| TableDefinition.Create | Create table definitions |
| TableDefinition.Delete | Delete table definitions |
| TableDefinition.Edit | Edit table definitions |
| TableDefinition.View | Access table definitions |
| Table.Create | Create tables |
| Table.Delete | Delete tables |
| Table.Edit | Edit tables |
| Table.View | Access tables |
You can grant privileges on two levels, Access and Admin; a privilege granted on Admin level allows the grantee to grant the privilege to other users. These privileges can be granted per partition or system-wide.
The Security tab allows you to specify which users can access, edit, and delete the table and table definition.
Procedure
Create a table definition
- Navigate to "Scripting > Table Definitions".
- Choose New Table Definition from the context-menu and enter a name and application (optional).
- Choose the Columns tab, and fill in some columns, with data types and specify if the value is allowed to be
null. - Choose Save & Close.
Create a table
- Navigate to "Scripting > Table Definitions".
- Choose New Table from the context-menu of a table definition and enter a Name and Application (optional).
- Choose the Values tab, and fill in some columns, the column names must exist in the table definition you specified. The Key column is the field that should contain the data that you want to use.
- Choose Save & Close.
Use a table as a reference in a parameter for parameter values
- Navigate to "Definitions > Processes".
- Choose Edit from the context-menu of the process definition.
- Choose the Parameters tab and select the parameter you would like to use.
- In the Simple Constraint Type field choose table, enter the name of the table into the Simple Constraint Data field.
- Choose Save & Close.
Result
When you submit the process definition, records in the table are available as list-of-values in the parameter field of the process definition.
Example
The following code checks the System_Variables table for a system variable with key RW_DEMO, if it does not exist it will create it and assign a value to it, finally, it will print all entries in the table.
{
Partition p = jcsSession.getPartitionByName("GLOBAL");
Table t = jcsSession.getTableByName(p, "System_Variables");
if (t.getTableValueBySearchKeySearchColumnName("RW_DEMO", "SystemValue") == null)
{
TableValue ctv = t.createTableValue();
ctv.setKey("RW_DEMO");
ctv.setColumnName("SystemValue");
ctv.setColumnValue("SomeValue");
jcsSession.persist();
}
t.getTableValues().forEach(item -> jcsOut.println(item.getKey() + " : " + item.getColumnName()+" > " + item.getColumnValue()));
}
See Also
table TableDefinition