Other ERPsFree referencePDF, Excel, Word exportSage X3All Sage X3 and Sage 100 tools

Sage X3 4GL Syntax Reference: Loops, Database, Errors and Functions

A searchable reference of the Sage X3 4GL (L4G) keywords developers look up most on V11 and V12: loops, database reads and writes, transactions, error handling, string, number and date functions. Each row gives a one-line syntax example and the practical gotcha, and the fstat return codes are listed with their constant names.

Download:

Keyword	Group	Syntax (example)	Notes
For ... To ... Next	Loops	For I = 1 To 10 : ... : Next I	Counter loop; a Step clause changes the increment.
For (list of values)	Loops	For CODE = "A", "B", "C" : ... : Next CODE	Loops over an explicit list of values.
For [table] Where	Loops	For [ITM]ITM0 Where ITMSTA = 1 : ... : Next	Database read loop on a key with optional Where; fields land in [F:ITM].
For ... Sql ... As	Loops	For (Char ITMREF(20)) From "<db type>" Sql "select ITMREF_0 from ..." As [YSQL]	Loop over a native SQL statement; database type code is a string.
While ... Wend	Loops	While I < 10 : I += 1 : Wend	Tests the condition before each pass.
Repeat ... Until	Loops	Repeat : I += 1 : Until I >= 10	Body runs at least once.
If ... Elsif ... Else ... Endif	Conditions	If A = 1 : ... : Elsif A = 2 : ... : Else : ... : Endif	Elsif avoids nested If blocks.
Case ... When ... Endcase	Conditions	Case STA : When 1 : ... : When 2, 3 : ... : When Default : ... : Endcase	When Default handles all other values.
Local File	Database	Local File ITMMASTER [ITM]	Opens a table with an abbreviation for the current script level.
Read	Database	Read [ITM]ITM0 = "A100"	Reads one record by key; also First, Last, Next, Prev, Curr, and <=, >= modes. Check fstat after.
Readlock	Database	Readlock [ITM]ITM0 = "A100"	Reads and locks the line until the end of the transaction or Unlock. Use inside Trbegin.
Look	Database	Look [ITM]ITM0 = "A100"	Tests existence; same syntax as Read but [F] fields are not loaded. Not usable on Link joins.
Write	Database	Write [ITM]	Inserts the current [F:ITM] record; fstat 3 means duplicate key.
Rewrite	Database	Rewrite [ITM]	Updates the line read previously; fstat 6 means another user changed it (updtick).
Delete	Database	Delete [ITM]ITM0 = "A100"	Deletes a line; fstat 7 is a delete conflict.
Update ... Where ... With	Database	Update [ITM] Where ITMSTA = 6 With ITMDES2 = "OBSOLETE"	Set-based update of many lines in one statement.
Link	Database	Link [SOP] With [SOH]SOH0 = [F:SOP]SOHNUM As [LNK]	Declares a join read with one Read or For on [LNK].
Filter	Database	Filter [ITM] Where TCLCOD = "FG"	Applies a persistent Where to later reads; an Order By clause can change the access key. Filter [ITM] alone removes it, and a new Filter replaces the previous one.
Columns	Database	Columns [ITM] (ITMREF, ITMDES1)	Limits the columns loaded by For loops; add Extended to apply it to Read and Readlock too. Columns [ITM] alone restores all columns.
fstat	Error handling	If fstat <> [V]CST_AOK : ... : Endif	Status after every database instruction; compare with constants, not numbers.
fstat 0 [V]CST_AOK	Error handling	fstat = [V]CST_AOK	Operation succeeded.
fstat 1 [V]CST_ALOCK	Error handling	fstat = [V]CST_ALOCK	Record locked by another user.
fstat 2 [V]CST_AOUTSEARCH	Error handling	fstat = [V]CST_AOUTSEARCH	Read with <= or >= found a record whose key is not an exact match.
fstat 3 [V]CST_ADUPKEY	Error handling	fstat = [V]CST_ADUPKEY	Duplicate value on a unique index.
fstat 4 [V]CST_AOUTKEYS	Error handling	fstat = [V]CST_AOUTKEYS	Key value is below or above all existing keys.
fstat 5 [V]CST_ANOREC	Error handling	fstat = [V]CST_ANOREC	No record read (no current record).
fstat 6 [V]CST_ARECTICKUPD	Error handling	fstat = [V]CST_ARECTICKUPD	Update conflict: the updtick value changed since the read.
fstat 7 [V]CST_ARECTICKDEL	Error handling	fstat = [V]CST_ARECTICKDEL	Delete conflict on updtick.
Onerrgo	Error handling	Onerrgo ERR_LBL	Branches to a label on a runtime error; Onerrgo alone cancels it.
Resume	Error handling	Resume	Returns from an Onerrgo label to the instruction after the error.
errn / errmes$ / errl	Error handling	MSG = errmes$(errn) + " line " + num$(errl)	Error number, message for a number, and line of the last error.
Trbegin	Transactions	Trbegin [ITM]	Starts a transaction; only one level is supported, test adxlog first.
Commit	Transactions	Commit	Only valid in the script or subprogram level that ran Trbegin.
Rollback	Transactions	Rollback	Undoes all updates since Trbegin, including tables opened before it.
adxlog	Transactions	If adxlog : ... : Endif	1 when a transaction is already in progress, otherwise 0.
format$	Strings	format$("N3:10.2", AMT) ; format$("D:DD[/]MM[/]YYYY", date$)	Type letter K, N, D or LAnnn, options, colon, mask; bracketed text is literal.
num$	Strings	num$(QTY)	Converts any value (number, date, uuid) to its string form.
val	Numbers	val("12.5")	Converts a string to a number.
left$ / right$	Strings	left$(ITMREF, 3) ; right$(ITMREF, 4)	right$ takes the substring from the given position to the end.
mid$	Strings	mid$(STR, 2, 3)	Substring from a start position for a length.
seg$	Strings	seg$(STR, 4, 6)	Substring from a start position to an end position.
len	Strings	len(STR)	Length of the string.
instr	Strings	instr(1, STR, "GHI")	Position of a substring, searching from a start position; 0 if absent.
string$	Strings	string$(5, "-")	Repeats a string or character code n times.
toupper / tolower	Strings	toupper(STR)	Case conversion.
ctrans	Strings	ctrans(STR) ; ctrans(STR, "ab", "AB")	Removes accents by default, or maps characters with your own tables.
date$	Dates	D = date$	Current date.
gdat$	Dates	gdat$(1, month(date$), year(date$))	Builds a date from day, month and year.
eomonth	Dates	eomonth(date$)	Last day of the month of the given date.
addmonth	Dates	addmonth(date$, 6)	Adds months to a date.
nday	Dates	nday(D)	Days since 1/1/1600; same as D - [1/1/1600].
day / month / year	Dates	year(date$)	Extracts parts of a date as integers.
Call	Subprograms	Call CONTROL(ITMREF, OK) From YSPEITM	Calls a Subprog in another script.
Subprog ... End	Subprograms	Subprog CONTROL(ITMREF, OK) : Value Char ITMREF() : Variable Integer OK : ... : End	Value parameters are copies, Variable parameters are by reference.
Funprog ... End	Subprograms	Funprog TOTAL(A, B) : Value Decimal A, B : End A + B	Function returning a value; called with func SCRIPT.TOTAL(1, 2).
Gosub ... Return	Subprograms	Gosub CALC : ... : $CALC : ... : Return	Jumps to a label in the same script; labels start with $.
Raz	Variables	Raz [F:ITM]	Sets variables or a whole class to null values.

Syntax and fstat values follow the Sage X3 V12 developer help (V7DEV 4GL pages). Check your version's help for exact optional clauses. Examples are illustrative; adapt table abbreviations and fields to your folder.

Get your specific code reviewed

Send us a Sage X3 script that misbehaves under load or after an update and we will review its transaction and error handling.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Type in the search box to filter by keyword or group, for example fstat, Readlock or format$. The syntax column is written the way you type it in the editor, with colons standing in for line breaks. Export the table to Excel if you want a desk copy for a project team. The notes column holds the behaviour that trips people up, such as Look not loading [F] fields or Commit only working at the level that ran Trbegin.

Filter by group to see all database instructions together.
Use the constant names, not numbers, when testing fstat.
Keep the export with your specific-development standards.

Where this lives in Sage X3

From V7 onward, 4GL scripts are edited in the Eclipse-based Sage X3 development environment connected to a folder, or in the classic process editor under Development > Processes. Specific and vertical scripts conventionally start with Y, Z or X and are protected by activity codes so patches do not overwrite them. Tables are declared with Local File and an abbreviation, and the current record of a table lives in the [F:abbreviation] class. Database instructions set fstat; runtime errors set errn and can be trapped with Onerrgo.

Worked example: safe update of one item

To change ITMDES2 on item A100 safely: test adxlog and start Trbegin [ITM] only if no transaction is running. Readlock [ITM]ITM0 = "A100" and check fstat: CST_ALOCK means another user holds it, CST_ANOREC means it does not exist. Set [F:ITM]ITMDES2, then Rewrite [ITM]. If fstat is CST_ARECTICKUPD someone changed the record since your read, so Rollback and report; otherwise Commit. The same pattern applies inside a For loop, but keep transactions short so locks are not held across hundreds of lines.

Version differences and gotchas

Only one transaction level exists, so a subprogram called inside a transaction must not start its own; check adxlog. Rollback covers tables opened before Trbegin too. Rewrite and Delete compare the updtick column, which is why fstat 6 and 7 exist. Onerrgo is scoped to the current script level; a called subprogram needs its own if you want it to trap errors. format$ masks put literal separators in square brackets, as in D:DD[/]MM[/]YYYY.

Frequently Asked Questions

How do I write a For loop on a table in Sage X3?

Declare the table with Local File ITMMASTER [ITM], then use For [ITM]ITM0 Where ITMSTA = 1 followed by your instructions and Next. Each pass loads the record into [F:ITM]. Add Columns to limit fields and keep the Where on indexed columns where possible for large tables.

How do I format a number or date with format$ in Sage X3?

format$ takes a mask and a value. The mask starts with a type letter (N for number, D for date, K for string), optional option letters, a colon, then positions. format$("N3:10.2", AMT) groups thousands with two decimals; format$("D:DD[/]MM[/]YYYY", date$) prints a day-month-year date with literal slashes.

What does fstat 6 mean in Sage X3?

fstat 6, constant [V]CST_ARECTICKUPD, is an update conflict: the line no longer has the updtick value it had when you read it, so another process changed it. Roll back, reread the record and retry or report the conflict to the user instead of overwriting their change.

How does error handling work in Sage X3 4GL?

Database instructions return a status in fstat, which you test after each call. Runtime errors are trapped with Onerrgo LABEL; inside the label, errn gives the error number, errmes$(errn) the message and errl the line. Use Resume to continue after the failing instruction, or exit cleanly with a Rollback.

If you want a private assistant that answers questions about your Sage X3 data and specific code from your own servers, we can set that up.

Talk to an Expert Explore More Free Tools