Column
- Gaurav
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
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
name after
search control
column name,
table name is valid.
(Table Validation type) and No info window define in field tab.
table name
from search
the Generic Info
on Field Tab, from
|
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.
- 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.
- 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.
For set default value user can set Literals: 'Text' or 123
Can set variables in format @Variable@ -
Set Login information from context as default value e.g. @#Date@, @#AD_Org_ID@, @#AD_Client_ID@ etc.
Current table column values from context e.g. @ColumnName@, set default value if not null.
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.
- If Data Access Level to Table-> System, then Default value of AD_Client_ID and AD_Org_ID is 0.
- If Data Access Level to Table -> System + Client, then default value of AD_Org_ID is 0.
- 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.
- 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.
- 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.