Table Prefix
Contents
Table Prefix
When you open the Adempiere's database you can see many tables prefixes. What is each one?
AD_ : Application Dictionary (i.e.: AD_Element) A_ : Assets Management (i.e.: A_Asset_Group) ASP_ : Application Service Provider (i.e.: ASP_Module) B_ : Marketplace (i.e.: B_Buyer) C_ : Common or Core Functionality (i.e.: C_AcctSchema) CM_ : Collaboration Management (i.e.: CM_WebProject) FACT_: Multi-Dimensional Cube (i.e.: Fact_Acct) GL_ : General Ledger (i.e.: GL_Journal) HR_ : Human Resource (i.e.: HR_Payroll) (Integration to Libero) I_ : Import (i.e.: I_BPartner) K_ : Knowledge Management (i.e.: K_Category) M_ : Material Management (i.e.: M_Cost) PA_ : Performance Analysis (i.e.: PA_Report) PP_ : Production Planning (i.e.: PP_Order) (Integration to Libero) R_ : Requests (i.e.: R_Request) RV_ : Report View (i.e.: RV_BPartner) S_ : Service (i.e.: S_Resource) T_ : Temporary Tables (i.e.: T_Report) W_ : Web (i.e.: W_Basket) WS_ : Web Service (i.e.: WS_WebService)
Prefixes used or reserved by add-on projects
These are not final, since I am still a newbie. But something like this is needed.
ABI_ : Automated Broker Interface (US Import and Export) SDGathman 18:00, 20 April 2007 (EDT) AWB_ : Air WayBill (US Import and Export - International Air Transportation) SDGathman LCO_ : Localization Colombia - Carlos Ruiz LBR_ : Localization Brazil - Fer_luck LMX_ : Localization Mexico - Victor Perez LVE_ : Localization Venezuela - Yamel Senih / Marcos Medina LRU_ : Localization Russia - Ryabikov Aleksandr
Special Column Suffix & Prefix
- _ID
- Data Type: Number(10)
- Primary key or foreign key column. Where possible, should use the pattern TableName_ID. For e.g, AD_User_ID is the primary key column for the AD_User table.
- Is
- Data Type: Char(1) Value: Y/N
- Use for yes/no flag, for e.g - IsApproved, IsActive, IsVendor, etc.
Some Tips about Custom Tables
- Naming:
- Use Prefix - EXT_, CUST_ or System Custom Prefix
- It's recommended to use custom prefixes with three or more letters. The model classes must be looked with prefix. I.e. if you have a table called XX_Invoice the model class must be looked as MInvoice and can conflict with the product model class, but if you have a table called CUS_Invoice, then the model class looked will be MCUSInvoice.
- Try to be descriptive.
- Use case conventions consistently.
- Use Prefix - EXT_, CUST_ or System Custom Prefix
- Required Columns:
- AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy
- Suggested Columns:
- Value (the Key), Name, Description, Help
- Very Important: The Column Names are case sensitive.
About References (Data Types)
- TableDir
- Column Name without _ID is the Table Name
- If Column: C_BPartner_ID ....Then Table is: C_BPartner
- Don't forget: Case sensitive
- Column Name without _ID is the Table Name
- Search
- Same as TableDir: displayed in Search and Info Window rather then ComboBox
- Table
- Define Display Column and Key Column
- Column CreatedBy = Table: C_BPartner - Display: Name - Key: C_BPartner_ID
- Dynamic Validation Rules
- The content is revaluated when change the Variable.
- AD_Column.AD_Table_ID= @AD_Table_ID@
- The content is revaluated when change the Variable.
Some Special Columns
- Processed
- Data Type: CHAR(1) - Values: Y/N
- Used for all Transaction Tables.
- If Y then your record is Read-Only.
- Fields marked as Always Updateable aren't read only
- When the table has this column the History button is enabled on the toolbar
- Data Type: CHAR(1) - Values: Y/N
- Posted
- Data Type: CHAR(1) - Values: Y/N
- If Y then is displayed as Posting Button.
- Data Type: CHAR(1) - Values: Y/N
- Processing
- Data Type: CHAR(1) - Values: Y/N
- Lock for Batch processing.
- Second use for starting any Process.
- Data Type: CHAR(1) - Values: Y/N
- Record_ID
- Data Type: NUMBER(10)
- With AD_Table_ID, system reference (Zoom)
- Data Type: NUMBER(10)
Standard Columns
- AD_Client_ID
- Data Type: Number(10) NOT NULL
- AD_Org_ID
- Data Type: Number(10) NOT NULL
- IsActive
- Data Type: Char(1) - Values: Y/N
- Created
- Data Type: Date NOT NULL
- CreatedBy
- Data Type: Number(10) NOT NULL
- Updated
- Data Type: Date NOT NULL
- UpdatedBy
- Data Type: Number(10) NOT NULL
Common column use in master
- Value
- Data Type: NVarchar2(40)
- Search Key or Code
- Data Type: NVarchar2(40)
- Name
- Data Type: NVarchar2(60)
- Display name
- Data Type: NVarchar2(60)
- Description
- Data Type: NVarchar2(255)
- Long description
- Data Type: NVarchar2(255)
- Help
- Data Type: NVarchar2(2000)
- Help or comments
- Data Type: NVarchar2(2000)
Virtual Columns
You can define virtual columns (not stored in the database). If defined, the Column name is the synonym of the SQL expression defined here. The SQL expression must be valid. Example: "Updated-Created" would list the age of the entry in days.
To create a Virtual Column you must go to Column Tab into Table and Column window and enter your Expression in the Column SQL Field.
- In a Table, define calculated Columns (not stored).
- Updated-Created - Age in days
- More complex, as
- (SELECT Value FROM M_Product p WHERE p.M_Product_ID=M_InventoryLine.M_Product_ID)
- Complex Expressions in ()
- Fully qualify base table
- In Column, define valid SQL Expression
- Column Name: AgeInDays
- Column SQL: SysDate-Created
- Creates SQL: SysDate-Created AS AgeInDays
Be careful: The virtual columns are not recalculated if you modify something on what is based the virtual column.
You can find some examples of Virtual Columns in the windows:
- Tax Declaration: The Accounted Credit, Accounted Debit, Source Credit, Source Debit, Business Partner, Currency, Tax and Account Date are all Virtual Columns.
- Product Costs: The Costing Method field used is a Virtual Column.
Note: in order to see the Columns definition, you must be logged as System Administrator Role.
- Link related: Virtual Columns Guide