# Screen tables

Source: https://wndsoftware.com/docs/screen-tables

### Screen tables

A screen table is a mainframe or IBM i screen that Excel, Power BI, Tableau and scripts read as a table, with no terminal. An analyst opens Excel, chooses Data , Get Data , From OData Feed , types the gateway's address, and gets the customer, the data sets under a level or the jobs in the queue as rows and columns. Numbers come as numbers and dates as dates.

[Download the product examples](https://wndsoftware.com/downloads/wnd-product-examples.zip): API scripts, Power Query, OData flows, test CI files and Power BI connector source. The gateway archive includes its full deployment README.

Someone who knows the screen defines each table once, in WND Terminal, with Save as table… . The table is a plain file, so your IT team reviews it and copies it to the gateway. The gateway runs it in a session of its own each time it's asked, read-only, recorded and in the audit log.

Screen tables are a product on your WND Terminal license. Trial licenses include them.


### Defining a table

There are two kinds:

- A list.  A list that runs over many screens: ISPF's data set list, an SDSF job list, Work with Spooled Files, your application's account list. Every page of it is read, as [Capture all pages](https://wndsoftware.com/capture-all-pages) does, and each row of the list is a row of the table.
- One row.  A screen of labelled values, such as an inquiry's answer: name, status, balance. Each run is one row, and its values are the columns.


#### From Capture all pages

- Go to the list and capture it (Capture all pages , Ctrl+Shift+A).
- In the result, choose Save as table… .
- Name the table (`datasets`, `open_jobs`: letters, digits and _), untick the columns you don't need, rename the others, and set their types: text, number, date or yes/no.
- Choose Record the steps . WND Terminal connects the tab again, and you go to the list the way you always do, from the host's first screen. Recording stops by itself when you're back on the list.
- Every value you typed into a field is offered as a parameter, named after the field's label: the data set level you typed into `DSNAME LEVEL` is `dsname_level`. Tick the ones whoever queries the table should give.
- Pick the key column (the one that tells rows apart), and Save table .
- Test it  runs the table in the tab, from the first screen, as the gateway will, and shows the rows.


#### From Modern view

On a screen of labelled values, right-click in Modern view and choose Save as table… . Each labelled value is offered as a column, named after its label. Tick Parameter  on the input fields whoever queries the table should fill in (the customer number), then record the steps and save as above.


#### Where tables are saved

Tables are saved in the first folder in the policy's TableFolders , or in `Documents/WND Terminal/Tables` when it isn't set. The gateway serves only the tables in its own `[tables] folder`, so nothing reaches analysts until your IT team copies a table there.


#### The file

A table is a [flow](https://wndsoftware.com/docs/mcp#flows-your-tested-procedures-as-tools) with a `table:` line. You can write one by hand, or change what Save as table wrote:


```text
#! name: datasets
#! description: The data sets under a high-level qualifier.
#! input level "The high-level qualifier, such as WND.TEST"
#! writes: no
#! table: datasets
#! rows: list
#! page_key: PF8
#! max_pages: 200
#! key: data_set
#! column 1 "Data set"
#! column 2 "Volume"
#! connection: Production (TSO)
wait "READY"
type "ISPF"
press Enter
type "3.4"
press Enter
wait "DSNAME LEVEL"
type {level}
press Enter
if screen has "No data set names found"
  fail "No data sets match that level"
end
wait "Row 1 of"
```

On a small screen, swipe the table to read all columns.


| Line | What it does |
| --- | --- |
| `table: NAME` | The flow is a table by this name. |
| `input NAME "description"` | A parameter. `optional` and `default "value"` work as for any flow, and `pattern` checks the value. |
| `output NAME "Label" TYPE` | One-row tables: a column, read by the macro's `read` line. |
| `rows: list` | The rows are the list on the screen the macro ends on, every page of it. |
| `column N "Name" TYPE` | List tables: keep the list's Nth column (from the left, from 1) under this name. Columns that aren't named are left out. |
| `page_key: KEY` | The key that shows the next page. PF8 on a mainframe and Roll Up (Page Down) on IBM i when it isn't set. |
| `max_pages: N` | Stop after this many pages (500 when not set). The answer then says the list may go on. |
| `key: NAME` | The key column. Without one, the rows are numbered in a `row` column. |
| `writes: no` | A table only looks things up. It must say so, and the gateway runs it read-only key by key. |
| `connection: NAME` | The policy's connection it runs in, on the gateway. |
| `dates: dmy` | How the host writes dates: `mdy` (the default), `dmy` or `ymd`. |

The types are `text`, `number`, `date` and `yesno`. A `number` drops spaces, commas and currency signs, and a trailing minus, `CR` or `DB` makes it negative. A `date` is read in the `dates:` order and given as YYYY-MM-DD. A `yesno` reads Y/N, YES/NO, 1/0, X and blank. A value that doesn't read as its type is empty (null).

A search that can find nothing should say so with `fail "..."`. The table then answers with no rows and the flow's words, which is what an analyst expects, rather than an error.


### On the gateway

In `gateway.toml`:


```text
[tables]
folder = "/etc/wnd-gateway/tables"

[mcp.service_accounts.finance-reports]
token_sha256 = "..."                 # wnd-gateway new-agent-token finance-reports
connections = ["Production (TSO)"]
```

On a small screen, swipe the table to read all columns.


| Setting | Default | What it does |
| --- | --- | --- |
| `folder` | none | The folder of tables. `/odata` is on when it's set, and the folder is read again within 10 seconds of a change. |
| `cache_seconds` | `60` | How long an answer is kept for the same caller, table and parameters, so a refresh that asks twice reaches the host once. `0` keeps nothing. |

Tables run in the sessions [screen flows as APIs](https://wndsoftware.com/docs/flow-apis) use, so the `[api]` session settings hold for them. Tables with `session: pooled` reuse sessions, and the others open a session for each query and close it after. With `[mcp] sandbox_only` every session plays a recording from the sandbox folder, which is how to try tables with no mainframe (examples/odata/README.md).

Who can query.  A service account, with Basic sign-in: its name as the user name and its token as the password. This is what Excel, Power BI and Tableau ask for. Programs can also send the token, or a person's token from your identity provider, as `Authorization: Bearer`. Basic sign-in is accepted at `/odata` only.

The feed's address is `https://<gateway>/odata/`.


### Excel

- Data , Get Data , From Other Sources , From OData Feed  (on a Mac: Data , Get Data (Power Query) , OData ).
- The address, with the parameters in `$filter`: `https://terminal.bank.example/odata/customers?$filter=customer eq '12345678'`. A table without parameters needs only its address: `https://terminal.bank.example/odata/jobs`.
- Choose Basic . The user name is the service account's name, and the password is its token.
- Load . Refresh  runs the table again.


### Power BI

Desktop.  Get Data , OData feed , the address as for Excel, and Basic  sign-in.

A report with a parameter.  So a report has a customer picker: Transform data , Manage Parameters , New Parameter  named `Customer`. Then in the query's Advanced Editor :


```text
let
    Source = OData.Feed("https://terminal.bank.example/odata/customers?$filter=customer eq '" & Customer & "'", null, [Implementation = "2.0"])
in
    Source
```

The WND Terminal connector.  `examples/powerbi` has the source of a Power BI connector that lists every table on your gateway in the navigator, each one a function of its parameters (`customers("12345678")`). You build it and sign it yourself with Microsoft's Power Query SDK, and its README says how. Then you install it like any other custom connector.

Scheduled refresh.  In the Power BI service, refresh goes through your on-premises data gateway, which must reach your WND Terminal gateway. Add an OData data source with the address and the service account (or install the connector on the data gateway).


### Tableau

Connect , To a Server , OData . The server is the address with `$filter` as for Excel. Authentication : Username and Password , with the service account's name and its token.


### Python


```text
import requests
rows = requests.get("https://terminal.bank.example/odata/customers", params={"$filter": "customer eq '12345678'"}, auth=("finance-reports", token)).json()["value"]
```

`examples/odata/query.py` and `query.ps1` (PowerShell) do the same with errors handled.


### Scripts on the desktop

`wnd-automate table query` runs a table from TableFolders in WND Terminal on the same computer, through the automation API:


```text
wnd-automate table list
wnd-automate table query datasets --where level=WND.TEST --connection "Production (TSO)" --csv > datasets.csv
```

The table runs in the session `--session` names (it must be on the host's first screen), or in the saved connection `--connection` names, opened for the query and closed after it. `--json` prints the rows as JSON.


### What the feed does

On a small screen, swipe the table to read all columns.


| Request | What you get |
| --- | --- |
| `GET /odata/` | The tables (the service document). |
| `GET /odata/$metadata` | Each table's columns and types, its key, and its parameters (as required filters). |
| `GET /odata/customers?$filter=customer eq '12345678'` | The rows. |
| `GET /odata/customers('12345678')` | One row by its key, when the key is the parameter. |
| `GET /odata/jobs/$count` | How many rows, as a number. |

Parameters go in `$filter`, joined with `and`: `$filter=customer eq '12345678' and region eq 'US'`. Each can also be given by itself: `?customer=12345678`. `eq` on a column that isn't a parameter keeps the rows with that value. `$select`, `$orderby`, `$top`, `$skip` and `$count=true` work on the rows. `or`, `ne`, `gt` and the other operators, functions such as `contains`, `$expand` and `$batch` aren't supported. The feed answers 501 and says what is.

On a small screen, swipe the table to read all columns.


| Type | In OData | In the answer |
| --- | --- | --- |
| text | `Edm.String` | `"ACME TRADING CO"` |
| number | `Edm.Decimal` | `12480.55` |
| date | `Edm.Date` | `"2019-03-14"` |
| yesno | `Edm.Boolean` | `true` |
| the row's number | `Edm.Int32` | `3` |

When something goes wrong the answer is OData's error, with a sentence:

On a small screen, swipe the table to read all columns.


| Status | When |
| --- | --- |
| 400 | A parameter is missing or doesn't fit ("The table customers needs customer (The 8-digit customer number) in $filter, for example $filter=customer eq '...'."), or the request can't be read. |
| 401 | No sign-in, or a wrong name or token. |
| 403 | The license doesn't include screen tables, or a request from another site's page. |
| 403 `refused` | The service account may not use the table's connection. A service account uses the connections in its own `connections` list, or the policy's AgentConnections  when it has no list. |
| 404 | No such table, or screen tables aren't set up on this gateway (`[tables] folder` isn't set). |
| 409 `read_only` | The table pressed a key that could change data, so the gateway stopped it. Tables run read-only. |
| 429 | Too many wrong sign-ins from this address. Wait a minute, then try again. |
| 500 `script` | The table's own steps went wrong, such as ending on a screen with no list on it. The sentence says what to change. |
| 501 | Something the feed doesn't do. |
| 502, 504 | The host didn't answer as the table expects, or ran out of time. The sentence says where. |
| 503 | Every session is busy. Try again in a few seconds. |


### What's recorded

- Every query is a `table.query` event in the audit log: the table, who asked, how many rows, how long it took, and whether the answer was kept from before. Never the parameters or the values.
- The sessions tables run in are recorded, as screen flows as APIs are.
- A table's values go to the caller as the screen shows them, to an authenticated caller, the same as screen flows as APIs. Password fields are never read. Your MaskPatterns  and card number masking apply to what the screen shows, so a masked value is masked in the table too.


### Reviewing tables before they go live

On a small screen, swipe the table to read all columns.


| Policy setting | What it does |
| --- | --- |
| TableFolders | Where Save as table saves (the first folder) and where Test it and `wnd-automate table` look. Left out, each person's `Documents/WND Terminal/Tables`. |

Tables are saved by the people who know the screens and served only after you copy them to `[tables] folder`. Review each one as you would a macro: its steps, its connection and its `writes: no`.

WND Terminal doesn't include an ODBC driver. Excel, Power BI, Tableau and most reporting tools read OData feeds with nothing to install. If a tool you use reads only ODBC, tell WND Software which one.

Technical reviewer: [Albert Joseph](https://wndsoftware.com/authors/albert-joseph), WND Software.