niota

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== database grid edit formatting =====

To enable grid formats and grid end user experience to your needs there is the database (based) grid edit format feature. Format of UI/grid edit pages is done on several levels and with different base objects.

==== format column collection ====
A column collection is the most generic and fastest way to set user friendly formatting for table. Most of the settings from a column collection are applied dynamically on all columns with the same name of all tables where the value of "collection ID" matches between the table and and the collection entry.

{{:niota-docs:pasted:20251214-135356.png}}

** Collection **\\ 
ID of an collection to which this column definition is assigned. The setting is only applied if the value on table level for collection matches this value on column level.
By this you can have the same column name in different tables with a totally different meaning.
E.g. in a table that belongs to your financing system the column "workshop" means a company that repairs car. It's a local synonym for "repairshop".

But in your HR system then column "workshop" has the meaning of a meeting where you work together. If you put "workshop" in two different collections you can then assign to different descriptions to it.

On table level the collection is assigned here.
{{:niota-docs:pasted:20251214-140023.png}}

The default value for collection can be set in the system_defaults configuration file.\\

-> 	define('CUSTOM_APPLICATION_CODE_DEFAULT',			'CUST');

** Columnname ** \\
Physical name of the column on which this setting are applied.

** activate **\\
Active/inactive flag

** Defaultvalue of column **\\

** Title of Column **\\
Title of column in grid mode/form mode. In case also a semantic domain is assigned to this column, then the title is concatenated as domain title + column title.

** Heading of Column **\\
Text that is used for mouse over info.

** Description Column **\\

** on/off read only **\\
Flag to toggle read only property for columns with this name. Can be overwritten on level of the specific column of a table. 

** on/off hidden **\\
Flag to toggle hidden property for columns with this name. Hidden means it's not visible in grid display format. Can be overwritten on level of the specific column of a table.

** on/off hidden editable **\\
Flag to toggle hidden editable property for columns with this name. If editable hidden is on, then hidden grid columns are visible in the edit form. Can be overwritten on level of the specific column of a table.

** Domain ID Physical **\\
Assign a physical domain to this column.

** Domain ID Logical **\\
Assign a logical domain to this column. 

** Domain ID Semantic **\\
Assign a semantic domain to this column. 

** Lookup for Column **\\
Assign a lookup definition to this column. This value is later ** copied ** to the column property setting of the table where a column with this name exists. This setting is not applied dynamically.

** Constraint for Column **\\
Assign a constraint definition to this column. This value is later ** copied ** to the column property setting of the table where a column with this name exists. This setting is not applied dynamically.

** Security Token to apply **\\
Assign a security token to this column. This value is later ** copied ** to the column property setting of the table where a column with this name exists. This setting is not applied dynamically.

** RegEx to match **/**Regex not to match**\\
The setting is only copied to columns where the column name matches the ''to match'' regular expression and not matches an optional regular expression in the ''not to match'' column.
By this you can define patterns where you want to apply the same column settings. E.g. you could here apply column setting for column ''Customer'' to all columns that begin with ''custom'' but not to column ''customize''.


==== format object - logical domain ====

With a logical domain object you can configure a reusable object for the overall appearance of an db-table grid column on the screen. A logical domain object controls the usage of the widget that is used to display/enter that values for a column.

E.g. when you have a db-table column which is defined as varchar(500) you can configure via a logical domain, that you want to have a form with 2 (character-lines) and 50 character (pixels) allow the user to enter the values.  


==== format object - physical domain ====

With a physical domain object you can configure a reusable object for size of the form field and the number of characters that can be entered in this field.
A physical domain object controls the UI appearance of db-table column on the screen.

E.g. when you have a db-table column which is defined as varchar(500) you can configure via a physical domain, that you want to have a form filed with 20 (character-pixels) and allow the user to enter only 50 columns. When the number of maximum characters is reached the keyboard in this form field is locked.  

==== format object - semantic domain ====

With a semantic domain object you can configure a reusable object that tells end user which type of business content a db-table grid column contains.
A logical domain object controls the texts and headlines and mouse over info of db-table column on the screen.

E.g. when you have a db-table column which is defined as varchar(500) and is named INV you can configure via a semantic domain, that this column contains always some "Invoice number of". With additional information on the db-table column properties you can add some additional business information. The system concatenates this text to a combined column title and column heading.

"Invoice number of purchase order"


==== number formatting ====
There is an option to format displayable numeric values. It is possible to display numbers with style of decimal number, currency, unit and percentage with the following settings:

**General settings**

Those settings are applied in the case of each styles:
  * NF_NAME - name / identifier of the number format
  * NF_CULTURE - locale definition of the number formatting. Refers to a //BCP 47 language tag//. E.g.: de-DE, en-US, en-GB. 
  * NF_STYLE - style of the setting. Accepted values are: //decimal, currency, percent, unit//. Default is **decimal**
  * NF_FRACTION_MIN - minimum displayed decimal digits. Default is **0**.
  * NF_FRACTION_MAX - maximum displayed decimal digits. Default and maximum value is **20**. Take care! If value of NF_FRACTION_MAX is lower then the decimal digits of the displayed number system makes rounding which can affect entered or existing data!
  * NF_GROUPING - use grouping separator (e.g. thousand separator) during display. Accepted values are: //always, auto// (uses locale preferences), //min2// (if at least 2 digits in a group), //true// (same as always), //false// (no grouping). Default value is **false**.

**Currency specific settings**
  * NF_CURRENCY - currency code of the currency.// ISO 4217 currency codes// are displayed. Default is **EUR**.
  * NF_CURRENCY_DISPLAY - display method of currency. Accepted values //are: code, symbol, narrowSymbol, name//. Default is **symbol**.
  * NF_CURRENCY_SIGN - further formatting setting for standard or accounting format. Accepted values are //standard, accounting//. Default is **standard**.

**Unit specific settings**
  * NF_UNIT - unit to use. E.g.: kilogram, celsius. Shorter list of values: [[https://tc39.es/ecma402/#table-sanctioned-single-unit-identifiers]] Longer list of values: [[https://github.com/unicode-org/cldr/blob/main/common/validity/unit.xml]]
  * NF_UNIT_DISPLAY - display method of the defined unit. Accepted values are:  //short, narrow, long//. Default is **short**.

**Sample configuration**
Number formats can be defined in table //etx_system_config_set//. Sample INSERT INTO statements for a number definition can be seen below. It will display numbers as currency (EUR), and uses EUR-sign to display. It will display 3-20 decimal digits, comma as decimal separator and dot as thousand separator (DE format) . Sample output: 1.023.456,780 € 

insert into etx_system_config_set (cfgset_id, cfgset_variant, cfg_id, description, ind_active, cfg_value, cfg_id_overlay, cfg_id_link) values 
 ('NBRFORMAT',	'CUR_01', 'NF_NAME', 'Standard EUR', 1, 'EUR', null,null),
 ('NBRFORMAT',	'CUR_01', 'NF_STYLE', 'Format Stype', 1, 'currency', null,null), 
 ('NBRFORMAT',	'CUR_01', 'NF_CURRENCY', 'EURO sign', 1, 'EUR', null,null),
 ('NBRFORMAT',	'CUR_01', 'NF_CURRENCY_DISPLAY', 'EURO sign', 1, 'symbol', null, null),
 ('NBRFORMAT',	'CUR_01', 'NF_CURRENCY_SIGN', 'EURO sign', 1, 'standard', null, null),
 ('NBRFORMAT',	'CUR_01', 'NF_UNIT', 'unit to use', 1, 'null', null, null),
 ('NBRFORMAT',	'CUR_01', 'NF_UNIT_DISPLAY', 'unit to use', 1, 'null', null, null),
 ('NBRFORMAT',	'CUR_01', 'NF_FRACTION_MIN', '3 decimals', 1, 3, null, null),
 ('NBRFORMAT',	'CUR_01', 'NF_FRACTION_MAX', '20 decimals', 1, 20, null, null),
 ('NBRFORMAT',	'CUR_01', 'NF_GROUPING', 'use grouping separators', 1, 'true', null, null),
 ('NBRFORMAT',	'CUR_01', 'NF_CULTURE', 'German German', 1, 'de-DE', null,null);

After number format details are inserted into the database you have to make the following steps:
  - Create a //physical domain// entry. In the add form // number format name// refers to the value of //NF_NAME// configuration value.
  - Define the column which you want to format in the //column properties// menu item.
  - Set //physical domain// in the menu item //format properties//

Following screenshots show the steps described upwards:

{{:niota-docs:pasted:20240226-120113.png}}

{{:niota-docs:pasted:20240229-144746.png}}

{{:niota-docs:pasted:20240226-120201.png}}

{{:niota-docs:pasted:20240226-120212.png}}


**System default**
In file //system_defaults.php// default number formatting can be set. It is used for each columns which does not have a number format setting. Only **culture setting** should be modified. Other settings are set by defaults (see previous sections). System default format sets a decimal number format with 0 to 20 decimal digits, without thousand separator and locale-specific decimal separator. Sample output for integer value: 123456. Sample output for decimal value: 123456,789123 (de-DE locale setting)

  // ---
	// number format default definition
	// it is set by default for all columns which does not have
	// a number format setting
	define('NUMBER_FORMAT_DEFAULT', 		array("name" => 'default_nf', 
							      "description" => 'default format', 
							       "culture" => 'DE-de')
	);

===== Server side grid edit formatting =====

Via grid edit formatting you can configure the look and feel for a single table. Grid edit formatting is done via server side configuration files for the values/settings to be displayed and database side configurations settings what should be applied to a single table.

Mind that there are a lot of approaches how to format a table via the server formatter table. It's not recommended to use this feature any longer because it's deprecated and will be removed in one of the future releases.
niota

User Tools

Site Tools

Page Tools
