Custom SQL Visualization+

Overview

When to use it

Use this symbol when you need to:

• Track a live position against a table of planned points, such as a depth track.
• Join several AF tables in one view and combine them with live values.
• Show a plan or limit list that people maintain in AF, without changing the display when the list changes.
• Have an AI write the visualization from a description, without programming skills.

Choose Custom Visualization+ when the document needs no tables. Choose SQL Table+ when a plain table of the rows is enough: it needs no code.

Before you begin

• The administrator has installed the frames folder and can limit code editing and writes. See "Install the Vision Library+ Files" and "Configure Custom Visualization+" in the installation and administration guide.
• The AF table exists, linked or static.
• The symbol has at least one data source: tables reload on PI Vision's data updates.
• You have access to an AI that your company allows. The prompt you paste there holds table names, column names and the current values of the data sources.

Add and set up the symbol

1. Select Custom SQL Visualization+ in the PI Vision symbol gallery and drag one or more data sources onto the display. The symbol shows a starting example: the values of the data sources and "Pick an AF table in the configuration pane."
2. Right-click the symbol and select Configure Custom SQL Visualization+....
3. Under Tables, select Add table (1).
4. Enter the Name (2) the document reads the table by, such as limits.
5. Select the table under AF Table (3).
6. Close the pane and save the display. The starting example now lists the rows of the table.

Next, replace the starting example with your own document, as the first task shows.

Common tasks

Get a document from an AI that uses your tables

Use this when you can describe the view in words and the tables are set up.

1. Right-click the symbol, select Configure Custom SQL Visualization+..., and select Edit code.
2. To show the AI what the values look like, turn on Include sample rows (1). The next copy sends up to 5 rows of each table to the AI, and the box is off again after that copy.
3. Select Copy AI prompt (2).
4. Paste the copied text into your AI chat and add your request. Name the tables and columns exactly, for example "From the table plan, mark each row done once the first source has passed its Depth column."
5. Paste the AI's answer into the code area, select Run, and check Output for errors.
6. Select Apply, close the pane and save the display.

The prompt lists each table's name, columns and column types as of the last load, so add the table before you copy. Without Include sample rows, the copied prompt holds no row values. For advice on writing the request, see Write a request that gets a good result.

Track a live value against a table of points

Use this for a live position against planned points. The example document does this and is a good start to adapt.

1. Drag two data sources onto the symbol in this order: a text value that names the active item, such as the active well, and the live number, such as the depth.
2. Add a table named plugs with the columns Stage, MD(Mt) and Well.
3. Select Edit code, paste the example document into the code area and select Run.
4. Select Apply, close the pane and save the display.
5. If your columns have other names, ask for the change with Copy AI prompt (change).

The list shows the rows of the active item. A row stays Open (1) until the live value reaches its point and then reads Done (2).

Combine several tables in one view

Use this when the view needs rows from more than one table, such as points from one table and a status from another. Joining happens in the document.

1. Under Tables, select Add table for each table, up to 5, and give each a Name (4) (5).
2. In your request to the AI, name the tables and the column that connects them, for example "Join plugs and wells on Well. Highlight the well in the first source."
3. Paste the answer, select Run, select Apply, and save the display.

Below, the status (1) comes from wells, the count (2) from plugs, and the highlight (3) from a live data source.

A table delivers at most 10,000 rows, the first ones that match any filter. Above that the document is told the rows were cut off and how many match, so ask the AI to say so (1).

Filter the rows with SQL Filter+

Use this when operators narrow the view by clicking values in a SQL Filter+ on the same AF table.

1. Add a SQL Filter+ on the table that the symbol reads.
2. To show selected against total, add the same table a second time under Tables, named for example all, and turn Ignore Master on (5) for that entry.
3. In your request to the AI, ask it to show the filter state, for example "Show rowCount of totalCount from selected, and say so when no rows match."
4. Paste the answer, select Run, select Apply, and save the display.
5. Click a value in SQL Filter+.

Only the matching rows reach the document. After a click on one value (1), the document reports 239 of 2,752 rows (2) and counts the selected rows (3) beside every row of the entry with Ignore Master on (4). With no match, the rows are empty and there is no error.

The symbol has no filter indicator of its own, so the document must show the filter state itself.

Follow the display asset

Use this when one display serves several assets, each with its own version of the table in its AF database.

1. Use attributes of the asset as data sources.
2. Under Tables, leave Source on Datasource.
3. Select the table under AF Table. Each asset's database needs a table of that name.
4. Save the display.

When the asset changes, the values follow and the symbol reads the table of the same name in the new asset's database. The rows of the old table are withdrawn first, so no one reads them as this asset's. Below, the chart shows the other asset's active well (1) and the rows of its table.

Check the values and fix the look with a follow-up

Use this after every answer from an AI, before operators see the result. Expect to ask for a follow-up on the layout: a first answer can have the values right and still crowd the view.

1. Right-click the symbol, select Configure Custom SQL Visualization+..., and select Edit code.
2. Select Run (2). The Preview (1) runs the draft on current data and rows, and Output (3) lists errors.
3. Compare a few values with the AF table and with a PI Vision trend. Include rows on both sides of a limit.
4. Select Copy AI prompt (change) (4), paste it into your AI chat and ask for one change at a time, for example "Keep every value. Make all rows fit at 800 by 500."
5. Paste the answer, select Run, and repeat steps 3 and 4 until the view is right.
6. Select Apply (5), close the pane and save the display.

Who owns the code: you own a generated document and its correctness on live operations. Software Athlete supports the symbol and its document functions. Compare the values against the AF table and a PI Vision trend before operators rely on it.

Use the symbol

What an operator sees and can do is defined by the document. The chart below is built from the rows of one AF table.

• Hover, click and keys do what the document provides. See Use the symbol in the Custom Visualization+ guide.
• Rows update when a reload brings a change, at the Reload Every interval, and when a SQL Filter+ selection changes them.
• Writes: the document cannot change a table. It writes values only to its data sources, when Allow writes is on.

When a table cannot be read

The symbol tells the document; what the operator then sees is up to the document, so ask the AI to show these states.

• A reload fails: the document keeps its last rows and receives the error (1). The symbol retries on its own, less often the longer the failure lasts.
• The first load fails: the document has no rows and receives the error.
• The table does not exist in the asset's database: the document has no rows and receives the "was not found" message from the table below.

Configure the symbol

Right-click the symbol and select Configure Custom SQL Visualization+.... The pane has the groups Datasources, Tables, Code, Parameters, Filtering and Style, followed by the standard Web API, License Status, Help and About groups.

Data sources

The data sources in the order the document reads them. See Data sources in the Custom Visualization+ guide.

Tables

Code

Code source, Edit code, Allow writes, Confirm every write and Allow time range work as in Code and Code editor of the Custom Visualization+ guide. The code editor adds Include sample rows: the next copy of the prompt includes up to 5 rows of each table, then the box is off again.

Parameters

One row per setting the document declares. See Parameters in the Custom Visualization+ guide.

Filtering

Channels for the selections and filters the document declares. See Filtering in the Custom Visualization+ guide.

Style

Theme, Surface and Container Style. See Style in the Custom Visualization+ guide.

Document API

A document has every function in the Custom Visualization+ document reference, plus the rows of the tables. Copy AI prompt gives the AI this text for this symbol.

Example document

The document of Track a live value against a table of points:

<!DOCTYPE html>
<html>
<head>
<style>
  html, body { margin: 0; height: 100%; overflow: auto; font-family: var(--vlp-font-family); color: var(--vlp-foreground); }
  table { width: 100%; border-collapse: collapse; font-size: 13px; }
  th, td { padding: 4px 6px; text-align: left; border-bottom: 1px solid var(--vlp-divider); }
  tr.done { color: var(--vlp-text-muted); }
  #note { padding: 4px 6px; font-size: 12px; color: var(--vlp-text-muted); }
</style>
</head>
<body>
<div id="note">Loading...</div>
<table><thead><tr><th>Stage</th><th>MD</th><th>State</th></tr></thead><tbody id="rows"></tbody></table>
<script>
(function () {
  var body = document.getElementById("rows");
  var note = document.getElementById("note");
  var plugs = null;
  var payload = null;
  function draw() {
    // Either can arrive first: draw from what is there.
    var well = payload && payload.sources[0] ? payload.sources[0].value : null;
    var depth = payload && payload.sources[1] && payload.sources[1].good ? payload.sources[1].value : null;
    body.textContent = "";
    var rows = plugs ? plugs.rows.filter(function (row) { return row.Well === well; }) : [];
    rows.forEach(function (row) {
      var tr = document.createElement("tr");
      var done = typeof depth === "number" && depth >= row["MD(Mt)"];
      if (done) tr.className = "done";
      [row.Stage, vlp.formatNumber(row["MD(Mt)"]), done ? "Done" : "Open"].forEach(function (text) {
        var td = document.createElement("td");
        td.textContent = text;
        tr.appendChild(td);
      });
      body.appendChild(tr);
    });
    if (!plugs) note.textContent = "Loading...";
    else if (plugs.error) note.textContent = plugs.error + (plugs.rows.length ? " Showing the last rows read." : "");
    else if (plugs.truncated) note.textContent = "Showing the first " + plugs.rows.length + " of " + plugs.rowCount + " rows.";
    else note.textContent = rows.length + " of " + plugs.rows.length + " rows";
  }
  vlp.onRows(function (rowset) {
    if (rowset.name !== "plugs") return;
    plugs = rowset;
    draw();
  });
  vlp.onData(function (data) {
    payload = data;
    draw();
  });
})();
</script>
</body>
</html>

Troubleshooting and limitations

Limitation: A symbol reads at most 5 tables and at most 10,000 rows per table.

Limitation: Tables are read only. A document can write only to its data sources.

Limitation: The symbol needs one data source, or its tables do not reload.

Limitation: Software Athlete supports the symbol and its document functions. You own the code of generated documents and its correctness on live operations.