Menu

Manufacturing and BOMs

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 200 : Extract/Amend/Import Stock BOM Records

PostTrans allows the extraction/importation of the Bill of Materials (BOM) and Manufacturing records.    These are covered in the following pages:

 

 

  • Stock - Import new Stock item, setting it as a BOM, setting the Default Manufacturing location and other BOM data all in on row of the spreadsheet.
  • Bill of Materials Items - BOM items, such as Component, Subassembly, Phantom, Component Comment, Component Document, Operation, Operation Comment, Operation  Document.
  • Bill of Materials records - BOM parents records.  Primarily used to edit BOM version when Version Control is ON

Manufacturing

Sage 200:Bill of Materials Items

All of the following functionality requires the PostTrans Manufacturing Module.

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

Extract/Import Sage 200 Bill of Materials Items

Explanation of this type of Import

This sheet allows the user to extract/create Bill of Materials (BOM) Item records, the child of BOM Records, which populates the “Components” and “Operations” Tabs of Sage 200 BOM window. The lines are assigned types, for the types of Components and Operations:

  • CO,Component
  • SB,Subassembly
  • PH,Phantom
  • CC,CompComment - Comment
  • CS,CompCommentShow – Comment with ‘Show comment line in ‘enquires/reports’ ticked
  • CD,CompDoc – Document, press Space + Tab in SiRef column to select file
  • OP,Operation
  • OC,OpComment - Comment
  • OS,OpCommentShow – Comment with ‘Show comment line in ‘enquires/reports’ ticked
  • OD,OpDoc – Document, press Space + Tab in SiRef column to select file

The SiParent Column should have the BOM code in it, which each item belongs to.

The In-Cell Searching in this sheet is of great help. To add an item to an extracted build you only need insert a line and enter the component ref in the SiRef column. PostTrans will by default assume it is a Component to search for. Having selected a component, it will set SiParent to the same ref as line above, scan for the next appropriate sequence number, and fill in type (SiType) “CO,Component”. The user then can enter the Qty for the BOM item.

If the user wishes to enter a Subassembly, then SiType is set to SB,Subassembly, then the search in SiRef will search just for Subassemblies.

To speed up data entry. Entering “,” in SiRef, before the Subassembly code, will force the line to be a Subassembly. SiType will be set to SB,Subassembly automatically.

These short cuts can be accessed in Setup

BOM Options in Setup window

BOM Options in Setup window

Items can be deleted from a BOM by entering an X in the SiCommand column, and pressing Import button.

Version Control

If version control is turned ON then multiple version will be listed. The extraction process will allow 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 SiCommand column:

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

Care must be taken

When BOM components are extracted the column SiID is populated with the BOM Component Line ID. This is then used by PostTrans to update any edits to the same Component/Operation Line within the BOM. Thus any part of the line can be edited. If the SiID does not relate to the current BOM then it is ignored and treated as a new line in BOM, and thus added to BOM.

If SiID is empty PostTrans will create a new item within the BOM.

Important

The BOM Components must be in BOM Code (SiParent) order. If you extract the BOM, and then sort into different order, you need to put back to BOM Code order before import.

Demonstration Video

 
 
   Sage 200 – Edit Bill of Material using PostTrans
  0:00 Video Introduction
  1:50 Opening Stock/BOM Template
  2:30 Create New Stock/BOM
  4:10 Import New Stock/BOM
  5:10 Edit other BOM Fields
  5:44 Extract BOM Items and Sub-Assemblies
  7:30 Edit BOM Items
  8:00 Add new item to BOM
  10:00 Import changes and view in Sage 200
  11:20 Copy, and Edit, BOM items to new BOM
  14:20 Quick Select BOM Item Type
  16:00 Import Copied BOM Items
 
 

Demonstration Video:Editing BOM Items with Version Control

 
 
   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
 
 

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 ‘Sage200manBOMitem’ in cell A1 to instruct PostTrans that we are interested in Sage 200 Bill of Materials Items, 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 Items Tags

Close up of template Sage200manBOMitem for Bill of Materials Items

Close up of template Sage200manBOMitem for Bill of Materials Items

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 SiSearch 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 Items Data

Having extracted data, the user can now simply edit the Bill of Materials Items in Microsoft Excel, before re-importing it back into Sage 200. 

PostTrans uses the tagged column SiCRC 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 SiParent 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:

Sage200manBOMItem

Text Req

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

SiCRC

Text Req

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

SiCommand^

Yes/No

Delete record if "X", or COMMAND if versioning is on. COMMAND should always be issued on BOM first line
        In Cell Searching values:Command
                  X, Delete Line
                  N, New Version
                  A, Activate Version
                  C, Create and Activate
                  R, Retire

SiSearch^

Text 30 Req

Search for record by entering code (CAPS LOCK on to get Sub-Components) or pasting list, at top of column, and pressing Extract button
    In Cell Searching values in table ManBOMCode

Common BOM:

SiID

Integer Req

Record ID. This is used to update BOM resource record in Sage 200. If the Parent code has changed, i.e. created new record, then it will be ignored and new record created with ID written back.

SiParent^

Text 30 EOF Req

BOM code
    In Cell Searching values in table ManBOMCode

SiVersion^

Text 50 Req Read

Status and Revision No applied by system. A=Active,H=Hidden,N=New,O=Obsolete,R=Retired
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

SiSeq^

Integer Req

Sequence within BOM/Operation, this should not be edited. See notes
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

SiType^

Text 2 Req

Type of line. The default value can be set in Setup, un der BOM tab
        In Cell Searching values:1BOM Line Type
                  CO, Component
                  SB, Subassembly
                  PH, Phantom
                  CC, CompComment
                  CS, CompCommentShow
                  CD, CompDoc
                  OP, Operation
                  OC, OpComment
                  OS, OpCommentShow
                  OD, OpDoc

SiRef^

Memo Req

Stock/Operation/Drawing Code/Comment/Filename. If Attachment then SPACE + TAB to find file.
    In Cell Searching values in table ManBOMSiCode

SiDesc^

Text 60 Req

Description. Setup 'Use Sheet Component/Operations Descriptions' makes PostTrans use this description when importing components. Operations description is ignored
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

SiUOM^

Text 20 Req

Componnet UOM
    In Cell Searching values in table BOMuom

SiQty^

Double Req

Component Qty or Operation 'Qty per run'
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

SiNoPrint

Yes/No

Do not print Operation or comment

Drawings:

SiDrawFrom

Date

From date on Drawing

SiDrawTo

Date

To date on Drawing

SiScrapPerc

Double

Scrap Percentage

SiUseOnlyInBOM

Yes/No

Use only in BOM module

SiFixedQty

Yes/No

Use only in BOM module

SiNotes

Memo

Component Notes

Operations:

SiOpSubContract

Yes/NoTF

Operation Sub Contract Flag

SiOpDoNotPrint

Yes/NoTF

Operation Do Not Print Flag

SiOpOverLapQty

Double

Operation OverLap Qty

SiOpShrinkPerc

Double

Operation Shrink Perc

SiOpOverlapPerc

Double

Operation Overlap Perc

SiOpRunH

Integer

Operation Run Hours

SiOpRunM

Integer

Operation Run Minutes

SiOpRunS

Integer

Operation Run Seconds

SiOpDelayH

Integer

Operation Delay Hours

SiOpDelayM

Integer

Operation Delay Minutes

SiOpDelayS

Integer

Operation Delay Seconds

Documents:

SiDocPATH^

Text 255

Document path. Press space and tab away to search for Document, f and Tab away to seelect folder
    In Cell Searching values in table BOMDoc


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

 

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