This function is used to create and modify views, by describing their characteristics in the data dictionary for the folder. It will be possible to create views on tables from different folders.

The confirmation of this description is then used to create the view in the database or to modify its characteristics.

A view can then be used in read-only mode. Programming relating to a view is similar to programming for a table. It will have its own class [F]. The following instructions are available for the views:

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

A view can be accessed via Crystal Report and the formula editor.

The restoration or validation of a table destroys the views based on this table. Upon restoration, the views and triggers are regenerated automatically by the supervisor. On the other hand, further to the forced validation of a table, the validation of the views of the current folder and the other folders concerning this table will need to be launched.

For instance, a view is used to:

  • avoid having to explicitly open a table of another folder, for instance, for the archived folders. A view is created for each non-archived table; in this way, the inquiry functions as well as the reports work just as well on the main folder and archived folder.
  • develop reports providing access to the data of several folders.
  • access SQL functionalities that could not be accessed so far: union, group by, SQL functions

    Prerequisites

    SEEREFERTTO Refer to documentation Implementation

    Screen management

    The definition of a view is done using three tabs: a query definition tab, the field definition tab and the key definition tab.

    Header

    Fields

    The following fields are present on this tab :

    The view code must be composed of 1 to 10 characters (letters or numbers but must start with a letter). It is the unique identifier for all the views of the data dictionary. A view cannot carry the same name as a table in the dictionary, the the validation, the supervisor generates the files*.srf et *.fde. the words reserved for Adonix are prohibits.

    • Abbreviation (field ABRVUE)

    The view abbreviation must be composed of 1 to 3 characters (letters or numbers but must start with a letter). This indicator need not be unique for all the abbreviation of the views in the data dictionary. The Adonix reserved words are prohibited.

    • Description (field INTITSAI)

    Enter the description of the relevant record.

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

    • Active (field ENAFLG)

     

    Close

     

    Tab Query

    Presentation

    This tab is used to write the query.

    The query must be written at least in the language of the current folder database. But for any standard view supplied, a script is delivered for each base. The *.viw file is created upon view validation with the script adapted to the database of the current folder, the latter being created in the FIL directory, on the data server.

    Using a formula is recommended if fields submitted to an activity code, or a folder name, need to be specified in the query. Indeed, it is better not to "hardcode" the folder in the query, so as to enable operation in another folder following a copying of this view to another view.

    For instance, activity code sized from 1 to 3 for the LIEN field, and formula on folder:

    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 further details on the query writing rules, please refer to the field help. 

    Close

     

    Fields

    The following fields are present on this tab :

    Characteristics

    The Activity Code and the Module that are used to identify if the view described in the dictionary must be created in the database of the folder. It is if the two conditions set out below are achieved simultaneously :

     the activity code field is empty or that the activity code (defined in the corresponding view) is actually activated.

     the module to which the view is attached has been declared active for the folder.

    A view with an assigned activity code starting with X, Y, or Z, is considered to be specific/custom and in no case affected by a version change (these activity codes can be placed at the line level).

    • Open access (field SECURE)

    This check box is used to restrict the access to the data table within the folder owning the table and certain authorized folders. This notion is identical 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 the folders for the solution can access the table to read or write the data in this table. This is the case, for system tables, for dictionary tables and certain supervisor tables.
    • secured : access to this table will be made as a function of the rights to the call folder. A folder can be non authorized, authorized for reading, authorized for read/write. It is the case for functional tables and the majority of supervisor tables.

    The authorized folders are :

    • the folder in which the table is defined (the system tables being in the reference folder for the solution : in the case of Sage X3, this folder is called X3).
    • the parent folders of the folder
    • an authorized folder (via the Folder management function, Links tab, Folders for the solution section).
    • the folders for the other solutions having an access by links (via the same function).

    It should be noted that this check box is not updated by patch : the security strategy for the table being considered as parameterization. On the validation of the table, the fact that this check box is checked provokes the creation of a configuration file (extension .cfg) with the following code :

    $SECURITY
    {  "LEVEL0" }
    End

    If other manual directives exist in the configuration file, they are of course respected. The directives can be in effect added by update of the Configuration file section in the corresponding tab of the table management).

    • Module (field MODULE)

    The Activity Code and the Module that are used to identify if the view described in the dictionary must be created in the database of the folder. It is if the two conditions set out below are achieved simultaneously :

     the activity code field is empty or that the activity code (defined in the corresponding view) is actually activated.

     the module to which the view is attached has been declared active for the folder.

    A view with an assigned activity code starting with X, Y, or Z, is considered to be specific/custom and in no case affected by a version change (these activity codes can be placed at the line level).

    • Validation group (field GRUCFM)

     

    Grid Tables and views used

    • Code (field OBCCOD)

     

     

    Oracle

    • field TEX1

     

    SQL server

    • field TEX2

     

    Close

     

    Tab Fields

    Presentation

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

     

    Fields

    The following fields are present on this tab :

    Grid Fields

    • Fields (field FLDVUE)

    In this column the field name for the view is defined as it will be expressed in the software (a field with the name FIELDNAME defined in an ABV abbreviation view can be accessed using the syntax [F:ABV]FIELDNAME ).

    For custom/specific fields, the field name must start with X_, Y_ or Z_.

    For a dimensioned field (FIELDNAME_0, FIELDNAME_1, FIELDNAME_2…), only a single field FIELDNAME is defined.

    The field type is defined here. The principal 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

     Other types previously defined. They usually make reference to an annex table (currency, dimension...). The F12 key allows you to obtain the list.

    It is not possible to use here, the data types related to the translated texts: AX1, AX2, AX3.

    • Menu (field NOLIB)

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

    When a field is of the type local menu (from 1 to 255) corresponding to the rank of a title in a table named local menu, stored in the messages table APLSTD.

    On entry or on display, the following are displayed according to the choices made in the user interface :

    • either a title can be chosen in a scrolling list commonly called a combo box
    • or a list of buttons.

    The interest of this type of entry is that the list of choices is displayed in the user connection language.

    Each local menu number characterises the list of available titles. For example, the local menu 1 corresponds to the choice No / Yes in this order. In this particular case, the user interface can also be a check box.

    • Length (field LNG)

    Used to define the length of a field when this field uses a generic data type where the length is not fixed. This is notably the case for the types A and DCB.

    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 will not be useable
    • The associated code will not be generated nor activated
    • Dim. (field DIME)

    Dimension of the field in the view. The dimension can also be assigned as a function of the activity code.

    • Description (field FLDINTSAI)

    Enter the description of the relevant record.

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

    • Options (field OPTION)

    These options are realized by characters that can be concatenated when several options are required. It is possible to choose these options thanks to a selection window. A detailed description for all the possible options is available.

     

    Action icon

    Addition

     

    Close

     

    Tab Keys

    Presentation

    This tab is used to define all the available keys for an 'order by' on the view. Caution: There is no index creation for the view.

    Close

     

    Fields

    The following fields are present on this tab :

    Grid Keys

    • No. (field NUMLIG)

     

    • Key code (field CODCLE)

    This field corresponds to the name under which the key is known in the order Order By. A standard is used in all the views in the application : it consists of naming the keys by using the abbreviation for the view, followed by 0 for the principal key, by 1 for the second key, etc.... For the specific/customization, start this code with one of the following three letters : X,Y or Z.

    • Key description (field DESCLE)

    A list of fields that make up the index is entered here, separated by a "+" if there are several fields.
    A sort field in descending order is preceded by the sign "-".
    The first field composing an index is without a sign and is therefore ascending.

    • Duplicates (field KEYDUP)

    The default value of this field is set to Yes. It is possible to set it to No. In this case, a warning message is displayed informing that there should not be two identical keys on the index of this view. If it happened, no failure would occur but homonyms could not be detected by a simple For loop.

    It is then advised to let it set to "Yes" except in specific cases where the option "No" is used to support the instructions "Read Next/Prev" on views.

    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 will not be useable
    • The associated code will not be generated nor activated

    Configuration file

    • field FICCFG

    A text describing the technical configuration of the view in the database is entered in this section. This text is recorded in a file "view_name.cfg" in the FIL directory for the application. This file is used by the "valfil" instruction. View the dedicated technical annex for more information.

    Close

     

    Specific Buttons

    Creates or updates the view in the database from the dictionary, via the "create view" instruction. A syntax control is performed on the query with respect to the folder database.

    The files created in the FIL directory are the following:

    • *.srf:  contains the list of fields. A "#V" flag is placed on line 3 to distinguish the views from the tables.
    • *.fde: generated by valfil -n
    • *.viw: contains the description of the query with the script corresponding to the folder database.
    • *.cfg: configuration file, generated if the associated clob (text file) is entered or access is secure.

    The following fields are included on the window opened through this button :

    Block number 1

    • field OBJET

     

    • field CLES

     

    Block number 2

    • From folder (field DOSORG)

    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 (field TOUDOS)

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

    • To folder (field DOSDES)

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

    Close

    This button is used to copy the view structure to another folder. Caution: Only the description of the view in the dictionary is recopied: the view is not created in the target folder (it will be necessary to validate this description so that the view can be created).

    Menu Bar

    Option / .srf source

    This is used to view the *.srf file corresponding to the current table in the current application.
    This option is notably interesting when on a server with no rights to the FIL directory.

    Option / .viw source

    This is used to view the *.viw file containing the source of the view in the current application.
    This option is notably interesting when on a server with no rights to the FIL directory.

    Error messages

    In addition to the generic error messages, the following messages can appear during the entry :

    There already is a table with the same name.

    It is not possible to create a view whose name is already given to a table. In effect, the names must be unique, all tables and view considered, because the system creates a *.srf and *.fde file.

    File size too large

    Size of the configuration file limited.

    Data type not managed

    it is not possible to create a view with an AXX type field (translatable text).

    Incorrect length

    the length is incorrect based on the field internal 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
    • alphanumerical: the length must be <= 250
    • blob (image file), clob (text file):  the length must be <= 20


    Invalid Option

    An attempt has been made to enter a non-existing option.

    Option incompatible with format x (data type y)

    When a format is specified for an alpha data type, options A and # must not be used.

    Abbreviation exists already...

    warning message, when an attempt is made to enter an abbreviation on a view that already exists for another view or table. It is indeed recommended to have single abbreviations.

    NB

    A view is created in the database instance of the folder where it is installed upon validation. All the X3 functions used in such a view (with the %formula% syntax) are evaluated upon validation and transmitted to the base as constants.

    When a folder is created or validated, the operation of view validation is triggered from the X3 folder, which is different from the folder where the view is installed. It is therefore necessary to take the following precautions:

    • in any formula, avoid using functions (func syntax) to compute constants that will be inserted upon creation of the view, unless it is certain that the function is available in every folder (including the X3 folder) and returns the same value.
    • when a table or a view is declared, avoid referring to the folder where it is located, especially if the nomap or adxmother functions are used since their definition depends on the current folder.

    SEEWARNING If these precautions cannot be followed out, it is absolutely essential that the views be revalidated manually in the folders themselves after the end of the folder validation operations.

    Tables used

    SEEREFERTTO Refer to documentation Implementation