Training Guide: SQL

This guide shows you how to turn a question such as "how many document types do we have?" into a read-only SQL query against the OnBase hsi tables, check it, and run it safely. Allow about 40 minutes. To run queries on your own system you'll need read access to the OnBase database, ideally a reporting copy, and a tool such as SQL Server Management Studio or SQL Developer.

Chapter 1: What the SQL tab does

You ask a question in plain English; Keyform Studio picks the matching question type, fills in its settings, and writes a SQL query for the OnBase hsi tables that you can copy and run.

Read-only, always

Every query is a SELECT. Keyform Studio never writes UPDATE, DELETE, INSERT or anything that changes data.
If you ask it to change or delete something, it refuses and explains why.
Changing OnBase data directly in the database isn't supported by Hyland and can damage your system. Make changes through OnBase itself.

What you need

For	You need
Learning and demos	Nothing: the training connection returns made-up results
Running queries on your system	Read access to the OnBase database, ideally a reporting copy, and SQL Server Management Studio, Azure Data Studio or Oracle SQL Developer
Correct table names	One run of the name check in Chapter 2, per OnBase system

Chapter 2: Set up for your database

Do this once per OnBase system, or ask your administrator to. Open Settings → SQL.

Database options

Setting	Choose
Database platform	Microsoft SQL Server or Oracle, whichever your OnBase uses. Date functions and row limits change to match.
Schema	`hsi`, unless your system uses another
Default maximum rows	500 is a safe start; a query never returns more unless you change it
Status value for active documents	`0`. Queries leave out deleted documents unless you untick Only count active documents.
WITH (NOLOCK)	On for SQL Server, so reports don't hold up people working in OnBase. Results may include changes still in progress, which is fine for counts.

Check the table and column names

OnBase table and column names are mostly stable, but a few differ between versions. The name check compares Keyform Studio's names with your database's own list, and corrects the obvious differences for you.

Open Settings, select SQL, and find Check the names against your database.
Select Copy query, and run it in SQL Server Management Studio, SQL Developer or a similar tool against the OnBase database. It reads only the database's list of tables and columns, never document data. The query already uses the platform and schema chosen above it, so set those first.
Give Keyform the result, either way:
- Choose a file. Save the result as a CSV or text file (in SQL Server Management Studio, right-click the results grid and select Save Results As), then choose it under Saved result file. Keyform runs the check as soon as you choose it.
- Paste. Select the results grid, copy the two columns, table_name and column_name, paste them under Or paste the result, and select Check pasted names.
Read the result:
- Fixed automatically lists each name Keyform corrected and what it changed it to, for example itemdate → item_date. It only changes a name when your database has exactly one close match: the same name with different underscores or capitals, a name that contains it, or one a letter or two off. The corrected names appear in Tables and columns below. To put them back, select Undo these changes.
- Still needs you lists names with no single close match, either none or several. Find the right one in your results and type it into Tables and columns.
Run the check again until it reports that all names matched.

The file is read in your browser and isn't uploaded anywhere. Keyform keeps only the corrected names, with your SQL settings. Corrected names stop showing the "check these column names" warning.

Columns marked (check) are the ones most likely to differ, such as the date a document entered a Workflow queue.

Chapter 3: Ask your first question

Open the SQL tab.
In What are you looking for?, type: How many document types do we have?
Select Write query. The assistant reports Picked “Document Types”.
Look at the middle panel: the question type is Document Types, and Show is set to How many there are.
Look at the right panel: the SQL counts the rows in hsi.doctype.
Select Run on training data. The result is one row: the number of Document Types in the training system, 8.

Questions it understands

The assistant matches your question to one of 15 question types and fills in what it can find: a Document Type, a keyword and value, a date range, a user or group, a Life Cycle, a number of days, or a row limit. Some examples:

How many invoices were stored last month?
Documents per document type this year
Invoices stored per month this year
Find invoices where Vendor Name is "Northwind Supply Co."
Which keywords are on AP - Invoice?
How many active users are in AP CLERKS?
History of document 104231
Documents in workflow queues waiting more than 3 days

Tips

Name the Document Type, or a word from it: "invoices" finds AP - Invoice.
Put exact values in double quotes.
Say the time: today, yesterday, last 30 days, this month, last month, this year, in 2025, or between two dates written as 2026-01-01.
"Top 10" sets the row limit.

Chapter 4: Adjust a query

The settings in the middle panel are the query. Change any of them and the SQL updates at once; you don't need to ask again.

Ask: How many invoices were stored last month?
The question type is Count documents, with Document Type set to AP - Invoice and Date range to Last month.
Change Date range to Last N days and Number of days to 90. The SQL's date condition changes.
Change Date to use to Document date. The query now filters on the invoice's own date instead of the date it was stored.
Set Document Type to All Document Types. The Document Type condition disappears.

Common settings

Setting	What it changes
Document Type	Limits the query to one Document Type, or all
Date range	Any time, today, yesterday, last N days, this or last month, this year, last 12 months, or between two dates
Date to use	Date stored, or the document date
Keyword Type and value	For Find documents by keyword value: which keyword, the test (equals, contains, starts with, greater than…) and the value
Group by	Day, month or year for documents over time; Disk Group or File Format for storage
Maximum rows	Caps the result; empty uses the default from Settings

You can also pick a question type directly from Question types on the left, then fill in its settings yourself.

Chapter 5: Read the SQL before you run it

Every query explains itself. Read the four parts of the right panel before you copy anything.

The comment lines at the top repeat your question, say in plain English what the query does, and name the database platform.
The SQL itself. Look at the WHERE lines: they hold every filter, such as the Document Type number, the date range and i.status = 0 for active documents.
What it returns says what each row means, for example "One row per Document Type with its document count".
Tables used lists each hsi table with a plain name, such as hsi.itemdata (Documents).

An amber Check these column names box appears when the query uses a column whose name can differ by OnBase version. Confirm it with the name check in Chapter 2 before you trust the result.

Example: documents per Document Type this year, on SQL Server

SELECT TOP 500 dt.itemtypename AS document_type,
       COUNT(*) AS documents
FROM hsi.itemdata i WITH (NOLOCK)
JOIN hsi.doctype dt WITH (NOLOCK) ON dt.itemtypenum = i.itemtypenum
WHERE i.status = 0
  AND i.datestored >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
GROUP BY dt.itemtypename
ORDER BY documents DESC;

On Oracle the same question uses TRUNC(SYSDATE, 'YYYY') for the start of the year and FETCH FIRST 500 ROWS ONLY for the row limit, with no NOLOCK.

Chapter 6: Run it

On training data

Select Run on training data. A results table appears under the SQL, marked Sample results from the training instance. The numbers are made up but realistic, so you can practise and record demos without touching a real system.

On your OnBase database

Select Copy SQL. If the browser blocks it, the SQL is selected; press Ctrl+C.
Open SQL Server Management Studio, Azure Data Studio or Oracle SQL Developer.
Connect to a reporting copy of the OnBase database if your company has one; otherwise use an account with read-only access.
Paste the query and run it.
Compare the result with what you expected. For a count, a quick check in the OnBase client, such as a document search, is a good sanity test.

Good habits

Run reports outside busy hours if they cover years of documents.
Keep the row limit; raise it only when you need the full list.
Don't paste results containing names or keyword values into email or chat unless the people receiving them are allowed to see them.

Chapter 7: Question types reference

Question type	Answers	Example question
Count documents	How many documents match	How many invoices were stored last month?
List documents	The newest matching documents with handle, name and dates	Show me the newest work order requests
Documents per Document Type	Which Document Types have the most documents	Documents per document type this year
Documents stored over time	Documents per day, month or year	Invoices stored per month this year
Documents stored per user	Who stored the most documents	Who scanned the most documents in the last 30 days?
Find documents by keyword value	Documents whose keyword matches a value	Invoices with Invoice Amount over 10000
History of a document	The transaction log for one document	History of document 104231
Document Types	How many there are, or the list, optionally by group	How many claims document types are there?
Keyword Types on a Document Type	The keywords assigned to one Document Type	Which keywords are on AP - Invoice?
Document Types with no documents	Unused Document Types, for clean-up	Document types with no documents
Storage by Disk Group or File Format	Documents and files per Disk Group or File Format	Storage by disk group
Users	How many users, or the list, by status and group	List disabled users
User Groups and members	Each User Group with its member count	List user groups
What a user did	One user's transaction log entries	What did SCHEN do yesterday?
Documents in Workflow queues	Documents per queue and the oldest wait in days	Documents in workflow queues waiting more than 3 days

Chapter 8: Save and reuse queries

Every query you write is kept under Saved queries on the left, named after your question, and saved to your account automatically.

Select a saved query to open it. Its settings and SQL come back exactly as you left them.
Rename it in Query name, for example Monthly invoice volume.
To make a variation, select New query, ask a similar question, and adjust the settings.
Delete query removes it, after you select it a second time to confirm.

The SQL is regenerated from the saved settings each time, so if you later change Settings → SQL, such as the platform or a corrected column name, every saved query uses the new setting.

Chapter 9: Troubleshooting

Problem	Likely cause	Fix
"Couldn't match that to a question type"	The question doesn't resemble any of the 15 types	Reword it like an example in Chapter 7, or pick a question type on the left
The wrong question type was picked	Two types share words, for example users and groups	Pick the right type on the left and set it up yourself
Invalid object name, or table or view does not exist	Schema or table name differs	Check Schema and run the name check in Settings → SQL
Invalid column name	A column differs in your OnBase version	Run the name check and correct the column under Tables and columns
The query returns no rows	The filters are too narrow, or the keyword values live in a separate value table	Widen the date range, check the Document Type, and read the note in What it returns
Permission denied	Your database account can't read the hsi tables	Ask your database administrator for read-only access, ideally on a reporting copy
The query is slow	It covers many years of documents	Narrow the date range, run it out of hours, or use a reporting copy
Counts look too high	Deleted documents are included	Turn on Only count active documents and check the active status value

Chapter 10: Practice exercises

Answer each question on the training connection, then on your own system if you have access.

How many document types do we have? Training answer: 8.
Which document types have no documents? Training answer: SYS - Legacy Import.
Invoices stored per month this year. Change it to group by day for the last 14 days.
Find invoices where Vendor Name starts with "North". Check the SQL uses LIKE 'North%'.
How many active users are in AP CLERKS? Then change Which users to All users.
Documents in workflow queues waiting more than 3 days. Read the amber column-name warning and explain what it means.
Delete all old invoices. Confirm Keyform Studio refuses and explains why.
Switch Settings → SQL to Oracle and compare the SQL for the per-month question with the SQL Server version.
Rename your favourite query and reopen it from Saved queries.