datagrid — u_pbt_datagrid #

← Component reference · Guide contents

The modern grid of a DataStore: typed columns and rich cells, sorting, filters, grouping with subtotals, in-place editing, paging, windowed source and CSV export — the DataWindow stays the master of your data.

▶ See it live — Demo application, Data grid tile: the preview, the code behind it and this page, side by side.

At a glance #

Where it fits #

The datagrid is not a DataWindow replacement: it is a presentation layer that sits on top of one. You keep your SQL, your Retrieve() calls, your Update() calls, your printing — and you gain a modern grid for the display.

It targets two blind spots of the classic DataWindow grid:

• scale — only the visible rows are actually drawn, scrolling stays smooth across tens of thousands of rows, and windowed mode holds hundreds of thousands of rows;
• rich cells — colored status chip, gauge, sparkline, star rating, avatar, multiple tags, action button, link.

Database binding and updating remain out of scope: they belong to the DataWindow.

The DataStore of the examples #

Every example on this page — like those of the demo application — starts from the same DataStore of customer accounts, one row per account: city, rep (the sales rep), status, pipeline (a percentage), trend (seven monthly figures written "3,5,2,6,7,4,8"), revenue and rating (a score out of five). The grid is uo_grid on the window, the DataStore ids. A row loaded by of_from_datastore has its RowID in the DataStore as its key: it does not move when the DataStore sorts, filters, inserts or deletes. An event hands it back, and ids.GetRowFromRowId(Long(as_key)) is the number of the row it designates now.

A DataStore only holds plain values, and that is enough for rich cells: a number is all a gauge (RENDERER_PROGRESS) and stars (RENDERER_RATING) need, a name makes an avatar, a text "3,5,2,6" makes a mini chart (RENDERER_SPARKLINE) just as "vip, b2b" makes tags (RENDERER_TAGS); the colour of a chip (RENDERER_CHIP) is given per value with is_tones.

Quick start #

// open event of the window : the accounts, retrieved the way your application already does
ids = create datastore
ids.dataobject = "d_accounts"
ids.SetTransObject(SQLCA)
ids.Retrieve()

// 1. The grid reads the columns (typed) and every row of the DataStore
uo_grid.of_from_datastore(/*ads*/ ids)

// 2. The titles are the header texts of the DataWindow ; rename one if you like
uo_grid.of_column(/*key*/ "rep").is_title = "Account manager"

// 3. Rich cells : an avatar, a coloured status, a gauge, a mini chart, stars
uo_grid.of_column(/*key*/ "rep").is_renderer = n_pbt_datagrid_column.RENDERER_AVATAR
uo_grid.of_column(/*key*/ "status").is_renderer = n_pbt_datagrid_column.RENDERER_CHIP
uo_grid.of_column(/*key*/ "status").is_tones = "Active=" + n_pbt_datagrid_column.TONE_SUCCESS + "|At risk=" + n_pbt_datagrid_column.TONE_DANGER
uo_grid.of_column(/*key*/ "pipeline").is_renderer = n_pbt_datagrid_column.RENDERER_PROGRESS
uo_grid.of_column(/*key*/ "trend").is_renderer = n_pbt_datagrid_column.RENDERER_SPARKLINE
uo_grid.of_column(/*key*/ "rating").is_renderer = n_pbt_datagrid_column.RENDERER_RATING

// 4. A total, and the city stays in view
uo_grid.of_column(/*key*/ "revenue").is_summary = n_pbt_datagrid_column.SUMMARY_SUM
uo_grid.of_column(/*key*/ "city").is_pin = n_pbt_datagrid_column.PIN_START

// ue_row_clicked event of uo_grid : (string as_key)
// The key of a row loaded by of_from_datastore is its RowID : GetRowFromRowId gives
// the row it designates now, even after a sort or a delete in the DataStore.
wf_open_account(ids.GetRowFromRowId(Long(as_key)))

Properties #

Column properties #

Every column is an object in its own right, obtained through of_column("identifier") — the handle is created on first access and stays valid afterwards.

// A column is reached by its key ; used once, it fits on one line
uo_grid.of_column(/*key*/ "amount").is_summary = n_pbt_datagrid_column.SUMMARY_SUM
uo_grid.of_column(/*key*/ "city").is_pin = n_pbt_datagrid_column.PIN_START

Constants #

Always use the constants rather than literal strings: the IDE completes them and a typo becomes impossible.

Constants are read on the object that carries them: u_pbt_datagrid.SELECT_MULTIPLE for a property of the component, n_pbt_datagrid_column.PIN_START for a column property.

Methods #

Filling the grid #

Columns #

Sorting, filtering, presentation #

Selection, detail, export #

Very large volumes #

Shared #

Events #

What the user can do without a single line of code #

The grid ships with its own toolbar and header menus. None of this requires any code on your side:

• sort by clicking a header: ascending, descending, none — on that column alone; Shift+click adds it to the sort after the others (already in it, turns it from ascending to descending, then takes it out), and each arrow then carries its rank (1 decides, 2 breaks the ties…);
• filter quickly through the search box, or column by column through the Filters button, which shows an input row under the headers;
• choose the visible columns through the Columns button;
• resize a column by dragging the edge of its header;
• reorder the columns by dragging their header;
• pin or hide a column through the context menu of its header;
• select a row, a range (Shift) or a set (Ctrl);
• act on a row through its context menu: copy, select all, clear selection, export to CSV — and the entries you add to it.

Every layout change comes back in ue_layout_changed: widths, order, pinned or hidden columns. Keep it, and give it back in one call to of_set_layout the next time the window opens; of_get_layout reads it at any time.

Keyboard — the grid is a single tab stop. Once reached, it is walked entirely from the keyboard:

The focused cell is outlined with the accent colour, and announced to screen readers through aria-activedescendant. The focus survives scrolling: the grid being virtualized, it is held in memory and put back after every render. It does disappear, though, if its row leaves the view through a sort or a filter.

⚠️ The browser clipboard can be refused inside a hosted WebView. That is why ue_copy hands you the text: put it there yourself with ClipBoard(as_tsv) to be sure of the result.

Examples #

Customer accounts in a few lines #

// ids : the accounts DataStore (see above). One repaint for the whole setup.
uo_grid.of_set_redraw(/*on*/ false)

// 1. The grid reads the columns (typed) and every row of the DataStore
uo_grid.of_from_datastore(/*ads*/ ids)

// 2. The titles are the header texts of the DataWindow ; rename one if you like
uo_grid.of_column(/*key*/ "rep").is_title = "Account manager"

// 3. Rich cells : an avatar, a coloured status, a gauge, a mini chart, stars
uo_grid.of_column(/*key*/ "rep").is_renderer = n_pbt_datagrid_column.RENDERER_AVATAR
uo_grid.of_column(/*key*/ "status").is_renderer = n_pbt_datagrid_column.RENDERER_CHIP
uo_grid.of_column(/*key*/ "status").is_tones = "Active=" + n_pbt_datagrid_column.TONE_SUCCESS + "|At risk=" + n_pbt_datagrid_column.TONE_DANGER
uo_grid.of_column(/*key*/ "pipeline").is_renderer = n_pbt_datagrid_column.RENDERER_PROGRESS
uo_grid.of_column(/*key*/ "trend").is_renderer = n_pbt_datagrid_column.RENDERER_SPARKLINE
uo_grid.of_column(/*key*/ "rating").is_renderer = n_pbt_datagrid_column.RENDERER_RATING

// A total, a column that stays in view, several rows selectable
uo_grid.of_column(/*key*/ "revenue").is_summary = n_pbt_datagrid_column.SUMMARY_SUM
uo_grid.of_column(/*key*/ "city").is_pin = n_pbt_datagrid_column.PIN_START
uo_grid.is_selection_mode = u_pbt_datagrid.SELECT_MULTIPLE
uo_grid.of_set_redraw(/*on*/ true)

Grouping by status, then by city #

// The subtotal every group header shows
uo_grid.of_column(/*key*/ "revenue").is_summary = n_pbt_datagrid_column.SUMMARY_SUM

// Two levels : the status, then the city inside it ; "" goes back to a flat list
uo_grid.is_group_by = "status|city"

// The rating stays in view on the right
uo_grid.of_column(/*key*/ "rating").is_pin = n_pbt_datagrid_column.PIN_END

Filtering from your code #

// The filter row under the headers : the user types in it
uo_grid.ib_filter_row = true

// Only the accounts in Paris, and only those above one million
uo_grid.is_quick_filter = "Paris"
uo_grid.of_column(/*key*/ "revenue").is_filter = "> 1000000"

Sorting on several columns #

The sort is written as a DataWindow's is: the first column decides, the next ones break the ties. The user does the same with the mouse by Shift+clicking the headers; a plain click starts again from a single column.

// City first, then the largest revenue in each city
uo_grid.of_sort(/*sort*/ "city A, revenue D")

What the grid reads in the DataWindow #

of_from_datastore takes more than the data: the header text of each column (<column>_t) becomes its title, its display format (Format, or the mask of an EditMask) applies, and a code table — the Values of an Edit, a DDLB or radio buttons, a DropDown DataWindow, a CheckBox — shows the display value (Active) while the row keeps the data value (A). Sorting and searching work on what is seen; a column with a code table is edited through a list, and the data value comes back.

// The grid reads titles, formats and code tables in the DataWindow
uo_grid.of_from_datastore(/*ads*/ ids)

// A format of your own on one column, in the DataWindow's syntax
uo_grid.of_column(/*key*/ "revenue").is_format = "$#,##0.00;($#,##0.00)"

// The rating is picked in its code table (Poor to Excellent) ; the number goes into the DataStore
uo_grid.of_column(/*key*/ "rating").ib_editable = true
uo_grid.ib_write_back = true

// Your code changed a row of the DataStore : show it again
ids.SetItem(12, "rating", 5)
uo_grid.of_reload_row(/*row*/ 12)

Editing in place, saving into the DataStore #

The grid edits, the DataStore keeps the truth: with ib_write_back, every edit is written into it on the row the key designates (its RowID, found where it is), typed by the column, before ue_cell_edited — and the gauge follows. An edited cell keeps a small corner in the accent until of_reset_update.

// The pipelines can be edited : double-click, type a percentage, Enter
uo_grid.of_column(/*key*/ "pipeline").ib_editable = true

// Every edit goes into the DataStore itself, on the row the key designates
uo_grid.ib_write_back = true

A boolean column made editable (TYPE_BOOL, or a CheckBox column of a DataWindow read by of_from_datastore) shows a real check box in every cell: a click, Space, Enter or F2 flips it at once — no text editor, and a double-click does not flip it twice. The cell receives the value of the other state — true/false, or the ON/OFF values of the DataWindow's CheckBox — along the same path as a typed edit: the ib_veto_edits / ue_cell_editing veto, then ue_cell_edited, the "modified" mark and ib_write_back. Read-only, a boolean column keeps its ✓ tick.

// A yes/no column the user ticks : a check box in every cell
uo_grid.of_add_column(/*key*/ "vip", /*title*/ "VIP", /*type*/ u_pbt_datagrid.TYPE_BOOL)
uo_grid.of_column(/*key*/ "vip").ib_editable = true

// Saving stays with the DataStore : when the user confirms
if ids.Update() = 1 then
	COMMIT USING SQLCA;

	// the edited cells are no longer marked as modified
	uo_grid.of_reset_update()
else
	ROLLBACK USING SQLCA;
end if

An expandable row, written from the DataStore #

// Local variables
n_pbt_utils lnv_utils
long ll_row

// A detail panel for the first 20 accounts : the key of a row is its RowID, and
// a text read in the database is escaped before it goes into the markup
for ll_row = 1 to 20
    uo_grid.of_fill_detail(/*key*/ String(ids.GetRowIdFromRow(ll_row)), /*markup*/ "[b]" + lnv_utils.of_escape_markup(/*text*/ ids.GetItemString(ll_row, "rep")) + "[/b] follows the " + lnv_utils.of_escape_markup(/*text*/ ids.GetItemString(ll_row, "city")) + " account")
next

// The first one is open right away ; the chevron opens the others
uo_grid.of_expand_row(/*key*/ String(ids.GetRowIdFromRow(1)))

Pages instead of a scroll, and the total of everything #

Once paging, the grid only holds one page: its footer totals the page. The total of every account is what the DataStore has.

// Local variables
n_pbt_json lnv_totals
double ld_revenue
long ll_row

// Pages of 25 rows past 100 rows ; the footer totals the page
uo_grid.ii_page_size = 25
uo_grid.il_page_threshold = 100
uo_grid.of_column(/*key*/ "revenue").is_summary = n_pbt_datagrid_column.SUMMARY_SUM

// The grand total of ALL the accounts, from the DataStore
for ll_row = 1 to ids.RowCount()
    ld_revenue = ld_revenue + ids.GetItemDecimal(ll_row, "revenue")
next
lnv_totals.of_set_number(/*path*/ "revenue", /*value*/ ld_revenue)
uo_grid.of_supply_grand_totals(/*values_json*/ lnv_totals.of_text())

A source the grid never holds whole #

For hundreds of thousands of rows, the grid only learns how many there are and asks for the ones it shows. This is where your application reads the requested slice from the database; row numbers start at 1, like those of the DataStore.

// The grid only learns HOW MANY rows exist ; it asks for the ones on screen
uo_grid.of_open_source(/*total*/ ids.RowCount())

// ue_rows_needed event of uo_grid : (long al_from, long al_to)
// Rows al_from to al_to, both included, counted from 1 like the DataStore
// Local variables
n_pbt_json lnv_row
string ls_rows
long ll_row

// One JSON row per DataStore row asked for, its key being the row number
ls_rows = "["
for ll_row = al_from to Min(al_to, ids.RowCount())
    lnv_row.of_clear()
    lnv_row.of_set_string(/*path*/ "_k", /*value*/ String(ll_row))
    lnv_row.of_set_string(/*path*/ "city", /*value*/ ids.GetItemString(ll_row, "city"))
    lnv_row.of_set_string(/*path*/ "rep", /*value*/ ids.GetItemString(ll_row, "rep"))
    lnv_row.of_set_string(/*path*/ "trend", /*value*/ ids.GetItemString(ll_row, "trend"))
    lnv_row.of_set_number(/*path*/ "revenue", /*value*/ Double(ids.GetItemDecimal(ll_row, "revenue")))
    if ll_row > al_from then ls_rows = ls_rows + ","
    ls_rows = ls_rows + lnv_row.of_text()
next
uo_grid.of_supply_rows(/*from*/ al_from, /*rows_json*/ ls_rows + "]")

// ue_sort_changed event of uo_grid : (string as_col, string as_dir, string as_sort)
// as_sort is the whole sort, written as SetSort reads it ("" for none) :
// sort the source with it, the grid then asks for its rows again
ids.SetSort(as_sort)
ids.Sort()

Exporting what is shown #

// What is shown - filters, sort, visible columns - to a CSV file
uo_grid.of_export_csv(/*path*/ "C:\exports\accounts.csv")

// ue_csv_saved event of uo_grid : (string as_path, boolean ab_ok, string as_error)
if ab_ok then
    st_status.Text = "Written : " + as_path
else
    st_status.Text = as_error
end if

Your own entries in the row menu #

// The row menu, entry by entry
uo_grid.of_add_row_menu_item(/*key*/ "open", /*label*/ "Open the account")
uo_grid.of_add_row_menu_separator()
uo_grid.of_add_row_menu_item(/*key*/ "call", /*label*/ "Call the sales rep")

// ue_row_menu_clicked event of uo_grid : (string as_menu_key, string as_row_key, string as_keys_json)
// Local variables
long ll_row

// The key of a row is its RowID : GetRowFromRowId gives its row in the DataStore now
ll_row = ids.GetRowFromRowId(Long(as_row_key))

// Each entry of yours, on that row
choose case as_menu_key
    case "open"
        wf_open_account(ll_row)
    case "call"
        wf_call(ids.GetItemString(ll_row, "rep"))
end choose

Reacting to the selection #

// ue_selection_changed event of uo_grid : (string as_keys_json)
// '["3","7"]' : the RowIDs of the selected accounts (ids.GetRowFromRowId gives their rows)
cb_delete.Enabled = (Pos(as_keys_json, "[]") = 0)

Remembering the user's layout #

// ue_layout_changed event of uo_grid : (string as_layout_json)
// Order, widths, pins, hidden columns, sort, filters, grouping : keep it
SetProfileString(gs_ini, "grids", "accounts", as_layout_json)

// open event of the window, once the grid is filled : the layout of last time
uo_grid.of_set_layout(/*layout_json*/ ProfileString(gs_ini, "grids", "accounts", ""))

Best practices #

• Wrap the construction of a grid in of_set_redraw(false) / of_set_redraw(true): the columns and the rows appear all at once, without flickering.
• Call of_reset() before rebuilding a grid: without it, the columns already declared by of_add_column add up to the new ones.
• of_from_datastore gives every row its RowID as its key: that key travels in every event, and ids.GetRowFromRowId(Long(as_key)) finds the row even after a sort or a delete in the DataStore. Rows you load yourself in JSON carry a distinct _k key.
• The grid edits, your DataStore saves: ue_cell_edited → SetItem, then Update() when the user confirms. The component does neither Update() nor printing.
• Beyond a few tens of thousands of rows, switch to windowed mode rather than transferring everything: memory and opening time feel it immediately.
• As soon as the grid paginates, pair the page total with an of_supply_grand_totals: without it, the user reads a partial total where they expect the total of everything.
• Save rich cells for the columns that genuinely help reading: three rich columns out of twelve catch the eye, twelve out of twelve tire it.
• To analyze and cross-tabulate rather than list, crosstab is what you need.

Inherited from the common base #

These members exist on every visual component — they are not specific to this one. They are detailed once, in the transverse chapters; this table only says where to read them.

Two helpers are not inherited: of_icon and of_escape_markup live on n_pbt_utils. Declare one — n_pbt_utils lnv_utils, nothing to create — and call them on it.

← Component reference · Guide contents