Automatically Display Description in Screen Grid

X3 has the ability to automatically display definitions in grids for certain fields. The following example, this technique is demonstrated for displaying the unit of measure short description for a given unit of measure value.

1. In X3, open the screen definition which contains the grid.

2. In the Field grid, locate the unit of measure row (in this example, PCU).

3. Locate the Type column. To the right of the column, click on the 3 vertical dots.

4. From the pop-up, select "Jump to".

3. Note that the "Data types" screen opens (Development > Data and parameters > Tables > Data types). Note that the type code is "UOM".

4. Note that in the "Convergence" section, the linked object is "TUN", which is the linked table "TABUNIT" (Table of units of measure).

4. Click the "Back" button to return to the screen definition.

5. In the screen definition, in the Field grid, on the same row as before, go to the "Link" column, open the drop-list and select "Short" for short description (if needed, select "Long" for long description).

6. Save the screen changes.

7. Run "Validation" and walk through the validation steps.

8. Exit the screen definition.

8. Open the screen in X3 and note that the "Description" column appears in the grid to the right of the UOM column.

9. Populate the screen fields and note that the "Description" column is automatically populated with the description of the displayed UOM value.

-

X3 Version

To confirm the X3 version through the UI, select: Administration > Utilities > Update > About

Click on the name of the production folder.

-

Excel Add-Ins

Sage X3 has multiple add-ins for use with Excel

Excel add-in for Sage

To install:

1. Log into Sage X3
2. Go to: Administration > Utilities > Install addins for Office. This will download "SyracuseOfficeAddinsSetup.EXE"
3. Run "SyracuseOfficeAddinsSetup.EXE"
4. Exit and re-open your Web browser

Excel add-in for Outlook

To install:

1. Log into Sage X3
2. Go to: Administration > Utilities > Install addins for Outlook. This will download "SyracuseOutlookAddinSetup.EXE"
3. (next steps uncertain)

Excel add-in for SEI

This is separate install. Install steps uncertain.

-

Working with Views

Use Views (GESAVW) in the Development > Data and Parameters block to create and modify views, by describing their characteristics in the data dictionary for the folder.

• Views allow you to do the following:

• Avoid opening a table in a folder, such as archived folders.

• Access data in several folders for report development.

• Use SQL Server functionality that was previously inaccessible, such as union or group by.

• Views are accessed like files with the [F:xxx] method. Views can be accessed using the formula editor and Crystal Reports. Programming relating to a view is similar to programming for a table (it has its own class [F]). The following instructions are available for the views:

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

• The view name cannot have the same name as a table in the dictionary. It is a unique identifier and can contain from 1 to 10 letters or numbers, but must start with a letter.

• You can create views on tables from different folders.

Query tab

Use the Query tab to write the query that will be used to create the view.

• The query must be written in the same programming language as the current database in the folder.

• It is preferable not to hard code the folder name into the query so that the query can be used with another folder when it is copied.

• The activity code and module are used in conjunction to identify whether the view will be created in the database of the folder.

• If fields submitted to an activity code or folder name need to be specified in the query, it is recommended that you use formulas.

• The Open access check box is used to restrict the access to the database table within the folder owning the table and certain authorized folders. Changing the setting at this field requires a re-validation of the table.

Fields tab

Use the Fields tab to define all the fields of the view. The description of the fields must be fully compatible between the Query tab and this tab (number, order, and field type).

• The compatibility check is performed during validation.

• These fields can exist in the table dictionary, although it is not mandatory.

• The query must be written in the same language as the current database in the folder.

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

Keys tab

Use the Keys tab to define all the keys available to sort the view.

• The Key code field corresponds to the name under which the key is known in Order By.

• A list of fields that make up the index is entered in the Key description field. Fields are separated by a plus sign.

• The standard naming convention for views consists of using the abbreviation for the view, followed by 0 for the principal key, by 1 for the second key, etc. For a customization, be sure to start the code with X, Y, or Z.

• When copying views to other folders, the Copy button only copies the view description to the other folder. You must validate this description in the other folder so the view can be created.

Validation

Click Validation in the Right panel to create the view in the database.

-

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.

-

Grouping Linked Tables

Use Data models (GESAWM) under Development setup in the Development > Data and Parameters block to define a group of linked tables (either directly or through cascade) by links of type (1,1) or (1,N) to a main table.

• The data model (group of linked tables) can then be used in the following:

• A workflow rule: Either because it features the only available context to describe the tables to be scanned (if using the Manual type), or to enrich the triggering context (if using the other types).

• An allocation rule: Where the model is compulsory, it defines the group of tables containing the fields on which the user allocation rule is based. The data model associated with a workflow rule and the data model associated to the allocation rule must be absolutely identical.

• The Template code identifies the current record.

• The Properties section specifies the data model is linked to a structure, template with index, setup template, and/or a workflow.

• The Main table field identifies the main table from which other tables are read using direct links or through cascade.

• The Linked table identifies the table linked to the original table.

• The abbreviation corresponds to the abbreviation under which the linked table is open.

• If there is no entry at the Abbreviation field, the default abbreviation for the table is used.

• It is particularly useful to enter an abbreviation if the table appears several times in the grid, because several linked lines in the same table need to be online simultaneously.

• The Origin table field identifies the main table at the origin of the link described on the current line. It may be the main table, or one of the linked tables on the previous lines.

• The Key field defines the table key code used to read the linked lines. By default, the first key of the table is used.

• The Type field takes on one of the following values:

• 1,1: Means that for each line in the table of origin, only one line in the linked table is read (this line is defined by the key expression).

• 1,N: Several lines in the linked tables can be read. They are defined by the key expression that can be incomplete if it is divided into several parts.

• The Link expression field is defined as one or more calculated expressions separated by a semicolon.

• Each expression is evaluated, and the result used to identify the value of the key used to perform the join.

• When multiple joins are authorized, only the first elements of the key can be given.

• In the expressions, constants can be used, as well as fields coming from tables previously defined in the link list.

-

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.

-

Table Maintenance

Use In lines (GMAINT) under Maintenances in the Development > Utilities block to modify the data in any table in the database without performing data validation or database integrity checking.

Warning: This function should only be performed by a developer. Access to this function should not be provided to unauthorized users. (Only users with the administrator profile ADMUSER should access this function.) Inappropriate use may corrupt the integrity of the data stored in the database.

• After selecting the folder and table to perform maintenance on, click OK.

• By default, the current folder is proposed; however, you can enter the name of another folder using the following syntax:

• Folder: Directly enter the name of the folder. In this case, the folder must be accessible on the same server and service.

• server@Folder: Enter the name for the server where the folder is located. An adxd service must work on the current service number to allow the connection.

• Server:service@Folder: This uses the same principle as server@Folder, but the service number can be different. For example, this gives the syntax server_01:1811@DEMO.

• Warning: These last two syntaxes assume the folder exists on the remote server. Entering an incorrect network name can result in a long wait time.

• This function is primarily used to view data within the Sage X3 database.

• Records can be modified, created, or deleted.

• Key values appear in the first section of the window, while all other fields are listed spanning across several tabs.

• The code in the header area is associated with each record.

• The table fields are displayed over several tabs (not all are necessarily filled in alphabetical order.

-

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.

-

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.

-