API Gateway Tables

These API Gateway tables are in this article so that the user is able to see what data may be imported or exported in or out of your SapphireOne data file. When creating a template for importing data the user has two choices.

Export the data out of an existing data file and use the exported tab delineated txt file therin saved as a template, and then populate it with the new data that is to be imported.

SapphireOne also have these tables saved as a word document, where the user may use the cut and paste procedure to creata the required template. There is a seperate article in this section of the knowledgebase named Creating an API Template that documents this procedure.

When ever running an import of Sapphire’s API Gateways always first do a test on a back up data file never on a live data file.

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 a Single User Mode, and do a test import of the Gateway data that you want to import first.

Thoroughly and carefully confirm all 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 the back up data file.
Only when the tests are completed and when you are completely satisfied with the result of your Gateway import and all looks correct, then proceed to import the Gateway Import file into the SapphireOne live data file.
If it is not correct find the error in the Gateway import file and then import it again into a fresh copy of the back up data file. Remember test first then import into live data files.

---

Clients #CL? – API Gateway Tables

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

---

Importing Client Balances (NEW data files ONLY):

During the setting up of a new SapphireOne data file the current Client Balances will need to be entered. This may be done by the creation of special spreadsheets as seen below. Data must only be in the cells as seen below. The cells in the spreadsheet as listed below must have the correct data entered.

If any data is in other cells it will overwrite data already in the data file.
If the is no data in a cell in the spreadsheet, SapphireOne will leave these cells as they are, if they contain any existing data.

Client Balances Import table:

A	#CLD	Must be #CLD to enable importing data across to column BU.
B	[Clients]ID	ID must be unique. Duplicates will over write existing ID’s and new ID’s in the file will create new Clients.
BD	[Clients]Total Due	Total of the 30 60 and 90 balances in BI, BJ, BK. This value must be correct as the system will not add them up.
BE	[Clients]Unposted_Total	Un-posted should not generally be imported. Check with SapphireOne support if this is required
BF	[Clients]Unposted_Receipt	Un-posted should generally not be imported. Check with SapphireOne support if this is required
BG	[Clients]Opening_Balance	Opening balance for the Client.
BI	[Clients]30_Day_Balance	Client 30 day balances
BJ	[Clients]60_Day_Balance	Client 60 day balances
BK	[Clients]90+Day_Balance	Client 90 day balances

Comments for Client Import:

Specific items to watch for.

• Checkboxes – Entering 0, zero will leave them as un-ticked. Entering 1 will tick them.
  • Case Insensitive – True and False may also be used and is not upper or lower case dependent.
  • Checkbox Selection – True or true will set a checkbox as active. False or false will set the checkbox as inactive.
• Blank Columns/Cells – Remember also that no entry into a cell tells SapphireOne to leave it as it is.
• AJ Cell – This column sets the Tax Code. For Australia enter an S. That will be for most of your Clients and alter any others as required. This will work and apply the tax but if the inventory item has a tax code set and the “Use” checkbox is selected it will override the setting in a Client or Vendor.
• AK Cell – Setting the tax code to S as above will not write in the actual rate into the data file so this must be forced. So, for a tax code of S enter in 10 for 10%. Other rates as applicable.

For importing the Client aged balances, the following details to be used.

• CLD Syntax – Use the #CLD syntax with the applicable Client ID only, all other fields must be blank.
• BI, BJ & BK Cells – This where the aged 30 60 and 90 day balances in the order as listed here.
• BD Cell – This is where the sum of the 30 60 and 90 day balances is stored and it must be precise.
  • Calculating Value of BD – Create a calculated cell beyond Column/Cell BU that adds up the balance of Columns/Cells BI, BJ and BK.
    • For example any cell beyond BU may be used, BV, BW and so on as it will not be read by SapphireOne as it will ignore any data beyond Cell BU.
  • Transferring Value – Then copy in the calculated values back into the BD field.
• BG Cell – This is where the the Clients opening balance is entered.

---

Vendors #VE – API Gateway Tables

---

---

Importing Vendor Balances using #VED

During the setting up of a new SapphireOne data file Vendor Balances will need to be entered. This may be done by the creation of special spreadsheets as seen below. Data must only be entered into the cells as seen below. If any data is in other cells it will overwrite data already in the data file.

Specific items to watch for:

• Checkboxes – Entering 0, zero will leave them as un-ticked. Entering 1 will tick them.
  • Case Insensitive – True and False may also be used and is not upper or lower case dependent.
  • Checkbox Selection – True or true will set a checkbox as active. False or false will set the checkbox as inactive.
• Blank Columns/Cells – Remember also that no entry into a cell tells SapphireOne to leave it as it is.
• AJ Cell – This column sets the Tax Code. For Australia enter an S. That will be for most of your Vendors and alter any other as required. This will work and apply the tax but if the inventory item has a tax code set and the “Use” checkbox is selected it will override the setting in a Client or Vendor.
• AK Cell – Setting the tax code to S as above will not write in the actual rate into the data file so this must be forced. So, for a tax code of S enter in 10 for 10%. Enter other rates as applicable.

For importing the Vendor aged balances, the following details are applicable:

• VED Syntax – Use the #VED syntax with the applicable Vendor ID only, all other fields must be blank.
• Aged Balances – Columns/Cells BE, BF & BG are the aged 30 60 and 90 day balances in order as listed here.
  • AZ Cell – This is the total sum of the 30 60 and 90 day Vendor balances and must be precise.
  • AZ Values – To enter these values, precisely, create a calculated field beyond Column/Cell BN, that adds up the three aged balances. For example column BO, BP and so on.
  • Transferring values – Then copy the values back into the AZ field.
• Column/Cell BC – This is where the the Vendors opening balance is entered.

---

Base Inventory #IN?

Notes for #INE from above

• BQ cell – [Inventory]BuildViaBJI = BGJ only.
• BR cell – [Inventory]Kit Item     = Manufacture from Invoices Kit.
• BS cell – [Inventory]Kit_Style     = Copy lines to Invoice

Leave them all blank if you want to manufacture from Invoice, since this is the default.

• BT cell – [Inventory]BillSerialOptions are as follows.

1= Auto Serialise.

2= Use Component.

3= Enter Serial on Sale.

4= Leave as zero for none.

There are additional Inventory tables following in this article.

Column W. The SapphireOne will override gateway with the Default Location specified in Master Defaults, Inventory page Default Location, and ignore the location entered here in column W.

Check Boxes:

The entry of either 0, zero or False will un-tick the check box. Remember also that no entry into a cell tells SapphireOne to leave it as it is. The entry of either 1 or True will tell the system to enter a tick into the checkbox. Upper or lower case is ignored by the system as it just looks for the word true True or false False.

Additional Base Inventory import tables

Note that these are stand alone tables that all commence with A cell with the table syntax.

Comments for Importing Base Inventory:

The importing of base inventory will automatically create Location Inventory stock according to the location as set in master defaults.

• AC to AH Cells – These cells will write the tag names from Tag through to Tag6 that are displayed on the first page of an Inventory Inquiry.
• AZ & BA Cells – AZ is Average Cost and BA is Last cost. If price book is to be used and it is to be linked to a cost, both columns will have to be written with the same values as price book normally refers to the average cost for its calculations.
• BJ Cell – This cell will write a fixed cost into an Inventory item. It should be noted that when the Fixed Cost cell is entered, SapphireOne will use the Fixed cost when price book does its calculations.
• F Cell This data in this cell enters the Standard Price as seen on the first page of an Inventory Inquiry.
  • Cell G – This is the stock control type and a number entered into this cell will determine how it is set. Once the inventory item is open the user has 10 options for setting the Stock Control Type.
    • 1=Non-Diminishing, 2=Normal, 3=Serial, 4=Batch, 5=Item, 6+Activity, 7=Hire, 8=Asset, 9=Vouched and 10=General Ledger.
• H Cell – This cell sets the Costing Type, 1=Average, 2=Standard, 3=Item & 4=Fixed.
• Y Cell – This cell set the Tax Code. If inventory items themselves are to set the tax code applicable to either a purchase or a sale this will have to be set accordingly. The most common usage in Australia would be a Tax code of S.
•  Z Cell – This cell sets the actual Tax Rate. Setting the tax code to S as above will not write in the actual rate into the data file so this must be forced. So, for a tax code of S enter in 10 for 10-%. Enter other rates as applicable.
• BC Cell – If price book is to be used don’t forget to set this checkbox as active, 1 or true, otherwise price book will not be used when setting selling or purchase prices.

---

Additional Vendors Inventory Items #INV – API Gateway Tables

This is different to other importing functions within the SapphireOne system as the items contained in the table below are in a sub-table. Exporting data out of these sub tables is not recommended. Please check with SapphireOne support before attempting to do so.

There is provision in an Inventory Items master file to have a number of preferred Vendors on the second Page More Details. Normally the Vendors Product ID and Names as supplied by them are used for inventory items.

Sometimes these are not suitable and the most common cause for not using them would be if this would result in duplicate ID’s. So within this list there is provision for the entry of the Vendors Product ID and Name as supplied by them for an item if it is different to what is used as the main ID’s and Names.
So as follows:

• B & C Cells – These cells are the System ID’s as set up within the user’s data file system using either a Vendor Inquiry or an Inventory Inquiry.
• D & E Cells – These are the Vendors Product ID and Product Name for the item or product if different.

The tab delineated text file as seen below was imported into Bondi Blue for AACUT. This could be extended by the use of a different Inventory ID (Base) column entries.

Note that the first line does not have an entry in cell A1 so the complete row is ignored by the system.

The result was as seen below in the screenshot.

In this list of Vendors on the second page of an Inventory Inquiry the user has to first highlight a line and double click. The details will be displayed in a small alert allowing the user to make modifications as required.

Remember to TEST all imports into a copy of your data file before importing into a live data file.

---

Base Inventory MSDS page #MSDS

This table imports data into the MSDS Page of a Base Inventory Inquiry.
While the page name is MSDS all Alpha, Real and Date data entry field headings are able to be altered by the user the page may be used for the storing of virtually any type of data.

---

Barcodes #BC

The Inventory ID must be the Base ID, and not the location ID. Both read and write work the same. The system will read consecutive rows of data to the same Base Inventory ID as consecutive Barcodes for that same item and add them in the order as entered.

A	#BC
B	[Inventory_Barcode]Barcode
C	[Inventory_Barcode]Base_Inventory_Lk
D	[Inventory_Barcode]Qty
E	[Inventory_Barcode]ref
F	[Inventory_Barcode]Client_ID

---

Inventory Locations #IL?

A	#ILA			#ILD
B	[Inv_Locations]MergedID		AC	[Inv_Locations]MTDB_COS
			AD	[Inv_Locations]MTDB_Purc_QTY
	#ILB		AE	[Inv_Locations]MTDB_Purc_val
C	[Inv_Locations]Location		AF	[Inv_Locations]MTDB_Sales_Qty
D	[Inv_Locations]Current		AG	[Inv_Locations]MTDB_Sales_val
E	[Inv_Locations]Allocated		AH	[Inv_Locations]MTD_COS
F	[Inv_Locations]On_Backorder		AI	[Inv_Locations]MTD_Purc_Qty
G	[Inv_Locations]On_Order		AJ	[Inv_Locations]MTDPurch_val
H	[Inv_Locations]Unposted		AK	[Inv_Locations]MTD_Sales_Qty
I	[Inv_Locations]AvailableStock		AL	[Inv_Locations]MTD_Sales_val
J	[Inv_Locations]Opening		AM	[Inv_Locations]Ord_Quote_Qty
K	[Inv_Locations]Maximum		AN	[Inv_Locations]Ord_Quote_Value
L	[Inventory_Locations]Minimum		AO	[Inv_Locations]Pur_Quote_Qty
M	[Inventory_Locations]Site		AP	[Inv_Locations]Pur_Quote_val
N	[Inventory_Locations]ProductionArea		AQ	[Inv_Locations]BayActive (CB)
			AR	[Inv_Locations]DefaultBuildBay (CB)
	#ILC		AS	[Inv_Locations]Tax_Code)
O	[Inv_Locations]Average_Cost		AT	[Inv_Locations]Tax_Rate)
P	[Inv_Locations]Last_Cost		AU	[Inv_Locations]Prod_Tax)
Q	[Inv_Locations]Std_Sale_Price		AV	[Inv_Locations]Tax_V_Code)
R	[Inv_Locations]Price_A		AW	[Inv_Locations]Tax_V_Rate)
S	[Inv_Locations]Price_B		AX	[Inv_Locations]Tax_V)
T	[Inv_Locations]Price_C
U	[Inv_Locations]Price_D			#ILE
V	[Inv_Locations]Price_E		AY	[Inv_Locations]TTDB_COS
W	[Inv_Locations]Price_F		AZ	[Inv_Locations]TTDB_Purch_Qty
X	[Inv_Locations]Price_G		BA	[Inv_Locations]TTDB_Purch_val
Y	[Inv_Locations]Price_H		BB	[Inv_Locations]TTDB_Sales_Qty
Z	[Inv_Locations]Price_Exempt		BC	[Inv_Locations]TTDB_Sales_Value
AA	[Inv_Locations]Fixed_Cost		BD	[Inv_Locations]TTD_COS
AB	[Inv_Locations]Floor Price		BE	[Inv_Locations]TTD_Purch_Qty
			BF	[Inv_Locations]TTD_Purch_val
			BG	[Inv_Locations]TTD_Sales_Qty
			BH	[Inv_Locations]TTD_Sales_val

Inventory Location Contracts #ILX

Note that this gateway import function is for contracts and not Contacts!

This function will import a list of Clients or Vendors to sell to, or not to sell to.
This Contracts page may be viewed by going to Inventory > Inventory > Inventory Locations
Then select an Inventory item and select the Contracts Table Page.

A	#ILX
B	[Inventory_Exclude]Inventory_ID
C	[Inventory_Exclude]Entry_ID
D	[Inventory_Exclude]Entry_type

• Cell B – Enter in the Inventory ID.
• Cell C – Enter in the Client or Vendors ID.
• Cell D – Enter in either VE for Vendors or CL for Clients.

Sapphireone will blind import all ID’s so it would probably be better to import the Clients and Vendors in separate import files as the selection made at column D will force the table that the ID is placed in!

Once the list has been imported the user will have to select each inventory item in turn.
Then select from the Show Table for menu, either Clients or Vendors. Next select the required radio button to select either Cannot Sell/Purchase or Can Sell/Purchase as seen in the screenshot below.

---

Bay Import:

This function which is in Custom Tools in Utilities, allows the importing of Bay Data in to the data file. The rules are the same as for any Gateway import including the requirement that the last row in column A contains %%%% symbols to indicate end of file to the system. The file must be a Tab delineated text file.

Since this function is very specific to Bays, the syntax #??? In column A is not required as in other Gateway Import files.

When SapphireOne finds a record that is the same it will overwrite the record with the data that is in the import file that is currently being imported.There are 5 Import functions available.

Import Bay Location:                     Import Bay Location Distance:

A	Location ID (Must be valid Location ID)		A	From Bay Name
B	Bay Location Name		B	To Bay Name
C	Bay Order number (0->?)		C	Location ID (Must be a valid Location ID)
D	Bay Level		D	Bay Level
E	Start Distance		E	Distance

Import Bay Inventory:

A	Inventory Location ID (MUST be a valid Inventory Location ID)
B	Bay Name (Exact)
C	Current Stock
D	Unposted stock
E	Allocated Stock
F	Priority (0 ->? Determines which bay to first use when creating an invoice)

Import Bay Invoice Line:

A	Location ID (MUST be a valid location ID)
B	Inventory Sequence number
C	Bay Name (Exact)
D	$ Order Value
E	$ Back Order Value
F	Quantity (Known as Delivery or Arrival as well)
G	Type (E.g. OVI, PMP)
H	Date (format dd/mm/yyyy)

---

Projects #PR?

A	#PRA		AH	[Projects]Markup
B	[Projects]ID		AI	[Projects]Est_Stock
C	[Projects]Name		AJ	[Projects]Est_StockC
D	[Projects]Class		AK	[Projects]BroughtForwardCost
E	[Projects]Client_LK		AL	[Projects]BroughtForwardCostA
F	[Projects]Address1		AM	[Projects]BroughtForwardCharge
G	[Projects]Address2
H	[Projects]City			#PRD
I	[Projects]State		AN	[Projects]Fnished (See Note)
J	[Projects]Zip		AO	[Projects]Actual_Finish
K	[Projects]Client_Order		AP	[Projects]Tag (On Details Page)
L	[Projects]Client_Proj_No
M	[Projects]Contact1		A	#PRX
N	[Projects]Phone1		B	[Projects]ID
O	[Projects]Project_Brief		C	[Projects]Alpha_1_20
			D	[Projects]Alpha_2_20
	#PRB		E	[Projects]Alpha_3_20
P	[Projects]Delivery1		F	[Projects]Alpha_4_20
Q	[Projects]Delivery2		G	[Projects]Alpha_5_40
R	[Projects]DCity		H	[Projects]Alpha_6_40
S	[Projects]DState		I	[Projects]Alpha_7_60
T	[Projects]DZip		J	[Projects]Alpha_8_80
U	[Projects]Contact2		K	[Projects]Real_1
V	[Projects]Phone2		L	[Projects]Real_2
W	[Projects]Start_Date		M	[Projects]Real_3
X	[Projects]Estimated_Finis		N	[Projects]Real_4
Y	[Projects]Quoted		O	[Projects]Real_5
Z	[Projects]Quote_no		P	[Projects]Real_6
AA	[Projects]Quote_Date		Q	[Projects]Real_7
AB	[Projects]Quoted_Value		R	[Projects]Date_1
AC	[Projects]Notes		S	[Projects]Date_2
			T	[Projects]Date_3
	#PRC		U	[Projects]Date_4
AD	[Projects]Est_Disburse		V	[Projects]Time_1
AE	[Projects]Est_DisburseC		W	[Projects]Time_2
AF	[Projects]Est_Disburse_Charge		X	[Projects]Text_1
AG	[Projects]Est_Expenses		Y	[Projects]Text_2

• For the above items, each new code extends the code above, unless otherwise stated.
• #PRD  AN Cell – Enter the numbers as follows, 1 = Finished,   2 = Hold   and  3 = Active.
• #PRD  AO Cell – On the Details Page it is the actual finish Date for the Project
• #PRD AP Cell – On the Details Page it is the ta1 heading.

---

Resource #RA

A	#RA		G	[Resource]Cost/Unit
B	[ResourceID		H	[Resource]Charge/Unit
C	[Resource]Name		I	[Resource]Charge_FX
D	[Resource]Activity_ID		J	[Resource]Units
E	[Resource]Activity_Name		K	[Resource]Transfer
F	[Resource]Allowance_ID

When the word, SAPCLEAR is into column D Activity_ID field, SapphireOne will Delete all Activities currently set-up in the Resource as listed in cell B when the file is imported into the system.

---

Build #BU

A	#BU
B	[BillMaterials]Link_ID
C	[BillMaterials]ID
D	[BillMaterials]Notes
E	[BillMaterials]Quantity

Comments for the #BU table

• B Cell – The Link ID is the Base Inventory ID of the component inventory.
• C Cell – The ID is the base inventory ID of the Master Inventory item.

Component Inventory #BUR Master Inventory #BUR

---

Comments for #BUR.

• Master Inventory – Importing the master line will delete existing Bill of materials and update the mark-up quantity.
• Components Inventory – Importing the component line will add the line to the bill of materials.

The difference between #BU and #BUR are as follows:

• #BU – Using this syntax does not update the line if the line already exists.
• #BUR – This syntax requires the creation of the master item for new inventory. (eg a #BUR line with both ID and Link_ID to be the master inventory item)

Examples:

In the following example, the bill of materials for KITPACKB will be deleted and the lines for AUTOBATCHB will be added to the existing bill of materials.

#BUR	KITPACKB	KITPACKB	2	x
#BUR	KITPACKB	FLIGHTB	4	x
#BUR	KITPACKB	AASILB	6	x
#BUR	AUTOBATCHB	FFOVEB	1	x
#BUR	AUTOBATCHB	DWADLB	2	x
%%%%

Using #BUR syntax will result in an empty bill of materials for KITPACKB.

#BUR	KITPACKB	FLIGHTB	4	x
#BUR	KITPACKB	AASILB	6	x
#BUR	KITPACKB	KITPACKB	2	x
%%%%

---

 Transactions(#TR?):

The next files are transaction types, which have a header, and then allow for the inclusion of multiple detail lines, which are repeated until completed. These templates are unusual as the additional templates add additional functionality from cell N and not cell A as follows.

• #TR – This template ends at cell S and note again cell S. Other tables listed below overlap and start from cell N ignoring cells N to cell S when the #TR syntax is used.
  • #TRA – This template carries on from cell N to Cell U inclusive.
  • #TRB – This template carries on from cell N to cell V inclusive.
  • #TRC – This template carries on from cell N to cell Z inclusive.
  • #TRQ – This template carries on from cell N all the way to cell AFinclusive.

A	#TR
B	[Transactions]Sequence  **Overridden
C	[Transactions]Depart
D	[Transactions]Type
E	[Transactions]Period  **Overridden, based upon the date in.
F	[Transactions]Date_In
G	[Transactions]Internal_Ref
H	[Transactions]External_Ref

Note that I and J below follow H from above. Cells I & J will alter depending on the Type of transaction being imported at Cell D for these seven transaction types, GJ, MR, MP,C@, CR, VQ, and VP.
Please leave cells I and J blank for any other type of transaction.

Cell K follows cell J above.

K	[Transactions]Notes
L	[Transactions]Drawer
M	[Transactions]Total

When the #TR syntax is used SapphireOne will continue to read or write from cell N through to cell S as seen in the table below.

#TR	Carries on from cell M when the #TR syntax is used.
N	[Transactions]Breakdown’GL_LK		Q	[Transactions]Breakdown’Tax_Code
O	[Transactions]Breakdown’Project_LK		R	[Transactions]Breakdown’Tax
P	[Transactions]Breakdown’Amount		S	[Transactions]Breakdown’Total

When the #TRA, #TRB, #TRC and #TRQ syntaxes are used, SapphireOne will append these items commencing at cell N for these tables as seen below.

	#TRA			#TRB
N	[Transactions]Bank		N	[Transactions]Bank
O	[Transactions]Branch		O	[Transactions]Branch
P	[Transactions]Breakdown’GL_LK		P	[Transactions]Date_Due
Q	[Transactions]Breakdown’Project_LK		Q	[Transactions]Breakdown’GL_LK
R	[Transactions]Breakdown’Amount		R	[Transactions]Breakdown’Project_LK
S	[Transactions]Breakdown’Tax_Code		S	[Transactions]Breakdown’Amount
T	[Transactions]Breakdown’Tax		T	[Transactions]Breakdown’Tax_Code
U	[Transactions]Breakdown’Total		U	[Transactions]Breakdown’Tax
			V	[Transactions]Breakdown’Total
	#TRC			#TRQ
N	[Transactions]Bank		N	[Transactions]Bank
O	[Transactions]Branch		O	[Transactions]Branch
P	[Transactions]Date_Due		P	[Transactions]Date Due
Q	[Transactions]Discount_Per		Q	[Transactions]Discount_Per
R	[Transactions]Discount_Days		R	[Transactions]Discount_Days
S	[Transactions]Expiry_Date		S	[Transactions]Expiry_Date
T	[Transactions]Finance_Per		T	[Transactions]Finance_Per
U	[Transactions]Breakdown’GL_LK		U	[Transactions]TRA1
V	[Transactions]Breakdown’Project_LK		V	[Transactions]TRA2
W	[Transactions]Breakdown’Amount		W	[Transactions]TRA3
X	[Transactions]Breakdown’Tax_Code		X	[Transactions]Breakdown’GL_LK
Y	[Transactions]Breakdown’Tax		Y	[Transactions]Breakdown’Project_LK
Z	[Transactions]Breakdown’Total		Z	[Transactions]Breakdown’Amount
			AA	[Transactions]Breakdown’Tax_Code
			AB	[Transactions]Breakdown’Tax
			AC	[Transactions]Breakdown’Total
			AD	[Transactions]Breakdown’Retention Perce
			AE	[Transactions]Breakdown’TRBalpha_1_35
			AF	[Transactions]Breakdown’TRBReal_1

For the #TR table previously, and the #TRA, #TRB, #TRC, and #TRQ, tables above, the detail lines in the table below are already included.

However, they are displayed below to indicate that they are detail lines, and that they are repeated until end of line (Packet). E.g. for #TR, the spreadsheet columns will run as follows:
(N O P Q R S), (T U V W X Y), (Z AA AB AC AD AE). You would continue repeating across the spreadsheet for each additional line in the transaction. For #TRA repeating the 6 columns starts at P, for #TRB at Q and so on.

#TR	#TRA	#TRB	#TRC	#TRQ
N	P	Q	U	X	[Transactions]Breakdown’GL_LK
O	Q	R	V	Y	[Transactions]Breakdown’Project_LK
P	R	S	W	Z	[Transactions]Breakdown’Amount
Q	S	T	X	AA	[Transactions]Breakdown’Tax_Code
R	T	U	Y	AB	[Transactions]Breakdown’Tax
S	U	V	Z	AC	[Transactions]Breakdown’Total

General Ledger Journals – GJ’s. If value in amount, this becomes the debit figure, or the Total figure becomes the credit figure. * If period is negative then this period is used, or the system will override the period based upon the transaction date.

---

Invoices #IV? – API Gateway Tables

Cells E and G will change depending on the Type of transaction being imported.
Also note that they are not consecutiove.

#IV is the only table that ends at cell AL and is used when there is a single line in transaction/s. This is provided for with the cells from AD to AL. For multiple lines in transactions use the #IVJ syntax which is documented later in this article under the heading on vertical importing.

These tables follow on from #IV after AC in the table above and not after AL.

	#IVQ		AS	[Invoices]Stock_Breakdown’Description
AD	[Invoices]Discount Percentage		AT	[Invoices]Stock_Breakdown’Quantity
AE	[Invoices]Discount Days		AU	[Invoices]Stock_Breakdown’Rate
AF	[Invoices]Expiry Date		AV	[Invoices]Stock_Breakdown’Discount_Code
AG	[Invoices]Finance_Per		AW	[Invoices]Stock_Breakdown’Discount_Percen
AH	[Invoices]IVAlpha1_20		AX	[Invoices]Stock_Breakdown’Tax_code
AI	[Invoices]IVAlpha2_20		AY	[Invoices]Stock_Breakdown’Tax_Rate
AJ	[Invoices]IVAlpha3_30		AZ	[Invoices]Stock_Breakdown’Ordered
AK	[Invoices]IVAlpha4_30		BA	[Invoices]Stock_Breakdown’Backordered
AL	[Invoices]IVAlpha5_10		BB	[Invoices]Stock_Breakdown’IVBAlpha1_20
AM	[Invoices]IVReal1		BC	[Invoices]Stock_Breakdown’IVBAlpha2_20
AN	[Invoices]IVReal2		BD	[Invoices]Stock_Breakdown’IVBAlpha3_30
AO	[Invoices]IVDate1		BE	[Invoices]Stock_Breakdown’IVBAlpha4_30
AP	[Invoices]IVDate2		BF	[Invoices]Stock_Breakdown’IVBAlpha5_10
AQ	[Invoices]Stock Breakdown’Inventory		BG	[Invoices]Stock_Breakdown’IVBReal1
AR	[Invoices]Stock_Breakdown’Project		BH	[Invoices]Stock_Breakdown’IVBDate1
			BI	[Invoices]Stock_Breakdown’Retent

---

Vertical Importing #IVJ – API Gateway Table

Vertical Importing of invoice line items is now supported. For single line invoices #IV may be used and the format for #IVJ follows this format through to cell AC.

By using #IVJ multiple lines for each invoice may be entered. SapphireOne will only stop writing lines into the current invoice when ^^^^ has been entered into cell T in the spreadsheet.

System Procedure:

• Correct Syntax – The syntax for this vertical import function is #IVJ which uses a combination of #IV and #IVJ as seen previously and below. This import function will contain 2 types of data. Invoice details and Line entry details.
• Invoice Details – These details are read from Cell A the cell containg the syntax through to cell AC. At this point it stops and looks to the next row expecting to find a valid Inventory ID in cell A.
• Line Details – SapphireOne expects to find an Inventory ID in cell A with additional details for the first line in the invoice ending with cell S.
• End of Invoice – It then looks to see if there are ^^^^ symbols in Cell T.
  • Cell T – If this cell does not have ^^^^, SapphireOne will return to cell A on the next line looking for a valid Inventory ID and enter this as the second line in the invoice. It will then continue to repeat reading from Cell A to cell S entering a line for each line in the spreadsheet and checking the status of cell T for each line.
  • End of Invoice – Once SapphireOne sees ^^^^ in column T, it closes of the current Invoice and goes to the next line in the template and expects to see the #IVJ syntax.
  • Next Invoice – If the #IVJ syntax is on the next line it will commence the procedure for the next invoice from thre first sep in this list above.
• End of Invoice – The system uses the #IV chart on a previous page and reads all the way across to column AC. This gives it all of the information it needs about the transaction apart from the actual inventory lines in the transaction.
• End of File – The last row in the spreadsheet must have %%%% symbols in column A to indicate to the system that it has reached the end of the import file.

Invoice Lines #IVJ

Unlike other spreadsheets in gateway a number of columns actually perform two functions during this import. Also, unlike other import functions in gateway the first column does not require the instruction of #IVJ as it is assumed by the SapphireOne. Column A must contain the inventory ID as seen below and not have #IVJ in cell A as in all other import files.

#IVJ

A	I[nvoices]Stock Breakdown’Inventory_ID		K	[Invoices]Stock_Breakdown’Backordered
B	[Invoices]Stock_Breakdown’Project_ID		L	[Invoices]Stock_Breakdown’IVBAlpha1_20
C	[Invoices]Stock_Breakdown’Description		M	[Invoices]Stock_Breakdown’IVBAlpha2_20
D	[Invoices]Stock_Breakdown’Quantity		N	[Invoices]Stock_Breakdown’IVBAlpha3_30
E	[Invoices]Stock_Breakdown’Rate		O	[Invoices]Stock_Breakdown’IVBAlpha4_30
F	[Invoices]Stock_Breakdown’Discount_Code		P	[Invoices]Stock_Breakdown’IVBAlpha5_10
G	[Invoices]Stock_Breakdown’Discount_Percen		Q	[Invoices]Stock_Breakdown’IVBReal1
H	[Invoices]Stock_Breakdown’Tax_code		R	[Invoices]Stock_Breakdown’IVBDate1
I	[Invoices]Stock_Breakdown’Tax_Rate		S	[Invoices]Stock_Breakdown’Retent
J	[Invoices]Stock_Breakdown’Ordered		T	[Invoices]   ^^^^   Last Line item Indicator

If you have a multi-line invoice the last line is terminated with a “^^^^” (shift-6, excluding quotations) in cell T which is the last column as seen above.

If you enter the characters PB into the rate field for an IVJ import, price book is looked up and the rate is calculated accordingly.

---

Periodic Invoices #PER – API Gateway Tables

This gateway function will import or export Periodic Invoices into the system.
The actual period is set by a number entered into column E as follows.

A	#PER
B	[PerInvoice]Sequence_Number
C	[PerInvoice]End_Date
D	[PerInvoice]Internal-Ref_Number
E	[PerInvoice]Period.  1 = Weekly, 2 = Fortnightly, 3 = Monthly and 4 = Daily
F	[PerInvoice]Client_ID
G	[PerInvoice]Amount
H	[PerInvoice]Tax
I	[PerInvoice]Total

---

Serial Number’s #SN and #SNA – API Gateway Tables

A	#SN			#SNA
B	[Serial_Nºs]Serial_No		K	[Serial_Nºs]Common_ID
C	[Serial_Nºs]Inventory_LK		L	[Serial_Nºs]Cost
D	[Serial_Nºs]Qty		M	[Serial_Nºs]Expiry_Date
E	[Serial_Nºs]Type		N	[Serial_Nºs]Period
F	[Serial_Nºs]Date		O	[Serial_Nºs]Invoice_Seq  **Overriden
G	[Serial_Nºs]Status		P	[Serial_Nºs]Invent_Class
H	[Serial_Nºs]Serial_Sequence  **Overriden
I	[Serial_Nºs]Qty_Available  **Overriden
J	[Serial_Nºs]Qty_Unposted

---

PriceBook 2 #PB2 – API Gateway Table

A	#PB2		Q	[Price_book2]Min_Quantity
B	[Price_book2]Reference_ID		R	[Price_book2]Max_Quantity
C	[Price_book2]Inventory_Field		S	[Price_book2]Units
D	[Price_book2]Inventory_FieldValue		T	[Price_book2]Ignore_Discounts
E	[Price_book2]Master_Table		U	[Price_book2]Date_Start
F	[Price_book2]Master_Field		V	[Price_book2]Date_Finish
G	[Price_book2]Master_FieldValue		W	[Price_book2]Date_Alarm
H	[Price_book2]Project_ID		X	[Price_book2]Department
I	[Price_book2]Rule_Type		Y	[Price_book2]Inventory_Tag 1
J	[Price_book2]Rule_Number		Z	[Price_book2]Inventory_Tag 2
K	[Price_book2]LU_Code		AA	[Price_book2]Inventory_Tag 3
L	[Price_book2]Rate		AB	[Price_book2]Inventory_Tag 4
M	[Price_book2]Discount		AC	[Price_book2]Inventory_Tag 5
N	[Price_book2]Markup		AD	[Price_book2]Inventory_Tag 6
O	[Price_book2]Margin		AE	[Price_book2]Inventory_Ignore discount rule
P	[Price_book2]Fixed_Quantity		AF	[Price_book2]NOT Used yet

Price Book 2 details

• Periods – If period is negative, then this period is used, else the system will override the period based upon the invoice date.
• Serial Numbers – These must appear in the next packet for linking with the invoice.
• Project ID’s – If JCI or JCC, the Project LK fields must be the same for each line as SapphireOne then copies this into the header file.
• B – This must be a Valid Unique Reference ID
• C – Enterable options are:  ID    CL    LC   (To select if it is Inventory ID, Class , Location)
• D – This must be a valid Inventory ID, Class ID or Location ID
• E – Enterable options are:  C or  V (To select eitherClient or Vendor).
• F –The enterable options are:    ID   CL   ALL
• G – THis must be a valid Client ID, Vendor ID or Client Class ID, Vendor Class ID
• H – This must be a valid project ID
• I – This must be a number corresponding to the drop-down menu position in the list, 1, 2 or 3. (Note, oddly 3 is the  first one at the top of the drop-down list! Test it and see).
• J – This must be a number corresponding to the drop-down menu the size of which will be determined by the selection made at I.
• K L M N O P Q R S – THese are linked to the selections made at I and J and will vary a lot.
• T – Enterable options are True Ticked or False Un-ticked. (Linked to I when number 2 is selected.
• U V W – These are entered as dates DD/MM/YY. If 00/00/00 the date is not set active.
• X – This is for a valid department ID

---

Contacts #CN – API Gateway Table

A	#CN			AD	[Contacts]Phone2
B	[Contacts]CN_ID			AE	[Contacts]Phone3
C	[Contacts]First_Name			AF	[Contacts]Phone4
D	[Contacts]Surname			AG	[Contacts]Email
E	[Contacts]Company			AH	[Contacts]Order
F	[Contacts]Address1			AI	[Contacts]CNA1_20
G	[Contacts]Address2			AJ	[Contacts]CNA2_20
H	[Contacts]City			AK	[Contacts]CNA3_30
I	[Contacts]State			AL	[Contacts]CNA4_30
J	[Contacts]Zip_Code			AM	[Contacts]CNA5_60
K	[Contacts]Main_Phone			AN	[Contacts]CNA_6_40
L	[Contacts]Salutation			AO	[Contacts]CNA_7_60
M	[Contacts]Initails			AP	[Contacts]CNA_8_80
N	[Contacts]Class			AQ	[Contacts]CNDate1
O	[Contacts]Physical_1			AR	[Contacts]CNDate2
P	[Contacts]Physical_2			AS	[Contacts]CNDate3
Q	[Contacts]Physical_3			AT	[Contacts]CN_Date4
R	[Contacts]Physical_4			AU	[Contacts]CNReal1
S	[Contacts]List_Name			AV	[Contacts]CNReal2
T	[Contacts]Al_Date			AW	[Contacts]CNReal3
U	[Contacts]Last_Date			AX	[Contacts]CNReal4
V	[Contacts]Area_LK			AY	[Contacts]CNReal5
W	[Contacts]Type_Link_ID			AZ	[Contacts]CNReal6
X	[Contacts]Active			BA	[Contacts]CNReal7
Y	[Contacts]MailOut			BB	[Contacts]CNReal8
Z	[Contacts]GenderIsMale			BC	[Contacts]CNText
AA	[Contacts]MobilePhone			BD	[Contacts]CNtext2
AB	[Contacts]HomePhone			BE	[Contacts]CNTime1
AC	[Contacts]Fax_No			BF	[Contacts]CNTime2

Comments:

To link these Contacts to the appropriate Client you will have to first Import all of the Clients. Then enter in the Client that they are to be associated with at column W Client ID in the above spreadsheet.
To complete the linking the CL_Type will have to be set at CLLink using an applied formula.

To link these Contacts to the appropriate Vendor you will have to first Import all of the Vendors. Then enter in the Vendor that they are to be associated with at column W Client ID in the above spreadsheet. To complete the linking the VE_Type will have to be set at VELink using an applied formula. 

Note: data entry for cell Z, EmployeesIsMale is no longer entered as true or false as we now have 3 options as follows: 0 not given, 1 Male and 2 Female.

---

Timesheets #RS – API Gateway Table

A	#RS
B	[Times]Sequence		G	[Times]Internal_Ref
C	[Times]Type		H	[Times]Date_In
D	[Times]Period		I	[Times]Start_Date
E	[Times]Resource_LK		J	[Times]Fin_Date
F	[Times]Project_LK		K	[Times]Memo

Sublines (Repeat until end of line (packet))

L	[Times]Sublines’Resource LK			#RSA
M	[Times]Sublines’Project LK		U	[Times]Sublines’TLAlpha_20
N	[Times]Sublines’Date		V	[Times]Sublines’TLReal
O	[Times]Sublines’Hours
P	[Times]Sublines’Rate
Q	[Times]Sublines’Amount
R	[Times]Sublines’Activity LK
S	[Times]Sublines’Cost
T	[Times]Sublines’Act Description

Comments

• C Cell –  [Times]Type          Either RS = Resource  or  PR = Project.
• D Cell –  [Times]Period       Leave blank if you want it to be the current period.
• H Cell –  [Times]Date_In     (Format is YYYYMMDD) (E.g.  20070428 is 28 April 2007).

Resources: #RR  All fields Refer to the field listing for the names and order of the fields.

Diary: #DL   ll fields Refer to the field listing for the names and order of the fields.

Thin 1-5:

#TH1   All fields Refer to the field listing for the names and order of the fields.
#TH2   All fields Refer to the field listing for the names and order of the fields.
#TH3   All fields Refer to the field listing for the names and order of the fields.
#TH4   All fields Refer to the field listing for the names and order of the fields.
#TH5   All fields Refer to the field listing for the names and order of the fields.

---

Award #AW? – API Gateway Table

Comments

#AWR and #AWT are very simple 1 and 2 cell tables both of which begin with cell A.
#AWA, #AWB and #AWC is the one table commencing with cell A.

A	#AWR			#AWB
B	[Award]ID		W	[Award]UseLeave
C	[[Award]Description		X	[Award]HourlyDailyhol
			Y	[Award]LLFirstyear
A	#AWT		Z	[Award]Hol1
B	[Award]ID		AA	[Award]Hol2
			AB	[Award]Hol3
A	#AWA		AC	[Award]Hol4
B	[Award]ID		AD	[Award]Hol5
C	[[Award]Description		AE	[Award]HolidayAnni
D	[Award]Sal1		AF	[Award]HolidayAnniDate
E	[Award]Sal2		AG	[Award]ResetLeave
F	[Award]Sal3		AH	[Award]AccumHoliday
G	[Award]Time1		AI	[Award]Sick1
H	[Award]Time2		AJ	[Award]Sick2
I	[Award]Time3		AK	[Award]Sick3
J	[Award]UseRDO		AL	[Award]Sick4
K	[Award]RDOTrigger		AM	[Award]Sick5
L	[Award]RDOPerc		AN	[Award]SickAnni
M	[Award]RDOAuto		AO	[Award]SickAnniDate
N	[Award]RDOMax		AP	[Award]ResetSick
O	[Award]RDOtrigperc		AQ	[Award]Usesetsick
P	[Award]SuperRate
Q	[Award]SuperFixed			#AWC
R	[Award]ExemptionMin		AR	[Award]LSL1
S	[Award]SuperCPer		AS	[Award]LSL2
T	[Award]SuperCFixed		AT	[Award]LSL3
U	[Award]WorkCPer		AU	[Award]LSLAnni
V	[Award]WorkCFixed		AV	[Award]LSLAnniDate
			AW	[Award]Deferred1
			AX	[Award]Deferred2
			AY	[Award]Deferred3
			AZ	[Award]Deferred4
			BA	[Award]Deferred5
			BB	[Award]Deferred6
			BC	[Award]Deferred7
			BD	[Award]AccumLSL

---

Employee Payroll #EM? – API Gateway Table

#EMR and #EMT are very simple tables both of which begin with Cell A.
#EMA, #EMB, #EMC, #EMD, #EME, #EMF and #EMX are all the one very long table commencing with cell A. Select the read or write option carefully as the last couple of options will make the reading or writing spreadsheet very wide across to cell GR.

A	#EMR		AE	[Employees]RoundDown
B	[Employees]Code		AF	[Employees]LLAllow
C	[Employees]Surname		AG	[Employees]Apprentice
			AH	[Employees]QuickPay
A	#EMT		AI	[Employees]Start_Date
B	[Employees]Code		AJ	[Employees]Finish_Date
			AK	[Employees]Review_Date
A	#EMA		AL	[Employees]Paid_to_date
B	[Employees]Code		AM	[Employees]Lastpaid
C	[Employees]Surname		AN	[Employees]BSB1
D	[Employees]First_Names		AO	[Employees]BSB2
E	[Employees]Title		AP	[Employees]BSB3
F	[Employees]Job_Title		AQ	[Employees]BSB4
G	[Employees]Class		AR	[Employees]Bank1
H	[Employees]Department		AS	[Employees]Bank2
I	[Employees]AwardLink		AT	[Employees]Bank3
J	[Employees]Address_1		AU	[Employees]Bank4
K	[Employees]Address_2		AV	[Employees]BankAccName1
L	[Employees]Suburb		AW	[Employees]BankAccName2
M	[Employees]State		AX	[Employees]BankAccName3
N	[Employees]Zip		AY	[Employees]BankAccName4
O	[Employees]Country		AZ	[Employees]BankCode1
P	[Employees]Phone		BA	[Employees]BankCode2
Q	[Employees]MbPhone		BB	[Employees]BankCode3
R	[Employees]Email		BC	[Employees]BankCode4
S	[Employees]Date_of_Birth		BD	[Employees]Bank1per
T	[Employees]Sex		BE	[Employees]Bank2Per
U	[Employees]Next_of_Kin		BF	[Employees]Bank3per
V	[Employees]NKinRelation		BG	[Employees]Bank4per
W	[Employees]NKPhone		BH	[Employees]Cheque_per
			BI	[Employees]Cash_per
	#EMB		BJ	[Employees]Bank1Max
X	[Employees]RDOtrigperc		BK	[Employees]Bank2Max
Y	[Employees]RDOTrigger		BL	[Employees]Bank3Max
Z	[Employees]RDOPerc		BM	[Employees]Bank4Max
AA	[Employees]RDOMax		BN	[Employees]Cheque_Max
AB	[Employees]RDOOwed		BO	[Employees]Cash_Max
AC	[Employees]RDOAuto		BP	[Employees]Notes
AD	[Employees]GcertDedName

Cell T [Employees]Sex is no longer entered as true or false as we now have 3 options as follows:
0 = Not given, 1 = Male and 2 = Female

#EMC and #EMD follow on from #EMB above

	#EMC			#EMD
BQ	[Employees]HolPay		CV	[Employees]Salary
BR	[Employees]SickPay		CW	[Employees]HoursDay
BS	[Employees]SuperPay		CX	[Employees]HoursWeek
BT	[Employees]SuperCont		CY	[Employees]DaysPerWeek
BU	[Employees]WorkComp		CZ	[Employees]Pay_Frequency
BV	[Employees]LumpA		DA	[Employees]Rate_of_pay
BW	[Employees]LumpB		DB	[Employees]Alt_Hourly1
BX	[Employees]ETPPreJuly83		DC	[Employees]Alt_Hourly2
BY	[Employees]ETPPostJuly83		DD	[Employees]Alt_Hourly3
BZ	[Employees]ETPPostJune94		DE	[Employees]Fulltime
CA	[Employees]LumpD		DF	[Employees]Permanent
CB	[Employees]LongSLeave		DG	[Employees]Package
CC	[Employees]PaidHoliday		DH	[Employees]Bonus
CD	[Employees]LLAllow		DI	[Employees]SuperPerc
CE	[Employees]TaxAdjust		DJ	[Employees]AddSuper
CF	[Employees]UnPaidLeave		DK	[Employees]SuperFund
CG	[Employees]RDO		DL	[Employees]MinSuperReq
CH	[Employees]Fund1Allow		DM	[Employees]MemberNo
CI	[Employees]Fund2Allow		DN	[Employees]Fund1Per
CJ	[Employees]IRD_No		DO	[Employees]Fund2Per
CK	[Employees]Hecs		DP	[Employees]Super1Fixed
CL	[Employees]LeaveLoading		DQ	[Employees]Super2Fixed
CM	[Employees]LeaveLoadPerc		DR	[Employees]Fund1Name
CN	[Employees]zLeavLoadTax		DS	[Employees]Fund2name
CO	[Employees]Resident		DT	[Employees]SuperCPer
CP	[Employees]TaxThreshold		DU	[Employees]SCompFixed
CQ	[Employees]Rebates		DV	[Employees]SuperCfund
CR	[Employees]TakeAcc		DW	[Employees]WorkComp
CS	[Employees]Zone		DX	[Employees]WorkFixed
CT	[Employees]TaxScaleA		DY	[Employees]WorkCompFund
CU	[Employees]FS_Credit		DZ	[Employees]TaxSSCWT

#EME, #EMF and #EMX carry on from #EMD above.

	#EME		FJ	[Employees]zYTDGross
EA	[Employees]StandardSick		FK	[Employees]ZYTDTax
EB	[Employees]StandardDaily		FL	[Employees]ZYTDNet
EC	[Employees]Sick_Ent		FM	[Employees]ZYTDTaxhours
ED	[Employees]Perc_Sick		FN	[Employees]ZYTDTaxall
EE	[Employees]zSickOwed		FO	[Employees]ZYTDNTaxall
EF	[Employees]zSicktaken		FP	[Employees]ZYTDTaxDed
EG	[Employees]Holidays_Ent		FQ	[Employees]ZYTDNtaxDed
EH	[Employees]Perc_Hol		FR	[Employees]ZYTDSuper
EI	[Employees]zHolidayowed		FS	[Employees]ZYTDSuperC
EJ	[Employees]zHolidaytaken		FT	[Employees]ZYTDFringe
EK	[Employees]LSL_Ent
EL	[Employees]PercLSL
EM	[Employees]zLSL_Owed			#EMX
EN	[Employees]zLSL_Taken		FU	[Employees]Alpha_1_20
EO	[Employees]TermLSL1		FV	[Employees]Alpha_2_20
EP	[Employees]LSLTakenSince78		FW	[Employees]Alpha_3_20
EQ	[Employees]zLSLTakenSinc93		FX	[Employees]Alpha_4_20
ER	[Employees]CasualHoliday		FY	[Employees]Alpha_5_40
ES	[Employees]HolBasis		FZ	[Employees]Alpha_6_40
ET	[Employees]DailyHourlyHol		GA	[Employees]Alpha_7_60
			GB	[Employees]Alpha_8_80
	#EMF		GC	[Employees]Real_1
EU	[Employees]zTTD_Gross		GD	[Employees]Real_2
EV	[Employees]zTTD_Paye		GE	[Employees]Real_3
EW	[Employees]zTTD_Net		GF	[Employees]Real_4
EX	[Employees]zTTd_Hours		GG	[Employees]Real_5
EY	[Employees]zTTD_Taxall		GH	[Employees]Real_6
EZ	[Employees]zTTD_NTaxAll		GI	[Employees]Real_7
FA	[Employees]zTTDDeduct		GJ	[Employees]Real_8
FB	[Employees]zTTDNdeduct		GK	[Employees]Date_1
FC	[Employees]ZTTDFringe		GL	[Employees]Date_2
FD	[Employees]zWitholding		GM	[Employees]Date_3
FE	[Employees]zTTD_FSTC		GN	[Employees]Date_4
FF	[Employees]PercLSL		GO	[Employees]Time_1
FG	[Employees]zTTDApprentice		GP	[Employees]Time_2
FH	[Employees]zTTD_Super		GQ	[Employees]Text_1
FI	[Employees]zTTD_SuperCont		GR	[Employees]Text_2

How to Use Import on Change ID
Column A should be the name of the table. This needs to match the wording on the Change ID screen. It only handles the tables listed in the screen e.g. employees, users.

Column B is the old ID. Column C is the new ID. It should end with 4 percentage marks and be saved as a CSV.

---

Other Employee Gateway #EO – API Gateway Table

A	#EO		N	[EmplOther]LastYearSick
B	[EmplOther]Employee ID		O	[EmplOther]LSLCurrent
C	[EmplOther]HolCurrent		P	[EmplOther]LSLProRata
D	[EmplOther]HolProRate		Q	[EmplOther]LSLSetAmount
E	[EmplOther]HolSetAmount		R	[EmplOther]LSLSetDate
F	[EmplOther]HolSetDate		S	[EmplOther]MonthSuperAccm
G	[EmplOther]LastDeferredLSL		T	[EmplOther]SickCurrent
H	[EmplOther]LastUpdateDef		U	[EmplOther]SickProRata
I	[EmplOther]LastUpdateHol		V	[EmplOther]SickSetAmount
J	[EmplOther]LastUpdateLSL		W	[EmplOther]SickSetDate
K	[EmplOther]LastUpdateSick		X	[EmplOther]Store
L	[EmplOther]LastYearHol		Z	[EmplOther]EmpOther
M	[EmplOther]LastYearLSL

---

Superannuation #SPR – API Gateway Table

A	#SPR
B	[Superannuation]_ID		L	[Superannuation]_Name as account name
C	[Superannuation]_Employee_LK		M	[Superannuation]_USI
D	[Superannuation]_Vendor_LK		N	[Superannuation]_AddSuper true false
E	[Superannuation]_Allowance  SUPER		O	[Superannuation]_Active true false
F	[Superannuation]_Project_LK		P	[Superannuation]_Super_Last Pay
G	[Superannuation]_GL_LK		Q	[Superannuation]_super_MTD
H	[Superannuation]_Compulsory true false		R	[Superannuation]_superYTD
I	[Superannuation]_Percentage  no % symbol		S	[Superannuation]_superTTD
J	[Superannuation]_Fixed as a dollar amount
K	[Superannuation]_Account_number

---

Allowances #AL – API Gateway Table

A	#AL
B	[Allowances]_ID		Q	[Allowances]_CalcC
C	[AllowMaximumances]_Description		R	[Allowances]_Hol_Calc
D	[Allowances]_Type		S	[Allowances]_Sick_Calc
E	[Allowances]_Int_Type		T	[Allowances]_Super_Calc
F	[Allowances]_Calc_Method		U	[Allowances]_GCPosition
G	[Allowances]_Apprenticepay		V	[Allowances]_GCName
H	[Allowances]_Amount		W	[Allowances]_Position
I	[Allowances]_Cost_Center_Allowed		X	[Allowances]_SuperCCalc
J	[Allowances]_G/L Code		Y	[Allowances]_WorkCompCalc
K	[Allowances]_HolidayPay		Y	[Allowances]_RDOCalc
L	[Allowances]_BonusRate		Z	[Allowances]_EarnerPremiumCalc
M	[Allowances]_SickPayTaken		AA	[Allowances]_FringebenefitCalc
N	[Allowances]_Maximum		AB	[Allowances]_Class
O	[Allowances]_CalcA		AC	[Allowances]_CustomSort
P	[Allowances]_CalcB		AD	[Allowances]_Prov_gl

---

Working Transactions #TTA – API Gateway Table

Comments

It cannot be emphasized enough that this function bypasses all checks and balances that SapphireOne makes when executing a Payrun. During testing I have noted that it will import anything into a Working Paysheet. The user will have to decide exactly what they will be importing and then see how SapphireOne manages the Pay runs from there.

While this function will import just about anything that appears in a Working transaction the user is probably advised to import the absolute minimum amount of data that is required to allow SapphireOne to complete the Payrun.

For employees working on an hourly basis the only items in the import would be, Employee ID. Start Date, End Date and the Hours worked and let the SapphireOne handle the rest.

Remember that the rules for Gateway Importing are as follows:

• Data in a spreadsheet Cell – Re-write the data file with the new data.
• Spreadsheet Cell is Blank – Leave the data for that cell in the data file as is.

This second rule aove only becomes a problem if a data field with data in it has to be removed and made blank. Note that this function in gateway imports Working Transactions only and once processed by executing a pay run will be immediately deleted from the system. Multiple lines for each Employee in the Working Paysheet area may be imported. The format is similar to the #IVQ table in that each additional Line in the spreadsheet appears in the Working Paysheet area as a new line.

Operational Procedure.

• Syntax – SapphireOne looks for the #TTA in Column A to indicate create a Working Transaction for an employee and then reads columns B, C and D to enter in the details as listed below into the Employee’s Payslip Details area.
• Reading Data – It stops after reading across to column D. SapphireOne now goes to the very next row in the spreadsheet starting with column A.
• First Line – It now reads from column A to column E and creates the first line in the Working Paysheet area.
• CEll F – It then checks column F for ^^^^ and if they do not exist it then goes to the next row in the spreadsheet.
  • Additional Lines – It starts again at column A and reads from column A to E and adds a second line to the Working Paysheet area again checking for ^^^^ in column F.
  • End of Transaction – It repeats the above until ^^^^ are found in Column F. SapphireOne will stop entering new lines into the Working Paysheet area for the current Employee save it.
• Next Transaction – It will start on the next employee by looking for the #TTA in the next row in the spreadsheet starting at column A and repeat the procedure.
• End of File – This is always indicated by the entry or %%%% in the last row in the import spreadsheet in cell A.

A	#TTA
B	[Employees]Working_Transactions_Employee_ID
C	[Employees]Working_Transactions_StartDate
D	[Employees]Working_Transactions_FinishDate
	Following Rows   Working Paysheet lines: Repeat on a line by line basis until ^^^^ is entered into Column F.
A	[Employees]Working_Transactions_Allowance_ID
B	[Employees]Working_Transactions_GL_Code
C	[Employees]Working_Transactions_Employee_Department_ID
D	[Employees]Working_Transactions_Number of Hours
E	[Employees]Working_Transactions_Rate
F	^^^^  Last Line indicator in the current working transaction

Note that for this import function, a number of columns are actually used to import two items as may be seen in the above table. For example, Column A will hold both the #TTA syntax and the Allowance _ID. Column B the Employee_ID and the GL_Code and so on.

---

Assets #AS

A	#AS		AQ	[Assets]Residual_A		CG	[Assets] Real_8
B	[Assets]ID		AR	[Assets]TTD_A		CH	[Assets] Date_1
C	[Assets]Name		AS	[Assets]DepType_B		CI	[Assets] Date_2
D	[Assets]Description		AT	[Assets]Percentage_B		CJ	[Assets] Date_3
E	[Assets]ClassA		AU	[Assets]Residual_B		CK	[Assets] Date_4
F	[Assets]ClassB		AV	[Assets]TTD_B		CL	[Assets] Time_1
G	[Assets]DupID		AW	[Assets]Period		CM	[Assets] Time_2
H	[Assets]Field7		AX	[Assets]GL_Asset		CN	[Assets]Text_1
I	[Assets]Serial_No		AY	[Assets]GL_Sale		CO	[Assets]Text_2
J	[Assets]Active		AZ	[Assets]GL_Dep		CP	[Assets]Pict_1
K	[Assets]Location		BA	[Assets]GL_ProvDep		CQ	[Assets]Pict_2
L	[Assets]Location_Name		BB	[Assets]G/L_Disposal		CR	[Assets]DeprComp
M	[Assets]Location_Address1		BC	[Assets]DepCurPeriod_A		CS	[Assets]Bar_Code
N	[Assets]Position		BD	[Assets]DepCurPeriod_B		CT	[Assets]Last_Read
O	[Assets]Location_Notes		BE	[Assets]Profit_A		CU	[Assets]Last_Read_Date
P	[Assets]Location_Class		BF	[Assets]Profit_B		CV	[Assets]AddCost_B
Q	[Assets]DepCurYear_A		BG	[Assets]CapGain_A		CW	[Assets]BalAdj_A
R	[AssetsDepCurYear_B]		BH	[Assets]Sell_Price		CX	[Assets]BalAdj_B
S	[Assets]AddCost_A		BI	[Assets]DepPreCurYear_A		CY	[Assets]GLBalAdj
T	[AssetsAdditionDate]		BJ	[Assets]DepPreCurYear_B		CZ	[Assets]RevReserve
U	[Assets]Keywords		BK	[Assets]RealValue_A		DA	[Assets]GLRevReserve
V	[Assets]Contact		BL	[Assets]RealValue_B		DB	[Assets]Old Asset
W	[Assets]Contact_Name		BM	[Assets]ReplacementV		DC	[Assets]Department
X	[Assets]OpenWDV_A		BN	[Assets]CapGain_B		DD	[Assets]ServiceUsage
Y	[Assets]Date_Purchased		BO	[Assets]Sale_Date		DE	[Assets]RepairDate
Z	[Assets]Purchase_Price		BP	[Assets]OpenWDV_B		DF	[Assets]ServiceDate
AA	[Assets]Warranty_Expiry		BQ	[Assets]GLCapGain		DG	[Assets]LoanDate
AB	[Assets]Depreciated		BR	[Assets]Alpha_1_20		DH	[Assets]NextService
AC	[Assets]Disposal_Date		BS	[Assets]Alpha_2_20		DI	[Assets]GL_TaxIn
AD	[Assets]Business_Use		BT	[Assets]Alpha_3_20		DJ	[Assets]GL_TaxOut
AE	[Assets]Maximum_Depr		BU	[Assets]Alpha_4_20		DK	[Assets]SelfAssessed
AF	[Assets]Insurance_Polic		BV	[Assets]Alpha_5_40		DL	[Assets]MotorVehicle
AG	[Assets]Insurer		BW	[Assets]Alpha_6_40		DM	[Assets]Calc
AH	[Assets]Ins_Expiry_Date		BX	[Assets]Alpha_7_60		DN	[Assets]Write
AI	[Assets]Ins_Notes		BY	[Assets]Alpha_8_80		DO	[Assets]Draw
AJ	[Assets]Percentage_Sold		BZ	[Assets]Real_1		DP	[Assets]Notes
AK	[Assets]By		CA	[Assets] Real_2		DQ	[Assets]Company
AL	[Assets]Mod_Date		CB	[Assets] Real_3
AM	[Assets]Life_A		CC	[Assets] Real_4
AN	[Assets]Life_B		CD	[Assets] Real_5
AO	[Assets]DepType_A		CE	[Assets] Real_6
AP	[Assets]Percentage_A		CF	[Assets] Real_7

The Asset import function in the table should be used first to setup the assets with at least some basic details. The Asset ID listed in column B below then enables the system to place the details for the Computer Equipment Page in the correct record. Be careful if the ID entered in the table below does not match an Asset ID already in SapphireOne as it will create a new Asset record. Note column B is an Asset ID and ce is an abbreviation for Computer Equipment Page.

Asset Computer Equipment Page #ASC

A	#ASC		Q	[Assets]ce_Host
B	[Assets]ID  ——Note ASSET ID		R	[Assets]ce_FireWallZone
C	[Assets]ce_Owner		S	Assets]ce_IPrange_Mininum
D	[Assets]ce_Address		T	Assets]ce_IPrange_Maximum
E	[Assets]ce_Office		U	Assets]ce_Service
F	[Assets]ce_Rack		V	Assets]ce_Software
G	[Assets]ce_Shelf		W	Assets]ce_Software_version
H	[Assets]ce_Box Configuration		X	Assets]ce_Software_Version_Date
I	[Assets]ce_DeviceType		Y	Assets]ce_Serial #
J	[Assets]ce_Manufacturer		Z	Assets]ce_License #
K	[Assets]ce_Platform		AA	Assets]ce_User
L	[Assets]ce_Version		AB	Assets]ce_Active
M	[Assets]ce_ID_Address_Internal		AC	Assets]ce_Updated Date
N	[Assets]ce_DHCP		AD	Assets]ce_Notes
O	[Assets]ce_IP_Address_External		AE	Assets]ce_Remote Access_Procedure
P	[Assets]ce_NetBIOS_Name

Asset Class #AZ

A	#AZ		C	[AssetClass]Name
B	[AssetsClass]ID		D	[AssetClass]Notes

Asset Locations #LO

A	#LO		E	[Location]Phone		I	[Location]Contact_Name
B	[Location]ID		F	[Location]Field5		J	[Location]Class
C	[Location]Name		G	[Location]Field6		K	[Location]Blank
D	[Location]Location		H	[Location]Contact

Asset Locations Class #LZ

A	#LZ		C	[LocationClass]Name
B	[LocationClass]ID		D	[LocationClass]Notes

Asset Method #ME

A	#ME		D	[MethodName]Order		G	[MethodName]Notes
B	[MethodName]ID		E	[MethodName]DateCreated
C	[MethodName]Name		F	[MethodName]PeriodCreated

Assets History #HJ

A	#HJ		U	[HistoryAssets]Amount_B
B	[HistoryAssets]Sequence		V	[HistoryAssets]GL_D
C	[HistoryAssets]Type		W	[History Assets]DontTransfer
D	[History Assets]Date		X	[HistoryAssets]Amount_C
E	[HistoryAssets]Amount		Y	[HistoryAssets]GL_E
F	[HistoryAssets]Internal_Ref		Z	[HistoryAssets]GL_F
G	[HistoryAssets]External_Ref		AA	[HistoryAssets]RevsaleStore
H	[HistoryAssets]Transfer		AB	[HistoryAssets]PurchaseFlag
I	[HistoryAssets]GL_A		AC	[HistoryAssets]Amount_D
J	[History Assets]GL_B		AD	[HistoryAssets]Amount_E
K	[HistoryAssets]Period		AE	[History Assets]Amount_F
L	[HistoryAssets]Asset_LK		AF	[HistoryAssets]MethodLk
M	[HistoryAssets]Notes		AG	[HistoryAssets]GL_G
N	[History Assets]Date_Due		AH	[HistoryAssets]RunNo
O	[HistoryAssets]Location		AI	[HistoryAssets]GL_H
P	[HistoryAssets]Contact		AJ	[HistoryAssets]Tax_Code
Q	[History Assets]GL_C		AK	[History Assets]Vendor
R	[HistoryAssets]Partial		AL	[HistoryAssets]Tax_Rate
S	[HistoryAssets]Keywords		AM	[HistoryAssets]Tax_Amount
T	[HistoryAssets]Reversed		AN	[HistoryAssets]Department

Asset Audit Lines #AU

A	#AU		K	[AuditLines]CapGain
B	[AuditLines]AssetLk		L	[AuditLines]BalAdj
C	[AuditLines]MethodLk		M	[AuditLines]AddCost
D	[AuditLines]ClassA		N	[AuditLines]DepCurPeriod
E	[AuditLines]ClassB		O	[AuditLines]DepCurYear
F	[AuditLines]Location		P	[AuditLines]RevalValue
G	[AuditLines]Period		Q	[AuditLines]DepPreCurYear
H	[AuditLines]OpenWDV		R	[AuditLines]TransactionLk
I	[AuditLines]Sale		S	[AuditLines]GST
J	[AuditLines]Profit

Asset Transactions #AV

A	#AV		AA	[Asset_Transactions]RevsaleStore
B	[Asset_Transactions]Type		AB	[Asset_Transactions]PurchaseFlag
C	[Asset_Transactions]Date		AC	[Asset_Transactions]Amount_D
D	[Asset_Transactions]Amount		AD	[Asset_Transactions]Amount_E
E	[Asset_Transactions]Internal_Ref		AE	[Asset_Transactions]Amount_F
F	[Asset_Transactions]External_Ref		AF	[Asset_Transactions]MethodLk
G	[Asset_Transactions]Transfer		AG	[Asset_Transactions]GL_G
H	[Asset_Transactions]GL_A		AH	[Asset_Transactions]RunNo
I	[Asset_Transactions]GL_B		AI	[Asset_Transactions]GL_H
J	[Asset_Transactions]Sequence		AJ	[Asset_Transactions]NextServiceDate
K	[Asset_Transactions]Period		AK	[Asset_Transactions]NextServiceUnit
L	[Asset_Transactions]Asset_Lk		AL	[Asset_Transactions]ReversedSeq
M	[Asset_Transactions]Notes		AM	[Asset_Transactions]Vendor
N	[Asset_Transactions]Date_Due		AN	[Asset_Transactions]TaxCode
O	[Asset_Transactions]Location		AO	[Asset_Transactions]Doofus
P	[Asset_Transactions]Contact		AP	[Asset_Transactions]Tax_Rate
Q	[Asset_Transactions]GL_C		AQ	[Asset_Transactions]Tax_Amount
R	[Asset_Transactions]Partial		AR	[Asset_Transactions]Department
S	[Asset_Transactions]Keywords		AS	[Asset_Transactions]By
T	[Asset_Transactions]Reversed		AT	[Asset_Transactions]Date_Created
U	[Asset_Transactions]Amount_B		AU	[Asset_Transactions]Time_Created
V	[Asset_Transactions]GL_D		AV	[Asset_Transactions]Post_Date
W	[Asset_Transactions]DontTransfer		AW	[Asset_Transactions]Post_Time
X	[Asset_Transactions]Amount_C		AX	[Asset_Transactions]Deleted
Y	[Asset_Transactions]GL_E
Z	[Asset_Transactions]GL_F

---

General Ledger #GL?

Notes

All values are to be entered without a plus or negative symbol leading the number. SapphireOne will determine from the data cell in the import spreadsheet if it is to be written in as a credit or debit.

These ID’s and details are from Bondi Blue.

Using #GLA or #GLB: For exporting data out, you may use either #GLA or #GLB as these have been setup so that the data may be exported to a text file and looked at using a spreadsheet.

If you use #GLA to import your data and take care to ensure that the ID written into E is a valid class ID that you have already created in the data file. SapphireOne will pick up the class ID entered and use that Class from then on. If it does not find a valid Class ID, the G/L account will not be linked to a Class and this will have to be done manually by going in to the G/L Account in error and typing in the Class ID required.

You do also have the option of using #GLB and directly creating the G/L account and the classes at the same time. But this increases the prospect of errors creeping in as each individual Class must have the same Sub A, Sub B, and Sub C positions. Any errors in setting these Class details will result in the errors being incorporated into the data file.

Remember that the #GLD table carries directly on from #GLA and does not access the #GLB table at all.

See the comments at the beginning of the section on the General Ledger.

Also, if the system finds an error with a line it will ignore the error and proceed to the next line.

Please check that ALL of the G/L accounts have been imported or exported correctly.

• A cell – Either #GLA or #GLB as required.
• B cell – This is the complete G/L account ID and the Department ID. E.g.   1000-0.
• C cell – This is the G/L Account Name. E.g.  Sales – Appliances.
• D cell – This is the Department ID. E.g. 0.
• E cell –  This is the Class ID. E.g 10
• O cell – This is the Class name E.g. Sales. (They are separated being in different tables, #GLA and #GLB).
• F cell – This is the G/L Account ID only. E.g. 1000.
• G cell – Debit = 1   and   Credit = 0. This is in a G/L Inquiry, (Details Page, Data Entry Controls area)
• H cell – This is for the tax rate which is normally linked to K the tax code but in this Gateway import it is not yet linked and a Tax Code will have to be inserted into  column K.
• K cell – Enter the tax code, for Australia it is usually S.
• I & J cells – Alternative ID and name for the GL Account. (2^nd page GL Inquiry).
• L M N cells – These are all in GL Classes. Look in the first page and the Report Position Area.
  • Order – Position A, Position B and Position C.
• BD 0 to 12 – A Budget Deficit, will write in a budget deficit. 100 will be displayed as ($100.00) indicating a negative amount.
• BC 0 – 12 – A Budget Credit will write in a budget credit. 100 will be displayed as $100.00 indicating a positive amount.
  • Period Pairs – The user should also note that they are in 13 pairs, a pair for each period from period 0, zero all the way up to period 12.
  • One entry – So, only a single entry for each period is required otherwise you will confuse the system badly. A single entry for BD0 or BC0, a single entry for BD1 or BC1 and so on until you get to BD12 or BC12 or as required.

A	#GLA			#GLB
B	[GeneralLedger] Account ID & Dept ID		L	[GeneralLedger]Sub_A
C	[GeneralLedger] AccountName		M	[GeneralLedger]Sub_B
D	[GeneralLedger] Department_ID		N	[GeneralLedger]Sub_C
E	[GeneralLedger] Class_ID		O	[GeneralLedger]Class_Name
F	[GeneralLedger]Account_ ID		P	[GeneralLedger]Custom_Class
G	[GeneralLedger]DebitOrCredit
H	[GeneralLedger]Tax_Rate
I	[GeneralLedger]Alter_ID
J	[GeneralLedger]Alter_Name
K	[GeneralLedger]Tax_Code

#GLA is as normal followed by #GLB as seen in the table above.
However, when the #GLD table is used below, #GLA is immediately followed by the table #GLD below. #GLB is ignored and not used. Columns L through to P in #GLB are not read or written and are replaced by columns L through to P, in #GLD.

	#GLD
L	[GeneralLedger] BD0  –  This Yr Per 0		Z	[GeneralLedger] BD7 – This Yr Per 7
M	[GeneralLedger] BC0  + This Yr Per 0		AA	[GeneralLedger] BC7 = This Yr Per 7
N	[GeneralLedger] BD1  – This Yr Per 1		AB	[GeneralLedger] BD8 – This Yr Per 8
O	[GeneralLedger] BC1 + This Yr Per 1		AC	[GeneralLedger] BC8 = This Yr Per 8
P	[GeneralLedger] BD2 – This Yr Per 2		AD	[GeneralLedger] BD9 – This Yr Per 9
Q	[GeneralLedger] BC2 + This Yr Per 2		AE	[GeneralLedger] BC9 = This Yr Per 9
R	[GeneralLedger] BD3  – This Yr Per 3		AF	[GeneralLedger] BD10 – This Yr Per 10
S	[GeneralLedger] BC3 + This Yr Per 3		AG	[GeneralLedger] BC10 + This Yr Per 10
T	[GeneralLedger] BD4 – This Yr Per 4		AH	[GeneralLedger] BD11 – This Yr Per 11
U	[GeneralLedger] BC4 + This Yr Per 4		AI	[GeneralLedger] BC11 + This Yr Per 11
V	[GeneralLedger] BD5  – This Yr Per 5		AJ	[GeneralLedger] BD12 – This Yr Per 12
W	[GeneralLedger] BC5 + This Yr Per 5		AK	[GeneralLedger] BC12 + This Yr Per 12
X	[GeneralLedger] BD6  – This Yr Per 6
Y	[GeneralLedger] BC6 + This Yr Per 6

General Ledger #GLR

This table is used to import existing General ledger accounts budgets from period 0, zero, to period 30.

A	#GLR
B	[GeneralLedger] Account ID & Dept ID		R	[GeneralLedger]Revised-Budget Period_15
C	[GeneralLedger]Revised-Budget Period_0		S	[GeneralLedger]Revised-Budget Period_16
D	[GeneralLedger]Revised-Budget Period_1		T	[GeneralLedger]Revised-Budget Period_17
E	[GeneralLedger]Revised-Budget Period_2		U	[GeneralLedger]Revised-Budget Period_18
F	[GeneralLedger]Revised-Budget Period_3		V	[GeneralLedger]Revised-Budget Period_19
G	[GeneralLedger]Revised-Budget Period_4		W	[GeneralLedger]Revised-Budget Period_20
H	[GeneralLedger]Revised-Budget Period_5		X	[GeneralLedger]Revised-Budget Period_21
I	[GeneralLedger]Revised-Budget Period_6		Y	[GeneralLedger]Revised-Budget Period_22
J	[GeneralLedger]Revised-Budget Period_7		Z	[GeneralLedger]Revised-Budget Period_23
K	[GeneralLedger]Revised-Budget Period_8		AA	[GeneralLedger]Revised-Budget Period_24
L	[GeneralLedger]Revised-Budget Period_9		AB	[GeneralLedger]Revised-Budget Period_25
M	[GeneralLedger]Revised-Budget Period_10		AC	[GeneralLedger]Revised-Budget Period_26
N	[GeneralLedger]Revised-Budget Period_11		AD	[GeneralLedger]Revised-Budget Period_27
O	[GeneralLedger]Revised-Budget Period_12		AE	[GeneralLedger]Revised-Budget Period_28
P	[GeneralLedger]Revised-Budget Period_13		AF	[GeneralLedger]Revised-Budget Period_29
Q	[GeneralLedger]Revised-Budget Period_14		AG	[GeneralLedger]Revised-Budget Period_30

---

Project General Ledger #PRGL

The table on the left above will only import general Ledger headers into a project.
The table on the right above will only import the period budget data into a project if the General Ledger header already exists in the project.

A	#PRGL   (Create headers only)		A	#PRGL   (Project Data Import)
B	[PR_GL] GL Code or ID		B	[PR_GL] GL Code or ID
C	[PR_GL] Project Code or ID		C	[PR_GL] Project Code or ID
D	[PR_GL] Period must be -1		D	[PR_GL] Period 1 to 30
			E	[PR_GL] Budget Debit.
			F	[PR_GL] Budget Credit

This could be done in the one import file as seen below.

	A	B	C	D	E	F
1		G/L ID	Project ID	Per	Project_Debit	Project_Credit
2	#PRGL	1010-0	1A	-1
3	#PRGL	1010-0	1A	1		111.11
4	#PRGL	1010-0	1A	2	222.22
5	#PRGL	1010-0	1A	3		333.33
6	#PRGL	1010-0	1A	4	444.44
7	#PRGL	1010-0	1A	5		555.55
8	#PRGL	1010-0	1A	6	666.66
9	#PRGL	1010-0	1A	7		777.77
10	%%%%

• A1 cell – SapphireOne at A1 will ignore the whole line it as it has no instructions in column A.
• A2 cell – At A2, it will write the G/L header into project 1A as it sees the period -1.
• Remaining lines – For the remaining lines it will then proceed to write various credit and debit amounts in from period 1 through to period 7.
  • Period #0 – You could go up to period 30 although this would get somewhat unwieldly.

For General Ledger account 1020-0 it will have to be repeated agai. Then repeated for each and every G/L header and subsequent budget period data that is to be written into the project.

When it sees #### or %%%% in column A, it will stop processing data as that indicates to the system that it is not to read or write any further doe the spreadsheet.

• Two Step – Or you could use a 2-step process as follows.
  1. For any G/L account that is not in a project it will have to be created manually one at a time, or created by using the left-hand table above as in line 2 above!
  2. Once this has been done the table on the right table may then be used to enter in the data for each period as required for the G/L headers that you have now imported into the project.

From the documentation for #GLD on a previously page E and F in the above tables will be one or the other, as the budget amount will either be written in as a Debit or a Credit!

---

Tracking Notes #TTN

This table has been provided fpr the import or exporting of tracking notes.

A	#TN
B	[TrackingNotes]_Sequence No		M	[TrackingNotes]_Reference
C	[TrackingNotes]_Record Type		N	[TrackingNotes]_Priority
D	[TrackingNotes]_Title		O	[TrackingNotes]_Component
E	[TrackingNotes]_Status		P	[TrackingNotes]_CustomA1
F	[TrackingNotes]_Type		Q	[TrackingNotes]_EmployeeLink
G	[TrackingNotes]_Product		R	[TrackingNotes]_ProjectLink
H	[TrackingNotes]_Document		S	[TrackingNotes]_Description
I	[TrackingNotes]_TN_Alpha1_30		T	[TrackingNotes]_RiskLevel
J	[TrackingNotes]_TN_Alpha2_40		U	[TrackingNotes]
K	[TrackingNotes]_ClientLink		V	[TrackingNotes]
L	[TrackingNotes]_VendorLink		W	[TrackingNotes]

---

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