Grid SQL Settings

SQL Configuration

Grid SQL configuration

In this section, the settings related to the main SQL command of the Grid are defined, including field selection, record limit, commands executed before the query, sorting, connection, and message options when no records are found.

SQL Command

Allows you to define the main SQL of the application.

In this field, you can edit the SQL command used by the Grid, adding or removing fields according to the application needs.

Limit

Allows you to limit the number of records retrieved by the SQL for display in the Grid.

This setting can be used to control the volume of data returned by the application, especially in queries with a large number of records.

SQL Preparation

Allows you to enter SQL commands or procedures that must be executed before the application’s main SELECT.

This option can be used when it is necessary to prepare data, execute auxiliary routines, or perform an action in the database before running the Grid.

Foreign key fields

Displays the fields identified as foreign keys in the database structure.

These fields will be used by Scriptcase in the automatic mapping of links between applications. When the application is used as the target of a link, the identified foreign keys can be selected in the link configuration, avoiding the need to create global variables in the SQL of the target application.

Grid sorting fields

Defines the fields used in the initial sorting of the Grid.

The fields selected in this option will be considered by Scriptcase to build the application’s default sorting.

If you want to define custom sorting directly in the Grid SQL, clear the fields selected in this option.

Connection

Displays the name of the connection used by the application.

This connection can be changed to another project connection, as long as it has the same table structure used by the Grid.

Use custom message

Defines whether a custom message will be displayed when the Grid does not return records.

When this option is enabled, you can configure the text and appearance of the message displayed to the user.

No records found message

Defines the message displayed when the Grid does not return any records.

This option is available when Use custom message is enabled.

Font name

Defines the font used in the no records found message.

To select the font, click the icon displayed next to the field. This option is available when Use custom message is enabled.

Font color

Defines the font color used in the no records found message.

This option is available when Use custom message is enabled.

Variable for table

Allows you to replace part of the table name defined in the SELECT with the content of a variable before the command is executed.

The first field must be filled in with the variable name. The second field must be filled in with the part of the table name that will be replaced.

Variable for fields

Allows you to replace the name of a field in the SELECT with the content of a variable before the command is executed.

The first field must be filled in with the variable name. In the second field, the name of the field that will be replaced must be selected.

Differentiate uppercase/lowercase letters

Defines whether the connection will be case sensitive, differentiating uppercase and lowercase letters during the Grid execution.

Manual parameters and filters in links

When the tables have foreign keys configured correctly, Scriptcase can identify these relationships and make them available in the link configuration interface. This way, it is possible to relate the applications without manually creating global variables in the SQL of the target application.

However, in some scenarios, it may be necessary to configure the filter manually. This can occur when there is no foreign key configured in the database, when the relationship has not been identified automatically, or when the link needs to use a specific condition.

In these cases, parameters can be used to pass values between applications. To do this, add a global variable to the WHERE clause of the SQL command, filtering the records that should be displayed when the application is accessed through a link.

For example, in a link between a customer Grid and an orders Grid, the orders Grid can receive the customer code through a global variable. This variable will be used in the WHERE clause to display only the orders related to the selected customer.

To configure the parameter manually, access the SQL option in the application’s left-side menu.

Accessing the Grid SQL

Edit the SQL command by adding the WHERE clause and the field that will receive the value passed by the global variable, as shown in the image below.

Informing the variable

After editing, generate the application again by clicking the Generate source button - Generating applications - or by using the F8 keyboard shortcut.

Use global variables when it is necessary to create a manual or custom filter. For links based on relationships already configured in the database, we recommend using automatic foreign key mapping whenever possible.