Sage 200:Import Nominal Journal

This type of PostTrans import, imports Nominal Journal, which can be input manually, data from an ODBC connection or imported from CSV file.

Import Sage 200 Nominal Journal Features

Detailed conformation before transaction importation
Highlighted Validation of codes and data
Search for Departments, Tax code, etc., using our In-Cell Searching
Add formula, vba or macros to manipulate the imported data
Cannot Post Sage journal twice. Writes POSTED in row, with transaction Ref generated.
Can skip Zero values enabling quick entry template to be created
Create valid Nom/Cost Centre/Department combination Pro
Chain posting, allows a second template to post, for Back to Back order Pro
Easily Add your own validation, using the THBefDisplayMessage tag
Easy importation from CSV file

Example Sage 200 Journal in Excel

Simple Sage 200 Stock Adjustment in Excel

Above is a screen shot of the Nominal Journal layout automatically generated after selecting this type of transaction. Many of the fields are optional and are filled in automatically by PostTrans.

The cell A1 has a tag "Sage200transNom" to designate the type of import. It uses a series of tags, placed in row 28, to identify the lines, or splits, in the Sage Line 50 Nominal Journal.

These tags, listed in table below, can be easily added to a spreadsheet using the Tags button. See Online Training for an explanation of this.

Customised User Defined Fields

For this type of import, PostTrans can be map Excel fields to customised user defined fields.

Template Structure for Transaction Data

The template must have tag ‘Sage200TransNom’ in the comment of cell A1 to instruct PostTrans that we are interested in importing Transactions, and that the sheet is a PostTrans Template. Pressing Import or Extract on a blank sheet will either open an existing example or create basic template for you to customise.

On-line Training video of how all this works

Common Features

In-Cell Searching

Any Tag ending with ^ will have a In-cell search facility to look up associated values. Use SPACE + TAB to see all values, or SPACE and TEXT to perform a text search on description. These are returned to the cell as "CODE, DESCRIPTION". PostTrans only uses the CODE and ignores the comma and anything after it, thus data from a field would only need the CODE part of the data if from an external imported file.

In-cell Searching can be turned off by removing the ^ from the end of the tag name, or name in first line of comment (Header cells) to disable in-cell searching for a single column, or header cell. All in-cell searching can be turned off in Setup. In-cell searching.

Cursor Path

The path the cursor takes through the sheet, when entering data manually, can also be easily defined in Setup. See Set Cursor Path

Importing the Data

Simply press the Import button to validate and import the data into Sage 200. PostTrans will read the Header cells and then process the rows of data until TLNomCode column contains no data. The Sage Company imported into can be set in the Setup window.

PostTrans ONLY edits those fields listed on the sheet when importing, thus any existing values, on a record, are unaffected. New records may have default values for certain fields.

Validation

Any values and associated codes will be invalid during import. The cursor will be moved to highlight any problems in data.

POSTED Document Ref

After Posting a transaction, PostTrans will write "POSTED:" and the transaction reference number in column A (tag TLPosted). Lines marked as "POSTED" are then ignored by PostTrans when re-posting. So, transactions cannot easily be posted twice. So the transaction can not easily be posted twice. The user would have to remove "POSTED" from column A in order to post the transaction a second time. The user would have to remove "POSTED" from column A, to post the transaction a second time.

Confirmation before Posting Transaction

Just before posting the validated transaction, PostTrans will optionally show a transaction summary for the user to validate. This can be turned off in Setup window.

File Import Button

This can be enabled in Setup to easily import CSV, or fixed length files, into sheet for importation. Formula can also be added, or macros/VBA to manipulate this data before importation. Import from CSV file.

Clear Button

The Clear button will clear all values on the sheet, according to the row just above the tag row, and copy down any formula. Clear Transaction and copy down formula

Evaluation and License

This functionality will work with Sage Demo data without a license. A license is required to import into any other Sage Company.
A subscription be purchased, and cancel on-line at any time very easily. Pricing

What else can I do?

Other Sage 200 Extract/Import types

Frequently Asked Questions?

Got another question? It may well be covered on our Frequently Asked Questions page.

Demonstration Video

The video shows how to use PostTrans for Sage 200, to import Nominal Journal into Sage 200 from within Excel. It shows how easy it is to enter Nominal Journal using PostTrans in-cell searching, to create a Contra Journal and a Contra Journal with VAT.

Sage 200 Importing Nominal Journal from Excel

	0:00	Video Introduction
	1:50	Posting a Contra Nominal Journal
	4:00	Showing the Journal in Sage 200
	4:30	Cost Centre and Department in separate columns
	5:55	Posting a Contra Nominal Journal with VAT

Download Nominal Journal Template
see 'Nom' sheet

To use the template, with Sage 200, install PostTrans. Download
Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price
Watch the On-line Training course on how to use and customise the templates.

Sage 200 Nominal Journal Import Options

Nominal Import has the following options, which are accessible by pressing the Setup button on the toolbar:

Simple Sage 200 Stock Adjustment in Excel

Option	Description
Turn off all Cell Lookups	This option turns all in cell searching ON/OFF more about Sage Line 50 Related Code Search
Show Summary before posting	This turns on and off Journal summary before posting. See below… more about Sage Line 50 Related Code Search
Create valid Nom/Cost Centre/Department comb.	Auto Create valid Nom/Cost Centre/Department comb. If not presnet in system. It uses the the first matching nominal code as a basisi for the settings for newly created option.

Confirmation window showing journal summary of transaction.

You would want to turn the summary off if you are importing a large number of Adjustment lines from an external file.

Tags which map to Sage 200

Not all of these tags/columns are needed. The table below lists the required and recommended:

Tag Name	Data Type	Description
Header:
THRef	Text 30 Head Chg XML	Text to use if TLDetails is blank. Change causes a new transaction on change if in lines
THJournalType^	Text 3 Head XML	Type of Journal. Default Pending Journal In Cell Searching values:Journal Type NOM, Normal Journal REV, Reversing Journal REC, Recurring Journal
THDate	Date Head Chg XML	Overide Heading Date, thus causing a new transaction on change
Extra Dates:
THReversalDate	Date Head Chg XML	Reversal date, if REV,Accrual Journal
THRecDates1	Date Head Chg XML	Recurring Date list, if transaction type REC. List dates down sheet from this tag to tag line, or until blank cell
THRecDates2	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates3	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates4	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates5	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates6	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates7	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates8	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates9	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates10	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates11	Date Head Chg XML	Recurring Date list, if transaction type REC.
THRecDates12	Date Head Chg XML	Recurring Date list, if transaction type REC.
Tax Defaults:
THTaxType^	Text 1 Head XML	Default Tax type, which selects Input or Output Nominal Code Sage 50 Vat Journal In Cell Searching values:Tax Type 0, N/A 1, Input Goods 2, Input Tax 3, Output Goods 4, Output Tax
THTaxCode^	Text 1 Head XML	Default Tax Code. Make sure the format of the cell is “General” else search may not work properly In Cell Searching values in table Tax
Important Fields:
TLPosted	Text Req	Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row
Nominal Lines:
TLNomCode^	Text 16 EOF Req XML	Nominal Code. This can be simple nominal code and express the Cost Centre and Department in TLCostCentre /TLDepartment, or entered in the format NominalCode-CostCenter-Department, as provided by the in cell search function. How Nom/CC/Dep are specified In Cell Searching values in table NOM
TLDepartment^	Text 3 Pro	Department. This column is provided to allow the Department to be expressed in a separate cell. How Nom/CC/Dep are specified In Cell Searching values in table DEP
TLCostCentre^	Text 20	CostCentre. Make sure the format of the cell is “General” else search may not work properlydem How Nom/CC/Dep are specified In Cell Searching values in table CC
TLDetails^	Text 60 Req XML	Details. If Blank will use THRef in header Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLDebit^	Currency XML	Debit. Posttrans will ignore line if zero value in TLDebit and TLCredit Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLCredit^	Currency XML	Credit. This column is provided to allow the Department to be expressed in a separate cell. Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLAmount^	Currency XML	Amount (used instead of Debit and Credit, thus +/- values in one column). Posttrans will ignore line if Zero Value in TLDebit and TLCredit Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLAnalCode^	Text 20 Pro	Additional Analysis Code In Cell Searching values in table NOMANALCODE
Tax:
TLTaxType^	Integer XML	Tax type, which selects Input or Output Nominal Code Sage 50 Vat Journal In Cell Searching values:Tax Type 0, N/A 1, Input Goods 2, Input Tax 3, Output Goods 4, Output Tax
TLTaxCode^	Integer XML	Tax Code. Make sure the format of the cell is “General” else search may not work properly In Cell Searching values in table Tax
TLTaxAmount	Currency Read	VAT Amount calculated based on Credit/Debit and Tax code on line Sage 50 Vat Journal
Contra:
THContraNom^	Text 16 Head	Post the opposite side of the nominal value to this nominal code. For Header use only. Sage 50 Contra Nominal Journal In Cell Searching values in table NOMCODECONTRA
THContraDesc	Text 60 Head	Description for Nominal Contra line Sage 50 Contra Nominal Journal
THContraDept^	Text 8 Head	Department for Contra line. Make sure the format of the cell is “General” else search may not work properly more about Contra Nominal Journal In Cell Searching values in table DEP
THContraCC^	Text 8 Head	Cost Centre for Contra line. Make sure the format of the cell is “General” else search may not work properly more about Contra Nominal Journal In Cell Searching values in table CC
TLForceNewTrans	Yes/NoTF	If YES then force a new transaction
Header:Before Posting:
THBefSWCompany^	Text 6 Head Chg	This will cause PostTrans to change company before posting if different from current Sage Line 50 Company. Thus can be user to post transactions into different companies, thus intercompany transactions are possible. It uses a 6 letter company code, which found by pressing the `Select Company` button in system setup. Posting to multiple companies In Cell Searching values in table CompanySelect
THBefDisplayMess	Text 60 Head	If contains value, then this text is displayed, and import cannot take place until this text if clear. Thus you can your own formula, macro or VBA code to stop import until a certain condition. Validating Transaction before posting
THBefDisplayMessYN	Text 60 Head	If contains value, then this text is displayed. Asks Yes/No to continue Validating Transaction before posting
Header - After Posting:
THAftProcessSheet	Text 60 Head	After posting this sheet, switch to work book with this name, and post that. Allows back to back purchase orders, or multiple company transfers. PRO Posting multiple sheets
THAftSwitchSheet	Text 60 Head	After posting this sheet, switch to work book with this name PRO Posting multiple sheets
Header - After Save:
THAftSavePATH	Text 60 Head Read	If THAftSavePATH and THAftSaveFilename specified, will save as PATH + ‘\YYYMM\’ + Filename + "_0000.xls". YYYMM can be overridden with value in THSaveSubDirectory cell. more about saving
THAftSaveSubDir	Text 60 Head Read	See THAftSavePATH more about saving
THAftSaveFilename	Text 60 Head Read	See THAftSavePATH. Example ‘ABAP01’ will save as ABAP01_0001.xls. more about saving
Import File:
THImportFullName	Text 500 Head	Full PATH and Filename of the file just imported using the `Import` button. This is required if you set the directory to move file after posting transaction. File Import instructions
THImportFileName	Text 500 Head	Filename of the file just imported using the `Import` button File Import instructions
ObjectStore Mappings:
ObjectStore Mappings Lines:
THUser1	Text 30 Head XML	10 user defined fields which can be mapped to Sage ObjectStore Builder customisations How to map Sage ObjectStore Builder customisations
THUser2	Text 30 Head XML	User2
THUser3	Text 30 Head XML	User3
THUser4	Text 30 Head XML	User4
THUser5	Text 30 Head XML	User5
THUser6	Text 30 Head XML	User6
THUser7	Text 30 Head XML	User7
THUser8	Text 30 Head XML	User8
THUser9	Text 30 Head XML	User9
THUser10	Text 30 Head XML	User10
TLUser1	Text 30 XML	10 user defined fields which can be mapped to Sage ObjectStore Builder customisations How to map Sage ObjectStore Builder customisations
TLUser2	Text 30 XML	User2
TLUser3	Text 30 XML	User3
TLUser4	Text 30 XML	User4
TLUser5	Text 30 XML	User5
TLUser6	Text 30 XML	User6
TLUser7	Text 30 XML	User7
TLUser8	Text 30 XML	User8
TLUser9	Text 30 XML	User9
TLUser10	Text 30 XML	User10

Req = Field is required EOF = End of file, Stop when no more values

XML = Un-attended PostTrans XML API for Sage 200

Head A header tag can be used in header section, before row 30, of the transaction template. Or in the rows data of data, below row 30, of the template like a traditional CSV importer.

With data type Yes/No fields, if cell starts with 'y' or 'Y' is taken as TRUE

Where ^ denotes a In Cell Code Search, delete ^ at end of tag, to turn off code search

All Text fields are trimmed (spaces before) by default. Add ' to the end of the tag name to stop this.

Related Example Sheets

You can easily access the example sheets by pressing Help button, on the PostTrans Toolbar to access these related examples, or downloading them from the links below.