Data dictionary: tables, data types, local menus, parameters

The data dictionary describes what a folder’s database holds and how values are entered: tables and indexes (GESATB), data types (GESATY), local menus (TXT), miscellaneous tables (GESADV / GESADI), SQL views (GESAVW) and general parameters (GESADP). Read this before changing one of them or reading a miscellaneous table or a parameter in code. Naming rules and number ranges: conventions-and-naming.md; labels: localization.md.

Contents

Tables (GESATB)

A table has a header (code CODFIC, abbreviation ABRFIC, description) and the General, Columns, Index and Audit tabs. Validating the definition creates or updates the table in the database.

Field (General tab) What it controls
Activity code CODACT, Module Table created only if the code is empty or active and the module is active. Tables with an X/Y/Z code are specific and “not affected by version changes”
Table type TYPFIC Application (default, functional tables), Supervisor (in every folder: users, logs…), Sage X3 system (supervisor folder only, never copied), Dictionary (merged during updates), Internal (Sage only, never for external development)
Copy type CRE, Copy option OPT What folder creation copies from the reference folder: nothing, everything, or groups picked from local menu 26
Open access SECURE Selected: every folder of the solution reads and writes. Cleared: access follows the calling folder’s rights (GESADS, Links tab). Not updated by patches
Columns grid Meaning
CODZONE Field name, read as [F:ABV]FIELD; a dimensioned field gives the database columns FIELD_0, FIELD_1…
CODTYP Data type: A, C (short integer), L (long integer), DCB (decimal), D, M / MM (local menu), ACB (CLOB), ABB (BLOB), or any GESATY type
NOLIB, LONG, DIME, CODACT Local menu number; length for A and DCB; dimension; activity code (optional element, X/Y/Z = specific, grid sizing)
LIEN, EXPLIEN, ANNUL Linked table and link expression, comp1;comp2 for a multi-part key (a field controlled by miscellaneous table 43: linked table ATABDIV, expression 43;MYFIELD; globals need [V]); on deletion of the linked record: Block, Delete, RTZ or Other

Adding a field to a standard table

  1. Name it X_..., Y_... or Z_... (GESATB rule) and give the line an X/Y/Z activity code: the patch tools treat an element without one as standard (conventions-and-naming.md).
  2. Never add fields or specific characteristics to system or dictionary tables: GESATB states they are not preserved during folder validation or migration.
  3. Add it at the end of the grid. When a patch integrates a table, a field added at the end keeps the database statistics; one inserted between two others makes the table be deleted and re-created, losing the statistics of the whole table (PATCH).
  4. Validate the table, filling existing rows with the Processing button if needed. Code reads the field like any column ([F:BPC]Y_SEGMENT). The GESATB page does not say that validation generates or updates a screen, class or representation: those are separate elements (screens-and-masks.md, v12-classes.md).

Indexes

Field Rule
Index code CODIND Abbreviation + 0 for the primary key, 1 for the next…; specific indexes start with X, Y or Z
Descriptor DESCRIPT, Duplicates HOMONYM Fields joined by + (LEGCPY+FCY on FACILITY); - before a field sorts it descending (never the first one); HOMONYM = duplicate values allowed
Specific clustered index ORDIND Only one per table; never modified by patches (the Sage default DEFORDIND can be)
Activity CODACT Empty = always present; inactive code = index not generated; X/Y/Z = specific key on a standard table

The Configuration file block (FICCFG) is saved as <table>.cfg in FIL and used by valfil; with Open access selected, validation adds a $SECURITY section. Index names are the key names of Read (database.md).

Validating tables and the dictionary

Validation (GESATB): a missing table is created empty; an existing one gets its structure updated with its records preserved (fields added, removed or recopied). If nothing changed, only the indexes are rebuilt. Forced validation revalidates data and indexes completely.

VALDICO (Validation; V11 menu: Development > Utilities > Dictionary > Validations > Dictionary) is a selective folder validation: element types (tables, screens, objects, windows, queries) and ranges, filtered by data type, activity code, action code or module, optionally in one language. Its forced validation regenerates tables by temporary copy; Test mode only lists elements in the log; batch task VALDICO.

In a patch these elements travel as ATB (definition, no data), ATY, AML (local menu), AVW, ADI (miscellaneous table contents) and ADP (parameter and general-level value): personalisation-activity.md.

Data types (GESATY)

A data type gives every field using it an internal type, length, format, options, linked object, default value and actions; characteristics left empty are entered on each field.

Element Content
Internal type TYPTYP, Length LNGTYP Alphanumeric, Local menu, Short integer (-32768..32767), Long integer, Decimal (N.M, up to 32 significant digits), Date (1600-9999, null date [0/0/0]), Blob, Clob; length 0 = entered on each field
Adonix format FORTYP Right part of a format (10X); = for a variable format (=[F:DEV]FM92; MD1 uses =GDEVFMT)
Linked object OBJLIEN (Convergence tab) Brings the existence control, selection windows and tunnel
Default value VALDEF Formula (type CRY proposes GPAYS); a screen field’s own default wins
Class tab Content type, class CODCLA, default representations (DEFREPDES, DEFREPMOB, DEFREPTAB), Rules grid (type, script, sub-program, sequence, activity code)

AX1 / AX2 / AX3 (translated text, length 12 / 20 / 30) are not created in the database: the text is in ATEXTRA. ADI holds a code of the miscellaneous table whose number is in the link expression.

Actions grid (Convergence tab, actions dictionary codes, run for every field of the type). Types ACTTYP: Before-field, Init_button, Init, Before_entry, Control, After-field, After-modif, Selection (F12), Button 1 (F9, tunnels), Button 2 to 20, Before_line, After_line, Click. Execution EXEACT: Interactive, Import / Web service (once all fields are loaded), Always. When the type and the screen field define the same action type, the type’s runs first, then the field’s; for Selection and Button only the field’s runs.

Validation compiles WWGLOBLON: one shortint global GLON<type> per alphanumeric data type, holding its length. Declare work variables with it so that a length change follows:

Local Char YITEM(GLONITM)            : # length of data type ITM
  [L]YITEM = [F:ITM]ITMREF

In Sage X3 SaaS, and on-premises from 2025 R2, the Length, Adonix format and Options of a Sage-supplied type are changed in GESATYC (Data type personalization), then applied with VALDICO; other properties in GESATY with an X/Y/Z activity code. Folder validation does not update types with an X/Y/Z activity code.

Local menus (TXT)

There is no GESAML function: local menus and message chapters are maintained in TXT (table APLSTD).

Field Meaning
Chapter LANCHP, TITCHP, Local menu MENLOCAL Number (ranges: conventions-and-naming.md), title; checked = local menu, cleared = message chapter
Activity code CODACT, Module X/Y/Z protects a specific chapter
Changeable AUZMOD Users may edit it in setup; it is then not updated by folder revalidation, version installation or patches (TXT_TRA)
NONTRA, LONG, MINI, MAXI Do not translate (new lines not created in other languages); label width; minimum and maximum number of choices
Grid LANNUM, CODE, LANMES Rank, one-character code, label

Miscellaneous tables (GESADV, GESADI)

Table ATABDIV [ADI], key CODE = NUMTAB+CODE (no duplicates): A1-A15 (alphanumeric, 40), N1-N15 (decimal 11.6), DEPCOD, ENAFLG (local menu 1), LNGDES / SHODES (types AX3 / AX1, text in ATEXTRA). The GESADI page still speaks of “1 to 4” additional values: check in GESADV which column holds which value.

# YADI_A1 - first alphanumeric column of code YCOD in specific miscellaneous table 6001
Funprog YADI_A1(YCOD)
Value Char YCOD()
Local File ATABDIV [ADI]
  Read [ADI]CODE = 6001; [L]YCOD
  If fstat : End "" : Endif                : # code not found
  If [F:ADI]ENAFLG <> 2 : End "" : Endif   : # inactive (local menu 1: 2 = Yes)
End [F:ADI]A1

The description sits in ATEXTRA under ATABDIV / LNGDES / language / table number / code. The help gives AFNC.TEXTRA(table_code, field_code, key_value_1, key_value_2) in calculator syntax only, without the number format: test func AFNC.TEXTRA("ATABDIV", "LNGDES", num$(6001), [L]YCOD) on your folder first.

SQL views (GESAVW)

# YVSALES: specific view, abbreviation YVS, sort key YVS0 on BPCNUM
Local File YVSALES [YVS]
Local Decimal YTOTAL
  For [YVS]YVS0 Where BPCNUM = "C0001"
    [L]YTOTAL += [F:YVS]Y_AMOUNT
  Next

General parameters (GESADP)

Field Meaning
Chapter CHAPITRE, Group GRPPAR Miscellaneous tables 901 and 903; a specific chapter is an X/Y/Z code added to table 901
Parameter PARAM 10 alphanumeric characters; specific ones start with X, Y or Z and carry an X/Y/Z activity code to survive version changes and revalidation
Definition level NIVDEF (local menu 987) Folder, Legislation, Company, Site or User: the finest level that can hold a value
Value type TYPVAL, NOLIB, Object OBJET Internal type, local menu number; an object brings its controls and selection
Control processing TRAIT Script with Subprog VERF_PARAM(PARAM,VALEUR,OK); OK = 0 plus GMESSAGE refuses the value
Global variable CODVAR [V] variable loaded at connection: G* standard, X/Y/Z specific
Changeable MODIF Cleared: only a program changes the value (TYPDBA)

Reading a parameter in L4G

V7+ code reads the APARAM cache of the context: this.ACTX in class code, GACTX outside a class.

Need Call
Folder, legislation, company or site value fmet GACTX.APARAM.AGETVALCHAR(LEVCOD, TYPVAL, PARAM); AGETVALNUM (integer or local menu); AGETVALDATE
LEVCOD / TYPVAL [V]CST_ALEVFOLD, [V]CST_ALEVLEG, [V]CST_ALEVCPY, [V]CST_ALEVFCY / the code of that level
Parameter defined at User level AGETUSERVALCHAR(PARAM), AGETUSERVALNUM, AGETUSERVALDATE
Classic code Call PARAM(SITE,PARAM,VALEUR) From ADOVAL; Call PARAMUTIL(PARAM,VALEUR,USER,"") From SUBAUS
Formula (automatic journals…) func AFNC.PARAM(PARAM, SITE): alphanumeric value, at most 30 characters 
# YCTL_ACTIVE - is the specific control enabled for site FCY?
# YCTLACT: specific parameter (GESADP), level Site, local menu 1 (2 = Yes), activity code YCTL
Funprog YCTL_ACTIVE(FCY)
Value Char FCY()
Local Integer YVAL
  [L]YVAL = fmet GACTX.APARAM.AGETVALNUM([V]CST_ALEVFCY, [L]FCY, "YCTLACT")
  If [L]YVAL = 2 : End [V]CST_ATRUE : Endif
End [V]CST_AFALSE

# Classic form of the same read: the value comes back as a string
Local Char    YVALUE(250)
Local Integer YVAL
  Call PARAM([L]FCY, "YCTLACT", [L]YVALUE) From ADOVAL
  [L]YVAL = val([L]YVALUE)

Gotchas

See also: conventions-and-naming.md, localization.md, function-codes.md, database.md, personalisation-activity.md, audit-compliance.md, v12-classes.md, screens-and-masks.md, accounting-automatic-journals.md.

Sources