API Gateway Overview
SapphireOne’s API Gateway is typically used by SapphireOne end users to facilitate the importation or exporting of vast amounts of data quickly and easily. In other articles we have provided an extensive array of documentation, which is being added to on a daily basis. The SapphireOne community typically will use the API function in the scenario that they bring on a new vendors and that Vendor may have a range of inventory that they have provided to you in an electronic form. You would now like to import that inventory including the cost price and the Recommended Retail Price (RRP). This is one example of the usage of the SapphireOne API Gateway function. The SapphireOne API Gateway functions have an extensive and wide and varied use within SapphireOne that is designed to provide a very quick and efficient way of getting vast sums of data into or out of SapphireOne quickly and easily.
The SapphireOne API Gateway II function, provides the user a single convenient source of functionality and data. When we at SapphireOne make reference to testing in a backup copy of your live data file, in the technology industry this is typically referred to as a sandbox. The beauty with the Sapphire Single User that is provided with the SapphireOne Client Server application is that you can repeat these imports as many times as you wish in a sandbox environment and each time you perform the import you can then trash the data file and then take a fresh copy of your current data file and retest the import. You can redo this as many times as you like until you’re completely satisfied with your end result. So always run Sapphire Single User in a sandbox environment with a copy of your live data file and test before you import any data into your live data file.
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 data 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 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 syntax’s 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 the Client 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 require 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º.
You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.