Priority Cheat Sheet — SQL, Triggers, Procedures & Best Practices

🟦 SQL System Functions

Priority exposes built‑in SQL functions prefixed with SQL.. These return system values usable in triggers, procedures, and reports.

SQL.DATE — current date & time
SQL.DATE8 — current date only
SQL.TIME — current time
SQL.DAY — weekday number
SQL.USER — internal user ID
SQL.ENV — current company
SQL.ENVLANG — current company language
SQL.WEBID — current web user id (number)
SQL.CLIENTID — current web user id (string)
SQL.LINE — row counter
SQL.TMPFILE — path to temp file
SQL.GUID — random GUID
SQL.GROUP — current user Group

SELECT SQL.DATE, SQL.USER 
INTO :now, :usr 
FROM DUMMY;

🧮 SQL Variables

Variables begin with : and can be form variables, parameters, or system variables.

:RETVAL — return value of last query
:SCRLINE — current form line
:PAR1–:PAR3 — message parameters
:FORM_INTERFACE — 1 when form load is running
:FORM_INTERFACE_NAME — the name of the interface (when form load is running)
:ACTIVATEREFRESH refresh the form after direct activation run
:KEYSTROKES — simulate keyboard actions
:SQL.NET — 0 = Windows, 1 = Web
:PRINTFORMAT current selected print format
:SENDOPTION current selected print option
:ISSENDPDF current selected print as pdf
:WANTSEDOCUMENT current selected send print by Email
:EXTERNAL.VARNAME get the variable from External winrun

SELECT DOC
FROM DOCUMENTS
WHERE CURDATE >= (SQL.DATE - 24:00 * 3);
:TotalLines = (:RETVAL <= 0 ? 0 : :RETVAL);

🔤 String & ODBC Functions

STRCAT(a,b) — concatenate
SUBSTR(str,start,len) — substring
LENGTH(str) — length
UPPER() / LOWER()

:first = 'First Text Here ' ;
:last = ' Last Text Here' ;
:full = STRCAT(TOLOWER(:first), ' ---> ',TOUPPER(:last));
output text: 
first text here ---> LAST TEXT HERE

📁 File & Execution Functions

COPYFILE(from,to) — copy file
MOVEFILE(from,to) — move file
DELWINDOW 'f', (path) — delete file
EXECUTE(cmd) — run OS command
LINK table TO file
UNLINK table

EXECUTE COPYFILE :TmpFile , '../../system/load/MyTmpFile.txt';

🧲 Form Triggers

Triggers run automatically when users interact with forms.

PRE-FORM
POST-FORM
PRE-INSERT
POST-INSERT
PRE-UPDATE
POST-UPDATE
PRE-DELETE
POST-DELETE
PRE-INS-UPD-DEL
POST-INS-UPD-DEL

🧪 Procedures & Step Logic

SQLI — SQL query
FORM — open form
REPORT — run report
MESSAGE — show message
INPUT — ask user
GOTO — jump

SELECT PARTNAME, PRICE
FROM PART
WHERE PRICE > :minprice
FORMAT;
/*- no price higher then :minprice found -*/
GOTO 555 WHERE :RETVAL <= 0 ;

🧩 Priority Snippets

Cursor

Cursor Name :

Form Mode

/* -- Start Cursor -- */
DECLARE MyCursor CURSOR FOR
SELECT ....
OPEN MyCursor;
GOTO 10101 WHERE :RETVAL <= 0 ;
LABEL 10102 ;
FETCH MyCursor INTO .... ;
GOTO 10103 WHERE :RETVAL <= 0;
SELECT ...
LOOP 10102;
LABEL 10103 ;
CLOSE MyCursor ;
LABEL 10101 ;
/* -- End Cursor -- */

Get File Size

/* Example: Get file size using Priority tools */
:FILENAME = 'path/myfile.txt';

/* Create a temporary file to store the result */
SELECT SQL.TMPFILE INTO :tmp FROM DUMMY;
LINK STACK TO :tmp;

/* Run OS command that writes the size into the linked file */
EXECUTE GETSIZE :FILENAME, :tmp;

/* Read the result */
:FILESIZE = 0;
SELECT ELEMENT INTO :FILESIZE FROM STACK WHERE ELEMENT > 0;
UNLINK STACK;

Get File Date

/* Example: Retrieve file date using Priority tools */
:FILENAME = 'path/myfile.txt';

/* Prepare a temporary file to capture the output */
SELECT SQL.TMPFILE INTO :tmp FROM DUMMY;
LINK STACK TO :tmp;

/* Run OS command that writes the file date into the linked file */
EXECUTE GETDATE :FILENAME, :tmp;

/* Extract the result */
:FILEDATE = 0;
SELECT ELEMENT INTO :FILEDATE 
FROM STACK WHERE ELEMENT > 0;

UNLINK STACK;

⚠️ Adding Table & Column Rules

Table names: max 20 chars
Custom tables: 4‑letter prefix
Custom columns: same prefix
Column types: CHAR, INT, REAL, DATE, TIME, DAY
Use autounique keys for joins

📘 Best Practices

Use autounique keys for relationships
Never update tables directly — use Form Interface or Linked Table (LINK)
Use SQL.LINE for numbering
Use SQL.TMPFILE for exports
Prefix all custom entities consistently

🔥 Priority Software Developer Cheat Sheet