Creating API Templates

Creating API Templates Overview

Templates may be created in one of two ways.

  1. Use this web page or a reference manual and manually enter the column headings. Then enter your data as required. Entering the required column headings will be time intensive and mistakes with maintaining the column order are a possibility.
  2. Use the word document which is available on request from the SapphireOne and use the Transpose function in Excel. (Details follow in this article)

The following table is provided as a test case. The complete API Gateway Table article with all of the current tables may be found by going here. The user should be aware that while these tables are updated from time to time as additional functionality is added, they are usually only minor changes.

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

Selecting the columns

From the screenshot above the user will note that the characters from A to Z, are the column headings going across in a spreadsheet so we do not copy these across. The second column is what we want the column headings starting with #CLA.

Read/Write rules

SapphireOne will only read or write lines in the spreadsheet that have the % or # symbol prefixing a valid syntax. All other lines in the spreadsheet sheet will be ignored. Four %%%% should be entered as the last row in column A which indicates to SapphireOne that this is the last row of data in the file.

Creating the Template

  1. Read Write Selection – First decide what columns are to be included in the template. The following rules apply to most tables although there are some that are different and documentation is provided for these in the table article itself. The data entered into column A1 determines how SapphireOne reads or writes the data from the spreadsheet for that particular row. So for the data written in to column A, SapphireOne will do the following.
    • CLA – Will read/write from column B read/write across to Column N. [Clients]area
    • CLB – Will read/write from Column B across to column AG. [Clients]Tag3
    • CLC – Will read/write from column B all the way across to column BC. [Clients]Country
    • CLD – Will read/write from column B to the end for this table BU. [Clients]Reseller
    • CLL – This is a different table and goes from column B to column Y. [Clients]Alpha.
      • Note that this Syntax entered into column A, reads and writes different data.
  2. Word Document – Open the document and select as far down the column in the Word document as required. If the column breaks, copy and paste the first column, then copy, and append the existing column into the spreadsheet
    • Word Formatting – If arrows and lines are in the way drag them away so as not to include them when the copy function is selected. If you do not save the document on closing, these changes will not be saved.
  3. Spreadsheet File – Open up the spreadsheet program and paste the selection into about C3 in the spreadsheet.
    • Accuracy is not essential but stay at least 3 rows down and across in the spreadsheet.
    • Select this column of data. Select from [Clients]ID down to [Clients]Tag3.
    • The spreadsheet will now have a column of data running from C3 down. Select this column.
      • It is very important to start at C3 and only go down to the end of the data in this column.
      • Select Copy for this selection.
  4. Transposing – Place the cursor in cell A1 or A2 in the spreadsheet.
    • Go to the main toolbar and select the Transpose option.
      • In excel it is the paste menu at the extreme left of the toolbar.
  5. Result – This should be as seen in the screen shot below but we have a few alterations to make. For both exporting and Importing data please refer to the article .
  6. Importing & Exporting Data – When importing/Exporting data the following applies
    • Importing – Column A must have a valid syntax for every row of data that is to be read into the data file. #CLA,#CLB, #CLC and so on. as follows.
    • Exporting – This a very simple text file and further documentation may be found by goig to the Summary & Usage of API Gateway II.
  7. Note the items in column O and P. Delete these columns and you have your template.
    • Note cell C17 and C18 match up with column O and P in the transposed row 1.
    • If you extend the template all the way across to #CLD you will have to remove some more blank columns and some with #followed by some characters. And also insert the #CLD syntax into column A all the way down for all lines that are to be read or written.
    • If the template crosses more boundaries you will have to remove more columns.
  8. Finally check that the columns are in the correct order. If they are delete from C3 to C37.
    * You could additionally select row one and select the Replace function. Replace [Clients] with “” which is nothing.

Selecting multiple columns

In the word document the user will note that the tables are displayed in up to 3 columns. These are to be read from top to bottom then back to the top. To select these additional cells proceed by selecting the required cell in the next column in the word document and append them to the existing cell in the spreadsheet. Once all cells required are in the spreadsheet, use the transpose function. Enter the required read, write syntax in cell A1, then work you way across the spreadsheet removing blank columns and any columns with the read write syntax. #CLB, #CLC,#CLB and so on.

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

Was this helpful?