Application Dictionary Deep Dive

Overview

• What you’ll learn: Advanced Application Dictionary techniques including building custom windows from scratch, mastering reference types, configuring validation rules, and leveraging virtual columns.
• Prerequisites: Lessons 1-12 (Beginner level)
• Estimated reading time: 25 minutes

Introduction

The Application Dictionary (AD) is the beating heart of iDempiere. Every window, tab, field, and process you see in the UI is defined as metadata in the AD. While the beginner curriculum introduced you to the AD concept, this lesson takes you deep into the mechanics of building and customizing the dictionary. By the end of this lesson, you will be able to create a fully functional window from scratch, understand every reference type available, and configure sophisticated validation logic — all without writing a single line of Java code.

Creating a Window from Scratch

Building a new window in iDempiere follows a precise sequence. Each step builds on the previous one, and skipping steps leads to errors. Here is the complete workflow:

Step 1: Register the Table (AD_Table)

Navigate to Application Dictionary > Table and Column. Create a new record with these essential fields:

• DB Table Name: Your physical database table name (e.g., Z_MyCustomTable). By convention, custom tables use the Z_ prefix or your organization prefix.
• Name: A human-readable name (e.g., “My Custom Table”).
• Table Type: Usually left as the default. Options are Table, View, and Single Row.
• Data Access Level: Controls which roles can access records. Common values include Client+Organization, System+Client, and All.
• EntityType: Set to “User Maintained” for custom work. This is critical — it prevents your customizations from being overwritten during migrations.
• Maintain Change Log: Enable this to track who changed what and when.

After saving, use the Create Columns from DB process (gear icon) if the physical table already exists. If you are starting fresh, you will define columns manually.

Step 2: Define Columns (AD_Column)

Switch to the Column tab. Every column in your table needs an AD_Column record. Essential columns that every table should have:

• AD_Client_ID — Multi-tenant client identifier
• AD_Org_ID — Organization identifier
• Created, CreatedBy, Updated, UpdatedBy — Audit trail fields
• IsActive — Soft-delete flag
• The primary key column (e.g., Z_MyCustomTable_ID)
• Z_MyCustomTable_UU — UUID column for replication and migration

For each column, you must configure:

• DB Column Name: The physical column name in the database.
• Name: The label shown in the UI.
• Reference: The data type/display type (String, Integer, Table Direct, List, etc.).
• Mandatory: Whether the field requires a value.
• Default Logic: An expression that pre-fills the field (covered in detail below).
• Key Column: Check this for the primary key column.

After defining your columns, run the Synchronize Column process to create or alter the physical database columns.

Step 3: Create the Window (AD_Window)

Navigate to Application Dictionary > Window, Tab & Field. Create a new window record:

• Name: The window title (e.g., “My Custom Window”).
• Window Type: “Maintain” for standard CRUD windows, “Transaction” for document-style windows (with header/line structure), or “Query Only” for read-only views.
• EntityType: “User Maintained” for custom work.

Step 4: Add Tabs (AD_Tab)

Switch to the Tab sub-tab. Every window needs at least one tab:

• Name: The tab label.
• Table: Link to the AD_Table you created in Step 1.
• Tab Level: 0 for the main (header) tab. Child tabs use 1, 2, etc.
• Sequence: Controls the display order of tabs.
• Link Column: For child tabs, this is the foreign key that links to the parent tab.
• WHERE Clause: Optional SQL filter applied to the tab.
• Order By Clause: Default sort order.
• Read Only: Makes the entire tab non-editable.

Step 5: Configure Fields (AD_Field)

After saving the tab, use the Create Fields process to auto-generate AD_Field records from your columns. Then customize each field:

• Sequence: Controls field display order.
• Displayed: Whether the field is visible.
• Display Logic: Conditional visibility expression.
• Read Only Logic: Conditional editability expression.
• Field Group: Groups fields under collapsible sections.
• Same Line / Span Column: Layout controls.

Step 6: Add to Menu

Finally, add a menu entry via Application Dictionary > Menu to make the window accessible. Set the Action to “Window” and select your new window.

Reference Types Deep Dive

References determine how a column is stored, displayed, and edited. Choosing the right reference type is one of the most important decisions in AD design.

Table Direct

The simplest foreign key reference. The column name must exactly match the target table’s key column (e.g., a column named C_BPartner_ID automatically references the C_BPartner table). The UI renders a dropdown populated with all active records from that table.

-- Column name: C_BPartner_ID
-- Automatically links to C_BPartner table
-- No additional configuration needed

Table

A more flexible foreign key reference. Unlike Table Direct, the column name does not need to match the target table. You configure a Reference Key (AD_Ref_Table) that specifies:

• Table: The source table.
• Key Column: Which column provides the stored value.
• Display Column: Which column is shown to the user.
• WHERE Clause: Optional filter on available records.
• ORDER BY Clause: Sort order of the dropdown.

This is the reference type you use when you need filtered dropdowns, custom display values, or when your column name does not follow the naming convention.

List

Stores a short string value selected from a predefined set. Configure a Reference List (AD_Ref_List) with value-name pairs. Ideal for status fields, type fields, and any enumeration with a small, fixed set of values.

-- Example: DocStatus reference list
-- Value: DR  Name: Drafted
-- Value: CO  Name: Completed
-- Value: VO  Name: Voided
-- Value: CL  Name: Closed

Search

Similar to Table, but renders as a search dialog instead of a dropdown. Used for tables with many records (e.g., Products, Business Partners) where a dropdown would be impractical. The user can type to filter or click the search icon to open a lookup dialog with multiple search criteria.

Button

Renders as a clickable button. Can trigger a process (AD_Process) or open a specialized dialog. The column typically stores a status code that the button action updates. Document processing buttons (DocAction) are the most common example.

Location

A specialized reference for address fields. Links to the C_Location table and renders the location editor dialog (Address 1, Address 2, City, State/Region, ZIP, Country). The dialog auto-adapts based on the selected country’s address format.

Account

Opens the GL Account combination editor. Used for fields that store accounting combinations (Account + Organization + Business Partner + Product + Activity + Campaign + etc.). Links to the C_ValidCombination table.

PAttribute (Product Attribute)

Opens the Product Attribute Set Instance editor. Used for fields that need to capture lot numbers, serial numbers, guarantee dates, and other attribute set instance values. Links to M_AttributeSetInstance.

Validation Rules

Validation Rules (AD_Val_Rule) restrict the records shown in a dropdown or search dialog. They are SQL WHERE clauses that dynamically filter available options.

Creating a Validation Rule

Navigate to Application Dictionary > Validation Rules and create a new record:

-- Name: Active Vendors Only
-- Type: SQL
-- Validation Code:
C_BPartner.IsVendor='Y' AND C_BPartner.IsActive='Y'

You then assign this validation rule to a column (AD_Column.AD_Val_Rule_ID) or a column’s reference table configuration. When the user opens the dropdown, iDempiere appends this WHERE clause to the lookup SQL.

Context Variables in Validation Rules

Validation rules become powerful when you use context variables to create dynamic filters:

-- Only show products from the selected warehouse's organization
AD_Org_ID = @AD_Org_ID@

-- Only show BPartner locations for the selected Business Partner
C_BPartner_ID = @C_BPartner_ID@

-- SQL-based context variable
@SQL=SELECT SalesRep_ID FROM C_BPartner WHERE C_BPartner_ID=@C_BPartner_ID@

Context variables use the @VariableName@ syntax. iDempiere resolves them at runtime from the current window context (field values, login defaults, preferences).

Display Logic and Read Only Logic

Display Logic controls whether a field is visible. Read Only Logic controls whether a field is editable. Both use the same expression syntax.

Column-Based Expressions

-- Show field only when IsSOTrx is Yes
@IsSOTrx@='Y'

-- Show field only when DocStatus is Drafted
@DocStatus@=DR

-- Combine conditions
@IsSOTrx@='Y' & @DocStatus@=DR

-- OR logic
@IsSOTrx@='Y' | @PaymentRule@=S

-- Not equal
@DocStatus@!CO

SQL-Based Display Logic

-- Show field only if user has a specific role
@SQL=SELECT COUNT(*) FROM AD_User_Roles
WHERE AD_User_ID=@#AD_User_ID@ AND AD_Role_ID=1000000 AND IsActive='Y'

The @SQL= prefix tells iDempiere to execute the SQL and use the result. If the result is greater than 0 (or not empty), the condition is true.

Default Values

Default values pre-populate fields when creating new records. iDempiere supports several expression types:

System Context Variables

@#Date@          -- Current system date
@#AD_Client_ID@  -- Current client ID
@#AD_Org_ID@     -- Current organization ID
@#AD_User_ID@    -- Current user ID
@#AD_Role_ID@    -- Current role ID
@#AD_Language@   -- Current language

Window Context Variables

@C_BPartner_ID@  -- Value from C_BPartner_ID field in current window
@IsSOTrx@        -- Value from IsSOTrx field (Sales Transaction flag)
@M_Warehouse_ID@ -- Value from parent tab's warehouse field

SQL-Based Defaults

-- Default to the first warehouse for this org
@SQL=SELECT M_Warehouse_ID FROM M_Warehouse
WHERE AD_Org_ID=@AD_Org_ID@ AND IsActive='Y'
ORDER BY IsDefault DESC, M_Warehouse_ID LIMIT 1

Literal Values

Y        -- Boolean Yes
N        -- Boolean No
0        -- Zero
DR       -- String literal (e.g., for DocStatus)

EntityType and Customization Tracking

The EntityType field exists on most AD tables (Table, Column, Window, Tab, Field, Process, etc.). It serves as a namespace mechanism that identifies who owns a particular piece of metadata:

• D (Dictionary): Core iDempiere elements. Never modify these directly.
• C (Compiere): Legacy Compiere elements. Also should not be modified.
• U (User Maintained): Your custom elements. Safe to modify and migrate.
• Custom EntityTypes: You can register your own (e.g., “MYORG”) in the AD_EntityType table.

EntityType is critical for migrations and pack-in/pack-out. The 2Pack migration tool uses EntityType to determine which elements belong to your customization and should be exported. Always set your custom elements to an appropriate EntityType.

Virtual Columns (Column SQL)

Virtual columns are computed columns that do not exist in the physical database table. They are defined entirely in the AD using the Column SQL field on AD_Column. iDempiere injects the Column SQL as a subquery into the SELECT statement.

Example: Total Line Amount

-- Column SQL for a virtual column "TotalLines" on C_Order
(SELECT COALESCE(SUM(LineNetAmt), 0)
 FROM C_OrderLine ol
 WHERE ol.C_Order_ID = C_Order.C_Order_ID)

Configuration requirements for virtual columns:

• The column must exist in AD_Column but not in the physical table.
• Set Column SQL to your subquery expression (enclosed in parentheses).
• The column is automatically read-only — you cannot edit computed values.
• Virtual columns can be used in grid views, printed in reports, and referenced in other logic.

Example: Days Since Creation

-- Column SQL for a virtual "DaysSinceCreated" column
(EXTRACT(DAY FROM (now() - Created)))

Field Groups

Field Groups (AD_FieldGroup) organize fields into collapsible sections within a tab. This improves usability for windows with many fields. Configure a Field Group record with a Name and Group Type (Label or Collapsible), then assign it to individual fields via the AD_Field.AD_FieldGroup_ID column. Fields with the same Field Group are visually grouped together.

Key Takeaways

• Building a window requires a precise sequence: Table, Columns, Window, Tab, Fields, Menu.
• Reference types (Table Direct, Table, List, Search, Button, Location, Account, PAttribute) control how fields store and display data.
• Validation Rules add dynamic SQL WHERE clauses to filter dropdown options based on context.
• Display Logic and Read Only Logic use @Variable@ expressions to control field visibility and editability.
• Default values can reference system context, window context, SQL queries, or literal values.
• EntityType tracks ownership of AD elements and is essential for proper migration packaging.
• Virtual columns (Column SQL) let you add computed fields without altering the database schema.

What’s Next

In Lesson 14, you will learn about Callouts — Java code that executes when a user changes a field value. Callouts bring dynamic, real-time behavior to the UI and are the bridge between the declarative AD approach you learned here and the programmatic customization that powers advanced business logic.

-- 欄位名稱：C_BPartner_ID
-- 自動連結至 C_BPartner 資料表
-- 不需要額外配置

-- 範例：DocStatus 參照清單
-- Value: DR  Name: Drafted（草稿）
-- Value: CO  Name: Completed（已完成）
-- Value: VO  Name: Voided（已作廢）
-- Value: CL  Name: Closed（已關閉）

-- 名稱：Active Vendors Only
-- 類型：SQL
-- 驗證碼：
C_BPartner.IsVendor='Y' AND C_BPartner.IsActive='Y'

-- 只顯示所選倉庫組織的產品
AD_Org_ID = @AD_Org_ID@

-- 只顯示所選業務夥伴的地址
C_BPartner_ID = @C_BPartner_ID@

-- 基於 SQL 的上下文變數
@SQL=SELECT SalesRep_ID FROM C_BPartner WHERE C_BPartner_ID=@C_BPartner_ID@

-- 僅當 IsSOTrx 為 Yes 時顯示欄位
@IsSOTrx@='Y'

-- 僅當 DocStatus 為 Drafted 時顯示欄位
@DocStatus@=DR

-- 組合條件
@IsSOTrx@='Y' & @DocStatus@=DR

-- OR 邏輯
@IsSOTrx@='Y' | @PaymentRule@=S

-- 不等於
@DocStatus@!CO

-- 僅當使用者具有特定角色時顯示欄位
@SQL=SELECT COUNT(*) FROM AD_User_Roles
WHERE AD_User_ID=@#AD_User_ID@ AND AD_Role_ID=1000000 AND IsActive='Y'

@#Date@          -- 當前系統日期
@#AD_Client_ID@  -- 當前用戶端 ID
@#AD_Org_ID@     -- 當前組織 ID
@#AD_User_ID@    -- 當前使用者 ID
@#AD_Role_ID@    -- 當前角色 ID
@#AD_Language@   -- 當前語言

@C_BPartner_ID@  -- 當前視窗中 C_BPartner_ID 欄位的值
@IsSOTrx@        -- IsSOTrx 欄位的值（銷售交易旗標）
@M_Warehouse_ID@ -- 父頁籤倉庫欄位的值

-- 預設為此組織的第一個倉庫
@SQL=SELECT M_Warehouse_ID FROM M_Warehouse
WHERE AD_Org_ID=@AD_Org_ID@ AND IsActive='Y'
ORDER BY IsDefault DESC, M_Warehouse_ID LIMIT 1

Y        -- 布林值 Yes
N        -- 布林值 No
0        -- 零
DR       -- 字串字面值（例如用於 DocStatus）

-- C_Order 上虛擬欄位 "TotalLines" 的 Column SQL
(SELECT COALESCE(SUM(LineNetAmt), 0)
 FROM C_OrderLine ol
 WHERE ol.C_Order_ID = C_Order.C_Order_ID)

-- 虛擬欄位 "DaysSinceCreated" 的 Column SQL
(EXTRACT(DAY FROM (now() - Created)))

-- カラム名：C_BPartner_ID
-- 自動的にC_BPartnerテーブルにリンク
-- 追加設定不要

-- 例：DocStatusリファレンスリスト
-- Value: DR  Name: Drafted（下書き）
-- Value: CO  Name: Completed（完了）
-- Value: VO  Name: Voided（無効）
-- Value: CL  Name: Closed（クローズ）

-- 名前：Active Vendors Only
-- タイプ：SQL
-- バリデーションコード：
C_BPartner.IsVendor='Y' AND C_BPartner.IsActive='Y'

-- 選択した倉庫の組織の製品のみ表示
AD_Org_ID = @AD_Org_ID@

-- 選択した取引先の住所のみ表示
C_BPartner_ID = @C_BPartner_ID@

-- SQLベースのコンテキスト変数
@SQL=SELECT SalesRep_ID FROM C_BPartner WHERE C_BPartner_ID=@C_BPartner_ID@

-- IsSOTrxがYesの場合のみフィールドを表示
@IsSOTrx@='Y'

-- DocStatusがDraftedの場合のみフィールドを表示
@DocStatus@=DR

-- 条件の組み合わせ
@IsSOTrx@='Y' & @DocStatus@=DR

-- ORロジック
@IsSOTrx@='Y' | @PaymentRule@=S

-- 等しくない
@DocStatus@!CO

-- ユーザーが特定のロールを持つ場合のみフィールドを表示
@SQL=SELECT COUNT(*) FROM AD_User_Roles
WHERE AD_User_ID=@#AD_User_ID@ AND AD_Role_ID=1000000 AND IsActive='Y'

@#Date@          -- 現在のシステム日付
@#AD_Client_ID@  -- 現在のクライアントID
@#AD_Org_ID@     -- 現在の組織ID
@#AD_User_ID@    -- 現在のユーザーID
@#AD_Role_ID@    -- 現在のロールID
@#AD_Language@   -- 現在の言語

@C_BPartner_ID@  -- 現在のウィンドウのC_BPartner_IDフィールドの値
@IsSOTrx@        -- IsSOTrxフィールドの値（販売取引フラグ）
@M_Warehouse_ID@ -- 親タブの倉庫フィールドの値

-- この組織の最初の倉庫をデフォルトに
@SQL=SELECT M_Warehouse_ID FROM M_Warehouse
WHERE AD_Org_ID=@AD_Org_ID@ AND IsActive='Y'
ORDER BY IsDefault DESC, M_Warehouse_ID LIMIT 1

Y        -- ブーリアン Yes
N        -- ブーリアン No
0        -- ゼロ
DR       -- 文字列リテラル（例：DocStatus用）

-- C_Orderの仮想カラム "TotalLines" のColumn SQL
(SELECT COALESCE(SUM(LineNetAmt), 0)
 FROM C_OrderLine ol
 WHERE ol.C_Order_ID = C_Order.C_Order_ID)

-- 仮想カラム "DaysSinceCreated" のColumn SQL
(EXTRACT(DAY FROM (now() - Created)))