Tables

The table 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 table abbreviations of the data dictionary. The Adonix reserved words are prohibited.

The abbreviation for a table must be composed of 1 to 3 characters (letters or numbers but it must start with a letter). It is the unique identifier for all the table abbreviations of the data dictionary. The Adonix reserved words are prohibited.

The Activity Code and the Module that are used to identify if the table 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 table) is actually activated.
• the module to which the table is attached has been declared active for the folder.

A table 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).

In addition, there is a dependence between the modules :

• Supervisor and Common data : must be installed as a minimum. Following this the Stock, Development and General accounting modules can be installed.
• Stock : the following modules can be installed Sales, Purchasing, Production.
• Sales : following from this the modules CRM, Commercial actions cam be installed.
• General accounting: there is then the possibility to install the modules fixed assets, AR/AP.

Base type for which the table must be generated. The "Folder" base type is used to indicate to the program for the management of folders to generate the table in the base type entered in the folder record for all the files of the folder. In certain exceptional cases (in particular if it is an external table) there is the possibility to add another parameter.

This check box is to be set if there is a requirement to access a table for a v140 folder from a v130 folder. In this case, the structure of this table must be respect the limits for version 130 (no clob, nor blob...). Note : the inverse is possible without specify in the dictionary (access to a table 130 from a v140 folder).

The number of records field is used to define the size of the table in the database. This field is used to size the table being created or modified. This value is entered on the creation of the folder for each table created, from the result of the evaluation of the sizing formula. This formula itself uses variables where the values are entered in the folder parameters.

During the revalidation of a folder, the number of records is re-evaluated, but the result of the calculation is only re-written in this field if it is greater than the value found in the table ; thus, it is possible to modify this value, increase the size of the table and this value will be recovered in the case of folder revalidation. If however, it is decreased in size, it will be taken into account immediately if the table is manually validated with the help of the corresponding button, but once a revalidation of the folder is carried out, the calculated value that is greatest, will be used in place of the entered value. This behaviour has been defined for security reasons : for performance reasons and ease of use, it is in fact better to over size a table rather than under size it. It is not necessary to protect the modification of this field with an activity code.

This check box is used to define the method used, in Crystal Report, for the translated texts in this table.

• checked : method coming from version 140, generation of the texts in the sequential files textrafra, textraeng, etc used in the Crystal report by the function X3TranslatedText.
• not checked : method created in V5, no file generation. The Crystal report acting on this table uses a view.

It is a parameterization field that does not require an activity code to protect it. Needs to be in coherence with the technique used in the Crystal report linked to the table. For new texts to be translated, it is advised to check this box, to use the view method and thus not overload the sequential files.   

This check box is used by the return to zero utility for the movements, which is used to re-zero the movements in a folder (which is used to guard the "fixed" data - products, business partners, accounts, BOMs.... -arising from an entry deleting the movements, documents, invoices, stock movements, WO... - linked by example to a test of a real startup). When this box is checked, the re-zero utility erases all the lines in the table. One of these flags is found in each field (when a field must be reset to zero without the table being emptied).

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).

This field is used to class the tables according to a typology used by the folder management. It can take the following values:

• Application: this corresponds to the default case (standard tables for a folder). These tables are present or not according to the activity code and module management.

• Supervisor  : the table then becomes part of the supervisor data managed in each folder outside of the dictionary (users, operation logs, attachments...). It is always present.

• X3 System : the table is part of the general data not attached to a given folder (tables linked to the folder management, to the batch server...). These tables are only present in the supervisor folder, this folder can take various names according to the installed product: for example X3, PAYE, GX. They are therefore common to all the folders. They are therefore not concerned by the types and copy options because they are never copied.

• Dictionary : the table, which is always present, forms part of the dictionary in each folder (reports, actions, screens, data types...). The folder management takes into account in a specific fashion the tables in the dictionary during update management (dictionaryfusion procedure). Again, the Copy type and Copy option fields are not involved with this type of table.

• Internal : no table of this type is shipped and this type is only used in the software development process and must not be used.

It determines if the creation of the table implies a transfer of the data between the reference folder and the folder to be created during the creation of the folder. This parameter can take the following values :

• No copy : the table is created clear of all data.

• Automatic copy  : the data in the table are recovered from the data existing in the source folder (the reference folder by default).

• Conditional copy : the table data is copied by logical groups. A group is created by a choice from local menu number 26, entered in the field Copy option. A list of tables is attached to each group. Amongst the groups, the Users and profiles, Miscellaneous tables, Sequence numbers, Parameters, General chart of accounts are found… On the creation of the table, Yes or No is selected.

The copy option is associated with the copy type in the case of a conditional copy.

You can decide which data from several table subgroups are copied from the source folder to the destination folder.

This checkbox is used to define the copy method in the dictionary validation:

• checked : It means that a filter is applied to the legislation field of this table or of the "master" table.
• not checked : No additional filter will be applied.

When validating the folder, only the data corresponding to the managed or empty legislations will be recovered by the validated folder.

This option will also be used in the legislation copy function.

Internal use for the industrialization of a version. This parameter can take the following values:

•  Not delivered: These tables are not generated in the X3 folder.
• Delivered empty: Delivery of the table without data.
• Delivered with indus data: Delivery of the table with the data of the indus folder (setup: styles, import templates, etc.).
• Common data: Delivery of the table from the X3REFFRA reference folder.
• Data by country: Delivery of the table from the X3REFxxx folder (xxx: language chosen upon installation on the customer's work station.

These fields are used to enter the name of the two fields in the table. They are used to identify the fields that have a title present in the table. This information is used to give an automatic display of the title of a record where the key is known (screen management, cancellations, code changes...)

Define the field name for the table as it will be expressed in the software. A field with the name FIELDNAME defined in an ABV abbreviation table can be accessed using the [F:ABV]FIELDNAME syntax.

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

In the database, each zone corresponds to one or more fields, according to whether or not the zone is sized. The corresponding fields are called FIELDNAME_0, FIELDNAME_1, FIELDNAME_2.

To enter and display the corresponding field on a screen, it is given the same name in the screen dictionary. The screen and the table will be used simultaneously in object management.

The field type is defined here, which characterize the entry format (numeric, any characters, date...) and the coherence controls (reference to a table for example).

The principal generic types that exist :

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

But many other predefined types exist. They often make reference to an annex table (currency, country...). A dedicated dictionary is used to described them and they can be selected using the F12 key that is used to obtain the list.

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.

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

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

Title for the field over three lengths : short title being limited to 12 characters, normal title at 20 characters and the long title at 35 characters.

These titles are translatable texts and they are stored in a dedicated table, ATEXTE. When a new text is entered, after creation of a section, a window appears whose title is Text. There is then a choice between the following three options :

• consider that the entered text is a new text and integrate it in the database by assigning a new number. This number must be greater than 50,000 (because it is a custom/specific text).
• search for a text close to that entered in order to select the same. A selection will then open for this.
• cancel the text entry.

When in modification mode for a text already entered there is also an additional choice of displaying in the window, where the title is presented Text nnn (nnn being its number). Indeed, it is then possible to modify the text whose number is given, to conform with the text that is being entered (this modification is made wherever the corresponding text is used, once the corresponding screens have been revalidated).

In addition, it is possible to call, using the right button on the mouse, the Abbreviation function that is used to select one of the standardised abbreviations defined when a title is too long (the use of abbreviations must be avoided as far as possible to facilitate the readability of the screens).

Title for the field over three lengths : short title being limited to 12 characters, normal title at 20 characters and the long title at 35 characters.

These titles are translatable texts and they are stored in a dedicated table, ATEXTE. When a new text is entered, after creation of a section, a window appears whose title is Text. There is then a choice between the following three options :

• consider that the entered text is a new text and integrate it in the database by assigning a new number. This number must be greater than 50,000 (because it is a custom/specific text).
• search for a text close to that entered in order to select the same. A selection will then open for this.
• cancel the text entry.

When in modification mode for a text already entered there is also an additional choice of displaying in the window, where the title is presented Text nnn (nnn being its number). Indeed, it is then possible to modify the text whose number is given, to conform with the text that is being entered (this modification is made wherever the corresponding text is used, once the corresponding screens have been revalidated).

In addition, it is possible to call, using the right button on the mouse, the Abbreviation function that is used to select one of the standardised abbreviations defined when a title is too long (the use of abbreviations must be avoided as far as possible to facilitate the readability of the screens).

Title for the field over three lengths : short title being limited to 12 characters, normal title at 20 characters and the long title at 35 characters.

These titles are translatable texts and they are stored in a dedicated table, ATEXTE. When a new text is entered, after creation of a section, a window appears whose title is Text. There is then a choice between the following three options :

• consider that the entered text is a new text and integrate it in the database by assigning a new number. This number must be greater than 50,000 (because it is a custom/specific text).
• search for a text close to that entered in order to select the same. A selection will then open for this.
• cancel the text entry.

When in modification mode for a text already entered there is also an additional choice of displaying in the window, where the title is presented Text nnn (nnn being its number). Indeed, it is then possible to modify the text whose number is given, to conform with the text that is being entered (this modification is made wherever the corresponding text is used, once the corresponding screens have been revalidated).

In addition, it is possible to call, using the right button on the mouse, the Abbreviation function that is used to select one of the standardised abbreviations defined when a title is too long (the use of abbreviations must be avoided as far as possible to facilitate the readability of the screens).

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.

Code for a table linked to that modified by means of the current field. For the data types making reference to an object, this field is assigned automatically.

This field is only entered if a linked table has been specified. If this field is not assigned, the link is made directly from the field in the current line (which supposes that the primary linked table key is not the only component). In the case where the primary key possesses several components the link expression must be indicated under the form : comp1;comp2;comp;... where comp1,comp2, comp3,... are the components of the key.
Here are a few examples of possible expressions :

• if a field MYFIELD is controlled in one of the miscellaneous tables (for example the table number 43), the value 43; MYFIELD will be placed in the column Table linked to the value ATABDIV and in the Link expression column.
• if a field MYFIELD is indexed, the expression can use the index variable that can vary between 0 to dim(MYFIELD)-1. Classic examples: the multiple statistics groups (products, suppliers, customers...) whose key structure is the group number followed by a statistics code. The key expression is then written in the form index+1; MYFIELD (index).
• If the key component is a global variable, it is necessary to specify the [V] class before the name of this variable.

This field is linked with the checkbox Copy legislation.

In effect, it specifies the field containing the legislation, or the field linking up to the master table that contains the legislation.

During the cancellation of a line in the linked table (source table), a particular action can be carried out on the table that is currently being modified or created (destination table). This action is defined by the code below :

• Blocking : Cancellation of the record in the source table is impossible if a linked exists with the table currently being defined.
• Deletion : Deletion of the records in the table currently verifying the link with the source table.
• RAZ: Re-zeroing of the field in the table currently verifying the link with the source table.
• Other : No particular process is carried out.

This field is used to indicate if the coherence verification utility must verify the link.

The field Mandatory is used to define if the field can be empty or if it is mandatory that it contains a value (not empty). An empty field may be a string with an empty length, a null numeric value, a local menu value equal to zero (no choice carried out) or an empty date [0/0/0].

When the field is a key in a table, if the cancellation control is set to the RTZ value, the link cannot be mandatory (because the cancellation can erase the value of the key field) : the Mandatory field is then automatically set to No.

This field corresponds to the name under which the index is known in the database. A standard is used in all the tables in the application : it consists of naming the keys by using the abbreviation for the table, 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.

This is where the list of fields that make up the index are entered. The fields are separated by a '+' if there are several fields (for example, in the sites table FACILITY there is a key whose definition is LEGCPY+FCY).

A descending sort field is preceded with the sign "-" (warning, it is used in the left list).

The first field that makes up the index is without a sign and is therefore ascending.

This column is used to define whether the key can or cannot accept homonyms.

Use this column to define a default clustered index provided by Sage.

If you select No, a default clustered index is not defined.

If you select Yes, this is the clustered index used.

To change the clustered index, you need to define a Specific cluster index or select the Clustered index deactivated check box to not to use a clustered index.

This value can be modified by patch.

This default setting does not apply if you already set up a clustered index locally in the configuration file or in the Specific clustered index field.

Select Yes to define this index as the clustered index. Only one index per table can be selected.

A clustered index is a specific type of index that reorders how records are physically stored in a table.

This is a special setting that can never be modified by patch.

If this field is not assigned, the index will always be present. If this field corresponds to an inactive activity code, the index will not be generated.

It is possible to add a specific/custom key by an activity code starting with X, Y or Z.

Use this column to disable the clustered indexes for the table. The potential index delivered by Sage in the Default clustered index column is disabled, even if you did not define a clustered index in the column dedicated to this setup.

Select this check box to disable the clustered index.

A text describing the technical configuration of the table in the database is entered in this section. This text is recorded in a file "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.

These three check boxes are used to define the operations to be logged for the records in the table :

• Creation
• Modification
• Deletion

Note: Warning for the "grid" and "combined" objects that do not carry an update of the record but operate by deletion and creation of the record.

makes it possible to have an automated notification by Workflow.

A Workflow batch task triggers a Workflow event for each line of the AUDITH table where the Workflow status is "to be processed", then update the status to "processed".

It is used for the incremental synchronization of the BI fact table. It is only present if the ABI activity code is active.

Update by the generation of the dataware according to the type of update planned for the table fact. According to the rules of BI synchronization, if a fact table is updated upon incremental synchronization from an X3 table, this table is flagged "AUDIT BI".

One of the keys for the table is specified here. It is preferable that it is without homonym, to facilitate the identification of the record in the read of the audit.

If this field is not entered, the primary key is used.

List of fields to be audited. Do not enter this grid, if a log of only the operations carried out on a record is required, without a log of the contents of the fields.

There will be a log for a modified record, if at least one of the conditions on the fields is verified by the new values of the field ; whilst on the creation or deletion of the record, there will systematically be a log.