Ask Reuben

Tables 101

I would like a TABLE to this?

Why can’t a TABLE do this?

One of the great things about our support portal is it gives great visibility to all the support cases from around the world. I have observed a number of questions relating to what you can and can’t do with TABLE’s.

For those that don’t know, I used to be a customer of Four Js ( I started with Four Js in 2008) and I transformed my companies application to Genero around 2003-4 and that application is still going strong. During that time there was a mailing list that allowed direct communication between the Genero developers in Europe and those transforming their applications to Genero around the world. That mailing list which was similar to the communication in an Early Access Program (EAP) allowed me to pick up knowledge on the architecture of tables and hence the why and how of how they operate, information that does not necessarily flow into documentation.

Before I get into it, I will also point out that in our roadmap, we are focussing on Responsiveness in our next release (vNext), and then in the release after that we are looking to add some functionality to Tables, we call that release (vNext+1). So any features you want for Tables, make sure you get involved at the beginning of that particular release cycle.

I would also like you to refresh your memory on how to view the Debug Tree which I covered in an earlier Ask Reuben.

First a little background. The DECLARE CURSOR, FOREACH, DISPLAY ARRAY statements work together to take the results set of an SQL statement, and display them to the screen. A key characteristic of a database cursor for a SQL statement that returns a result set is that the number of columns is fixed, whilst the number of rows is variable. A TABLE has the characteristic that the number of columns is fixed whilst the number of rows is variable.

The Genero architecture involves a back-end, which is where the fglrun process is running, and a front-end which is where the GUI client (the GDC/GBC) is running. Even the Genero Mobile products have this same architecture, an app is in effect two threads, an fglrun thread, and a front-end client thread, and if you are using runOnServer you are using just the front-end thread. The information collected by the back-end has to be sent to the front-end to be rendered. This is where the Dynamic User Interface and the Front End Protocol come into play. A key factor of this architecture design for Tables was that if a database cursor returns and populates an array with 1000 rows, if the user can only see 20 rows, it does not make sense to send all 1000 rows from the back-end to the front-end.

The Front-End Protocol only sends the visible number of rows for a TABLE to the front-end client. It does this with some communication that asks

how many rows can be displayed in the Table?
the front-end calculates how many rows can be displayed and sends this to the back-end
the back-end sends this many rows
if the user scrolls up/down, requests are made to send the additional rows of data
if the user resizes the window or table, requests are made to send the additional rows of data

We can see this happening in the GDC Debug Console window . So what I will get you to do is run a program displaying to a TABLE using the GDC with the GDC Debug Console window. Any program with a DISPLAY ARRAY will do, I will use one of the demo programs shipped in FGLDIR/demo

cd $FGLDIR/demo/Lists
fglrun Lists
In the Lists window, double-click the entry Table01e, I chose this one as it has some data
In the GDC Debug Console click Clear
Rearrange your windows so you should see something like this …

Now click the DisArr1 button, you should see something like this (the TABLE populated and an entry in the Console showing the values used to populate)

The Front-End protocol sent some content to populate the initial array (1). It then asked how many rows were visible (2) and decided it did not need to send any more rows (3).

If in the array we scroll down we initially see that it sends just the information to change the current row …

… the blue text being the action the user selected, in this case scroll down a row, the black being the information to change the current row, but then if we keep scrolling down off the bottom of the page we see it sends data from the back-end to the front-end, this is the rows that were not previously visible …

If we resize the window so that the table is resized …

… we see the blue text being the instruction to increase the page size, the black text being the extra rows sent to the front-end.

This protocol and the fact that only the visible rows are sent to the front-end leads to the following observations/conclusions …

the request for how many rows can be displayed is based on the height of each row being known. The conversation is along the lines of, how many rows can you display, I can display X rows, please send me that number of rows, here are X rows. Hence the height of each row is fixed, you can’t have rows of varying heights. That would require the conversation to be along the lines of, is there room for one more row?, yes please send me another row, here is that row, is there room for one more row? (repeat and so on until there is not room for another row). That is many messages being sent backwards and forwards, rather than just one message being sent backwards and forwards.
if you right-click in the array you will see the GDC local actions include “Copy Visible Column” and “Copy Visible Table”. The front-end only has the visible rows in its memory so that is all it can send to the clipboard without making a request back to the back-end to send more rows of data. That is why you need the techniques discussed here if you want to copy ALL rows
if you hide a column or have a Table that forces a horizontal scrollbar, this same optimisation does not occur. Data for every column in each row is always sent. This means you can unhide a column and it will appear quickly as no extra communication needs to go to the back-end to send the values of the hidden column, the value has already been sent. Similarly if you resize the table/window horizontally so more columns appear, they have already been sent to the front-end and hence the rendering is quick. You may notice this if you do something like have 100 hidden columns and only unhide the columns are appropriate for the user, you are still sending the information for the 100 hidden columns when you scroll
if you executed the built-in find, navigate, and sort functions, you will that the protocol sends information to the back-end to process and then send the user interface changes back to the front-end

On the 4gl side there is one more optimisation that can be made. The design I described above, if there were 1000 rows in the database cursor would send 1000 rows to the 4gl array, and then send the visible 20 rows to the GUI front-end. Some would consider it a waste to send 1000 rows from the database to the 4gl array when the user can only see 20 rows at a time, so why not only send the 20 or so visible rows from the database to the 4gl array and then onto the GUI front-end. That is what the Paged Mode of a DISPLAY ARRAY utilising ON FILL BUFFER can do. You can read up on that here. That is a topic in itself but the fact that when using ON FILL BUFFER, the back-end only has the visible rows has a few interesting behaviour observations. Like any optimisation there is a cost somewhere else and using Paged Mode …

you have to manage the sort yourself through ON SORT trigger as the back-end doesn’t have all the rows available to sort on in memory.
you cannot use Multi Row Select because what if you wanted to select rows that are both visible and not visible?
the vertical scroll bar can only be drawn accurately if the total number of rows is known. Hence the use of set_count() and the use of -1 for unknown.
automatic calculation of aggregates can’t occur as the back-end does not have all the rows of data in order to calculate the aggregate.

I want to draw your attention back to the demo program we were running earlier. With the mouse somewhere in the Table, bring up the Debug Tree and expand the Table node so you can see the Table Column and its children the Edit (widget) node, and the ValueList node and expand the Value List node to see all the Value nodes.

The ValueList node and its children relate to the values you can see for each row. You should make the observation that the number of Value nodes is the same as the number of visible rows. That corresponds to what we said above about the front-end only receiving and storing the information for the currently visible rows.

By having a value node for each visible row, we can have a different value for each visible row. Now look at the Edit or widget node, the attribute values there are defined once for the TableColumn. That is the properties of a widget are the same for every row of a table.

So that means for COMBOBOX, the ITEMS list is the same for every row, that is you can’t change the values in the dropdown for different rows. For all EDIT widgets, the FORMAT attribute is the same for every row, that is you can’t say have a different number of decimal places on different rows. For all widgets, the STYLE attribute is the same for every row, you can’t apply a different style to different rows (except using pseudo selectors).

How can you have different attribute values per row? If you go back and look at the Value node again, you will see that are some additional elements other than Value. These include blink, bold, color, dim, reverse, underline and these can be set on a per cell basis via the setCellAttributes, and setArrayAttributes methods. This requires maintaining a seperate array and the Front End Protocol has the additional responsibility of only sending the values from that additional array for the visible rows. (This functionality means we can mirror what you could do with Informix-4gl and using DISPLAY ATTRIBUTES on an individual cell). The developer has the responsibility to keep the two arrays in sync, what if you insert or delete a row?

The other place you may see different values per row is via the IMAGECOLUMN attribute (and similar attributes for use with TREE container), there you use a PHANTOM column to send the value you want to change on a per row basis in amongst the value data for the other columns and the front-end reads the attribute value from an unseen PHANTOM column.

We could potentially use one of those two techniques to manage different values per row for particular attributes such as STYLE, FORMAT, ITEMS. However that would require changes to the Front End Protocol and AUI Tree. The changes to the front-end and back-end would have to be co-ordinated. It is something that would not be done in a maintenance release but only in a major release. With INPUT ARRAY, where you do need different attribute values per row, my inclination is that a better design is to use DISPLAY ARRAY to select a row, and then open a window with a form that has the attribute values you desire for the widgets and use INPUT to edit a single row of data. This approach also helps with locking rows.

It is important to note that the behaviour to send more rows when the user resizes the Window or Table vertically is only active when a DISPLAY ARRAY or INPUT ARRAY is active. This has an impact in the following…

When the table is first displayed, the initial display of the array may have historically been coded …

FOR i = 1 To 5
    DISPLAY arr[i]. TO scr[i].*
END FOR

With a GUI screen, this code pattern has a few problems. Most obvious is how do you know there are 5 visible rows? You can code this …

DISPLAY ARRAY arr TO scr.*
   BEFORE DISPLAY
      EXIT DISPLAY
END DISPLAY

and that will at least look at the current size of the table and display that many rows. If you don’t want to touch the 4gl code, use the WANTFIXEDPAGESIZE attribute so that the Table is fixed to have the number of rows defined in the .per/.4fd. and then your for loop code is still valid and you don’t have to worry about the case of the user resizing vertically the window or table.

Without using WANTFIXEDPAGESIZE, the code pattern of

FOR i = 1 To 5
    DISPLAY arr[i]. TO scr[i].*
END FOR
MENU ""
   ON ACTION query
       ...
   ON ACTION append
       ...
   ON ACTION update
       ...
   ON ACTION delete
       ...
   ON ACTION first
       ...
   ON ACTION previous
       ...
   ON ACTION next
       ...
   ON ACTION last
       ...

… has the issue of what happens if the user resizes the window/table if the MENU is active. The solution is to make the DISPLAY ARRAY active …

DISPLAY ARRAY arr TO scr.*
   ON ACTION query
       ...
    ON ACTION append
       ...
    ON ACTION update
        ...
    ON ACTION delete
        ...
    ON ACTION first
        ...
    ON ACTION previous
        ...
    ON ACTION next
        ...
    ON ACTION last
        ...

… or use DIALOG statement if there is more than one table. I find developers tend to avoid that change, but I like to think that rather than a form consisting of MENU, INPUT, CONSTRUCT, DISPLAY ARRAY, INPUT ARRAY statements, each form can be consolidated down to a single multi-dialog DIALOG statement.

A few points to finish up with. I mentioned earlier that the number of columns is fixed and the number of rows is variable and that relates to the properties of a database cursor. What about data that has two or more columns as the primary key? A database cursor will still return this with a fixed number of columns and variable number of rows and so the natural display is something like the first table on the left below, whereas an alternative display requested might be something like the second table. …

Colour	Size	Quantity
Blue	Small	10
Blue	Large	5
Red	Small	3
Red	Large	7
Yellow	Small	1
Yellow	Large	0

	Small	Large
BLUE	10	5
RED	3	7
YELLOW	1	0

… if you use the TABLE to construct a solution like the second table that has both a variable number of rows and columns, then you are going down the path of having an arbitrary maximum number of columns and hiding unused columns, no ability to style column and row headers the same, and no built-in display of row totals.

You can use FOCUSONFIELD as part of this solution to allow the user to select a cell, and you can interrogate what row and what column the user has selected, Ordinarily a DISPLAY ARRAY has no concept of current field but by using this attribute you can interrogate what column the user was above when they clicked. The screenshot below shows FOCUSONFIELD being used to indicate a row/column that is clicked.

The above screenshot, the left most column is a Label with style=”html”, and the value of the cell is an html string if you are wondering how I did the colouring.

You can create more advanced solutions using WebComponents such as in this repository but this won’t have any built-in optimisation, you would either send all (visible and hidden) data to the web component, or implement your own optimisation to mirror what our Front-End protocol does. When I wrote that repository it highlighted just how much built-in functionality our Tables have. You forget the built-in find, the column sorting, the column reordering is there automatically without you having to do explicitly do anything.

You will note that repository highlights a lot of functionality I would like to see included in Tables when we do our push in vNext+1. This includes …

variable number of columns
row headers / row totals / variable row heights
splitting / merging cells
applying individual attribute values (including styles) for individual cells.
multi-line aggregates
sub-groups/sub-totals

… and that there is a lot of potential work involving data structures and optimisations that could go into it. I’d hope that you’d participate in the Early Access Program.

For now, understand what our Tables can do, understand the architecture behind it, and maximize what you can accomplish. You’ll note I bolded some statements in this article, they are the key points you should go over in your mind when working with TABLEs’.

Tables 101

Lastest news

Four Js Development Tools Inc.

Four Js around the world: