The authoritative list of OpenList LIST and SELECT statement keywords for OpenInsight is found in the Programmer's Reference Manual under the section 'Appendix A: OpenList Keyword Reference > With'. It's difficult to find when using the help file index or search feature. Below are a few of my favorite examples.

OpenInsight List and Select Keywords.md

Selecting Data

Executing a select statement will return the number of rows found and set the in memory active list of keys which can be accessed using a list statement.

Select records in table based on key name

@ID contains the name of the record key, use this field in you comparison criteria.

SELECT INVENTORY WITH @ID GT 100 AND @ID LT 200

Select records in table based on a search term in the beginning or ending of a field

@ID contains the name of the record key, use this field in you comparison criteria. For example find all window source code belonging to the EXAMPLES app. The right bracket operator means 'beginning with'.

SELECT SYSREPOSWINS WITH @ID ] "EXAMPLES"

Conversely the left bracket operator means 'ending with' and the example below will return all windows in all applications named CUSTOMER_INFO.

SELECT SYSREPOSWINS WITH @ID [ "CUSTOMER_INFO"

Select records in table based on a field beginning with a search term

@ID contains the name of the record key, use this field in you comparison criteria. For example find all window source code belonging to the EXAMPLES app.

SELECT SYSREPOSWINS WITH @ID ] "EXAMPLES*"

Select records in table containing a string

The keyword ALL refers to the entire contents of a record. Use the keyword all in conjunction with the comparison keyword CONTAINING or []

SELECT INVENTORY WITH ALL CONTAINING "Needle"

SELECT INVENTORY WITH ALL [] "Needle"

Select table with multiple OR field criteria.

SELECT INVENTORY WITH LCOST LE 0 OR WITH PCOST LE 0 OR WITH LPRICE LE 0

Select A Limited Number of Records

SELECT 100 INVENTORY

Saved Lists

The active list of keys can be saved or loaded from a record. The sort order of the keys depends on the BY and BY-DSND operators in the select statement used to generate the list of keys.

Save Active List

SAVELIST MyList

Get List and Set As Active List

GETLIST MyList The system will report the number of keys in the list but does not guarantee the keys in the list are unique or the records still exist.

Creating a New Saved List with Code

If you have a @FM list of keys and want to turn it into a saved list for use at the TCL:

Declare Subroutine Make.List, Save_Select
TABLE_NAME = 'CUSTOMERS'
Open TABLE_NAME To TblHnd Else Debug
Open "DICT.":TABLE_NAME To DictHnd Else Debug
Make.List(0,Keys,TblHnd,DictHnd)
Save_Select('', 'MyList', '', '')

Listing Data

These commands report on the records in the active list which can be set by SELECT or GETLIST.

Show the output in a grid

LIST TABLENAME BY @ID col1 col2 col3 ... (G

Adjust the display length of the key field

LIST SYSREPOS @ID JUSTLEN 50 ID-SUPP

Sort results sorted by a field.

Descending

LIST SYSREPOS UPDATED BY-DSND UPDATED

Ascending

LIST SYSREPOS UPDATED BY UPDATED

To sort by more than one field repeat the BY or BY-DSND statement. LIST SYSREPOS UPDATED BY UPDATED BY @ID

List Limited Number of Records

LIST 100 INVENTORY

Note: When limiting the number of records returned and using sort criteria it's important to remember sorting is performed after the limit. Normally in SQL sorting occurs on the result set and the limit statement is used to return a subset of the result. In OpenInsight the limit statement occurs before the result set is sorted. For instance, listing 100 rows sorted by date will return different results than listing 10,000 rows sorted the same way.

Limit Listed Data To Show Only Matching Multivalue Rows

LIST INVOICES PARTNO LIMIT PARTNO = 1234 QTY PRICE WITH DATE > '5/1/18' AND WITH PARTNO = 1234 BY-DSND DATE

Assuming PARTNO is a multivalue field the LIMIT PARTNO = 1234 will show only values that match PARTNO = 1234. Without this statement all of the PARTNO values would be listed as long as the DATE selection criteria passed and the invoice had at least one PARTNO matchin 1234. THe LIMIT command further specifies which matching multivalue rows should be displayed.

RLIST Function

When using symbolics

SELECT and LIST statements both return the correct results but when using a statement with symbolics it is helpful to use SELECT to ensure symbolic fields don't get needlessly evaluated. Given the statement where DAYS_DUE is a symbolic:

LIST PAYABLES WITH VENDORID = 300 BY DAYS_DUE NAME ADDRESS DAYS_DUE

DAYS_DUE will be evaluated twice, once for sorting and again for display. Using this statement in RLIST to get the results in your program will return the same results but it's more efficent (at a minimum) to change the statement to:

SELECT PAYABLES WITH VENDORID = 300 BY DAYS_DUE NAME ADDRESS DAYS_DUE

In this case even though DAYS_DUE is listed twice it will only be evaluated once during the sort because RLIST knows a SELECT statement will not display any data so it ignores the NAME, ADDRESS, and DAYS_DUE display columns.