Sage 50: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.
Related Pages
Import Sage 50 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
- 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 50 Journal in Excel
Simple Nominal 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 "Sage50transNom" 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.
Template Structure for Transaction Data
The template must have tag ‘Sage50TransNom’ 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 50.
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
Restrictions
- Does not support Foreign Currency
What else can I do?
Other Sage 50 Extract/Import types
Frequently Asked Questions?
Got another question? It may well be covered on our Frequently Asked Questions page.
Demonstration Video
Sage 50 - Posting a Nominal Journal with VAT PostTrans
| |
0:00 |
Video Introduction |
| |
1:50 |
Opening Nominal Journal Example |
| |
2:30 |
Posting a Normal Nominal Journal into Sage 50 |
| |
3:25 |
Posting a Nominal Journal with Contra Nominal line |
| |
4:00 |
Using In cell searching to enter nominal lines |
| |
6:10 |
Posting a VAT Nominal Journal into Sage 50 |
| |
7:20 |
How to find more information and Training |
To use the template, with Sage 50, 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.
Journal Import Options
Nominal Import has the following options, which are accessible by pressing the Setup button on the toolbar:
Switches in Setup window
| 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… |
Confirmation window showing summary of transaction.
This would be turn off if importing a large number of lines from an external file.
The above settings are stored in each sheet, Cell A1 Comment, where as most settings are stored per workbook
Related Topics...
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
|
Overide Heading Ref, thus causing a new transaction on change
|
|
THDate
|
Date Head Chg XML
|
Overide Heading Date, thus causing a new transaction on change
|
|
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 8 EOF Req XML
|
Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
In Cell Searching values in table NomCodeNoDesc
|
|
TLExterRef
|
Text 30 XML
|
External Referance set on each line
|
|
TLDepartment^
|
Integer XML
|
Department. Make sure the format of the cell is “General” else search may not work properly
In Cell Searching values in table DeptCode
|
|
TLDetails
|
Text 60 Req XML
|
Details
|
|
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 TaxCode
|
|
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. Posttrans will ignore line if zero value in TLDebit and TLCredit
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.
|
|
TLTaxAmount
|
Currency Read XML
|
VAT Amount calculated based on Credit/Debit and Tax code on line
Sage 50 Vat Journal
|
|
THTaxContNom^
|
Text 8 Head
|
Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
Sage 50 VAT Nominal Journal
In Cell Searching values in table NomCodeVAT
|
|
Contra:
|
|
THContraNom^
|
Text 8 Head
|
Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
Sage 50 Contra Nominal Journal
In Cell Searching values in table NomCodeContra
|
|
THContraDept^
|
Integer Head
|
Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
more about Contra Nominal Journal
In Cell Searching values in table DeptCode
|
|
THContraDesc
|
Text 60 Head
|
Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
Sage 50 Contra Nominal Journal
|
|
THContraDonFund^
|
Integer Head
|
Contra Charity Fund Number
In Cell Searching values in table CharityCode
|
|
TLForceNewTrans
|
Yes/NoTF
|
If YES then force a new transaction
|
|
TLDonFund^
|
Integer XML
|
Charity Fund Number
In Cell Searching values in table CharityCode
|
|
Header:Before Posting:
|
|
THBefSWCompany^
|
Text 6 Head Chg Pro
|
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 HeadOnly
|
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 HeadOnly
|
If contains value, then this text is displayed. Asks Yes/No to continue
Validating Transaction before posting
|
|
Header - After Posting:
|
|
THAftProcessSheet
|
Text 60 HeadOnly Pro
|
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 HeadOnly Pro
|
After posting this sheet, switch to work book with this name PRO
Posting multiple sheets
|
|
Header - After Save:
|
|
THAftSavePATH
|
Text 60 HeadOnly Read Pro
|
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 HeadOnly Read Pro
|
See THAftSavePATH
more about saving
|
|
THAftSaveFilename
|
Text 60 HeadOnly Read Pro
|
See THAftSavePATH. Example ‘ABAP01’ will save as ABAP01_0001.xls.
more about saving
|
|
Import File:
|
|
THImportFullName
|
Text 500 HeadOnly
|
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 HeadOnly
|
Filename of the file just imported using the Import button
File Import instructions
|
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.
To use the template, with Sage 50, 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.
|
|
|
|
Nominal Journal - 2 Col
|
Sheet:'Nominal 2 Col'
Nominal Journal with Credits and Debits
Main page for Nominal Journal
Help article for Nominal Journal
|
|
|
Nominal Journal - 1 Col
|
Sheet:'Nominal 1 Col'
Nominal Journal with Single Credit/Debit Column
Help article for Nominal Journal - 1 Col
|
|
|
Nominal - Contra
|
Sheet:'Contra'
Total to contra nominal account
Main page for VAT Nominal Journal
|
|
|
Nominal - VAT
|
Sheet:'NOM with VAT'
Calculates VAT on a nominal and posts to VAT control account, plus total to contra account
Main page for VAT Nominal Journal
|
|
|
|
|
Manual VAT
|
Sheet:'Manual VAT'
Manual VAT on a order
Main page for VAT on Transactions
|