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

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