Menu

Sage 200:Stock Bin Stock Take

All of the following functionality requires the PostTrans Extras Module.

PostTrans imports Stock Bin Stock Take, which could be manually entered with the assistance of in-cell searching, using copy and paste, imported from CSV file or extracted from Sage 200 using the Extract button.

Extract/Import Sage 200 Stock Bin Stock Take

Extract/Import Sage 200 Stock Bin Stock Take

Explanation of this type of Import

This sheet allows the user to extract/ delete/create Warehouse Bin records, and Stock Take Bins in the same or different Warehouses. If the item is a Serial or Batch item then this is expeanded out to show each Serial or Batch item stock level.

The extraction process allows the user to filter by Warehouse, and Stock Group, Supplier, Search Analysis Codes, plus much more.

Indeed individual stock items, or a list can be extracted in the Search column.

Extract Bins from Locations selected

Extract Bins from Locations selected

Having extracted the Bin records, we simply:

Extracted data with edited In Stock figures

Extracted data with edited In Stock figures

Bin In Stock Qty is entered in SbNewInStk column. PostTrans will then stock adjust the difference between SbInStk and SbNewInStk column.

Add record is created by simply adding a row, and entering selecting a Warehouse, and entering a Bin name, or simply entering a Bin name in blank row.

Delete a Bin simply enter -1 in the In Stock column. PostTrans will process as a zero stock level, and delete Bin if an alternative Bin exists in that Warehouse.

Rename a Bin, simply edit the Bin name in SbLocBin column

Confirmation is given before posting any adjustments

Confirmation is given before posting any adjustments

Sage 200 Stock Take Features

  • Detailed conformation before adjusting figures
  • Adjust Serial and Bactch number items Pro
  • Create Bins/Warehouses on the fly Pro
  • Extract current stock levels for all or specific stock items based on criteria or a list of stock codes
  • Highlighted Validation of codes and data
  • Bins are all treated as 'Unspecified' if Bin column is deleted

Example

If Qty is reduced in one bin by 2, and increase ion another by 2, the a movement is

stock movement is posted for 2 between the two locations

If Qty is reduced in one bin by 2 on its own for a stock item, then the stock is written

off using Wrtie Off Category 'Stock Write Offs', set in Setup)

If Qty is increased in one bin by 2 on its own for a stock item, then the stock is Moved

IN at Last Cost price

Demonstration Video

 
 
   Sage 200 – Stock Take using PostTrans
  0:00 Introduction
  0:35 Template Explanation
  1:30 Extract In Stock Qty for specific Locations
  3:00 Entering new In Stock Qty
  4:40 Adding a single stock item to stock take
  5:10 Processing new figures
  7:10 Extract In Stock Qty for a List of Stock Codes
 
 

Sage 200 Stock Take Batches/Serial

Adding Tag 'SbBatSerail' will list any Batches/Serial items on each row of the sheet when Extracted:

Sage 200 Stock Take Batches Example

Sage 200 Stock Take Batches Example

The stock level can be then easily adjusted in the 'NewInStk' column.

Serial Items are also listed in a similar fashion:

Sage 200 Stock Take Serial Example

Sage 200 Stock Take Serial Example

Stock Take Batches/Serial Template

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.

Apart from column A these fields can be rearranged, hidden, and more columns mapped by using the Tags button to insert new tags. See 'Template Structure' section below.

Template Structure for Static Data

The template must have tag ‘Sage200stkBin’ in cell A1 to instruct PostTrans that we are interested in Sage 200 Stock Bin Stock Take, 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.

Row 1 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 suit your needs, and the tags are added using the Tags button. See on-line Training   Deleting the Tag name, in the cell, from this row removes the Sage 200 mapping.   Table of Stock Bin Stock Take Tags

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

Common Features

In-Cell Searching

Any Tag ending with ^ with 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.

Extracting the Data

The data extracted using the Extract button can be filtered, by entering criteria in row 2. Hover over cell A2 for instructions. Extracting Data

In addition to this some sheets have a SbSearch tag/column. In this case the user can paste a list of codes in column, and press Extract button, to extract those records to the sheet. Entering a single code in this column will extract a single record.

Amending Stock Bin Stock Take Data

Having extracted data, the user can now simply edit the Stock Bin Stock Take in Microsoft Excel, before re-importing it back into Sage 200. 

PostTrans uses the tagged column SbCRC to detect changes in rows.  Therefore, only records/rows which have changed since extraction are updated.  If you add or delete a tagged column, PostTrans will treat ALL rows as having changed, and thus ALL records will be updated on next Import.

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

File Import Button

This can be enabled in Setup to easily import cvs, 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.

Validation

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

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

Other Useful Topics

Clear Transaction and copy down formula

PostTrans Tags which map to Sage 200

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

Tag Name

Data Type

Description

Important Fields:

Sage200stkBin

Text Req

Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row

SbCRC

Text Req

Contains CRC number to help PostTrans detect changes in current row.

SbDelete

Text 20

Delete Bin record if "X" if allowed

SbID

Text Req

Record ID. This is used to update Bin record in Sage 200.

SbSearch^

Text 40

List of Stock. In settings you can set a specific location
    In Cell Searching values in table Stock

Stock:

SbStkCode^

Text 30 Req

Stock Code
    In Cell Searching values in table Stock

Create Location:

SbLoc^

Text 20 EOF Req

Location/warehouse code. If blank then will asume the same code as previous line
    In Cell Searching values in table LocNoDesc

SbLocMin

Double

Bin min level

SbLocMax

Double

Bin max level

SbLocReorder

Double

Bin reorder level

Bin:

SbLocBin

Text 20 Req

Bin Code, `X` is shortcut to `Unspecified`. If Stock Take, or Movement balnk will be `Unspecified`

SbLocBinPri

Integer

Bin priority, sets to 9 if not specified

Qty:

SbInStk

Double Read

Qty In stock

Other:

SbTraceType

Text 20 Read

Nothing,Bat or Ser

SbUOM

Text 20 Read

Stock Unit of Measure

SbLastCost

Text 20 Read

Last Cost

SbQtyAloc

Double Read

Qty Allocated

SbQtyFree

Double Read

Qty Free

Stock Take (Extras Module):

SbNewInStk

Double

New qty in stock. Requires 'Extras Module' This will write off, move in stock levels to produce a stock take. If one bin is increased, and another decreased then it is assumed stock has been moved, hence a movement takes place. -1 is zero stock and delete bin.

Stock Move (Extras Module):

SbMovIn

Double

Qty to move IN. 'X' to match the QtyIN in same stock item

SbMovOut

Double

Qty to move OUT. 'X' to match the QtyIN in same stock item

Batch and Serial:

SbBatSerial^

Text 30 Pro

Batch Ref or for Serial then a comma seperated list or list with each on new line
    In Cell Searching values in table AdjTLBatSerial

SbSellByDate

Date Read Pro

Sell lBy Date

SbUseByDate

Date Read Pro

Use By Date

SbSupplier^

Text 20 Read

Batch/Serial Supplier
    In Cell Searching values in table Supp


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

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.

 

Stock Location and Bins Records

Sheet:'Stk Bins'
Import/Extract Stock Location/Bin Records

 

Locations and Bins

Sheet:'Bins'
This sheet allows the user to extract/amend/delete/create Warehouse Bin records
   Main page for Locations and Bins
   Help article for Locations and Bins

 

Stock Take Locations and Bins with Serial and Batches

Sheet:'Bins Stock Take Batch Serial'
Stock Take Bins in the same or different Warehouses with Serial and Batches
   Main page for Bins Stock Take

 

Stock Take Locations and Bins

Sheet:'Bins Stock Take'
Stock Take Bins in the same or different Warehouses.
   Main page for Bins Stock Take
   Help article for Bins Stock Take

 

Stock movement between Locations and Bins

Sheet:'Bins Movement'
Move Stock items between Bins in the same or different Warehouse
   Main page for Bins Movement
   Help article for Bins Movement