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