Menu

Project Accounting

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 Project Accounting Records

PostTrans allows the extraction/importation of the Projects Structure and related records.  These are covered in the following pages:

Sage 200:Project Structure

All of the following functionality requires the PostTrans Projects Module.

PostTrans imports Project Structure, 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 Project Structure

Extract/Import Sage 200 Project Structure

Explanation of this type of Import

This type of PostTrans template allows the user to extract/amend/create Project Structure. You can set the associated Budgets, Additional Information and Project Customer.

Features

  • Import Projects, Phases and Project Items to make Project Structure (Video 1)
  • Copy existing Projects (Video 2)
  • Extract/Edit Project Title (Video 4)
  • Extract/Edit Budget, Unit cost, Additional Information (Video 1 9:20)
  • Set one Customer for a Project, with Quoted Price, Sales Order number, etc.
  • Filter extraction by Customer, Project Status, plus much more. (Video 2 6:00)
  • Extract a whole list of Projects, edit and Import changes (Video 2 4:40)
  • Build data entry template for Project Managers to easily create Projects (Video 5)
  • Auto Link PostTrans template document to project

Project Structure in Excel

Project Structure in Excel

As we can see from above, the Parent Project code goes in the PjCode column. We then have a series of columns, which allow PostTrans to represent the Sage 200 Project Structure.

To allow PostTrans to distinguish between Phase and Project Items, when using In-Cell Searching, Phases are prefixed with “.”. Project Items are placed in the appropriate column, indented by one level, to designate which phase they belong to.

Having Extracted a Project Structure, associated data can be edited. Project Items/ Phases can be added/removed.

If the Job code is changed to an ‘X’, then on new Import, PostTrans will create a new project, and replace ‘X’ with the Project Number. See the first video below. Video 3 explains how this works when AUTO project numbers are turned off, where we basically enter new Project Code instead.

Budgets/Unit Costs

 Budgets/Unit Costs

Budgets/Unit Costs

Simply extract, edit and update one or many budgets for one or many Projects. If zero, or empty then PostTrans will use the default values set on Phase/Project Item.

Additional Information

PostTrans uses a series of tags, pairs, on for label and one for vale, to extract/import Project Additional Information

 Additional Information

Additional Information

The first of these PjAddL1 shows the field label, PjAddV1 contains the value. When extracted the label shows *=Required ^=In-cell Searching for value available. (see video). Required fields are always in the first columns. For CSV import this column only need contain the Fieldname. “*^:” are all ignored.

Things we can’t do

Once committed the structure cannot be rearranged using PostTrans, that should be done in Sage 200 if need be.

We cannot import sub projects. But you could import the bulk of a project, then in Sage 200 add the sub project to structure.

Set Period by Budget. Possibly in the future.

Sort sheet before importing. PostTrans expects the data to be grouped by Project.

Video 1 - Create Project from Template

 
 
   Sage 200 – 1/6 Create Project from Template
  0:00 Introduction to all Videos
  2:00 Product Demo
  3:00 Extract Project Template
  5:00 Set New Project Title
  5:20 Explain Structure Levels + Data Grouping
  6:20 Add Phase and Project Item
  8:00 Where the extracted data came from?
  9:20 Additional information
  11:20 Add Phase and Project Item
  12:00 Edit some budgets
  13:00 Import Project Structure
  14:20 Show In Sage 200
  15:55 Walkthrough of new Project
  16:30 Extract new project again
  17:00 Review Setup Settings
 
 

Video 2 - Edit/Copy existing Project or Multiple Projects

 
 
   Sage 200 – 2/6 Edit/Copy existing Project or Multiple Projects
  0:20 Extract existing Project
  0:35 Edit values
  1:00 Import Project Changes
  1:40 Review changes in Sage 200
  2:20 Copy a Project
  4:40 Extract Projects from a list of codes, edit and Import
  6:00 Extract all Projects for Customer and Edit
  7:15 Import changes
 
 

Video 3 - Edit/Copy with Manual Project Code

 
 
   Sage 200 – 3/6 Edit/Copy with Manual Project Code
  0:00 Review Sage 200 Project Settings
  1:10 The Searching Column Changes
  2:30 Creating a new Project
 
 

Video 4 - Edit Project Titles

 
 
   Sage 200 – 4/6 Edit Project Title
  0:30 Extract All Project Lines
  1:41 Import Changes
 
 

Video 5 - Customised Template to create Projects for Project Managers

 
 
   Sage 200 – 5/6 Customised Template to create Projects for Project Managers
  0:00 Open Example
  0:30 How it works
  2:30 How to Ignore a row of data
  4:50 Recap on new Project data
  4:30 Add Data Validation Lists
  5:00 How to attach Template to Project automatically
  6:30 Import Project Structure
  8:50 Open template from Sage 200 Project
 
 

Video 6 - Project Items and Phases/Groups

 
 
   Sage 200 – 6/6 Project Items and Phases/Groups
  0:00 Open Example
  1:00 Adding Columns of data
  1:20 Extract Project Items
  2:00 Extract Phases
 
 

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 ‘Sage200proj’ in cell A1 to instruct PostTrans that we are interested in Sage 200 Project Structure, 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 Project Structure 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 PjSearch 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 Project Structure Data

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

PostTrans uses the tagged column PjCRC 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 PjTitle 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:

Sage200proj

Text Req

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

PjCRC

Text Req

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

PjSearch^

Text 8

Search for Job/Template structure by entering code or pasting list, at top of column, and pressing Extract button. Will search for Project or Template if auto job number is on, else - CAPS LOCK is ON then searches for Project, if OFF then searches for Template.
    In Cell Searching values in table PjList

PjID

Integer Req

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

Project Structure:

PjCode

Text 30 Req

Job at top of structure. `X` to create a new Job. If copied from a template then will have X_TemplateName

PjL1^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL2^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL3^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL4^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL5^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL6^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL7^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL8^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL9^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

PjL10^

Text 30 Req

Structure level 1-10. When creating new Job this can be Group Code, or ProjectItem prefixed with ".".
    In Cell Searching values in table ProjectL1_10

Project:

PjTitle

Text 80 EOF Req

Project/Group/ProjectItem Title. Switches in setup control if system Titles are used. Since this column must have a value 'X' will use the system title for this line

PjDescription

Memo

Project Description

Cost Budget:

PjCostQty

Double

Budget Cost Qty, set to 1 if 0 and PjCostVal has value

PjCostVal

Double

Budget Cost Value, if mapped then will overide the calculatetion of this value PjCostQty * PjUnitCost

PjCostOvr

Yes/NoTF

Y to overide budgets below this item in Tree

Rev Budget:

PjRevQty

Double

Budget Revenue Qty

PjRevVal

Double

Budget Revenue Value

PjRevOvr

Yes/NoTF

Y to overide budgets below this item in Tree

Total Cost Budget:

PjTotCostQty

Double Read

Total Budget Cost Qty, for all items below this Phase/Project

PjTotCostVal

Double Read

Total Budget Cost Value, for all items below this Phase/Project

Total Rev Budget:

PjTotRevQty

Double Read

Total Budget Cost Qty, for all items below this Phase/Project

PjTotRevVal

Double Read

Total Budget Cost Value, for all items below this Phase/Project

Status:

PjStatus

Text 30

Group/Project Status

PjPercent

Double

Percent done

Other:

PjOwner^

Text 30

Owner of Job
    In Cell Searching values in table SageUser

Costs & Charges:

PjUnitCost

Double

Job Unit Cost

PjUOM^

Text 30

Job Unit Cost Unit of Measure
    In Cell Searching values in table ProjectUOM

PjUplift

Double

Uplift Percent

Costs & Charges Rules:

PjTimeSheet^

Text 30

TimeSheet Costing Rule
    In Cell Searching values in table ProjectTimeAndMaterialsPricingRule

PjStockIssues^

Text 30

Stock Issues Costing Rule
    In Cell Searching values in table ProjectTimeAndMaterialsPricingRule

PjOtherCost^

Text 30

Other Cost Costing Rule
    In Cell Searching values in table ProjectTimeAndMaterialsPricingRule

Nominal:

PjExpNom^

Text 16

Expense Nominal
    In Cell Searching values in table NOM

PjRevNom^

Text 16

Revenue Nominal
    In Cell Searching values in table NOM

PjWIPNom^

Text 16

WIP Nominal
    Sage V2015 onwards
    In Cell Searching values in table NOM

PjExpNomCC^

Text 3

Expense Nominal CC
How Nom/CC/Dep are specified
    In Cell Searching values in table CC

PjExpNomDep^

Text 3

Expense Nominal Dep
How Nom/CC/Dep are specified
    In Cell Searching values in table DEP

PjRevNomCC^

Text 3

Revenue Nominal CC
How Nom/CC/Dep are specified
    In Cell Searching values in table CC

PjRevNomDep^

Text 3

Revenue Nominal Dep
How Nom/CC/Dep are specified
    In Cell Searching values in table DEP

PjWIPNomCC^

Text 3

WIP Nominal CC
How Nom/CC/Dep are specified
    Sage V2015 onwards
    In Cell Searching values in table CC

PjWIPNomDep^

Text 3

WIP Nominal Dep
How Nom/CC/Dep are specified
    Sage V2015 onwards
    In Cell Searching values in table DEP

Special:

PjIgnore

Yes/NoTF

Ignore line when importing, if contains anything. This allows list of ProjectItems, then formula to ignore import if certain condition, such as no budget.

PjIsPI

Yes/NoTF Read

"YES" if line is a ProjectItem

PjIsCost

Yes/NoTF Read

"YES" if line is a Cost item

PjIsRev

Yes/NoTF Read

"YES" if line is a Revenue item

Customer:

PjCust^

Text 8

Customer set on Project line. IMPORTANT: If specified, PostTrans will set to this customer, and delete any others set
    In Cell Searching values in table Cust

PjQoQty

Double

Customer Quote Qty for PjCust, set to 1 if 0 and PjQoUnit has value

PjQoUnit

Double

Customer Quote Unit for PjCust. Price Quoted is set to PjQoUnit*PjQoQty

PjQoUOM^

Text 30

Customer Quote UOM for PjCust
    In Cell Searching values in table ProjectUOM

PjQoPrice

Double

Customer Quote Price for PjCust. If left blank will be calulate from PjQUnit*PjQoQty

PjQoSalesOrdNo

Text 30

Customer Quote Sales Ord for PjCust

Additional Fields:

PjAddL1

Text 30

Additional Name Label.

PjAddV1^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL2

Text 30

Additional Name Label.

PjAddV2^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL3

Text 30

Additional Name Label.

PjAddV3^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL4

Text 30

Additional Name Label.

PjAddV4^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL5

Text 30

Additional Name Label.

PjAddV5^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL6

Text 30

Additional Name Label.

PjAddV6^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL7

Text 30

Additional Name Label.

PjAddV7^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL8

Text 30

Additional Name Label.

PjAddV8^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL9

Text 30

Additional Name Label.

PjAddV9^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL10

Text 30

Additional Name Label.

PjAddV10^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL11

Text 30

Additional Name Label.

PjAddV11^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL12

Text 30

Additional Name Label.

PjAddV12^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL13

Text 30

Additional Name Label.

PjAddV13^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL14

Text 30

Additional Name Label.

PjAddV14^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL15

Text 30

Additional Name Label.

PjAddV15^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL16

Text 30

Additional Name Label.

PjAddV16^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL17

Text 30

Additional Name Label.

PjAddV17^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL18

Text 30

Additional Name Label.

PjAddV18^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL19

Text 30

Additional Name Label.

PjAddV19^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField

PjAddL20

Text 30

Additional Name Label.

PjAddV20^

Text 30

Additional Name Value.
    In Cell Searching values in table ProjAddField


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.

Project Accounting:

 

Phase/Groups (Module: Projects)

Sheet:'Groups'
Allow Extract Phase/Groups
   Main page for Phase/Groups
   Help article for Phase/Groups

 

Project Items (Module: Projects)

Sheet:'ProjectItem'
Allow Extract Project Items
   Help article for Project Items

 

Project Structure and Budgets (Module: Projects)

Sheet:'Project Structure'
Allow Extract/Amend/Import Project and Budgets
   Main page for Project Structure and Budgets
   Help article for Project Structure and Budgets

 

Project Template for Project Manager to fill in (Module: Projects)

Sheet:'INPUT'
Allow Extract/Amend/Import Project additional fileds and Budgets. Plus add customer and quoted price which optional sets revenue budget
   Help article for Template for Project Manager to fill in

 

Project Cost Adjustments

Sheet:'Cost Adj'
Allow Import Project Cost Adjustments

 

Project Cost Adjustments + NOM line

Sheet:'Cost Adj with NOM line'
Allow Import Project Cost Adjustments with Nominal Line

 

Project Revenue Adjustments

Sheet:'Rev Adj'
Allow Import Project Revenue Adjustments

 

Project Cost Opening Balance

Sheet:'Cost OB'
Allow Import Project Cost Opening Balance Adjustments

 

Project Revenue Opening Balance

Sheet:'Cost OB'
Allow Import Project Revenue Opening Balance Adjustments

 

Project Expense Items(Module: Projects)

Sheet:'ExpenseItems'
Extract Expenses Items

 

Project Expenses + manual Tax (Module: Projects)

Sheet:'Expenses manual Tax'
Import Expenses

 

Project Expenses with Phase Filter (Module: Projects)

Sheet:'Expenses Phase Filter'
Import Expenses

 

Project Expenses (Module: Projects)

Sheet:'Expenses'
Import Expenses

 

Project Expenses By Resource (Module: Projects)

Sheet:'Expenses Entry'
Import Expenses