Sapphire Gateway II Automation - Activate Auto Sapphire Gateway II for Superior Data Handling

Sapphire Gateway II Automation Overview

SapphireOne supports the automatic operation of the Sapphire Gateway II functionality within your data file. You can find the SapphireOne Automated Gateway in the ‘Automatic Functions’ area by navigating to Utilities > Controls > Master Defaults > Sapphire Gateway page. Automatic functions can be scheduled to run at a specified time by selecting the ‘Run Gateway Script’ checkbox. Then, use the ellipsis button (…) to choose a script file to execute.

Never directly import gateway files into an active live data file. First, prepare a recent version of your production data file using the same build as the server, and initiate it in Single User Mode. Conduct a trial import with the intended data and meticulously review the outcome, ensuring particularly that the columns in the import file align correctly with the appropriate fields in your data file replica. After confirming the test import is accurate and everything appears in order, proceed to create a backup of the production (live) data file, and only then carry out the actual import. If discrepancies are detected, identify and rectify the issues in the import file, then re-attempt the import with a new copy of the production data file. Always prioritise testing before importing your data.

File Creation

• The Script file must not contain formatting and must be saved as a plain text file.
• The Table files are are tab delineated text files, saved with a spreadsheet program like excel. There are two methods to create the script files. The table files are interpreted as documented in the Sapphire Gateway II Tables article.

1. Use any Plain text editor (no formatting).
2. Use a spreadsheet program like Excel to create both the Script file and the Table files but when these are saved it is critical to select Save As, then select, Tab Delimited text. Excel can have issues with the treatment of dots and certain characters in table cells. If uncertain, look at the saved file with any plain (non-formatting) text editor and check for errors.

Script Execution Procedure

1. First place the script file and all of the table files in the same folder.
2. Once the script file is executed SapphireOne then reads the lines in the Script file in sequence.

• Each line in the Script file must be the name of a Table file in this same folder.
• Every Table file must be set up as seen in the Table files in the article Sapphire Gateway II Tables.       

Gateway Automation Procedure

1. Folder – First create a common folder where these files may be placed.
2. Script File – Next create and load the Script file into this new folder. It is basically a set of instructions containing the names of all the Table files that are to be run into this new folder.
   • Once the Script file has been loaded go back to Master Defaults and use the ellipsis button (…) button to select it. (See above in this article).
3. Table Files – Next create and load the Table files that are to be run. (The table files that are normally used in Gateway II).
4. Execution – On execution, the Script file selected in master defaults will be looked for, and the first line Table name read. SapphireOne will then look for this Table file in the current folder and run it.
   • If unsuccessful a sub folder will still be created called Gateway but no files will be created in it.
   • When a Table file is successfully run the resultant output will be placed in this Gateway folder.
   • If there are additional Lines/Table names, each line will be read and run until the end of file marker %%%% is found.
     Remember that for each line in the Script file pointing to a table file, there HAS to be a matching Table file for it to run in this folder!

When using Sapphire Gateway II directly the ‘%’ symbol is commonly used as a prefix to the Table file name to indicate to the user what is in the file. E.g. %Serial.txt.
Do not use this Table file naming convention when using the automated gateway function.

Use for example Serial.txt or ExportSerial.txt.
Sapphire Gateway II automation will not work with the % symbol as a prefix to the Table file name. Do not use any of the upper-case characters/symbols at all! Always check that the Table file name used in the main script file is complete including any file extension used E.g. Serial.txt

Each time the Sapphire Gateway II automation functions are run, the previous files will be over written. Care will have to be exercised to ensure that the data written out each day is copied elsewhere on the hard drive before it is overwritten with current data from the data base.

The Script File

The name of the Script file in the example is ExportAll.txt. You can use any name that is suitable for you. This should be located in its own folder somewhere that the server has access to.
The reason a folder is suggested is that if you have multiple files they will be grouped in the folder and be easy to find and manage.
This Script file will contain information that points to other Table files that have the commands that are to be run. From here you have 2 choices.

1. Have a Script file pointing to a single Table file containing multiple commands.
   E.G. The ExportAll.txt file will have just 2 lines in it:
   Serial.txt The name of the Table file.
   %%%% The end of file indicator.
2. Have a Script file pointing to multiple Table files each Table file having a single command in it.
   E.G. The ExportAll.txt would have a line for EACH Table file that is to be run in sequence.
   Serial.txt The name of the first Table file (Read Serial Nº?)
   Clients.txt The second Table file. (Read Clients?)
   Inventory.txt The third Table file. (Read Inventory?)
   PriceBook2.txt The fourth Table file and so on. (Read PriceBook2?)
   %%%% The end of file indicator.
   In the second option above SapphireOne would read and run each Table file in succession until it reached the %%%% symbols indicating end of file.

The %%%% is critical in ALL of these script files. If missing in any file SapphireOne will probably go into a loop which will be very difficult to break out of. It must be at the beginning of the last row to be read in each and every file.

Table Files with Single or Multiple Commands

Please read the following section carefully in particular (Format of Packets).
In using the first option and multiple commands you must be aware of the following.
Since you’re Reading then outputting the data to a file each command line must start with a % and end with a carriage return. In all cases %%%% must be used in the first cell on the LAST row/line to indicate end of file.

Final Result

Running the Sapphire Gateway II automated function will create a folder called gateway and then create the resulting documents in the new folder.

Examples

These are simple Table files saved as Tab Delimited text files:
Multiple Table files with Single Command in each one:

• The serial.txt file could look like this:
  • %SN @ The command line.
  • %%%% End of file marker.
• The Clients.txt file:
  • %CLE @ The command line.
  • %%%% End of file marker.
• The Inventory.txt file:
  • %INQ @ The command line.
  • %%%% End of file marker.
• The PriceBook2.txt file:
  • %PB2 @ The command line.
  • %%%% End of file marker.
• Single Table file containing multiple command lines:
  • %SN @ The 1st command line with a C/R at the end.
  • %CLE @ The 2nd command line with a C/R at the end.
  • %INQ @ The 3rd command line with C/R at the end.
  • %PB2 @ The 4th command line with C/R at the end and so on.
  • %%%% @ End of file marker.

Data Reading and Writing

Rules on Writing of Data with tables

If a cell in the Import file contains no data (Blank), the system will ignore that field in the data file and not over write it. Any data already there will be left as is. A blank cell in the Import file will not over write existing data with nothing (Blank).
There are also data entry fields which normally cannot be altered when SapphireOne is running and there are two rules for writing to these fields when importing data.

1. If the ID does NOT exist and the Import file is creating the record for the first time, writing to these fields IS allowed and the data will be written normally.
2. If the ID already exists in the data file, any information in these fields in the Import file will NOT over write the data already in these restricted fields.

Never directly import gateway files into an active live data file. First, prepare a recent version of your production data file using the same build as the server, and initiate it in Single User Mode. Conduct a trial import with the intended data and meticulously review the outcome, ensuring particularly that the columns in the import file align correctly with the appropriate fields in your data file replica. After confirming the test import is accurate and everything appears in order, proceed to create a backup of the production (live) data file, and only then carry out the actual import. If discrepancies are detected, identify and rectify the issues in the import file, then re-attempt the import with a new copy of the production data file. Always prioritise testing before importing your data.

Format of Packets

SapphireOne reads each line of information as a complete entirety which is a standard format of tab delimited, and return for end of line. This combined field is always in Column A and is referred to in the Table Format article as syntax. It can be split into three sections, % or #, read or write, middle section for selecting the table, can be two or three letters, and the last character tell SapphireOne how much to read from ther table. CLA, CLB CLC and so on.

Write Data

The second field in column B contains the ID for the file. If this ID already exists, the system will update the record with the new information; else the system will create a new record.
For Transactions and Invoices, a new record is always created.
Example:

#CLA ABC ABC Company Box 1 Redfern Sydney NSW
NB: You may have blanks and also not have all the fields, but you must not skip fields.

Read Data

Reading of data on a normal basis is the use the above codes, but replace the # with a %.
The format required for this method is:
%CLA ABC
%CLA Read file and type of data to return.
ABC This is the ID to search for.
If the ID returns more than one record, the first returning packet states the number to be received and then a packet is sent for each record using the keyword ‘1000’, which is incrementing by one for each record. Eg. 1000, 1001, 1002.

Extra % Commands

%CLD = %CLA + Balances
%CLE = %CLB + Balances
%CLF = %CLC + Balances
This also applies to Vendors (VE).
%??R This is the relation search command, and returns the ID and Name for the file specified. This command also only applies to CL, VE, GL, PR, GL, and RS.
%??Z This will return all fields for a record.

Querying

If a packet has nothing in the ID field for selection the program will read the next field(s) for commands to run for the selection of records. These follow the standard 4D format.
Allowed Commands are:
Query Commands.
Order Commands.
Set Commands.

Example

%CLDBlank Query([Clients];(([Clients]State=‘WA’)&([Clients]Total Due>100))
This will return the records the meets this rule, and include balances.
Refer to the 4D books for specifications on the formatting, and usage of these commands.
This allows the building of complex searches for the selection of the records to be passed back.

To have Query done the format is: %CLDDon’t Search
This will retrieve the data based upon the current selection.
The below describes the fields for each table on data that can be written. Each level adds to the before level.
** Refer to the Format chapter for structure of formats.

Custom Retrieval

Custom retrieval of records and fields can be used by using the %F methods.
%FS Search for data in any file, or field of the database.
%FN Return database upon file and field numbers.
%FF Return database upon field name.
%FR Return field name based upon number.
%FS follows the same format as above but does not return any values, examplebelow.
%FS Blank Query([Clients];(([Clients]State=‘WA’)&([Clients]Total Due>100)).

%FN 15 1 2 3 4 5 6
This returns the values for the first six fields in Clients.

%FF 15 ID Name Contact1
This returns the fields specified for the file Clients. ** 15 = the file number.

%FR 15 1 2 3
This returns the fields names for file 15 (Clients).

Custom Commands Using Excel Link

These are commands for returning specific data. Normally, they are used as spreadsheet functions, i.e. Excel Link and are not to be used for Gateway II or Gateway III.
These commands are as follows:

%SP Commands

These are formatted with the %SP syntax, and then the command to be executed.
These commands must follow the format as shown below.

SP GLPRCLBal

SP GLPRCLBal(GL Account;Project Class;Period)
Parameter Type Description
GL Account string GL Account to look at
Project Class string Project Class
Period integer Period to sum, if -1 then total.

This reads the G/L balance for a project group for a set period.
%SP SP GLPRCLBal(‘1000-00’;’ENG’;1)

SP GLPRBal

SP GLPRBal(GL Account;Project;Period)
Parameter Type Description
GL Account string GL Account to look at
Project string Project ID
Period integer Period to sum, if -1 then total.
Reads the G/L balance for a project for a set period.
%SP SP GLPRBal(‘1000-00’;’ENG-1’;1)

SP GLPRCLBal

SP GLPRCLBal(GL Account ;Project ;Period)
Parameter Type Description
GL Account string GL Account to look at
Project string Project ID
Period integer Period to sum, if -1 then total.
Reads the G/L Account for a project for a set period.
%SP SP GLPRCLBal(‘1000-00’;’ENG-1’;1)

SP GLCLPRBal

SP GLCLPRBal(GL Class;Project ;Period)
Parameter Type Description
GL Class string GL Class to look at
Project string Project ID
Period integer Period to sum, if -1 then total.
Reads the G/L Class for a project for a set period.
%SP SP GLCLPRBal(‘100’;’ENG-1’;1)
Page 19 A.P.I Gateway II
SapphireOne Pty Ltd 2022 Page 19

SP GLCLBalance

SP GLCLBalance(GL Class;Period;Year;Actual)
Parameter Type Description
GL Class string GL Class to look at
Period integer Period to sum, if -1 then total.
Year integer 0 this year, 1 last year
Actual integer 0=actual,1=budget
Reads the G/L Class total a set period.
%SP SP GLCLBalance(‘100’;’1;0;0)

SP GLBalance

SP GLBalance(GL Account;Period;Year;Actual)
Parameter Type Description
GL Account string GL Accountto look at
Period integer Period to sum, if -1 then total.
Year integer 0 this year, 1 last year
Actual integer 0=actual,1=budget
Reads the G/L Class total a set period.
%SP SP GLBalance(‘1000-00’;’1;0;0)

SP DSO Average

SP DSO Average(ID;type)
Parameter Type Description
ID string Client or vendor’s ID
type string C or V for client or vendor
This reads the day’s sales outstanding, and returns the average period overdue.
%SP SP DSO Average(‘BOB’;’V’)

SP DSO

SP DSO(ID;type)
Parameter Type Description
ID string Client or vendor’s ID
type string C or V for client or vendor
*** There are more commands, which are used by the Excel link system.
This reads the day’s sales outstanding based upon the current balance.
%SP SP DSO(‘BOB’;’V’)

---

You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.