Column

Owned by Gaurav
Last updated: Dec 15, 2023
15 min read

This tab contains column information of a table. Other than default column you can add more columns according to the requirement. All the columns, except default 9 columns, should start with MPC (Module Prefix Code) if you want to create a separate module which can deploy from Vienna Market. 

Primary Key Column of table is always TableName_ID. 

 

Most of the fields in are self-explanatory; important fields are described below: 

 

  • DBColumnName 

The name of the column in Database 

 

  • Column SQL 

Virtual Column (r/o) 

You can define virtual columns (not stored in the database). User has to define SQL expression here. The SQL expression must be valid. 

Example: "Updated-Created" would list the age of the entry in days 

 

  • System Element 

To create columns you need to define System Element for the column in System Element window. System Element should be unique.  

 

Click on Zoom Button of the System Element Field, it’ll take you to System Element Window. 

In the “System Element” Window, click on New Button in the Toolbar, enter the unique DB Column Name in the DB Column Name field with Prefix of the Module + “_” and without spaces and you can give name in the Name field and Print Text field whatever you want it to be without prefix. Then you can add description and comment for the System Element. Set Active checkbox to true (Checked). 

 

Save the record. Now go back to Table and Column Window and under System Element filed, find the newly created system element or existing and select for use here. 

  • Name 

The name of an entity (record) is used as default search option in addition to the DB Column Name. The name is up to 60 characters in length. 

 

  • Comment 

This Field contains comment or help about an item.  This help text will appear on help window which open from Help icon in right panel on window.

 

  • Reference 

Reference signifies that what type of column you are going to create or what type of value it contains in the Database.  Basically it represents display type of column on the window/tab and data type of the column in database. There are number of References are available here. List of References is shown in the image below. 

 

In the below we represent all the reference with description in a tabular format 

Sr. No.    

Reference Name  

Description  

  

1 

Account  

It specifies the column as an accounting element and display  

account control. 

2 

Amount 

It will show text box control which accepts number and up to 

2 decimal place like (0.00). 

If you insert more digits after decimal place it will round the 

value up to 2 decimal places. 

3 

Binary 

This reference is used to save byte Array, it will create BLOB  

Data type column in the database.  

It will show as a button on the widow/tab. On click button it 

will show popup with option  

  1. Save on local -> Safe the file on local 

  2. Open/Load into Database-> Open File Dialog window,  

choose the file and save in the database 

4 

Button  

It will show Button control on the window/tab. Use to perform  

action like run process. 

5 

Cost+Price  

It will show textbox control.  It accepts numeric value with  

a minimum precision of 2 decimal places and a maximum of 12.  

This reference is typically used for monetary values. 

6 

Date 

Show control on window/tab which accept date as input.  

Save in database as it is as input by user. 

7 

Date + Time 

Show control on window/tab which accept Date + Time as input.  

Save UTC Format in database. 

8 

FileName  

Show open File dialog control on window/tab. It will show selected  

File Name. If you want to save file or upload file in the database then 

Use Binary Reference. 

9 

File Path 

Show open File dialog control on window/tab. It will show selected  

File Path. Not implemented in HTML5 only show file name instead of  

file path. 

10 

ID 

It specifies the primary key that is unique identification table.  

11 

Integer  

It will show textbox control on the window/tab. It specifies the whole  

Numbers and show default value like (0). It will accept maximum  

10 digits. 

12 

Image 

It specifies the image control. On click this control it will open image  

Dialog there you can view the saved image.  On this dialog there is  

button Choose File.  On press this button it will open file dialog control 

there you can pick any image, selected image can view in the dialog.  

On press OK button, image will save in the system. If checkbox Save in  

Database is checked (true) then image save in database otherwise image  

will save in images folder under hosted application path. By Default   

Checkbox Save in Database is unchecked. 

13 

List  

It shows dropdown control look like as below  

It will give the set of different value from which user has to select anyone.   

However when on choose List reference, user has to specify the data  

source for dropdown control by choose Reference Key of  

validation type-> List Validation or create new reference of List Validation  

type. In database, value will save which represent the list item in table  

AD_Ref_List. To know more about Reference Key field go through Reference  

Key section  

 

On click icon

will open a option list like Zoom , Refresh etc. 

14 

Quantity  

It will show textbox control. By Default it will show number only like (0).  

But it allow to user enter both number and decimal and it will accept  

maximum precision of 12 decimal places. 

15 

Locator(WH)  

It specifies the address of a specific locator with Aisle, Row, Bay and  

Search key in a warehouse.  

16 

Location(Address) 

  

This reference will show Address control on window/tab.  Control will  

look like as below.   

 

The control does not accept text directly. 

 

On click icon

will open a location dialog as below, There you can enter full  

address detail after save the record it will show in address control. 

On Click icon will open the entered location in Google Map. 

 

Note: - Ok button will remain disabled until select country from the list. 

17 

MultiSearch 

Multi Search Reference is working is same as Search Reference control.  

The difference is that it allow user to select multiple record from opened  

info window whereas in Search control user can select only single record.  

For further information check Search Reference information. 

18 

Number  

It will show textbox control. It accepts both numbers and decimal.   

By default it shows one decimal place like (0.0) however it will accept 

maximum precision of 12 decimal places. 

19 

Search  

It also use for reference to look up data like Table and Table Direct.  

However instead of a dropdown control it will show search control.  

Combination of text box + Icon.  

Text box is editable and on press icon will open info window for search  

a record.  User can use search control in different ways 

  

  • Define DB Column name end with ‘_ID’ (tableName_ID) and No info window define in field Tab. 

  1. In this case system will fetch first found info window link with table  

name after  

  1. remove ‘_ID’ from db column name and open info window from  

search control  

  1. for search the record. 

  2. If no info window is linked with table after remove ‘_ID’ from DB  

column name,  

  1. then system will open Generic Info Window from search control if 

 table name is valid. 

 

  • Define DB Column name end with ‘_ID’ (tableName_ID)  and info window define on Field Tab. 

  1. In this case system will open the defined info window, on Field Tab, from the search control for search a record. 

 

  • Define DB Column name (always end with _ID) + define Reference key 

 (Table Validation type) and No info window define in field tab. 

  1. In this case system will fetch first found info window link with  

table name  

  1. (get table name from reference key) and open a info window  

from search  

  1. control for search the record. 

  2. If no info window is linked with table, then system will open 

 the Generic Info  

  1. Window from search control if table name is valid. 

 

  • Define any DB Column name (always end with _ID) + define Reference key (Table Validation type) and info window define on Field Tab 

  1. In this case system will open the defined info window,  

on Field Tab, from  

  1. the search control. 

20 

Table  

Use as reference to look up data.  It also shows dropdown control like List reference. It will look like as below 

However when on choose Table reference, user has to specify  

the data source for dropdown control by choose Reference Key 

of validation type-> Table Validation or create new reference of  

Table Validation type.  To Know more about Reference Key field go 

through Reference Key section.  

 

 On click icon

will open a option list like Zoom , Refresh etc. 

Note: Column Name always end with _ID  

21 

Table Direct  

Use as direct Reference to look up data in a table, in this case  

use key column (tableName_ID) of reference table as DB Column  

Name and System Element.  Like Table Reference here no need  

to link any reference key.  It directly shows the identifier column 

 value of reference table in dropdown control on window/tab.  

If reference table have multiple identifier it will show with hyphen 

(_). It’s like a foreign key column in a database but need to apply  

constraints separately. 

22 

Text 

The Text reference specifies the text area control. It is used to 

save string character length up to 2000 long.  

 23 

Text Long 

This reference is used to save character length >2000. It will  

create CLOB data type column in database in oracle. 

24 

Time 

Show control which accepts time as input. Save in database as 

 it is as input by user. 

25 

URL 

This reference will create control combination of textbox and 

 icon which look like as below

For system entered text is a URL and on click icon

system will try to open the text in local system browser. Treated entered text  

as URL. 

26 

Yes-No  

Specifies Checkbox. 

  • Dynamic Validation 

Default Dynamic Validation field is not visible. This field will visible on select reference type Button, list, Search, Table and Table Direct. This field is used to show valid entry in the respective control according to the validation. 

For example if you check AD_Client_ID column of any table then there we have to defined dynamic  validation that only show login client in the table reference control.  On zoom on this field it will open Validation Rule window as below 

 

Currently supporting SQL Code Type validation.  

Set Code Type -> SQL 

Code -> Enter where clause with tableName.ColumnName 

@Context VariableName@ indicates to fetch the current value of variable from the context. 

@#Context VariableName @ indicates to fetch the login value of variable from the context. 

Here Context VariableName  is name of column name or any valid variable names whose value will system try to fetch from the context at run time.  

Reference Key: Reference key is required when selected reference is Table or List. It indicates the data source of dropdown control when reference is Table or List. 

  

By Default this field is hidden, on select Reference open as List, Reference key dropdown will show 

  

On press Zoom Icon from Reference key field, it will open Reference Window there user can create new reference. 

  

  • List Validation Reference 

When Reference is List, then you have to select reference key of validation type-> List Validation, or create new reference of List Validation type. 

 

On Enter Name and choose Validation Type->List Validation on first tab Reference, List Validation tab get enable, on this tab user will create list item that will show in the dropdown.  Here user has to enter Search Key(The Value Save in the Database) and Name(The Appear In dropdown) of the list item.  

  

  1. The number of character use for search key should be same for all the list item record e.g if length of the field is 2 then number of character use in search key for all the list item should be 2. 

  

  • Table Validation Reference 

When Reference is Table, then you have to select reference key of validation type-> Table Validation, or create new reference of Table Validation type. 

 

On Enter Name and choose Validation Type->Table Validation on first tab Reference, Table Validation tab will enable.  

In Table Validation tab user has to set  

 

Table – define table from data will fetch,  

 

Key Column – key column of the table normally tableName_ID, which store in the database and Display Column.  

 

SqlWhere - If user want that dropdown show value according to some filter then user can enter where condition in Sql Where multiline textbox, 

 

Sql Order By - if want record in specific order then enter order by in Sql Order By. 

 

  1. There are many example of List and Table reference key in Application Dictionary, user can view these example like default column UpdatedBy, CreatedBy etc.  

 

In the current release version, the column which is marked as identifier in the selected table, that column value will show in the dropdown.  Display Column, Display Value and Display Identifiers field on Table Validation tab is not supported yet. 

  • Default Logic 

Default Logic is the field where you can define any default value for your column, i.e. whenever a window opens it’ll show the value or evaluate the expression which you entered here. 

Default value hierarchy, separated by; 

The defaults are evaluated in the order of definition; the first not null value becomes the default value of the column. The values are separated by comma or semicolon.  

  

  1. For set default value user can set Literals: 'Text' or 123 

  2. Can set variables in format @Variable@ -  

  1. Set Login information from context as default value e.g. @#Date@, @#AD_Org_ID@, @#AD_Client_ID@ etc.  

  2. Current table column values from context e.g. @ColumnName@, set default value if not null. 

  3. SQL code with the tag: @SQL=SELECT something AS Default Value FROM ...TableName, it should be scalar query. 

The SQL statement can contain variables. There should be only one SQL statement.  

Default definitions are ignored for record columns set as Key and Parent. 

 

  1. If Data Access Level to Table-> System, then Default value of AD_Client_ID and AD_Org_ID is 0. 
  2. If Data Access Level to Table -> System + Client, then default value of AD_Org_ID is 0. 
  3. Other than this Default Value is set according to the value defined in AD_Column table for particular column. 

 

  • Key Column 

It indicates the key column of the table.  This checkbox is set by default true for the Primary key column of the table when default 9 columns are created. 

 

  • Parent Link Column 

If this column is marked then it indicate that this column is a link to the parent table and record will filter according to this column value on child tab. It makes a parent child relationship between tables records. 

 

  • Mandatory 

This checkbox is set to true if value is Mandatory (Not Null) for this column at the Database level. On save record to the database, this column must have a value. 

 

  • Mandatory UI 

Indicate that data entry is required for the field.  On Save record user must enter value in the field.  It will not make table column Not Null in Database. 

 

  • Mandatory Logic 

Based on certain logic user can make filed mandatory.  Further how to define logic, see below. 

Format := {expression} [{logic operator} {expression}]  expression := @{context}@{relation operator}{value}   logic operator = single pipe symbol {|},  use for logical OR                                   single ampersand symbol {&},  use for logical AND  context := any global or window context   value := strings or numbers  relation operator  := eq{=}, gt{>}, le{<}, not{^ !}   Examples:   @AD_Table_ID@=14 | @Language@!GERGER   Above format use two expression   {expression}{ logic}{ expression}   {AD_Table_ID variable value from context is EQUAL TO 14}{OR}{Language variable value from context IS NOT GERGER}  Above format is true if one of the expression is true.  @PriceLimit@>10 | @PriceList@>@PriceActual@  @Name@>J  Strings may be in single quotes (optional)

 

 

  • Updatable 

It signifies that filed is an updatable, user can update values in this field. 

 

  • Always Updatable 

Default in the system if record is not active or processed then you cannot update that record.  So if user set the checkbox to true (checked) then this particular column become always updatable if the window / tab is not read only, even if the record is not active or processed. This might be useful for comments, etc. 

 

  • Read Only Logic 

Way to make any field read only at runtime.  User can define here logic to determine if field is read only (applies only when field is read-write).  Further how to define logic, see below. 

Format := {expression} [{logic operator} {expression}]  expression := @{context}@{relation operator}{value}   logic operator = single pipe symbol {|},  use for logical OR                                   single ampersand symbol {&},  use for logical AND  context := any global or window context   value := strings or numbers  relation operator  := eq{=}, gt{>}, le{<}, not{^ !}   Examples:   @AD_Table_ID@=14 | @Language@!GERGER   Above format use two expression   {expression}{ logic}{ expression}   {AD_Table_ID variable value from context is EQUAL TO 14}{OR}{Language variable value from context IS NOT GERGER}  Above format is true if one of the expression is true.  @PriceLimit@>10 | @PriceList@>@PriceActual@  @Name@>J  Strings may be in single quotes (optional)

 

  • Callout 

It indicates that column has implemented a callout. 

 

  • Callout Code 

Here you specify the full path of the class where you have written the callout for this column which you can learn from Section related to Callouts. 

 

  • Identifier 

The purpose of this checkbox is to make this column is part of the record identifier.  You can define more the one column as an identifier.  In this case columns will show separated with underscore ‘_’ wherever use table as a reference. 

 

  • Selection Column 

It indicates that column is part of the search record criteria.  If selected column will show in first tab of find record window.  We are not supporting this feature currently in HTML5. 

 

  • Selection Sequence 

 Sequence in Selection 

 

  • Constraint Type 

It indicates to create foreign key constraints.  Currently, supported below four type of constraints. 

Null-> it will create foreign key constraint of type Set Null.  It will set reference id to Null in child table if that reference record is deleted. 

Cascade-> It will create foreign key of type cascade delete.  If record is going to delete from parent table then all its the referenced record will automatic delete from child table. 

Restrict-> It will create foreign key constraint of type Restrict and do not allow to delete the records if its reference is exist in the child table. 

Do Not Create> It will not create any constraint in database. 

 

  • Synchronize Column button: 

Create or Update database table definition from Application Dictionary 

On Press this button, based on the table column definition from Application Dictionary, physically database table is created in the Database if table does not exist. 

Specific column definition will update if table in database already exist. 

  

  1. After you create all the columns make sure to click “Synchronize Column” button. It’ll sync column in the database. No Need to sync Virtual Column. 

  

  1. There are few column names in Application Dictionary, where System will provide default functionality if user require that functionality then there is Exceptions for these columns which should not be created with MPC 

 

  • Value -> Use for search key in any table, on created column with name Value, will auto creates search key value by the system in the respective table within the client. 

  • DocumentNo-> Use for Document number, system will create number in sequence for every record within the client. 

  • Processed -> On Window/tab, Record will show as read only if related table has column with name Processed and its value is ‘Y’. 

  • Processing-> On Window/Tab, Record will show as read only if related table has column with name Processing and its value is ‘Y’. 

  •  IsSummary, Name and Description -> Use to achieve Tree on window/tab.  If user wants to show tree on window/tab then linked table should have column IsSummary, Name and Description. 

  • DocAction-> Use to implement process workflow that runs from button on the window. 

  • DocStatus -> Use with DocAction, to show the status on the window update by workflow. 

  • Posted -> Use for Account Posting.