Menu

Project Items

Sage 200:Project Items

All of the following functionality requires the PostTrans Projects Module.

PostTrans imports Project 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 Project Items

Extract/Import Sage 200 Project Items

Explanation of this type of Import

This type of PostTrans template allows the user to extract Project Accounting ProjectItems.

It can be used to extract the data to for use in your own lookup, or excel data validation, when creating your own bespoke Project import template.

Future development may include the ability to reimport new or edited data.

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

Template Structure for Static Data

The template must have tag ‘Sage200projItem’ in cell A1 to instruct PostTrans that we are interested in Sage 200 Project 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 Project Items 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 PiSearch 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 Items Data

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

PostTrans uses the tagged column PiCRC 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 PiCode 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

Restrictions

  • Does not Import Project items

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:

Sage200projItem

Text Req

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

PiCRC

Text Req

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

Record:

PiType^

Text 60 Req

ProjectItem Type
    In Cell Searching values in table ProjectItemType

PiCode

Text 20 EOF Req

ProjectItem Code

PiTitle

Text 60 Req

Title

PiDescription

Memo

Description

Cost Budget:

PiCostQty

Double

Budget Cost Qty

PiCostVal

Double

Budget Cost Value

Rev Budget:

PiRevQty

Double

Budget Cost Qty

PiRevVal

Double

Budget Cost Value

Other:

PiOwner

Text 30

Owner of Job

Costs & Charges:

PiUnitCost

Double

Job Unit Cost

PiUOM

Text 30

Job Unit Cost Unit of Measure

PiUplift

Double

Uplift Percent

Costs & Charges Rules:

PiTimeSheet^

Text 30

TimeSheet Costing Rule
    In Cell Searching values in table ProjectTimeAndMaterialsPricingRule

PiStockIssues^

Text 30

Stock Issues Costing Rule
    In Cell Searching values in table ProjectTimeAndMaterialsPricingRule

PiOtherCost^

Text 30

Other Cost Costing Rule
    In Cell Searching values in table ProjectTimeAndMaterialsPricingRule

Analysis - Nominals:

PiExpNom^

Text 16

Expense Nominal, if blanks set to ProjectItem Type default
How Nom/CC/Dep are specified
    In Cell Searching values in table NOM

PiRevNom^

Text 16

Rev Nominal, if blanks set to ProjectItem Type default
How Nom/CC/Dep are specified
    In Cell Searching values in table NOM

PiWIPNom^

Text 16

WIP Nominal, if blanks set to ProjectItem Type default
How Nom/CC/Dep are specified
    Sage V2015 onwards
    In Cell Searching values in table NOM

Analysis - Nominal Cost Center:

PiExpNomCC^

Text 3

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

PiRevNomCC^

Text 3

Rev Nominall CC
How Nom/CC/Dep are specified
    In Cell Searching values in table CC

PiWIPNomCC^

Text 3

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

Analysis - Nominal Department:

PiExpNomDep^

Text 3

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

PiRevNomDep^

Text 3

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

PiWIPNomDep^

Text 3

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

Other2:

PiAvail

Yes/NoTF

Available for selection

PiMarkup

Double

Markup

PiBillRate

Double

Bill Rate


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