SYSPRO can be implemented as a Relational Database Management System (RDBMS) or a C-ISAM indexed file architecture.
The system was engineered with the understanding that while not all companies require a full Relational Database Management System, they do require a database structure that provides a fast access method.
C-ISAM (Indexed Sequential Access Method) is a file management system that allows records to be accessed either sequentially (in the order they were entered) or randomly (with an index). Each index defines a different ordering of the records.
Information is data arranged or presented in such a manner that they yield an understanding not available from any single data element.
| Data element | A Data Element (data field) is a characteristic that is common to a particular entity. |
| File | A File is an organized collection of records or the storage device in which these records are kept. Each file is made up of a file structure. |
| File structure | A File Structure is the manner in which records are stored within a file (e.g. sequential, random, or index-sequential). |
| Data file | A Data File is a collection of related data records organized in a specific manner (e.g. one record for each inventory item showing product code, unit of measure, production costs, transactions, selling price, production lead time, etc.). |
| Record | A Record is a collection of data fields arranged in a predefined format or a set of related data that a computer program treats as a unit. |
| Field | A Field is a specified area of a record used for a particular category of data. |
| Data | Data is any representations such as alphabetic or numeric characters to which meaning can be assigned. |
In SYSPRO, you can choose whether to implement a Relational Database Management System (RDBMS) or a C-ISAM indexed file architecture.
The Relational Database Management System is responsible for:
-
Maintaining the relationships between data in the database.
-
Ensuring that the data is stored correctly – that the rules defining the relationships between data are not violated.
-
Recovering all data to a point of known consistency in the event of a system failure.
In SYSPRO, if you define your company to be an SQL company then your data is stored in a database. Each database consists of various tables.
A Table defines a collection of rows that have associated columns. Tables are comparative to files in a C-ISAM system in the same way that rows compare to records, and columns to fields.
SYSPRO's SQL data is stored in tables. The table names consist of a module identifier and the Table name.
For example:
Our Company Id is 0. This identifier is held in the Sysprodb database. The SysproAdmin table is found in the Sysprodb database and contains a list of the Company, the Database Name and the Collation Name.
Every module has its own unique identifier as indicated in the table below:
| Module | Module Identifier |
|---|---|
| Accounts Payable | APS |
| Accounts Receivable | ARS |
| Inventory | INV |
| Company Control | ADM |
| Company Tables | TBL |
| Accounts Payable Table Name | Description |
|---|---|
| APSMST | A/P Supplier Master Table |
| APSINV | A/P Supplier Invoice Detail Table |
| APSBNK | A/P Bank Table |
| APSBRN | A/P Supplier Branch Table |
| Accounts Receivable Table Name | Description |
|---|---|
| ARSMST | A/R Customer Master Table |
| ARSINV | A/R Customer Invoice Table |
| ARSMOV | A/R Customer Movement Table |
| ATSSTD | A/R Sales Transaction Detail Table |
| Accounts Receivable Table Name | Description |
|---|---|
| INVJND | Inventory Journal Detail Table |
| INVMOV | Inventory and Sales Movements Table |
| INVMST | Inventory Master Table |
| INVFOR | Inventory Foreign Prices Table |
| INVPRC | Inventory Selling Prices Table |
| INVWHS | Inventory Warehouse Table |
SYSPRO programs sequence the tables in our primary key or alternate sequence.
For example, the Inventory Master file is sequenced by Stock Code, and the Inventory Warehouse file is sequenced by Stock Code, Warehouse.
Within the database the data types are pre-defined as Alphanumeric, Numeric or Date formats.
| Alphanumeric Columns | When defining an alphanumeric column you just need the length of the column. SYSPRO stores these as a character data type. |
| Numeric Columns | When defining a numeric column you just need the number of integers and decimals. These are defined as decimal data types. |
| Date Columns | When defining a date column you use the date time data type. |
The majority of SYSPRO 7 character data is stored in columns with a data type of CHAR or VARCHAR and these columns are affected by the collation when comparing data or sequencing results. SYSPRO requires that all its databases have a case sensitive collation. This includes collations known as ‘Binary’.
When you add a SYSPRO 7 database you should ensure that the database collation is either ‘Binary’ or ‘Case Sensitive’. See the following examples:
| Collation Name | Remarks |
|---|---|
| Latin1_General_BIN | A Binary collation - providing high performance and covering English and many Western European languages. Used for backward compatibility between previous Binary collations. |
| Latin1_General_BIN2 | A Binary collation - providing high performance and covering English and many Western European languages. A newer Binary collation available in more recent versions of SQL Server. |
| Latin1_General_CS_AS | A Case Sensitive and Accent Sensitive collation - covering English and many Western European languages. |
| Chinese_PRC_CS_AS | A Case Sensitive and Accent Sensitive collation - covers the simplified Chinese language used in mainland China and Chinese speaking territories. Can also store English data. |
Data in a C-ISAM system is stored in files.
A File is an organized collection of records or the storage device in which these records are kept. Each file is made up of a file structure.
A File Structure is the manner in which records are stored within a file (e.g. sequential, random, or index-sequential).
A Data File is a collection of related data records organized in a specific manner (e.g. one record for each inventory item showing product code, unit of measure, production costs, transactions, selling price, production lead time, etc.).
The file size limit for a C-ISAM file in SYSPRO is 4 GB.
In SYSPRO, C-ISAM data is stored in files and the data file names are made up of a Company Id, a module identifier, the file name and an extension. For example:
Our Company Id is 0; therefore all our file names will start with this character.
Every module has its own unique identifier and here are some examples:
| Module | Module Identifier |
|---|---|
| Accounts Payable | APS |
| Accounts Receivable | ARS |
| Inventory | INV |
| Company Control | ADM |
| Company Tables | TBL |
| Accounts Payable Table Name | Description |
|---|---|
| APSMST | A/P Supplier Master File |
| APSINV | A/P Supplier Invoice Detail File |
| APSBNK | A/P Bank File |
| APSBRN | A/P Supplier Branch File |
| Accounts Receivable Table Name | Description |
|---|---|
| ARSMST | A/R Customer Master File |
| ARSINV | A/R Customer Invoice File |
| ARSMOV | A/R Customer Movement File |
| ATSSTD | A/R Sales Transaction Detail File |
| Accounts Receivable Table Name | Description |
|---|---|
| INVJND | Inventory Journal Detail File |
| INVMOV | Inventory and Sales Movements File |
| INVMST | Inventory Master File |
| INVFOR | Inventory Foreign Prices File |
| INVPRC | Inventory Selling Prices File |
| INVWHS | Inventory Warehouse File |
Extensions of files are dependent on the operating system. In a Windows environment, the extension would be DAT, whilst on Unix systems there would be no file extension.
Therefore, the Inventory master file name, in our example would be: 0INVMST.DAT in Windows.
Each SYSPRO module contains many files; each of these files containing related information. Almost all files used by the SYSPRO programs are indexed files.
Alphanumeric columns are the simplest columns to define, comprising between 1 and 100 characters, inclusive. Any character can be stored, including letters (A-Z and a-z), numbers (0-9), punctuation and special characters (!",’&+-*/@). Alphanumeric columns only require the position and length of the column to be defined.
All key columns such as stock code, customer, supplier, order numbers etc., that can be configured as alphanumeric or numeric within the Setup options, are defined as alphanumeric within the standard data dictionary. The only problem with this is that editing of the column is required if it is numeric.
For example
If the customer is defined to be numeric, then a customer code of ‘0001234’ is printed as ‘0001234’. If you want the customer code to be printed as ‘ 1234’ then you can achieve this (despite being defined as alphanumeric within the data dictionary) by selecting the option: Suppress leading zeros.
If your company defines one or more of these key columns as numeric, then it is advisable to add a new column to the data dictionary defining the column as numeric. This applies especially if you use these key columns as run time prompts.
When defining a date within the data dictionary, there are currently thirteen formats to choose from:
| Date Format | Description |
|---|---|
| 1 | Standard SYSPRO date format (either DDMMYY, MMDDYY or YYMMDD) |
| 2 | DDMMYY |
| 3 | MMDDYY |
| 4 | YYMMDD |
| 11 | Standard SYSPRO date format edited (either DD/MM/YY, MM/DD/YY or YY/MM/DD) |
| 12 | DD/MM/YY |
| 13 | MM/DD/YY |
| 14 | YY/MM/DD |
| 21 | Standard SYSPRO date format edited (either DD/MM/YYYY, MM/DD/YYYY or YYYY/MM/DD) |
| 22 | DD/MM/YYYY |
| 23 | MM/DD/YYYY |
| 24 | YYYY/MM/DD |
| 31 | CCYYMMDD |
![[Note]](images/note.png)
|
|
|
All dates in SYSPRO are stored as CCYYMMDD (Format number 31). |
|
Numeric columns are stored in a special format within SYSPRO C-ISAM tables. The numbers are stored with an implied decimal place (i.e. the programs assume a decimal position).
For example:
If a value were held as 8 digits to the left of the decimal, and 2 decimal places (8.2), then the value 123.5 would be stored as the ten digits 0000012350.
The programs assume that the decimal place is between digits 8 and 9. Therefore, when defining a number stored using this method, the number of decimals is required together with the length of the column.
SYSPRO format numbers do not store the sign as a separate character, but instead change the last digit according to a table.
For example
If a value of 123.50 Cr. (123.50-) is stored in the same 8.2 column as the previous example, the 10 characters becomes 000001235p. Note: The last 0 has become a p.
The following table indicates an example of how negative values are stored in a C-ISAM table if they had a value of 5.2.
| Normal Format | SYSPRO C-ISAM Positive | SYSPRO C-ISAM Negative |
|---|---|---|
| 123.10 | 0012310 | 001231p |
| 123.11 | 0012311 | 001231q |
| 123.12 | 0012312 | 001231r |
| 123.13 | 0012313 | 001231s |
| 123.14 | 0012314 | 001231t |
| 123.15 | 0012315 | 001231u |
| 123.16 | 0012316 | 001231v |
| 123.17 | 0012317 | 001231w |
| 123.18 | 0012318 | 001231x |
| 123.19 | 0012319 | 001231y |
When defining a numeric column, selecting the option SYSPRO FORMAT NUMBER indicates that numbers have an implied decimal point and that negative numbers adhere to the above table.
If SYSPRO FORMAT NUMBER is not selected, then the number format is assumed to be in the same format as normally entered within the system at a numeric prompt. It has an embedded decimal point and sign, if required.
For example
The value could be ‘123.45-‘, the sign may be trailing or leading.
An indexed file, often called an ISAM (Indexed Sequential Access Method) file, consists of two physical files. Collectively, the two portions are known as an indexed file.
-
A Data File (.DAT on Windows)
This is the data portion, containing the actual records of information. Each of these records has a portion called a primary key that must be unique within the file.
For example
In the Inventory Master file the Stock code will be unique. Although operating system commands can be used to view the data portion, care should be taken when doing this, as deleted records may still be visible.
-
An Index Portion (.IDX on Windows)
This is the index portion, containing an alphabetical list of all the keys in the data portion. This file is in a special format and should not be viewed (or edited) using normal operating system commands.
Index files can be created through a software application or by using the Report Writer conversion utility, which converts a sequential file to an index file.
If using the Report Writer or the RW Sequential to Index Converter program, you must first define the format of the file in the Browse on Data Dictionary Tables and Browse on Data Dictionary Columns setup programs. The programs will then know how the file is structured.
Once the index file is created, you may review the contents by adding a report through Create Report. The data is displayed in order of sequence.
The first characters of the sequential file become the key in the new indexed file. The number of characters will depend on the key length. Make sure the sequential file is structured accordingly.
The index file may be used exactly as any other standard SYSPRO file.
Index files can be reindexed using the Reindex User File function of the Reindex Data Files program.
SYSPRO programs can access an indexed file in two distinct ways:
- By sequentially reading through the file (using the alphabetical list of keys) and retrieving each record in turn. The Report Writer processes the primary file in this way.
- By using the key to directly read a required record without sequentially processing the file to find the required record. The Report Writer accesses linked files in this way.
Sequentially processing the data portion of an indexed file (by defining the data portion as a sequential file) should not be done for the following reasons:
- There may be deleted records in the data portion of an indexed file
- The data portion of an indexed file can be in any sequence.
Sequential files consist of a single physical file containing the actual records to be processed (in the sequence to be processed). As there is no key (and therefore no index) the records can only be read sequentially from the first record through to the last record. Sequential files are normally created through one of the following methods:
- Manually by using a text editor.
- By a third party software application.
- By using the Report Writer extract option.
In the SYSPRO Report Writer, sequential files must be defined as the Primary table, because it is not possible to access a record in the file without first scanning the entire file up to the required record. You may re-sequence sequential files, but they may not be updated.
Only User Defined files are normally sequential and therefore, no sequential files are defined in the standard SYSPRO Data Dictionary.
It is possible to convert a user defined sequential file to be a user defined index file, using the RW Sequential to Index Converter program. If the extract facility within the report writer is used to create a file, the extract file will be a sequential file.
The extract file does not need an extension (i.e. .DAT)
If you are converting the sequential file to an index file, then make sure that no headings or messages exist (i.e. if the extract option is selected, these may be turned off in the Report Definition under the Options, messages and extract options).
If using the Report Writer or the RW Sequential to Index Converter program, you must first define the format of the file in the Browse on Data Dictionary Tables and Browse on Data Dictionary Columns setup programs. The programs will then know how the file is structured.
You may review the contents of a sequential file by adding a report using the Create Report program. The data will be displayed exactly as it is found in the sequential file, in order of sequence, unless re-sequenced.