Bins Stock Take

/ / /

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

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

Having extracted the Bin records, we simply:

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

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

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

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

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.


	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