The Grid Editor component is not a report type in the classical sense. It is rather a very flexible spreadsheet like database editor that can be used in situations where you want to enable a user to do some basic data administration. Once defined the grid editor can be used as any report, that is, it can be used by users in their TeamSpace and users can even export the underlying data to Excel and schedule the report.

The grid editor component is configured by providing a datasource and a ReportServer script. ReportServer scripts are covered in greater detail in Chapter 21. and in the separate ReportServer scripting guide and it might be helpful to skip the following details on a first read and come back to grid editors once you have a basic understanding of ReportServer scripts.

The simplest use case for a grid editor is when you have a database table and you want to give a user the possibility to edit the data in that table. At the very basis you need to generate a GridEditorDefinition which handles the interaction with the user. For relational databases, ReportServer provides a helper class called DbGridEditorDefinition (located in net.datenwerke.rs.grideditor.service.grideditor.definition.db) which tries to handle as much of the interaction (loading data, storing values, etc.) as possible. Consider the following example script:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinition definition = GLOBALS.getInstance(DbGridEditorDefinition)

DbGridEditorDefinitionAdapter adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'

return definition

Here we obtain a new instance of a DbGridEditorDefinition and load an adapter object which is used for most of the configuration. At the very least you need to specify which database table you want to work on. With the above configuration ReportServer will attempt to load the data as

SELECT * FROM T_AGG_CUSTOMER

and display the data paged with a page size of 100 rows. To change the number of rows per page you can call setPageSize() on the adapter and set the number of rows. By default the editor will allow the user to edit every cell, to delete entire rows and to insert new rows. The entire editing process is cached on the client and only if the client calls save will the data be stored.

If you need to filter the data on your table, you can use set the whereClause property as shown in the following example:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinition definition = GLOBALS.getInstance(DbGridEditorDefinition)

DbGridEditorDefinitionAdapter adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.whereClause = 'CUS_CUSTOMERNUMBER > 100'

return definition

Consider the following data table (a small extract of the demo data):

CUS_CUSTOMERNUMBER	CUS_CUSTOMERNAME	CUS_CREDITLIMIT
386	Lordine Souveniers	121400
412	Extreme Desk Decorations, Ltd	86800
456	Microscale Inc.	39800

Now suppose the user changed the credit limit of Microsale to 50000. In this case ReportServer builds the following update statement

UPDATE T_AGG_CUSTOMER 
    SET  
        CUS_CUSTOMERNUMBER = 456 , 
        CUS_CUSTOMERNAME = 'Microsale Inc.' , 
        CUS_CREDITLIMIT = 50000 
    WHERE 
        CUS_CUSTOMERNUMBER = 456 AND  
        CUS_CUSTOMERNAME = 'Microsale Inc.' AND 
        CUS_CREDITLIMIT = 39800

Assuming that CUS_CUSTOMERNUMBER is the table's sole primary key, this statement is unnecessarily complex, as the WHERE clause includes columns that are not part of the primary key. You should therefore always specify the table's primary key in ReportServer. Additionally, it may be desirable to display only a subset of the table's columns rather than the entire dataset as shown below.

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinition definition = GLOBALS.getInstance(DbGridEditorDefinition)

DbGridEditorDefinitionAdapter adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.primaryKey = 'CUS_CUSTOMERNUMBER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

return definition

Now if a user updates the table the following statement will be generated behind the scenes:

UPDATE T_AGG_CUSTOMER 
    SET  
        CUS_CUSTOMERNUMBER = 456 , 
        CUS_CUSTOMERNAME = 'Microsale Inc.' , 
        CUS_CREDITLIMIT = 50000 
    WHERE 
        CUS_CUSTOMERNUMBER = 456

If you are updating a table containing an auto-increment primary key, you should add it to the list of columns and set its column configuration to be non-editable (setEditable(false)). You can further hide it if desired (setHidden(true)). As an example, here you can see a table ''TABLE_NAME'' having an ''id'' auto-increment primary key and a text column ''your_column''. The example uses the fluid API (see Section A Fluid API for more details).

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinitionAdapter adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'TABLE_NAME')
    .setPk('id')
    .columns()
        .add('id')
			.setEditable(false)
			.setHidden(true)
        .add('your_column')
    .done()

return adapter

You should avoid using the Grid Editor on tables that do not have a primary key (or have the primary key not displayed). Assume the following configuration:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinition definition = GLOBALS.getInstance(DbGridEditorDefinition)

DbGridEditorDefinitionAdapter adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

return definition

Now if a user updates the table ReportServer it may generate the following update query:

UPDATE T_AGG_CUSTOMER 
    SET  
        CUS_CUSTOMERNAME = 'Microsale Inc.' , 
        CUS_CREDITLIMIT = 50000 
    WHERE 
        CUS_CUSTOMERNAME = 'Microsale Inc.' AND 
        CUS_CREDITLIMIT = 39800

This update statement might not uniquely identify the data row and thus trigger an update on multiple rows and thus might not have the intended effect.

In addition to the standard API for configuring the grid editor, a more compact and fluent API is available. This API simplifies the process of obtaining an adapter object. Within the scope of the script, an object named gridHelper is provided, which offers the method initDbGridEditor. To initialize the fluent API, the configure method must then be invoked on the returned adapter object. The example shown above can therefore be rewritten using the fluent API as follows:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinitionAdapter adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'T_AGG_CUSTOMER')
   .columns()
      .add('CUS_CUSTOMERNUMBER')
      .add('CUS_CUSTOMERNAME')
      .add('CUS_CREDITLIMIT')
   .done()

return adapter

The configure method takes as parameter a report object (the corresponding report object is present in the script's scope) and the table name. From there, you can access various configuration, amongst others, column configuration. By calling the columns method you start the column configuration which you end again by calling done.

For the remainder of the description of the grid editor, we present features first with the "classical" API and then how to do the same with the fluid API.

A Grid Editor's adapter object provides several additional configuration options that we discuss next.

Per default the Grid Editor displays the data in a paged fashion showing 100 records on each page. In order to increase the number of records on each page you can call the adapter object on setPageSize(pagesize) specifying the size a page should have. To disable paging you can call setPaging(false).

By default, users can filter the table by specifying a search string for every column. Furthermore users can sort the Grid Editor by every column. If you would like to globally disable sorting or filtering you can use the following methods of the adapter object:

setSortable()	Enables sorting when set to true. (Default: true)
setFilterable()	Enables filtering when set to true. (Default: true)
setCanAddRecords()	Allows records to be added when set to true. (Default: true)
setCanDuplicateRecords()	Allows records to be duplicated when set to true. (Default: true)
setCanRemoveRecords()	Allows records to be removed when set to true. (Default: true)

Note that filtering and sorting can also be specified on a per column basis.

All the above configuration can also be done via the fluid API by calling, for example,

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinitionAdapter adapter = gridHelper.initDbGridEditor()

adapter.configure(report,'T_AGG_CUSTOMER')
   .setPaging(false)
   .columns()
      .add('CUS_CUSTOMERNUMBER')
      .add('CUS_CUSTOMERNAME')
      .add('CUS_CREDITLIMIT')
   .done()

return adapter

In order to further configure how columns are presented to the user, you can specify so called column config objects which provide column specific configurations. Basic column configs are specified via instances of class GridEditorColumnConfig located in net.datenwerke.rs.grideditor.service.grideditor.definition:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*

DbGridEditorDefinition definition = GLOBALS.getInstance(DbGridEditorDefinition)

DbGridEditorDefinitionAdapter adapter = definition.adapter

adapter.tableName = 'T_AGG_CUSTOMER'
adapter.primaryKey = 'CUS_CUSTOMERNUMBER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

GridEditorColumnConfig nameConfig = new GridEditorColumnConfig()
nameConfig.displayName = 'NAME'
adapter.setColumnConfig('CUS_CUSTOMERNAME', nameConfig)

return definition

Or more compactly via the fluid API

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinitionAdapter adapter = gridHelper.initDbGridEditor()

adapter.configure(report,'T_AGG_CUSTOMER')
    .setPk('CUS_CUSTOMERNUMBER')
    .columns()
        .add('CUS_CUSTOMERNUMBER')
        .add('CUS_CUSTOMERNAME')
            .setDisplay('Name')
        .add('CUS_CREDITLIMIT')
    .done()

return adapter

For setting the display name you may also use the shortcut .add('columnName','displayName').

Besides changing the name of columns you can also specify a number of display options

setWidth()	Defines the display width. (Default: 200)
setEditable()	If true then the column is editable. (Default: true)
setHidden()	If true then the column is not displayed. (Default: false)
setSortable()	If true then the column is sortable. (Default: true)
setOrder()	Allows to specify the order by supplying "asc" or "desc". For the fluid API there is the shortcut of calling setOrderAsc() and setOrderDesc().
setFilterable()	If true then the column can be filtered. (Default: true)
setEnforceCaseSensitivity()	If true then filtering on that column is always case sensitive. (Default: false)

When a user adds a record to a table, all values are by default set to NULL. You can specify a default value for a column by using the setDefaultValue methods of a column configuration object.

Without further configuration ReportServer will only enforce that entered data is of the correct type. For example, if a field is of type INTEGER, then a user can only type in digits into the text field. In order to further restrict what users can enter you can add one or more Validators to each column. Validators are located in package net.datenwerke.rs.grideditor.service.grideditor.definition.validator. The following validators are available

MaxIntegerValidator	Allows to specify an upper bound for columns of type INTEGER
MinIntegerValidator	Allows to specify a lower bound for columns of type INTEGER
MaxBigDecimalValidator	Allows to specify an upper bound for columns of type DECIMAL
MinBigDecimalValidator	Allows to specify a lower bound for columns of type DECIMAL
MaxLongValidator	Allows to specify an upper bound for columns of type LONG
MinLongValidator	Allows to specify a lower bound for columns of type LONG
MaxDoubleValidator	Allows to specify an upper bound for columns of type DOUBLE
MinDoubleValidator	Allows to specify a lower bound for columns of type DOUBLE
MaxFloatValidator	Allows to specify an upper bound for columns of type FLOAT
MinFloatValidator	Allows to specify a lower bound for columns of type FLOAT
MaxDateValidator	Allows to specify an upper bound for columns of type DATE
MinDateValidator	Allows to specify a lower bound for columns of type DATE
MaxLengthValidator	Allows to specify a maximum length for character-based columns
MinLengthValidator	Allows to specify a minimum length for character-based columns
RegExValidator	Allows to restrict text-based fields to match a given pattern

In order to configure a validator you instantiate the corresponding object and provide the necessary configuration in the constructor. All Min/Max validators take as configuration the bound as well as an error message that is displayed in case a user enters a value that violates the bound. For example

new MaxIntegerValidator(15, "Values must be less than 15");

The RegExValidator takes as configuration a regular expression (see https://docs.oracle.com/en/java/javase/21/docs/api/java.base/java/util/regex/Pattern.html for an introduction) and an error message. To, for example, restrict data to conform to a date format of type "yyyy-mm-dd" you could use

new RegExValidator("^\d{4}-\d{2}-\d{2}$", "Value should be of format yyyy-mm-dd");

With the fluid API there are also shortcuts to validators. You can call addValidator(new ..) when configuring a column. Additionally there are the following shortcuts:

addEmptyValidator(message)
addFixedLengthValidator(length, message)
addRegExValidator(regex, message
addMinLengthValidator(min, message
addMaxLengthValidator(max, message)
addMinValidator(min, message)
addMaxValidator(max, message

By default, the Grid Editor creates form fields that correspond to the data type of each column. Text columns are rendered as text fields, date columns as date pickers, and boolean columns as checkboxes. For certain columns, this default behavior can be overridden by specifying a custom editor. To assign a custom editor to a column, use the setEditor method on a GridEditorColumnConfig instance. All available custom editors are located in the package net.datenwerke.rs.grideditor.service.grideditor.definition.editor.

Sometimes boolean values are not stored as booleans in a database but as text or int values. For example, you might have a text column with the values "true" and "false" or an integer column with values 1 and 0. In this case you can use a TextBooleanEditor or an IntBooleanEditor in order to still present a user with a simple checkbox, rather than a textfield or a number field. As configuration you can tell the editor which value is representing TRUE and which value is representing FALSE. Per default TextBooleanEditor uses the strings true and false and IntBooleanEditor uses integers 1 and 0. To change the default use methods setTrueText (resp. setFalseText) and setTrueInt (resp. setFalseInt).

The following is an example assuming the column names textbool and intbool.

GridEditorColumnConfig textbConf = new GridEditorColumnConfig()
textbConf.editor = new TextBooleanEditor()
adapter.setColumnConfig('textbool', textbConf)

GridEditorColumnConfig intbConf = new GridEditorColumnConfig()
intbConf.editor = new IntBooleanEditor()
adapter.setColumnConfig('intbool', intbConf)

Using the fluid API, we can define quasi-boolean editors for columns by calling withIntBooleanEditor(), or withTextBooleanEditor().

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinitionAdapter adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'TABLE')
    .setPk('...')
    .columns()
        .add('column')
            .withIntBooleanEditor()
    .done()

return adapter

Sometimes dates are stored in text form, for example, as strings of the form yyyy-mm-dd. In these cases you can tell the Grid Editor not to use a basic text field but a date picker. For this use the editor TextDateEditor. For configuration you should provide the corresponding date pattern (see https://docs.oracle.com/en/java/javase/21/docs/api/java.base/java/text/SimpleDateFormat.html for an introduction). The following is an example for how to use the TextDateEditor.

GridEditorColumnConfig dateConf = new GridEditorColumnConfig()
dateConf.editor = new TextDateEditor('yyyy-mm-dd')
adapter.setColumnConfig('textdate', dateConf)

Using the fluid API, we can define quasi-boolean editors for columns by calling withTextDateEditor(), or withTextDateEditor(format) and specifying the format.

Often you might want to allow users to choose from a list of values. For text based columns and integer columns you can define selection lists via

TextSelectionListEditor	A drop down editor for text based columns.
IntSelectionListEditor	A drop down editor for text based integers
LongSelectionListEditor	A drop down editor for text based longs
DateSelectionListEditor	A drop down editor for text based dates
DecimalSelectionListEditor	A drop down editor for text based BigDecimals
FloatSelectionListEditor	A drop down editor for text based float
DoubleSelectionListEditor	A drop down editor for text based double

A selection list can be configured in two ways. Either you can specify a simple list of values or you can specify a map of label-value pairs. Each entry of a selection list consists of a label (the string that is shown to the user) and a value (the actual value that is stored in the database). In case you provide a simple list, each entry serves both as label and as value. Following is an example of a simple selection list for a text column.

GridEditorColumnConfig ddTextConf = new GridEditorColumnConfig()
TextSelectionListEditor textddEditor = new TextSelectionListEditor()
textddEditor.setValues(['a','b','c','d'])
ddTextConf.editor = textddEditor
adapter.setColumnConfig('textdd', ddTextConf)

It configures a selection list with the entries a, b, c, and d. Alternatively, you can specify each value individually:

GridEditorColumnConfig ddTextConf = new GridEditorColumnConfig()
TextSelectionListEditor  textddEditor = new TextSelectionListEditor()
textddEditor.addValue('a')
textddEditor.addValue('b')
textddEditor.addValue('c')
textddEditor.addValue('d')
ddTextConf.editor = textddEditor
adapter.setColumnConfig('textdd', ddTextConf)

If you want to distinguish between labels and values you can either specify the map directly by calling setValueMap(). Or you can add each entry individually as follows:

GridEditorColumnConfig ddTextConf = new GridEditorColumnConfig()
TextSelectionListEditor textddEditor = new TextSelectionListEditor()
textddEditor.addEntry('a','b')
textddEditor.addEntry('c','d')
textddEditor.addEntry('e','f')
ddTextConf.editor = textddEditor
adapter.setColumnConfig('textdd', ddTextConf)

For integer columns the configuration works identical with the only difference that you assign a IntSelectionListEditor instead of a TextSelectionListEditor and that values are of type integer. Following is an example using a simple list to define values:

GridEditorColumnConfig ddIntConf = new GridEditorColumnConfig()
IntSelectionListEditor intEditor = new IntSelectionListEditor()
intEditor.setValues([2,3,5,7,11])
ddIntConf.editor = intEditor
adapter.setColumnConfig('intdd', ddIntConf)

Following an example with custom labels.

GridEditorColumnConfig ddIntConf = new GridEditorColumnConfig()
IntSelectionListEditor intEditor = new IntSelectionListEditor()
intEditor.addEntry('foo',2)
intEditor.addEntry('bar',7)
ddIntConf.editor = intEditor
adapter.setColumnConfig('intdd', ddIntConf)

Using the fluent API, selection editors can also be created in a compact and expressive manner. Calling withSelectionEditor() initiates the editor configuration, which is finalized by invoking done(). This approach allows you to configure a selection editor, for example, as follows:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinitionAdapter adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'T_AGG_CUSTOMER')
    .setPk('CUS_CUSTOMERNUMBER')
    .columns()
 		.add('CUS_CUSTOMERNUMBER')
        .add('CUS_CUSTOMERNAME')
            .withSelectionEditor()
               .addValue('FunGiftIdeas.com')
               .addValue('Marseille Mini Autos')
            .done()
    .done()

return adapter

Within the editor's edit mode, the same methods addValue() and addEntry() are available. In addition, the fluent API provides the from() method, which allows multiple values to be added at once. This method accepts either a list of values (corresponding to addValue()) or a map (corresponding to addEntry()).

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinitionAdapter adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'T_AGG_CUSTOMER')
    .setPk('CUS_CUSTOMERNUMBER')
    .columns()
 		.add('CUS_CUSTOMERNUMBER')
        .add('CUS_CUSTOMERNAME')
            .withSelectionEditor()
			   .from(['FunGiftIdeas.com', 'Marseille Mini Autos'])
            .done()
    .done()

return adapter

Finally, a frequent objective is to construct the values for the selection list from a database query. To this end, you can use the fromSql which takes either a SQL statement or a connection object and a SQL statement. In case you provide no connection, the same connection as to the grid editor is used. In case you provide a connection, be sure to close the connection after usage. The SQL statement needs to return either two columns (key,value) or a single column.

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinitionAdapter adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'T_AGG_CUSTOMER')
    .setPk('CUS_CUSTOMERNUMBER')
    .columns()
		.add('CUS_CUSTOMERNUMBER')
        .add('CUS_CUSTOMERNAME')
            .withSelectionEditor()
               .fromSql('SELECT CUS_CUSTOMERNAME FROM T_AGG_CUSTOMER')
            .done()
    .done()

return adapter

Within your script you can access a couple of predefined variables that allow you to access the report object, as well as the current user and the parameters. The following variables are available:

report	The corresponding GridEditorReport object.
user	The current user.
parameterSet	The ParameterSet object with the current parameters.
parameterMap	A map allowing to easily access parameters.
gridHelper	Used to easily construct an adapter object

In the following section, we explain how foreign key relationships can be used to simplify data editing. Consider a database table Products with the following structure:

productNumber	The primary key (INT)
productName	(VARCHAR)
productCategory	A foreign key pointing to Table Categories (INT).
productSupplier	A foreign key pointing to Table Suppliers (INT).

In this example, two foreign key relationships exist: one linking a product to a product category and another linking it to a supplier. This models a many-to-one relationship. Each product belongs to exactly one category and one supplier, while a category or supplier may be associated with multiple products.

Assume the corresponding category and supplier tables have the following structure:

categoryNumber	The primary key (INT)
categoryName	(VARCHAR)
categoryDescription	(VARCHAR)

supplierNumber	The primary key (INT)
supplierFirstName	(VARCHAR)
supplierLastName	(VARCHAR)

A MySQL example of the DDLs is shown below

CREATE TABLE Categories (
  categoryNumber INT AUTO_INCREMENT PRIMARY KEY,
  categoryName VARCHAR(255) NOT NULL,
  categoryDescription VARCHAR(1000)
) ENGINE=InnoDB;

CREATE TABLE Suppliers (
  supplierNumber INT AUTO_INCREMENT PRIMARY KEY,
  supplierFirstName VARCHAR(255) NOT NULL,
  supplierLastName VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE Products (
  productNumber INT AUTO_INCREMENT PRIMARY KEY,
  productName VARCHAR(255) NOT NULL,
  productCategory INT NOT NULL,
  productSupplier INT NOT NULL,
  FOREIGN KEY (productCategory) REFERENCES Categories(categoryNumber),
  FOREIGN KEY (productSupplier) REFERENCES Suppliers(supplierNumber)
) ENGINE=InnoDB;

Now consider a basic grid editor configuration used to manage the Products table:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinition definition = GLOBALS.getInstance(DbGridEditorDefinition)

DbGridEditorDefinitionAdapter adapter = definition.adapter
adapter.tableName = 'Products'
adapter.primaryKey = 'productNumber'
adapter.addColumns(
	'productNumber',
	'productName',
	'productCategory',
	'productSupplier'
	)

return definition

With this setup, changing a product's category requires knowing the numeric categoryNumber. In many cases, however, it is more convenient to select a category by its name. Since the category name is stored in a separate table, this is not directly possible with the basic configuration.

To address this, the grid editor allows you to define foreign key columns. Foreign key columns enable you to display alternative values-such as descriptive names-instead of the raw foreign key values stored in the table. During insert or update operations, the displayed values are automatically translated back into the appropriate foreign key values.

Foreign key columns can be defined using the adapter's addForeignKeyColumn() method. This method accepts four or five parameters:

column	The column in the current table that holds the foreign key. In this example, this would be productCategory.
fkTableName	The name of the referenced table. In this example, Categories.
fkColumn	The primary key column of the referenced table. In this example, categoryNumber.
displayExpression	A SQL expression that selects the value to be displayed from the foreign key table. This is typically a single column but may also be a more complex expression. In this example, categoryName.
displayName/config	An optional parameter that accepts either a string or a GridEditorColumnConfig object. If a string is provided, it is used as the column's display name. If a configuration object is provided, it defines the column's behavior. If omitted, the column name defaults to the value of column.

We could thus specify the relationship as follows in groovy code:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

DbGridEditorDefinition definition = GLOBALS.getInstance(DbGridEditorDefinition)

DbGridEditorDefinitionAdapter adapter = definition.adapter
adapter.tableName = 'Products'
adapter.primaryKey = 'productNumber'
adapter.addColumns('productNumber', 'productName')
           .addForeignKeyColumn(
			'productCategory',
			'Categories',
			'categoryNumber',
			'categoryName',
			'Category'
			)
           .addColumns('productSupplier')

return definition

Using the same approach, you can also define a foreign key column for productSupplier.

In most cases, you will additionally configure an editor that allows users to select a category from a predefined list rather than entering values manually. To do so, you can pass a configuration object as the fifth parameter to addForeignKeyColumn(). An example configuration is shown below (note the additional imports):

import net.datenwerke.dbpool.DbPoolService
import net.datenwerke.rs.base.service.datasources.definitions.DatabaseDatasource
import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.editor.*
import net.datenwerke.rs.terminal.service.terminal.TerminalSession
import net.datenwerke.rs.terminal.service.terminal.objresolver.ObjectResolverDeamon

import groovy.sql.Sql
import java.sql.Connection

// enter the path to your datasource here
String datasourcePath = '/datasources/RS_GRIDEDITOR_TEST'

DbGridEditorDefinition definition = GLOBALS.getInstance(DbGridEditorDefinition)
TerminalSession session = GLOBALS.getInstance(TerminalSession)
ObjectResolverDeamon objectResolver = session.objectResolver
DbPoolService dbPoolService = GLOBALS.getInstance(DbPoolService)

DatabaseDatasource datasource = (DatabaseDatasource) objectResolver.getObjects(datasourcePath)[0]

DbGridEditorDefinitionAdapter adapter = definition.adapter
adapter.tableName = 'Products'
adapter.primaryKey = 'productNumber'

// define config for categories
GridEditorColumnConfig categoryConfig = new GridEditorColumnConfig(displayName: 'Category')
TextSelectionListEditor categoryEditor = new TextSelectionListEditor()

dbPoolService.getConnection(datasource.connectionConfig).get().withCloseable { Connection connection ->
   	 new Sql(connection).eachRow('SELECT categoryName AS name FROM Categories ORDER BY 1' ){
        categoryEditor.addValue(it.name)
   }
}

categoryConfig.editor= categoryEditor

adapter.addColumns('productNumber', 'productName')
           .addForeignKeyColumn(
			'productCategory',
			'Categories',
			'categoryNumber',
			'categoryName',
			categoryConfig
			)
           .addColumns('productSupplier')

return definition