Table Prefix

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.

Return to Tutorials

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.
  • 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
  • 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@

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
  • Posted
    • Data Type: CHAR(1) - Values: Y/N
      • If Y then is displayed as Posting Button.
  • Processing
    • Data Type: CHAR(1) - Values: Y/N
      • Lock for Batch processing.
      • Second use for starting any Process.
  • Record_ID
    • Data Type: NUMBER(10)
      • With AD_Table_ID, system reference (Zoom)

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
  • Name
    • Data Type: NVarchar2(60)
      • Display name
  • Description
    • Data Type: NVarchar2(255)
      • Long description
  • Help
    • Data Type: NVarchar2(2000)
      • Help or comments

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.