Menu

Adjustments and O/B

Sage 200:Import Project Adjustment and Opening Balance

This type of PostTrans import, imports Project Adjustment and Opening Balance, which can be input manually, data from an ODBC connection or imported from CSV file.

Page Contents

Sage 200 Project Adjustment and Opening Balance

  • Import a simple list of Cost/Revenue Adjustments which also update the Nominal Ledger
  • Import a simple list of Cost/Revenue Opening Balances
  • Detailed conformation before posting
  • Validation highlights any problems for easy correction
  • Looks Nominals if not specified
  • Single or Multiple Transactions
  • In-Cell Searching allows user to lookup Projects and Project item codes
  • Quick data entry

Simply add new Adjustments and Opening Balances lines to the spreadsheet from row 30 on-wards. Then press Import button, on the PostTrans Toolbar, to import single or multiple Sage 200 Project Expenses transactions (Examples shown below).

Examples of importing Sage 200 Adjustments and Opening Balances

Posting a list of Adjustments and Opening Balances

Multiple Expenses can be imported by adding the Header fields to the Tag row 28. These are easily inserted using the Tags button, see all tags listed at the bottom of page.

Posting a list of Expenses

Posting a list of Expenses

After pressing the Import Button:

Conformation and Validation before Posting Expense Line

Conformation and Validation before Posting Expense Line

Conformation can be turned off in PostTrans Setup

Customised User Defined Fields

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

Template Structure for Project Adjustment Data

The template must have tag ‘Sage200projAdj’ in the comment of cell A1 to instruct PostTrans that we are interested in importing Project Adjustment and O/B, 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.

The top section of this template contains Transaction header fields, in the case of a single transaction, or just transaction type of all multiples transactions list from row 30 onwards.

These Transaction header fields can be deleted if not required, or added by moving the cursor, to a cell in the header area, and pressing the Tag button. This will display a list of PostTrans Tags. A tag beginning with “PeH”, denoting for use in a header, can then be inserted. Table of Project Adjustment and Opening Balance Tags

Row 28 contains a series of tags, which denote to PostTrans the data to be read/written in each column. These tags, and data columns can be rearranged to suite your needs, and the tags are added using the Tags button. Deleting the Tag name, in the cell, from this row removes the Sage 200 mapping.

Row 27 designates what will happen when the Clear button is pressed, see Clear Transaction and copy down formula

Hovering over these tags, and header cells will show a comment which explains the tags purpose, data type and any other useful details.

For importing multiple transactions, most header tags can be copied/moved to row 28, thus designating a column to set the header value. 

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 SetupIn-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 TLProjectCode 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.

PostTrans 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 (Required):

THJournalType^

Text 3 Head

Type of Journal. Default Pending Journal
        In Cell Searching values:Adjustment Type
                  COST, Cost Adj
                  REV, Revenue Adj
                  ROP, Revenue Opening Balance
                  COP, Cost Opening Balance

THDate

Date Head Chg

Overide Heading Date, thus causing a new transaction on change

Header (Optional):

THRef

Text 30 Head Chg

Overide Heading Ref, thus causing a new transaction on change

THRef2

Text 30 Head Chg

Overide Heading Ref, thus causing a new transaction on change

THUseJrNum^

Yes/NoTF HeadOnly

Use Journal Number
    Sage V2016 onwards
        In Cell Searching values:Use Journal Num
                  YES, ON
                  No, OFF

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 Spec:

TLNomCode^

Text 16

Nominal Code. Looked up if not specified from priject item. 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 3

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

Project:

TLProjectCode^

Text 30 EOF Req

Project Code, or can be ProjectCode + `/` + Phase Code
    In Cell Searching values in table ProjectTimeProjectCode

TLPhaseFilter^

Text 30

Phase Filter. If project has duplicate Project items in different phases, this can direct to correct Phase
    In Cell Searching values in table ProjectAdjPhaseCode

TLProjectItem^

Text 30 Req

Project Item code
    In Cell Searching values in table ProjectAdjItemCode

Value:

TLDebit^

Currency

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

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.

TLValue

Currency

Value of Opening Balance/ADJ

TLQty

Currency

Qty Defaults to 1 if not specified.

Details:

TLDetailsOvr

Text 60

Details as stated in sheet

TLDetails

Text 60

Details Type/Job/Item/ + TLDetails

Tax Tags:

TLTaxCode^

Text 1

Tax Code. From Sage Help: The VAT Code displayed here is for information purposes only and does not affect the VAT Return, as only the Goods value is posted to your projects.
    In Cell Searching values in table Tax

Opening Balance only:

TLRevRate

Currency

Value of Revenue Rate for Cost Opening Balance (ADJ N/A)

TLCostRate

Currency

Value of Cost Rate for Cost Opening Balance (ADJ N/A)

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


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

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 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.

 

Project Cost Adjustments

Sheet:'Cost Adj'
Allow Import Project Cost Adjustments

 

Project Revenue Adjustments

Sheet:'Rev Adj'
Allow Import Project Revenue Adjustments

 

Project Cost Opening Balance

Sheet:'Cost OB'
Allow Import Project Cost Opening Balance Adjustments

 

Project Revenue Opening Balance

Sheet:'Cost OB'
Allow Import Project Revenue Opening Balance Adjustments