Managing Tables

Use Tables (GESATB) under Tables in the Development > Data and Parameters block to view and manage tables inside the Sage X3 interface. This allows you to define the table structure and indexes. You can create or modify tables in the database by describing their characteristics in the data dictionary for the folder. Activity codes can be used to inhibit certain tables and/or indexes.

Note: Tables should never be modified outside of the Sage X3 Developer’s Toolkit.

• At the Table code field, a table is identified by its name and also has an abbreviation. These identifiers must be unique.

• In addition to the table structure, the following can also be defined in the data dictionary:

• Links to other tables (referential integrity links). These links are used by the cancellation utilities, by the import/export functions, and other tools of this type.

• Information proposed by default in the screens created from these tables.

• When creating a table, the following limitations exist:

• Keys are limited to 256 characters and 16 fields.

• The number of open tables is limited to 255.

• A record under SQL Server is limited to 8060 bytes. Under Oracle, there is no limit; but the Sage X3 limit is fixed to 32 K.

• There is a limit of 255 fields per table and 512 columns.

• The name and abbreviation of the table must be unique; no two tables can have the same name.

• A table can be managed with the help of an object, providing that the necessary screens are created.

General tab

Use the General tab to enter or view general information relative to the table and its management.

• The Activity code field is used in conjunction with the Module field to determine whether or not the table will be created in the database of the folder.

• The title fields are used to identify the fields that have title in the table. This information is used to automatically display the title of a record where the key is known.

• The Database type field is the base type for which the table must be generated.

• A table starting with X, Y, or Z is considered to be specific/custom and is not affected by a version change. These activity codes can be placed at the line level.

• Note the following dependencies of modules:

• Supervisor and Common data: Must be installed as a minimum. The Stock, Development, and General accounting modules can then be installed after these.

• Stock: After installing the Stock module, the Sales, Purchasing, and Production modules can be installed.

• Sales: After installing the Sales module, the CRM and Commercial actions can be installed.

• General accounting: After installing the General accounting module, you can install Fixed Assets and AR/AP.

Table management

• If Folder is selected as the base type, a table is generated in the folder record for all the files of the folder.

• The Number of records field is used to define the size of the table in the database. This value can be used to increase/decrease the size of the table in the database; however, if the actual physical size of the table is larger than the entered amount, then the physical size is used.

• This value is entered when the folder and tables are created resulting from the evaluation of sizing formulas.

• The sizing formulas use variables for which the values are entered in the folder parameters.

• During revalidation of a folder, the number of records is re-evaluated, but the result is only re-written if it is greater than the value found in the table.

• The Generate translated text check box is used to define the method used in Crystal Reports for the translated text in this table.

• If the check box is selected, text is generated in sequential files textrafra, textraeng, etc. and used in the Crystal Report by the function X3TranslatedText.

• If the check box is cleared, there is no file generation. The Crystal Report acting on this table uses a view.

• The Reset to zero check box is used by the Return to Zero function, which removes all transactional data, but allows static data like product numbers, descriptions, customers, and addresses to remain in their files. Performing this allows you to start with a clean system in preparation for the first day of production, for example.

• Select the Open access check box to restrict or allow access to the data table within the folder.

• If this check box is selected, all the folders for the solution can access the table to read or write the data in the table.

• If this check box is cleared, access to this table is made as a function of the rights to the call folder, which can be authorized or not for read/write.

Folder management

• The Table type field is used to class the tables according to a classification used by folder management and can have the following values:

• Application: This type 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 becomes part of the supervisor data managed in each folder outside of the dictionary (users, operation logs, attachments, etc.).

• Sage X3 System: The table is part of the general data not attached to a given folder (tables linked to the folder management, batch server, etc.).

• Dictionary: The table, which is always present, forms part of the dictionary in each folder (reports, actions, screens, data types, etc.). The folder management takes into account, the tables in the dictionary during update management. The Copy type and Copy option fields are not available for this table type.

• Internal: No table of this type is shipped and this table type is only used in the software development process.

• The Copy type field 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 with no data.

• Automatic Copy: The data in the table is 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 the local menu 26, entered in the Copy option field.

• The Copy option field is available if the Conditional Copy option is selected at the Copy type field. Tables can be copied from the reference folder during the initialization of the generated folder if needed.

• The Delivery type field is used internally. The options allow you to define the table for delivery or not and with data or not.

Columns tab

Use the Columns tab to add or modify columns (fields) in a table.

• You can enter the title using three different lengths. The normal title is 20 characters, the abbreviated title is 12 characters, and the long title is 35 characters in length.

• In the Column column, the field name for the table is defined.

• The Menu column defines the local menu number associated with the field being defined.

• You can use standard fields, which are automatically updated if they exist in object management. The standard fields include the following:

• CREDAT and UPDDAT: These fields are the creation and update dates.

• CRETIM and UPDTIM: These fields are the creation and last update times.

• CREUSER and UPDUSR: These fields are the user codes associated with the creation and last update fields.

• EXPNUM: This field is a sequential number used to date the records with respect to the import/export module (that makes it possible to only export that which has been updated since the last export).

• The ENAFLG field is also inserted in each table. This field corresponds to the Active check box.

• The Type field to define the type of field, such as alphanumeric, integer, decimal, etc. Many pre-defined type exist. Below are some of the main generic types:

• A: Alphanumeric

• C: Short integer

• L: Long integer

• DCB: Decimal

• D: Date

• M: Local menu

• MM: Local menu with filter

• ACB: CLOB

• ABB: BLOB

• Note: To view all the data types available, use the Data types function under Tables in the Development > Data And Parameters block. For more information, see the Data Types section.

• The Length field defines the length of the field when the field uses a generic data type where the length is not fixed, as in the case of types A (alphanumeric) and DCB (decimal).

• The Dim field is used to enter the dimension of a field. A dimension is the maximum value of an array.

• The Key Work Help field provides help text that is linked to the current field.

Understanding field options

You can enter options for the field, such as centered, aligned to the left, etc.

• For an alphanumeric field, the following options are possible.

Option	Meaning
A	Uppercase letters only: The characters entered are automatically transformed to uppercase.
#	Numbers only
T	Tokenization: Keywords are understood in the entry of a calculation formula.
D	No key word are authorized
C	Format the key
z	Non-displayed comments
X	Entry masked (passwords)
U	File format: (You can choose a file on the server or the local workstation. The path and file name are recovered.)
M	File format: (You can choose a file on the server or the local workstation. The path and file name are recovered.)
L	Complete length for the field.

• For a numeric type field, the following options are possible:

Option	Meaning
z	Zero values not displayed
+	Only positive numbers
3	Separation if groups of 3 integers
F	Floating numbers: Used to provide precise control at the level of decimals. In the .srf file, a decimal section is generated whose length is the minimum integer (integer - 1) or to (28 - integer).
0	Display of 0 before numbers
-	Display sign +
D	Sign after the number

• For a date type field, the following options are possible:

Option	Meaning
Z	Blank data authorized
z	Blank value not displayed
1	Date in the form: 18-01-11
2	Date in the form: 18-01-2011
3	Date in the form: 18 JAN 2011
4	Date in the form: 18 January 2011
5	Date in the form: 18-01
6	Date in the form: 01-02
7	Date in the form: 01-2011

• For all field types, the following options are possible:

Option	Meaning
c	Display of a field in color according to the pcolor variable (managed only on the fields in grid block).
W	Contents of the field displayed at the bottom of the screen (only on the displayed screen).

Linked tables and expressions

• The Linked table field displays a code for a table linked to that modified by the current field. For the data types referencing an object, this field is assigned automatically.

• The Link expression 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 has several components, the link expression must be indicated under the form: comp1;comp2;comp3;… (where comp1,comp2, comp3 are the components of the key).

• If a field MYFIELD is controlled in one of the miscellaneous tables (such as table number 43), the value 43;MYFIELD is placed in the Linked table column 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 and dim(MYFIELD)-1. Examples include: the multiple statistics groups (products, suppliers, customers etc.) whose key structure is the group number followed by a statistic code. The key expression is then written in the form index+1;MYFIELD (index). If the key component is a global variable, you must specify the [V] class before the name of the variable.

• During the cancellation of a line in a linked table (source table), an action can be carried out on the table that is currently being modified or created (destination table). This action is defined from the following:

• Block: Cancellation of the record in the source table is not possible if a link exists with the table currently being defined.

• Delete: Deletion of the records in the current table, verifying the link with the source table.

• RTZ: Set the field in the current table to zero, verifying the link with the source table.

• Other: No particular process is carried out.

• The Verification field is used to indicate if the verification utility must verify the link.

• The Mandatory field is used to define if the field can be empty or if it is mandatory that it contain a value. An empty field can be a string with an empty length, a null numeric value, a local menu values equal to zero, or an empty date [0/0/0].

• When the field is the key field 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 set to No.

• The RTZ field makes it possible to reset the field to zero when used in conjunction with the Reset to zero utility.

Index tab

Use the Index tab to define the different indexes defined in the table. The first index is the primary key of the table.

• The Index code field corresponds to the name under which the index is known in the database.

• The Index descriptor is where the list of fields that make up the index are entered. The fields are separated by a plus sign, if there are several fields.

• The Duplicates column is used to define whether the key can or cannot accept homonyms.

• If the Activity field is not assigned, the index is always present.

• If this field corresponds to an inactive activity code, the index will not be generated.

• You can add a specific/custom key by an activity code starting with X, Y, or Z.

Configuration file

• Use the Configuration file section to describe the technical configuration of the table in the database.

• This text is recorded in a .cfg file in the FIL directory of the application.

• This file is used by the valfil instruction.

Audit tab

Use the Audit tab to track changes to records. Settings are used to log, by means of automatically generated database triggers, the insertions, deletions, and modifications carried out on a table. The “before” and “after” values are stored, if needed, for certain fields. The modification of the record is logged if one of the conditions on the field has been verified. The “trigger” technique is used to log the operations even if a program other than Sage X3 is used.

• Audit settings are not shipped with the standard software. This tab is therefore reserved for customizations and no protection is required by activity code X, Y, or Z.

• Use the check boxes in the Type of audit section to define the operations to be logged for the records in the table.

• Select the Workflow check box to automate a workflow notification process.

• In the Tracking key section, specify the key for the table. You should not use a homonym in order to aid the reading of the record in the audit.

• Sage X3 triggers create SQL triggers in the database (either SQL Server or Oracle). This means as long as the trigger is active, regardless how the data is manipulated, SQL will audit the changes. In Sage X3, these are referred to as audit triggers.

• Note: Audit triggers, particularly on tables that have a high change rate, may affect performance.

• Clicking Validation creates the SQL triggers.

Workflow

• At the Workflow field, a workflow batch task triggers an event for each line of the AUDITH table where the workflow status is to be processed.

• The Audit BI check box is used for the incremental synchronization of the BI facts table and is active only if the ABI activity code is active.

Fields audited

• The Fields audited section lists the fields to be audited. Do not enter this grid if a log of only the operations carried out on a record is required.

• A log is generated for a modified record if at least one of the conditions on the fields is verified by the new values of the field. A log is generated if a record is created or deleted.

• The audit is globally set by the activity code AUDIT.

• The log is stored in two dedicated tables: AUDITH and AUTITL.

• The following cannot be audited:

• The audit tables themselves.

• The fields of type CLOB and BLOB.

• The fields whose data type is AXX.

• The SUBTRIGGER process contains the description of the triggers as a function of the database.

• The SPETRIGGER process is reserved for customization in order to personalize their description, if required.

Validation

After saving changes to a table or creating a new table, you must click Validation. The validation creates the SQL triggers.


-

Sage X3 and Sage Software are trademarks of Sage Software, Inc. Microsoft SQL Server, Microsoft SQL Enterprise Manager and SQL Server Management Studio are registered trademarks of Microsoft Corporation. Oracle is is a registered trademark or Oracle Corporation. All other product names used here are trademarks or registered trademarks of their respective owners.

-