Menu

Product Adjustment

Releated Topics.

Training on Transaction Data
Importing from file easily
Project
Product
Clear transaction copy formula
Protecting the template
In Cell Searching

Save time updating

Sage 50

Stock/Product Creation
Customer/Supplier Prices
Bill of Material
Project Creation
Location Bins

Sage 200

Simply extract, edit and import

Xero

Import from Excel

50 Trans

Nominal Journals
Quotes/Invoices/Orders
Remote Orders
Product/Stock Adjustments
Sales Receipts
Quick OrderPad

200 Trans

Transaction from Manual Input

Image Alt TextImage Alt Text

Multiple Transactions from CSV file

Xero

Solve the Puzzle?

Sage 50

Help on every type of import
Demo Video of all functionality
Loads of Examples included
Online Training
We can help too…

Sage 200

Image Alt Text

Xero

Great Customer Satisfaction

Customer Comments

Full Trial with Demo Data
Low cost Subscription
Cancel anytime
From a £1 a day
Saving you hours

Simply extract, edit and import

Power of Excel

Sage 50

Quick Template Creation
Customise with:
    Formula
    Macros
    VBA

Sage 200

To reach your goals quickly

Sage 200

Sage 200 Bins

Sage 200 Warehouse & Bins

Create Sage 200 Bins
Stock Take Bins
Move Bin Stock

Fast, cost effective, easy…

Image Alt TextImage Alt Text

Manufacturing

200 Manufact.

Create BOM Stock Easily
Create Bill of Materials
Labour, Machine, Tools

Image Alt Text

Complex tasks quicker...

50 BOM

Project Accounting

200 Projects

Create Project Structure
Update Budgets
Simple Project Entry

Make life easier...

Image Alt Text50 Projects

Sage 50:Product Adjustment

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

Page Contents

Stock Adjustment in Sage 200

Import Sage 50 Product Adjustment Features

  • Detailed conformation before transaction importation
  • Highlighted Validation of codes and data
  • Search for Products and other codes 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 Product Adjustment in Excel

Simple Sage 50 Product Adjustment in Excel

Simple Sage 50 Product Adjustment in Excel

The above is a screen shot of a Product Adjustment layout which is automatically generated after selecting this type of transaction, many of the fields are optional and are filled in automatically by PostTrans. The user need only enter Product Code and Quantity, all other fields are then filled in by PostTrans.

Indeed it is possible, using the "Ignore Zero Qty" system setting, to list all stock codes in the sheet. And thus the user simply fills in the qty against those stock items required. See Creating a Sales Order pad, which shows how to do this with an order, but the principle is the same. Pro

The cell A1 has a tag “Sage50transAdj” 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 Transaction.

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 ‘Sage50TransAdj’ 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 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 50.  PostTrans will read the Header cells and then process the rows of data until TLProduct 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 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 - Import Product Adjustment using PostTrans
  0:00 Video Introduction
  0:45 Creating Product Adjustment Template
  2:14 Entering Sage Line 50 Product Adjustment
  4:35 Extracting Product List into Adjustment Pro
  6:35 Refreshing stock levels and cost price
  7:30 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.

Product Adjustment Import Options

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

Switches in Setup window

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…
Enforce Project and Cost Code entry This makes sure that the user specifies Job Costing codes, other wise they are optional Pro

Confirmation window showing summary of transaction.

Confirmation window showing 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.

If Project codes are entered in Stock Adjustment then confirmation will show a summary of Project Costing figures posted.

Related Topics...

Training on Transaction Data Importing from file easily Project
Product Clear transaction copy formula Protecting the template
In Cell Searching    

PostTrans Tags which map to Sage 50

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

Tag Name

Data Type

Description

Important Header Fields:

THAdjType^

Text 3 Head XML

Transaction Reference, overides vallue set on Header
        In Cell Searching values:Stock Adjustment Type
                  AI, Adjustment In
                  AO, Adjustment Out
                  MI, Movement In
                  MO, Movement Out
                  GI, Goods IN
                  GO, Goods Out
                  GR, Goods Returned
                  DI, Damages In
                  DO, Damages Out
                  WO, Write Off

THTranRef

Text 30 Head XML

Transaction Reference, overides vallue set on Header

THTransDate

Date Head XML

Transaction Date, overides the date set on Header

Project (Pro):

THProject^

Text 12 Head Pro XML

Project
    In Cell Searching values in table ProjectCode

THCostCode^

Text 8 Head Pro XML

Cost Code
    In Cell Searching values in table ProjectCostCode

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

Adjustment Lines:

TLProduct^

Text 30 EOF Req XML

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
    In Cell Searching values in table ProdCodeNoDesc_STOCK

TLDetails

Text 60 XML

Details Description (120 chars in 2023 onwards)

TLQty^

Double Req XML

Quantity, ignores any lines with zero qty. This allows a long product list to be listed, and user simply edit a qty to book IN/OUT
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLCostPrice

Currency XML

Cost Price

TLTotCost

Currency Read

Total Cost Price of line

TLSalesPrice

Currency Read XML

Sales Price

TLTotSales

Currency

Total Sales Price for line

Goods In,Out Fields:

TLTransRef

Integer XML

For Good Out drill down put a valid Transactions Ref here. This is not validated so make sure it is correct

TLTransRefType^

Integer XML

Type of the above
        In Cell Searching values:Trans Ref Type
                  1, Invoice
                  2, SOP
                  3, POP

Other Fields:

TLProject^

Text 12 Pro XML

Project
    In Cell Searching values in table ProjectCode

TLCostCode^

Text 8 Pro XML

Cost Code
    In Cell Searching values in table ProjectCostCode

Stock Level (PostBack):

PrQtyFree

Double Read

Product Qty free, updated when stock code entered

PrQtyInStock

Double Read

Product Qty In stock, updated when stock code entered

PrQtyOnOrder

Double Read

Product Qty On Order, updated when stock code entered

PrQtyReOrder

Double Read

Product Qty ReOrder, updated when stock code entered

PrLocation

Text 16 Read

Product Location, updated when stock code entered

PrSuppPartNo

Text 16 Read

Product Supplier Part Number, updated when stock code entered

Special Fields:

TLForceNewTrans

Yes/NoTF

If YES then force a new transaction

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

Header - After Posting:

THBefDisplayMessYN

Text 60 HeadOnly

If contains value, then this text is displayed. Asks Yes/No to continue
Validating Transaction before 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 Pro

After posting this sheet, switch to work book with this name PRO
Posting multiple sheets

Header - After Save:

THAftSavePATH

Text 60 HeadOnly Pro

If THAftSavePATH and THAftSaveFilename specified, will save as PATH + ‘\YYYMM\’ + Filename + "_0000.xls". YYYMM can be overridden with value in THSaveSubDirectory cell.
Saving template after posting

THAftSaveSubDir

Text 60 HeadOnly Pro

See THAftSavePATH
more about saving

THAftSaveFilename

Text 60 HeadOnly Pro

See THAftSavePATH. Example ‘ABAP01’ will saveas 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.

Transaction - Product Adjustment:

 

Product Adjustment

Sheet:'Product ADJ'
Product Adjustment sheet
   Main page for Product Adjustment
   Help article for Product Adjustment

 

Product Adjustment Goods OUT Simple (Code + Qty)

Sheet:'Product ADJ GO'
Product Adjustment sheet with referance to SO for Goods Out
   Main page for Product Adjustment

 

Product Adjustment Simple (Code + Qty)

Sheet:'Product ADJ Simple'
Product Adjustment sheet
   Main page for Product Adjustment