Menu

Project

Releated Topics.

Filter Extracted Data
Training on Static Data
Product Adjustment
Nominal Journal
Sales Quote/Order/Invoice

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 50:Extract/Amend/Import Projects

All of the following functionality requires the Pro version of PostTrans.

PostTrans imports Projects, which could be manually entered with the assistance of in-cell searching, using copy and paste, imported from CSV file or extracted from Sage 50 using the Extract button.

Page Contents

Project Import in Sage 200

Import Project Records Features in Sage 50

Simply add new lines to the spreadsheet from row 30 on-wards. Then press Import button, on the PostTrans Toolbar, to import single or multiple Suppliers (Examples shown below).

Advantages

Examples of importing Sage 50 Project Records

Project Import template for importing Projects into Sage 50

Project Import template for importing Projects into Sage 50

Demonstration Video

To be recorded but the same priciple as Customer Import.

To use the template, with Sage 50, 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.

Template Structure for Static Data

The template must have tag ‘Sage50proj’ in cell A1 to instruct PostTrans that we are interested in Sage 50 Projects, 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 50 mapping.   Table of Projects 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

Amending Projects Data

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

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 50.  PostTrans will read the Header cells and then process the rows of data until PjCode 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 50 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 50

Not all these tags/columns are needed. The table below lists the required and recommended:

Tag Name

Data Type

Description

Important Fields:

Sage50proj

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.

PjDelete

Text

This cell is very important, as it allows PostTrans to let you know of any errors or warnings while importing. If data is extracted out of the system, then it will contain a number. This number is calculated so PostTrans can work out if you have changed

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 ProjectCode

Project Details:

PjCode

Text 12 EOF Req XML

Project code

PjName

Text 255 Req XML

Project Name

PjDesc

Text 255 XML

Project Description

PjStartDate

Date XML

Date the project starts

PjEndDate

Date XML

Date the project ends

PjStatus^

Text 15 Req XML

ProjectStatus Object
    In Cell Searching values in table ProjStatus

Customer Details:

PjCustCode^

Text 8 XML

Customer object
    In Cell Searching values in table CustCode

PjOrderNo

Text 30 XML

Project Order Number

Site Details:

PjContact

Text 30 XML

Site Contact Name

PjAdd1

Text 60 XML

Site Address Line 1

PjAdd2

Text 60 XML

Site Address Line 2

PjAdd3

Text 60 XML

Site Address Line 3

PjAdd4

Text 60 XML

Site Address Line 4

PjAdd5

Text 60 XML

Site Address Line 5

PjPhone

Text 30 XML

Telephone Number (misspelling of Property name is correct)

PjFax

Text 30 XML

Fax Number

PjEmail

Text 255 XML

Project email address

PjCounty^

Text 2 XML

Country Object
    In Cell Searching values in table CountryCode

Billing Totals:

PjLastBilled

Date Read

Last Billed Date

PjTotalBilled

Currency

Total Billed

PjBilledNet

Currency Read

Billed Net

PjBilledVAT

Currency Read

Billed VAT

Profit to date:

PjProfitToDate

Currency Read

Profit to Date

PjPriceQuoted

Currency

Project Price Quoted

PjOutToBill

Currency Read

Outstanding to Bill

Analysis Types:

PjAnalysis1

Text 30 XML

Project Analysis 1

PjAnalysis2

Text 30 XML

Project Analysis 2

PjAnalysis3

Text 30 XML

Project Analysis 3

Budget Totals:

PjTotalBudget

Currency Read

Total Budget for the Project

PjVariance

Currency Read

The TotalBudget less the TotalCost

Cost Totals:

PjLastCostDate

Date Read

Last Cost Date

PjTotalCost

Currency Read

Total Cost for the Project

PjComCost

Currency

Committed Cost


Req = Field is required    EOF = End of file, Stop when no more values    

XML = Un-attended PostTrans XML API for Sage 200

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 50, 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.

 

Assets

Sheet:'Assets'
Edit Assets
   Main page for Project records Import

Projects:

 

Project

Sheet:'Projects'
Edit Projects
   Main page for Project records Import
   Help article for Project records Import