Genie Grid

A grid can enhance an existing subfile listing with professional looking headings, alternating rows, hover effects, scrollbars, and more.  In Genie, the grid component can also tap directly into the underlying database files without custom programming.  This allows you to display additional columns of application data, even when that data was not available on the original application screen.

Automatically Created Grids

Genie will automatically create grids over subfiles on your screens if the "Detect Subfiles" and "Detect Subfile Patterns" properties are set to true either in the Genie Administrator or at the Screen Level with the Genie Designer.  Because 5250 does not provide this information, Genie does not know directly whether the data presented on the screen is part of a subfile or not.  However, the Detect Subfile Patterns feature detects patterns of rows that are formatted in the same way to make this determination.  Genie can also look for an option column in order to determine that there is a subfile.  This is done by setting "Find Option Column" to true.  If Genie detects a false positive, it can be overridden either by setting "detect subfiles" to false on that screen if there is no subfile, or by setting the "subfile start" screen-level property to the row where the subfile actually starts.

Open

To work with the grid created for you by Genie, turn Design Mode on, and click on the grid.  The default id of the grid is "subfile".

Creating a New Grid Manually

A new Grid can be created by dragging the Grid Widget from the Widgets Toolbox to the screen.

Open

A new grid, similar to the one shown below, will be created.

General Properties

Just like any other graphical component in Genie, the Grid is resizable. You can add new columns or remove existing ones by clicking the + or – signs on the last column (circled below). The same could also be accomplished with rows. However, a shortcut for adding new rows in a Grid is to expand it by dragging its bottom border downwards. As a result, the number of new rows will be determined based on your default row height, and then added to your Grid.

Note: Resizing any of the rows (except for the last row) will maintain equal row heights for the rest of the Grid rows. As a result, all other rows will resize automatically to reflect the change in row height.

It is also possible to set the header height.  This can be done by dragging the Grid's top border with the mouse, or by using the Grid Properties Window.  In the Grid Properties Window, find the “header height” property and set that to the desired height. The screenshot below sets the “header height” property to 30. 

The header height is independent of other rows. In other words, changing the height of the header row does not affect the heights of other rows in the grid. 

Note: Columns resize horizontally independently of each other. In other words, resizing one column, does not affect the widths of other columns on the Grid.

To specify column headers, find the “column headings” property and type in a comma separated list of heading text for each column of the grid. Alternatively, you can click on the prompt button located to the right of the “column headings” property to get the “Edit Column Headings” dialog. Start entering column names and click the “Add” button.  You can also simply double-click any heading to modify its content.

When done entering the column names, click OK. Now, your Grid should have column names as shown below.

Note: If you enter more column headers than the columns available in the previously created Grid, you can just click on the + sign to add more columns and the text will appear automatically in the new column.

It is also possible to control whether a Grid should have headers or not. For example, to remove the header row from the Grid, find the “has header” property and select “false” from the dropdown menu as shown below.

As a result, the Grid component will look as follows:

Grid Settings

Select the grid created in the previous step to work with more Grid properties and settings, which will appear in the Properties Dialog Window. Scroll down to the Grid Settings section of the Grid Properties Window to change any of the Grid settings.

Some of the properties listed below are self-explanatory and can be controlled through point and click, drag and drop manner in the Visual Designer. These properties include the number of rows and columns, row height and column widths.

Additionally, here are two more Grid properties that can visually enhance your application.

hover effect -- Determines whether the grid rows will be highlighted when the user hovers the mouse over them.  If set to true, the hover effect can enhance the overall look and feel of your grid.

Note: The “hover background” and the “hover font color” properties can be used in this case to set the hover background color and font color according to user preferences.

Note: To define a different color for each grid column, specify a comma separated list of color values in the “hover background” or the “hover font color” property.

paging scrollbar -- Determines whether a vertical scrollbar for paging through data records will appear within the grid. If the grid is not a database-driven grid, the scrollbar will automatically send the PageUp/PageDown keys to the underlying application.

Color Properties 

There are additional color properties that can visually enhance the look and feel of the Grid component. For instance, the “odd row background” and “even row background” properties add an alternating row color effect to the grid. You can pick a color from the color palette as shown below.

The “hover background” property defines the background color of a grid row when the user hovers the mouse cursor over it. To define a different color for each grid column, specify a comma separated list of color values.

Note: Make sure the “hover effect” property is set to true.

Grid Data

There are 5 different approaches to populating a Grid with data:

1. Having a Grid consume data from the existing screen

2. Overlaying the Grid over existing data or subfile.

3. Adding a database file in the Designer and choosing the fields to populate.

4. Writing a custom SQL statement to populate the data.

5. Calling a program or web service that populates the data.

Consuming data from the screen

In order for the Grid to consume and embed data from the screen, you must locate the "Grid Data from Screen" section in the Properties Window, and specify the following properties:

• "starting row" - specifies the starting 5250 row number for the subfile

• "ending row" - specifies the ending 5250 row number for the subfile

• "data columns" - specifies a comma separated list of column numbers where the data can be found

Genie will then find output fields int he positions specified by the properties above and embed the data contained in these output fields directly into the grid cells.  The original output fields will be removed from view.

This method of populating data is the default method used when Genie creates grids automatically using the Detect Subfile Patterns feature.

Overlaying the Grid over existing data

In Genie, Grids can be overlaid on top of existing data on the screen such as a subfile screen. In this example, we overlaid a Grid over a subfile program that displays a product listing. In Design Mode, add a new Grid and place it on top of the data. Start adding rows and/or columns to fit the subfile screen. Add the appropriate headers in the “column headings” property as shown previously. In this example, we added the following column headings: Product, Product Name, Price, Qty, And Special Flag.

Once a Grid is placed behind screen data, users can utilize the scrollbar to scroll up and down through all the data in the subfile. Additionally, you can add events that are specific to the grid component, such as: onrowclick, onrowdblclick, etc.

Data from database file

In this section, we will populate the Grid using a database file. In the illustration below, we are using an item master file named ITM001P for the database file property.  The file name can be qualified with a library; however, if the library is omitted, the session’s library list will be used to find the database file.

Next, you must specify the “database fields” property, which will be used to determine the database fields from which the data will be populated.  You can type the field names separated by commas or choose them from a dialog. Click on the button circled in green to display the database fields dialog for the database file you specified.

Note: Make sure that the library in which the database file exists is included within your session’s library list. If the library does not exist, you will get a message that looks like this:

Otherwise, you will get a dialog with the available database field names to pick from. Select the appropriate fields from the list:

When you click OK, you will be presented with a dialog asking you whether you want to use the field descriptions as column headings or provide your own column headings. Click OK to use the database field descriptions.

The column headings should now appear on your Grid as in the screenshot below.

Save your work and exit Design mode to see the data populated in the Grid as in the screenshot below.

Additionally, you can sort the data on certain columns by specifying the database fields that determine the order of the records in the “order by” property. See below.

This is the final result after the data was ordered by Sales Person and Quantity Available.

Data from a custom SQL statement

In this section, we will populate the Grid using the same database file we used in the previous section (ITM001P). However, this time, we will use an alternative method to populate the data into our Grid: a custom SQL statement. In the SQL, the file name can be qualified with a library; however, if the library is omitted, the session’s library list will be used to find the database file.

In the Grid’s “custom sql” property, type in the SQL statement, such as the one shown below:

SELECT ITMNR, ITMDS, ITMSP, ITMAVL FROM ITM001P

Save your work and exit Design Mode. Now the Grid should be populated with the same data as shown in the previous example (Data from database file). Additionally, we can also add an ORDER BY command to our SQL statement to order the columns by Sales Person and Quantity Available as shown previously. Now, the SQL statement becomes:

SELECT ITMNR, ITMDS, ITMSP, ITMAVL FROM ITM001P ORDER BY ITMSP, ITMAVL

Save the work and exit Design Mode to see the results. You should get the same result as before. Furthermore, we can add a WHERE clause to the SQL statement to return the items for which the Quantity Available is 25 or more:

SELECT ITMNR, ITMDS, ITMSP, ITMAVL FROM ITM001P WHERE ITMAVL >= 25 ORDER BY ITMSP, ITMAVL

Note: Only SELECT statements are allowed in the “custom sql” property.

Data from a program or web service

In Genie, Grids can also be populated using an external program. This program or web service can be written in any web capable language and hosted on either the IBM i or on any other platform.

Since the Grid expects JSON as its data format, the program or web service needs to send the data to the Grid in that format or data interchange. This can be accomplished by calling the program that generates the JSON data from within Genie through a URL.

From the properties dialog window, find the “data url” property and type in the URL of the program that you wish to call to send the JSON data as shown in the screenshot below:

The “data url” property sets the URL to a program or web service that returns the Grid’s columns definition and data in JSON format.

Here is a sample of the JSON data that is sent to the Grid through the program/web service to build and populate the Grid.

Grid-specific Events 

In this section, we will introduce the events that are available to the Grid component and provide an example of how the events can be used. In the example, we will allow the user to edit record information in the Grid by double-clicking on the appropriate row. Once a row/record is double-clicked, the user will be directed to the change screen in order to change the information for that row/record.

Listed below are all the Grid events that are available along with their description:

onrowclick – Initiates a client-side script when a row within the grid is clicked. The script can determine the row number using the row variable.

onrowdblclick – Initiates a client-side script when a row within the grid is double-clicked. The script can determine the row number using the row variable.

onrowmouseover – Initiates a client-side script when the mouse is moved over a row within the grid. The script can determine the row number using the row variable.

onrowmouseout – Initiates a client-side script when the mouse is moved off of a row within the grid. The script can determine the row number using the row variable.

onpagedown – Initiates a client-side script when the user pages down using the grid's scrollbar. To prevent the grid's default paging action, the script must evaluate to false.

onpageup – Initiates a client-side script when the user pages up using the grid's scrollbar. To prevent the grid's default paging action, the script must evaluate to false.

Below is a sample Product Maintenance screen with options to change and display records (2=Change, 5=Display) in the subfile listing. We will need to further customize this screen to make it more web-friendly.

First, select the option textboxes right next to the product id in the subfile. You can select all of textboxes by holding down your Ctrl key on the keyboard and clicking on the each textbox. Alternatively, you can use the rubber band by clicking on a blank area of the screen and dragging the mouse around the elements as shown below.

Once the textboxes are selected, right-click on any of them and select “Hide” from the context menu as shown below.

This should prevent the selected elements from appearing on the screen. However, the elements will only be grayed out in Design Mode. Repeat the previous step with the output fields that represent headings: Opt, Product, Product Name, Price, Qty, and Special Flag.

Now, add a Grid component and overlay it on top of your subfile as shown below.

The last step is to add the “onrowdblclick” event handler to the Grid. In this case, a little bit of JavaScript code can do the trick. Inside custom.js, past the end of any other existing code, insert a small function called editRecord as shown below. Then save the file.

Before we move on to the next step, let’s explain the piece of code above:

var rowOffset; 

This defines the row offset between the first output field and the first row. This can be found by finding the row number of the first output field in the first Grid row.

var screenRow = row + rowOffset;

The screen row of the option textbox we are after (equivalent to the row number that was double-clicked plus the rowOffset).

getObj(“I_” + screenRow + “_1”).value = “2”;

This sets the value for the textbox to option “2” (2=Change). This textbox was hidden in the previous steps. However, it’s still functional.

pressKey(“Enter”);

Hits the enter key on the keyboard to proceed to the change screen.

In Design Mode, select the Grid and find the “onrowdblclick” event in the Events Section of the Grid Properties dialog. Enter a call to the function that we wrote in the previous step: editRecord(row)

Save your work and exit Design Mode. Once you double-click on a row in the Grid, you should automatically be directed to the Change Record screen.