Sage 200: Static Data for Sage 200: Manufacturing and BOMs: Bill.

Sage 200:Bill of Materials with Version Control

All of the following functionality requires the PostTrans Manufacturing Module.

PostTrans imports Bill of Materials Records, 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 Bill of Materials Records

Explanation of this type of Import

This sheet allows the user to extract/create Bill of Materials (BOM) records. It is used when Version Control is turned to list versions when Version Control is ON.

The extraction process allows the user to filter the BOM records by version status New, Active, Obsolete, Retired, Hidden or get the latest version.

A new version can be created by entering following values in the SbCommand column:

X,Delete BOM,
N,New Empty Version,
A,Activate Version,
C,Create Empty and Activate,
R,Retire

Prerequisites

BOM reference must have been defined in Stock module as type BOM.

Download Stock + BOM Extract/Import Template
see 'BOM Items' sheet

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.

Demonstration Video

Sage 200 – Edit Bill of Material with Version Control using PostTrans

	0:00	Video Introduction
	1:00	Open BOM Version Sheet
	2:00	Create a New BOM Version
	3:00	Edit a BOM Items to create new Version
	4:30	Import New Edited Version

Template Structure for Static Data

The template must have tag ‘Sage200manBOM’ in cell A1 to instruct PostTrans that we are interested in Sage 200 Bill of Materials Records, 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 Bill of Materials Records Tags

Close up of template Sage200manBOM for Bill of Materials Records

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 Bill of Materials Records Data

Having extracted data, the user can now simply edit the Bill of Materials Records 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 SbRef 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:
Sage200manBOM	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.
SbCommand^	Yes/No	Delete record if "X", or use other commands if versioning is on In Cell Searching values:Command X, Delete BOM N, New Empty Version A, Activate Version C, Create Empty and Activate R, Retire
SbSearch^	Text 30 Req	Search for BOM record by entering code or pasting list, at top of column, and pressing `Extract` button In Cell Searching values in table ManBOMCode
Details:
SbRef	Text 30 EOF Req	BOM Stock Code
SbDesc	Text 60	BOM Description, if blank uses stock record Description
SbChangeRef	Text 255	Change Reference
SbQualStd	Text 255	Quality Standard
Version/Status:
SbVersion	Text 50 Req	Revision. This read only and required. It is used when BOM versions are used. Otherwise it can be hidden.
SbReason^	Text 60	Reason In Cell Searching values in table ManBOMReason
SbStatus^	Text 60 Read	Version Status, use SbCommand to change this status In Cell Searching values:1BOM Status N, New A, Active O, Obsolete R, Retired H, Hidden
IsOnHold	Yes/NoTF	Is on hold
Dates:
SbEffFrom	Date	Effective From
SbEffTo	Date	Effective To
Analysis:
SbAnal1^	Text 60	Anaysis Code 1 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 01
SbAnal2^	Text 60	Anaysis Code 2 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 02
SbAnal3^	Text 60	Anaysis Code 3 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 03
SbAnal4^	Text 60	Anaysis Code 4 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 04
SbAnal5^	Text 60	Anaysis Code 5 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 05
SbAnal6^	Text 60	Anaysis Code 6 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 06
SbAnal7^	Text 60	Anaysis Code 7 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 07
SbAnal8^	Text 60	Anaysis Code 8 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 08
SbAnal9^	Text 60	Anaysis Code 9 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 09
SbAnal10^	Text 60	Anaysis Code 10 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 10
SbAnal11^	Text 60	Anaysis Code 11 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 11
SbAnal12^	Text 60	Anaysis Code 12 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 12
SbAnal13^	Text 60	Anaysis Code 13 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 13
SbAnal14^	Text 60	Anaysis Code 14 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 14
SbAnal15^	Text 60	Anaysis Code 15 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 15
SbAnal16^	Text 60	Anaysis Code 16 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 16
SbAnal17^	Text 60	Anaysis Code 17 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 17
SbAnal18^	Text 60	Anaysis Code 18 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 18
SbAnal19^	Text 60	Anaysis Code 19 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 19
SbAnal20^	Text 60	Anaysis Code 20 - `Setup` View >> Analaysis code menu option will list mapping numbers and values In Cell Searching values in table AnalBOMs 20

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.

Download Sage200BOM.xlsx
	Stock Import with Bill of Material (Module: Manufacturing)	Sheet:'Stock' Import/Extract Stock with Bill of Material Location and Type Help article for Stock Import with Bill of Material
Bill of Materials:
	Bill of Material Version (Module: Manufacturing)	Sheet:'BOM' Import/Extract Bill of Material Versions Help article for BOM Items, operations, drawings with Versions
	Bill of Material Items (Module: Manufacturing)	Sheet:'BOM Items' Import/Extract Bill of Material Items Help article for BOM Items, operations, drawings
Manufacturing:
Download Sage200manufacturing.xlsx
	Labour (Module: Manufacturing)	Sheet:'Labour' Import/Extract Labours Help article for Labour
	Labour Group (Module: Manufacturing)	Sheet:'LabourGrp' Import/Extract Labour Groups
	Tool (Module: Manufacturing)	Sheet:'Tool' Import/Extract Tools
	Tool Group (Module: Manufacturing)	Sheet:'ToolGrp' Import/Extract Tool Groups Help article for Tool Group
	Machine (Module: Manufacturing)	Sheet:'Machine' Import/Extract Machines
	Machine Group (Module: Manufacturing)	Sheet:'MachineGrp' Import/Extract Machine Groups
	Operation (Module: Manufacturing)	Sheet:'Operation' Import/Extract Operations Help article for Operations
	Operation Resources (Module: Manufacturing)	Sheet:'Operation Resources' Import/Extract Operation Resources Help article for Operation Resources