Views

This function is used to create and modify views by describing their characteristics in the folder data dictionary. You can create views in tables in different folders.

Validating its description allows the view to be created in the database, or its characteristics to be modified.

A view will then be read-only. Programming with a view is similar to programming with a table. The view will have its own class [F]. The following instructions are available for views:

  • (Local) File,
  • Close (local) File,
  • Filter,
  • For ... Next,
  • Read,
  • Columns,
  • Link.

A view can be accessed using Crystal Reports and the formula editor.

Restoring or validating a table clears views based on that table. During restore, views and triggers are automatically regenerated by the supervisor. However, after you force the validation of a table, you must launch the validation of the views of the current folder and the other folders that apply to that table.

For example, a view allows you to:

  • Avoid having to explicitly open a table from another folder, for example, for historical records. A view is created for each table that is not archived. In this way, the inquiry functions and reports work equally in the main folder and the archived folder.
  • Develop reports that access data from multiple folders.
  • Access SQL functions that could not be previously accessed: union, group by, SQL functions.

Prerequisites

SEEREFERTTO See the Implementation documentation.

Screen management

A view is defined in 3 tabs:

  • the query tab,
  • the fields tab,
  • the sort keys tab.

Header

View code (CODVUE field)

The view code must contain between 1 and 12 characters (letters or numbers but must start with a letter). It features a unique identifier for all views in the data dictionary. A view cannot have the same name as a dictionary table: On validation, the supervisor generates the *.srf and *.fde files. Reserved Adonix words are prohibited.

Abbreviation (ABRVUE field)

The view abbreviation must contain 1 to 8 characters (letters or numbers but must start with a letter). This identifier may not be unique for all the abbreviations of the views of the data dictionary. Reserved Adonix words are prohibited.

Description (INTITSAI field)

Enter the description of the relevant record.

This long description is used as a title in screens and reports.

Active (ENAFLG field)

This checkbox indicates whether the view is validated.

Only active views are generated.

Query tab

Use this tab to write the query.

The query must be written at least in the language of the folder current database. On the other hand, for any delivered standard view, a script is delivered for each database. When the view is validated, the *.viw file is created with the script adapted to the database of the current folder. It is created in the FIL directory on the data server.

In the query, if you want to specify fields that are linked to an activity code, as well as a folder name, it is recommended to use a formula. It is indeed advised not to “hard code” the folder in the query. This will allow operation in another folder where this view could have been copied.

Example: Activity code sized from 1 to 3 for the LIEN field, and formula on file:

Select CLENUM1_0, CLENUM2_0, LIEN_0

%string$(find(func AFNC.ACTIV("ZZM"),2,3)<>0,", LIEN_1")%

%string$(find(func AFNC.ACTIV("ZZM"),3)<>0,", LIEN_2")%

From %nomap+"."%ZZMB

For more information about the query writing rules, you can read the field help.

Characteristics

Activity code (CODACT field)

The activity code and the module determine whether the view described in the dictionary should actually be created in the folder database. It will be the case if the following two conditions are met simultaneously:

  • The activity code field is empty or the activity code (defined in the corresponding table) is actually activated.
  • The module to which the screen is associated has been declared as active in the folder.

A view associated with an activity code starting with X, Y, or Z is a specific view and will not be impacted by a version change (these activity codes can be set at line level).

Open access (SECURE field)

This checkbox allows you to restrict access to the database table to only the folder that owns the table and some authorized folders. This concept is the same for the view. A modification of this field requires a revalidation of the table, to be taken into account in the database.

  • Non secured: All folders in the solution can access the table to read or write data from the table. This is the case of the following tables:
    • System tables
    • Dictionary tables
    • Some supervisor tables
  • Secured: Access to this table is based on the rights of the used folder. A folder can be:
    • Unauthorized
    • Read-only
    • Read-write This is the case of functional tables and most supervisor tables.

The authorized folders are:

  • The folder in which the table is defined (the system tables are in the solution reference folder. For Sage X3, this folder is called X3)
  • The parent folders of the folder
  • An authorized folder (via the Folder Management function, Links tab, Solution folders block)
  • The folders of other solutions that have access by links (via the same function).

SEEINFO Since the security policy for the table is considered to be part of the setup, this checkbox is not updated by patch. When the table is validated, this selected checkbox causes a configuration file (.cfg extension) to be created with the following code:

$SECURITY

Module (MODULE field)

The activity code and the module determine whether the view described in the dictionary should actually be created in the folder database. It will be the case if the following two conditions are met simultaneously:

  • The activity code field is empty or the activity code (defined in the corresponding table) is actually activated.
  • The module to which the screen is associated has been declared as active in the folder.

A view associated with an activity code starting with X, Y, or Z is a specific view and will not be impacted by a version change (these activity codes can be set at line level).

Validation group (GRUCFM field)

The validation group is used to sort the validation of views.

This field is used to:

  • Group views.
  • Validate the order of the views by:
    • Validation group in alphabetical order
    • Alphabetical order

Example:

  • View ATEST1, validation group 200
  • View BTEST2, validation group 100
  • View CTEST3, validation group 100
  • View DTEST4, validation group 400

Views will be sorted in the following order:

  • BTEST2
  • CTEST3
  • ATEST1
  • DTEST4

Table Used tables and views

Code (OBCCOD field)

This table contains the list of tables or views that can be used by the view. It is not mandatory to populate it nor is it necessary to complete all the associated tables and views.

A table or view is present only once even if it is present several times in the view with different abbreviations. This allows a link to the view and the tables and views that make them up to be established.

This field displays the reference of the table or view being used.

Type (OBCTYP field)

This table contains the list of tables or views that can be used by the view. It is not mandatory to populate it nor is it necessary to complete all the associated tables and views.

A table or view is present only once even if it is present several times in the view with different abbreviations. This allows a link to the view and the tables and views that make them up to be established.

This field indicates whether it is a table (ATB) or a view (AVW).

Oracle

TEX1 field

This field contains the SQL code of the view creation for Oracle.

SQL Server

TEX2 field

This field contains the SQL code of the view creation for SQL Server.

Fields tab

This tab is used to define all the fields in the view in a drop-down table. The description of the fields in the query and in this tab (number, order, and type of the fields) must be fully compatible. This control is performed upon view validation. These fields can exist in the table dictionary. This is nevertheless not obligatory.

Table Fields

Fields (FLDVUE field)

In this column, the zone name of the view is defined as it will be defined in the software (a field with the name NOMCHAMP defined in an abbreviation view ABV can be accessed by the syntax [F:ABV]NOMCHAMP).

For fields created for specific developments, the zone name must begin with X_, Y_ , or Z_.

For a sized field (NOMCHAMP_0, NOMCHAMP_1, NOMCHAMP_2, etc), only one zone is defined NOMCHAMP.

Type (CODTYP field)

The field type is defined here. The main types are:

A: Alphanumeric
C: Short integer
L : Long integer
DCB: Decimal
D: Date
M: Local menu
MM: local menu with filter
ACB: Clob
ABB: Blob

There are other predefined types. They are often linked to an appendix table (currency, dimension, etc.). Press F12 to get a list.

Here, it is not possible to use the data types relating to translated texts: AX1, AX2, AX3.

Menu (NOLIB field)

Enter the local menu number associated with the field defined on the line.

When a field has the local menu type, it is stored in the form of a numerical value (from 1 to 255) corresponding to the position of a description in a table called local menu stored in the APLSTD message table.

When data is entered or simply displayed, you can see the following elements, depending on the choices you made on the user interface:

  • A description that can be chosen from a drop-down list commonly known as a combo box,
  • A list of selection buttons.

The advantage of this type of entry is that the list of choices is available in the user's login language.

Each local menu number refers to the list of possible descriptions. For example, local menu 1 corresponds to the No/Yes choice, in that order. In this particular case, the user interface can also be the checkbox.

Length (LNG field)

This field is used to define the length of a field when this field uses a generic data type with an unfixed length. This is namely the case for A and DCB types.

For blobs and clobs, you must enter the length which will be a maximum storage length. The coding is as follows:

Length

Memory size

Maximum number of characters

0

1020

510

1

2044

1022

2

4092

2046

3

8188

4094

n

1024*(2^n) - 4

512*(2^n) - 2

Act (FLDACT field)

An activity code is used to:

  • Make an element optional in the dictionary if the value associated with the activity code is null.
  • Identify the specific/custom elements if they are marked with a code starting with X, Y, or Z.
  • Size a maximum number of lines when the activity code marks elements from a grid.

If the activity code is disabled:

  • The marked element cannot be used.
  • The associated code will not be generated nor activated.

Dim. (DIME field)

Dimension of the zone in the view. The dimension can also be assigned based on the activity code.

Description (FLDINTSAI field)

Enter the description of the relevant record.

This long description is used as a title in screens and reports.

Options (OPTION field)

These options are materialized by characters that can be regrouped when multiple options are needed. It is possible to choose these options via a selection window. A detailed description for all the possible options is available.

Insert

Keys tab

This tab is used to define the set of keys available for an Order By code on the view.

Warning: There is no index creation for the view.

Table Keys

No (NUMLIG field)

Current line of the table.

Key code (CODCLE field)

This field corresponds to the name by which the key is known in the Order by order. A standard is used in all the views of the application: It consists in naming keys using the view abbreviation, followed by 0 for the first key, 1 for the second key, and so on. For specific developments, start this code with one of the three letters: X, Y or Z.

Key description (DESCLE field)

Enter the list of fields that make up the index, separated by a + if there are more than one field.

A descending sorted field is preceded by a - sign.

The first field composing an index has no sign and it is necessarily ascending.

Duplicates (KEYDUP field)

This field defaults to Yes. It can be modified and set to No. In this case, a warning message warns that there should be no duplicate keys on the index of this view. If this was the case, there would be no error but a simple For loop would not detect the duplicates.

Consequently, it is recommended to leave it to Yes except in very special cases where the No option supports the Read Next/Prev instructions on views.

Activity code (CLEACT field)

An activity code is used to:

  • Make a dictionary element optional if the value associated with the activity code is null.
  • Identify the specific/custom elements if they are marked with a code starting with X, Y, or Z.
  • Size a maximum number of lines when the activity code marks elements from a table.

If the activity code is deactivated:

  • The marked element cannot be used.
  • The associated code is neither generated nor activated.

Configuration file

FICCFG field

In this block, you can enter text describing the technical configuration of the view in the database.

This text is saved in a viewname.cfg file in the FIL directory of the application. This file is used by the valfil instruction.

Refer to the dedicated technical appendix for more details.

Specific buttons

  • This button creates or updates the view in the database from the dictionary, using the create view instruction. A syntactic check of the query is performed against the folder database.

    The files created in the FIL directory are the following:

    • *.srf: They contain the list of fields. An indicator #V is positioned on line 3 to distinguish the views from the tables.
    • *.fde: They are generated by valfil -n.
    • *.viw: They contain the description of the query with the script corresponding to the folder database.
    • *.cfg: Configuration file, generated if the associated clob is entered or if access is secured.

  • This button is used to copy the view structure to another folder.

    Warning: Only the view description in the dictionary is copied. The view is not created in the target folder. This description will need to be validated for the view to be created.

    Block 1

    OBJET field

    CLES field

    Block 2

    From folder (DOSORG field)

    Use this field to define the folder from which the record will be copied. The possible syntaxes are described in the Dedicated appendix.

    All folders (TOUDOS field)

    Use this option to copy the record to all the folders defined in the dictionary (ADOSSIER table of the current solution).

    To folder (DOSDES field)

    Use this field to define the folder to which the record will be copied. The possible syntaxes are described in the Dedicated appendix.

Option/.srf source

This option is used to view the *.srf file corresponding to the current table in the current application.

This option is particularly interesting in the event of the FIL directory of a server for which you do not have rights.

Option/.viw source

This option is used to view the *.viw file containing the source of the view in the current application.

This option is particularly interesting in the event of the FIL directory of a server for which you do not have rights.

Error messages

Other than the generic messages, the following error messages can appear when entering data:

A table with the same name already exists

You cannot create a view whose name is already given to a table. In effect, the system creates a *.srf file and a *.fde file.

The file size is too large

Limited size of the configuration file.

Data type not managed

You cannot create a view with a field with type AXX (translatable text).

Incorrect length

The length is incorrect against the internal field type.

  • Description: The length must be ≤ 30.
  • Short integer: The length must be ≤ 4.
  • Long integer: The length must be ≤ 8.
  • Decimal: Integer part + decimal part ≤ 32.
  • Alphanumeric: The length must be ≤ 250.
  • Blob, clob: The length must be ≤ 20.

Incorrect option

You tried to enter an option that does not exist.

Option incompatible with format x (data type y)

When specifying a format on an alpha data type, you must not use the A and # options.

Abbreviation already exists

This warning message appears if you try to enter an abbreviation on a view that already exists for another view or table. It is recommended to use unique abbreviations.

Notes

A view is created in the folder database where it is installed at the time of validation. All Sage X3 functions used in such a view (with the %formula% syntax) are valued on view validation and transferred to the database as constants.

When a folder is created or validated, the operation that will validate the views is triggered from the X3 folder which is different from the one where the view is installed. You should therefore take the following precautions:

  • In formulas, avoid using functions (func syntax) to calculate constants that will then be inserted when the view is created, unless you are absolutely certain that the function in question is available in all folders (including the X3 folder) and generates the same value.
  • Prefer the declaration of tables or views without specifying in which folder they are located (and in particular not nomap or adxmother whose definition may vary depending on the folder).

SEEWARNING If these precautions cannot be followed, you must manually revalidate the problematic views in the folder itself after the folder validation operations.

Tables used

SEEREFERTTO See the Implementation documentation.