Summary & Usage of Gateway II

Important Please Read FIRST:

API stands for Application Protocol Interface. This article will be updated as additions and alterations are made.

Never blind import any gateway files into a live data file. Run up a recent copy of your live data file using the same build as on the server in Single user Mode, and do a test import of the data that you want to import first.

Look carefully at the results and in particular check that the columns of data in the import file have been imported into the correct fields in the copy of your live data file.

Only when the test has been completed and when all looks correct, then proceed to backup the live data file and only then import the Gateway Import file into the live data file. If it is not correct find the error in the import file and then import it again into a fresh copy of the live data file.
Remember test first then import your data.

Types of Imports:

As of the updating of this reference manual there are a number of methods used to both import and export data from the data file. Using Gateway II, or III to export or import data.
Excel Link may also be used which is for exporting data out only.

Gateway.

  • Gateway I – This function is no longer used.
  • Gateway II – This function is used for forth importing and exporting from from the data file.
  • Gateway III – This is a simplified function for the importing of limited items into the data file.
Differences gateway II and III:
  • Gateway II – The user has to organise the import spreadsheet columns of data to match the requirements of SapphireOne. It uses the tables as documented in another article titled, Table Format of Data for Read & Write.
  • Gateway III – The user is able to enter the columns of data in any order. Then by clicking on each headings name, map the columns to match SapphireOne’s requirements.
    • New Data Files – This function has mainly been provided for the importing of data into new data files.
    • Functionality – For the above reason reason, functionality in Gateway III is limited to the items in the drop-down list Also note that it only allows for the importing of data into the data file, not exporting.

Summary:

Gateway II allows the direct interaction with SapphireOne’s Database for retrieving information, and the adding of data under controlled conditions. The information may be passed to SapphireOne via a text file or via TCP/IP.

This is mainly used when converting from an existing data base across to SapphireOne. Most data bases will export to a Tab delimited (.txt) or comma delimited (.csv) text file. Once that is done the text file can be opened using a spreadsheet program and the columns of data re-arranged to suit importation by SapphireOne.

To import this data into SapphireOne it must be in a tab delimited text file or a CSV file. You may use any spreadsheet program so long as it will allow you to save the file as Text (Tab Delimited) or as a CSV file.

Import / Export Syntax

SapphireOne expects to see in column A of any table, 2 to 4 letters preceded by a % or # symbol. That gives it the instructions for the current row of data and what it is to do with it.

  • % Export – The % symbol instructs SapphireOne to read data out of the data file and then write it into a tab delineated text file.
  • # Import – If SapphireOne is told to read a tab delineated file into a data file, the % symbol must be replaced with the # symbol so that it knows that all after the #CLA from column B through to column N in the row is to be read and written into the data file. #CLB would tell SapphireOne that it is to import every thing from column B through to Column BC

This is an example of a special file using the @ symbol in column B. SapphireOne looks at the first cell A1 and determines that it is to read from the database the CLA fields. The second cell B1 tells it to read them all. The last cell A2 tells it end of file.

Based on the letters in the first cell A SapphireOne will read almost any data from the database into a Tab delimited text file. For details on what can be read please look at the tables in another article here in knowledge base.

Usage Procedure.

Gateway II may be accessed by going to:
Utilities > Utilities > Sapphire Functions

It is under the General Tab and is named Gateway II

% To Export Data – . When the user selects gateway II they will be asked by the operating platform Mac or Windows, to select a file for this operation.

SapphireOne will look at the file and determine that is is to export data out. The user will see an alert asking for the name of the exported file and then where the file is to be sent to.

# Importing data – When a file has the # symbol prefixing the letters in column A the request immediately above will be displayed. Do not enter any name or details just select OK. There will be a brief display displaying numbers as SapphireOne imports the details in the file into your data file.

Controlling the reading and writing

Please look at the Client table below. It is an import table as the syntaxes all start with the # symbol. The letters from A to Z, then carrying on from AA to AZ, are the columns of data in the table article. Some import tables go pas this and will commence with BA through to BZ as may be seen in theclient import file below.

For the documentation here please read the left column from top to the bottom, then start at the top of the next column and go to the bottom. If there is a third column again read it from top to bottom.

A #CLA   AO [Clients]Order_No   I [Clients]Phone1  
B [Clients]ID   AP [Clients]Discount1   J [Clients]Physical_Line1  
C [Clients]Name   AQ [Clients]Discount_Days   K [Clients]Physical_Line2  
D [Clients]PostalAddr_Line1   AR [Clients]Discount2   L [Clients]PhyCity  
E [Clients]PostalAddr_Line2   AS [Clients]Payment_Terms   M [Clients]PhyState  
F [Clients]PostalCity_ Line3   AT [Clients]Due_Days   N [Clients]PhyZip_Code  
G [Clients]PostalState_Line4   AU [Clients]Bank   O [Clients[Rep  
H [Clients]PostalZip_Code   AV [Clients]Branch   P [Clients]Fax_No  
I [Clients]MainPhoneNo.   AW [Clients]Drawer   Q [Clients]Active  
J [Clients]Contact1_Name   AX [Clients]Discount_price   R [Clients]Real_2  
K [Clients]Contact1_Position   AY [Clients]EDI_Link   S [Clients]UDF1  
L [Clients]Contact1_Phone   AZ [Clients]EDI_ShipID   T [Clients]UDF2  
M [Clients]Class   BA [Clients]EDI_Location   U [Clients]UDF3  
N [Clients]Area   BB [Clients]EDI_DCLocation   V [Clients]Real_3  
    BC [Clients]Country   W [Clients]UDF4  
  #CLB         X [Clients]Class  
O [Clients]PhysicalAddr_Line1     #CLD   Y [Clients]Alpha
P [Clients]PhysicalAddr_Line2   BD [Clients]Total Due      
Q [Clients]PhysicalCity_Line3   BE [Clients]Unposted_Total   A #CLX
R [Clients]PhysicalState_Line4   BF [Clients]Unposted_Receipt   B [Clients]ID
S [Clients]PhysicalZip_Code   BG [Clients]Opening_Balance   C [Clients]Alpha_1_20
T [Clients]Delivery_Instru   BH [Clients]Balance1   D [Clients]Alpha_2_20
U [Clients]Contact2_Name   BI [Clients]30_Day_Balance   E [Clients]Alpha_3_20
V [Clients]Contact2_Position   BJ [Clients]60_Day_Balance   F [Clients]Alpha_4_20
W [Clients]Contact2_Phone   BK [Clients]90+Day_Balance   G [Clients]Alpha_5_40
X [Clients]Facsimile (Client)   BL [Clients]Balance5   H [Clients]Alpha_6_40
Y [Clients]Open_Item   BM [Clients]Overdue   I [Clients]Alpha_7_60
Z [Clients]UDF1   BN [Clients]Warning_Message   J [Clients]Alpha_8_80
AA [Clients]UDF2   BO [Clients]Notes   K [Clients]Real_1
AB [Clients]UDF3   BP [Clients]Last_Transaction   L [Clients]Real_2
AC [Clients]UDF4   BQ [Clients]Last_Receipt   M [Clients]Real_3
AD [Clients]Client_Email   BR [Clients]Last_Receipt_Am   N [Clients]Real_4
AE [Clients]Tag   BS [Clients[Rep   O [Clients]Real_5
AF [Clients]Tag2   BT [Clients]CLDept   P [Clients]Real_6
AG [Clients]Tag3   BU [Clients]Reseller  (CB)   Q [Clients]Real_7
          R [Clients]Real_8
  #CLC   A #CLL   S [Clients]Date_1
AH [Clients]Tax_No   B [Clients]ID   T [Clients]Date_2
AI [Clients]Tax_Exempt   C [Clients]Name   U [Clients]Date_3
AJ [Clients]Tax_Code   D [Clients]Address_Line1   V [Clients]Date_4
AK [Clients]Tax_Rate   E [Clients]Address_Line2   W [Clients]Time_1
AL [Clients]Default_GL   F [Clients]City   X [Clients]Time_2
AM [Clients]Credit_Limit   G [Clients]State   Y [Clients]Text_1
AN [Clients]Credit_Stop   H [Clients]Zip_Code   Z [Clients]Text_2

If you use %CLA syntax in column A in the text file, SapphireOne will write data out of the data file into the tab delineated file from column B only up to column N and no further.
If you create a text file and use #CLA syntax in Column A in the text file, SapphireOne import all rows of data commencing with column B through to column N. Even though you may have data in column M the very next column it will NOT read it.

The read write rules are as follows

  • % or # CLB extends the read write to column AG.
  • % or # CLC continues to extend this read write functionality to column BC.
  • % or # CLD continues this functionality on to extend this to column BU.
  • % or # CLL is different as it is an independent table starting at column A through to column Y.
  • % or # CLX is differentas it is another independant table from column A through to column Z.

For most tables column A holds the read write syntax, and the actual data commences at column B.

There are a few tables where data read write commences at column A but it is not normal and has been documented in the article on table formatting. For example if you have a multi-line invoice, SapphireOne will reach the end of the columns of data that is has been told to to import into the data file. If this last column does not contain four ^^^^ (shift-6) in the required column, it will expect to write an ID into column A and enter the line details. Extended details are in the Table format article.

Formatting.

Any formatting will cause problems if not removed before importing data into the data file.
Tab can be replaced by other characters that have an ASCII value greater than 138 instead of the tab.
Returns in line items (eg mailing details) need to be replaced with a grouped combination of three standard characters =|=. That is the two equal signs and the vertical pipe in the order as seen.
The file needs to end with %%%% to mark end of file as some programs do not write end of file markers.

TCP/IP:

SapphireOne will receive text via tcp to do the required actions.
Open a session to the tcp/ip address of the serving program as set up under remote access defaults.
Using that session number text packets can be sent and will be received identified by that session number. A packet of ???? will return the Serial Nº, Company Name, and Version Nº.

Automated Gateway

SapphireOne supports running Gateway automatically.
It is located in the Automatic Functions area in.
Utilities > Controls > Master Defaults > API Page
Automatic functions may be Run at a time specified time here when the Run Gateway Script checkbox is selected. Then use the button with three dots, … to select a Script file to run.

It should be noted that this Script file must contain NO formatting and be a simple text file. These files would then contain Custom Commands (Table Files) from the article here, Format of Data for Read and Write.

  • 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.
  • Each Table file must be set up as seen in the Table files in the article Format of Data for Read and Write.       

To create these files, you have two choices.

  1. Use any Non-Formatting text editor.
  2. You may use a spreadsheet program like Excel to create both the Script file and the Table files but when these are saved you MUST force the spreadsheet program to Save As, then select, Tab Delimited text. Excel also treats dots and the uppercase characters above the numbers on the main keyboard somewhat strangely in cells. If uncertain look at the saved file with any Non-Formatting text editor and check for errors.
Setup and Procedure:
  1. The Script file containing the names of all the Table files that are to be run. (In the case below Export All.txt).
  2. The Table files themselves that are to be run. (The table files normally used in Gateway II).
    Once the Script file has been created go back to Master Defaults and use the button to select it. (See Previous Page).
    • On execution, the Script file selected in master defaults will be looked for, and the first line (Table file 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 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.
Automated Gateway 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 Automatic 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 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 in them

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 automated Gateway 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

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 blind import any gateway files into a live data file. Run up a recent copy of your live data file using the same build as on the server in Single user Mode, and do a test import of the data that you want to import first.

Look carefully at the results and in particular check that the columns of data in the import file have been imported into the correct fields in the copy of your live data file.

Only when the test has been completed and when all looks correct, then proceed to backup the live data file and only then import the Gateway Import file into the live data file. If it is not correct find the error in the import file and then import it again into a fresh copy of the live data file.
Remember test first then import 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 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 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’)

Was this helpful?