Menu

Sage 200:Import Project Timesheets

This type of PostTrans import, imports Project Timesheets, which can be input manually, data from an ODBC connection or imported from CSV file.

Page Contents

Sage 200 Expense Import Features

  • Import a simple list of Timesheet lines
  • Import a 7 day Timesheet lines
  • Expense Item can be "Group/ExpenseItem"
  • Assign to ProjectItems in specific Project Group
  • Validation highlights any problems for easy correction
  • Looks up Charge/Cost values unless overridden in sheet
  • Project Item phase can be "Project/Phase" in Project column
  • Can import simple list of Timesheet lines, for sheet per employee (Resource), Project

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 Sage 200 Project Timesheet transactions (Examples shown below).

Examples of importing Sage 200 Timesheet

Posting a list of Timesheet

Multiple Timesheet lines can be imported by adding the Header fields to the Tag row 28. These are easily inserted using the Tags button, see all tags listed at the bottom of page.

Posting a list of Expenses

Posting a list of Expenses

After pressing the Import Button:

Conformation and Validation before Posting Timesheet Line

Conformation and Validation before Posting Timesheet Line

Conformation can be turned off in PostTrans Setup

Posting a list of Timesheet for 7 days

Posting Timesheet for 7 Days

Posting Timesheet for 7 Days

In the above example we have added tag 'PtHFirstDayDate' to the Header section of the template. This specifies the first day of the week for Tag "PtDay1" (column G). Tag "PtDay2" (column H) will be the next day after this.

Any tag beginning with 'PtH' can be placed in the Header Section (Above line 28).

Posting a list of Timesheet for 7 days Single Employee

Posting Timesheet for Single Employee

Posting Timesheet for Single Employee

In the above example we have moved tag 'PtHWorksNumber' to the Header section of the template. Thus all lines will be posted with the values specified.

Any tag beginning with 'PtH' can be placed in the Header Section (Above line 28).

Template Structure for Transaction Data

The template must have tag ‘Sage200projTime’ in the comment of cell A1 to instruct PostTrans that we are interested in importing Transactions, 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.

The top section of this template contains Transaction header fields, in the case of a single transaction, or just transaction type of all multiples transactions list from row 30 onwards.

These Transaction header fields can be deleted if not required, or added by moving the cursor, to a cell in the header area, and pressing the Tag button. This will display a list of PostTrans Tags. A tag beginning with “PeH”, denoting for use in a header, can then be inserted. Table of Project Timesheets Tags

Row 28 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 suite your needs, and the tags are added using the Tags button. Deleting the Tag name, in the cell, from this row removes the Sage 200 mapping.

Row 27 designates what will happen when the Clear button is pressed, see Clear Transaction and copy down formula

Hovering over these tags, and header cells will show a comment which explains the tags purpose, data type and any other useful details.

For importing multiple transactions, most header tags can be copied/moved to row 28, thus designating a column to set the header value. 

On-line Training video of how all this works 

Common Features

In-Cell Searching

Any Tag ending with ^ will 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.

Cursor Path

The path the cursor takes through the sheet, when entering data manually, can also be easily defined in Setup.  See Set Cursor Path

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

Validation

Any values and associated codes will be invalid during import.   The cursor will be moved to highlight any problems in data.

POSTED Document Ref

After Posting a transaction, PostTrans will write "POSTED:" and the transaction reference number in column A (tag TLPosted).  Lines marked as "POSTED" are then ignored by PostTrans when re-posting. So, transactions cannot easily be posted twice. So the transaction can not easily be posted twice.   The user would have to remove "POSTED" from column A in order to post the transaction a second time.  The user would have to remove "POSTED" from column A, to post the transaction a second time.

Confirmation before Posting Transaction

Just before posting the validated transaction, PostTrans will optionally show a transaction summary for the user to validate.   This can be turned off in Setup window.

File Import Button

This can be enabled in Setup to easily import CSV, 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.

Clear Button

The Clear button will clear all values on the sheet, according to the row just above the tag row, and copy down any formula.  Clear Transaction and copy down formula

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. 
A subscription be purchased, and cancel on-line at any time very easily.   Pricing

Delete Records

The Tag/Column PtDelete can be used to delete records, following the usual Sage 200 rules.

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.

Demonstration Video

Since the functionality of this type of import is quite broad, the following Videos/Blog articles have videos showing it in action:

 
 
   Sage 200 – Import TimeSheet from List
  0:00 Template Design - Where, How and optional
  1:30 Import Timesheet
  2:50 How to Override Charge/Cost Rate
  3:50 Charge and Cost Rates
  3:10 Total Charge/Cost written back for conformation
  3:30 Using formulee to manipulate data
  4:28 Explain in-cell searching
  5:00 Add a line manually
  5:40 Disable in-cell searching
  6:30 CSV File import
  8:50 How to select specific Project Items in tree
 
 

 
 
   Sage 200 – TimeSheet 7 day entry import
  0:00 Template Design - Where, How and optional
  1:40 Set first working day
  2:10 Enter a new line
  3:00 How to Override Charge/Cost Rate
  3:30 Override Charge and Cost
  4:00 Import Timesheet
  4:20 Total Charge/Cost written back for conformation
 
 

PostTrans Tags which map to Sage 200

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

Tag Name

Data Type

Description

Important Fields:

PtPosted

Text Req

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

Resource Or In Lines:

PtHWorksNumber^

Text 20 Head

Resource Works Number
    Sage V21 onwards
    In Cell Searching values in table ProjectTimeResourceByWorksNumber

Project:

PtHProjectCode^

Text 30 Head

Project Code, or can be ProjectCode + '/' + Phase Code
    Sage V21 onwards
    In Cell Searching values in table ProjectTimeProjectCode

PtProjectItem^

Text 30 Req

Project Item code
    Sage V21 onwards
    In Cell Searching values in table ProjectTimeItemCode

PtPhaseFilter^

Text 30

Phase Filter. If project has duplicate Project items in different phases, this can direct to correct Phase
    Sage V21 onwards
    In Cell Searching values in table ProjectTimePhaseCode

Details:

PtNarative

Text 6000 EOF Req

Narative
    Sage V21 onwards

Hours if list:

PtDate

Date

Date for hours id a simple list of Hours
    Sage V21 onwards

PtHours

Double

Hours
    Sage V21 onwards

PtDays

Double

Days
    Sage V21 onwards

Cost Rate:

PtChrgRateType^

Text 30

Chrg Rate Type code
    Sage V21 onwards
    In Cell Searching values in table ProjectTimeChargeRate

PtChrgeRate

Double

Chrge Rate leave blank, or -1, to use system rate, else overide system charge rate
    Sage V21 onwards

PtCostRateType^

Text 30

Cost Rate Type code
    Sage V21 onwards
    In Cell Searching values in table ProjectTimeCostRate

PtCostRate

Double

Cost Rate leave blank, or -1, to use system rate, else overide system cost rate
    Sage V21 onwards

Hours if entered for week:

PtHFirstDayDate

Date HeadOnly

Defines the date of PtDay1
    Sage V21 onwards

PtDay1

Double

PtHFirstDayDate
    Sage V21 onwards

PtDay2

Double

PtHFirstDayDate+1
    Sage V21 onwards

PtDay3

Double

PtHFirstDayDate+2
    Sage V21 onwards

PtDay4

Double

PtHFirstDayDate+3
    Sage V21 onwards

PtDay5

Double

PtHFirstDayDate+4
    Sage V21 onwards

PtDay6

Double

PtHFirstDayDate+5
    Sage V21 onwards

PtDay7

Double

PtHFirstDayDate+6
    Sage V21 onwards

Other:

PtHStatus^

Integer Head

Line Status. If autorise is OFF use Submitted
    Sage V21 onwards
        In Cell Searching values:Status
                  0, Draft
                  1, Saved
                  2, Submitted
                  3, Authorised
                  4, Rejected
                  5, Complete

Post Back Figures:

PtTotalChrge

Double

Total Charge is wrtten back for conformation
    Sage V21 onwards

PtTotalCost

Double

Total Cost is wrtten back for conformation
    Sage V21 onwards


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

Head  A header tag can be used in header section, before row 30, of the transaction template.  Or in the rows data of data, below row 30, of the template like a traditional CSV importer.

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.

 

TimeSheets with Phase Filter (Module: Projects)

Sheet:'TimeSheet List Phase Filter'
Import TimeSheets

 

TimeSheets from List (Module: Projects)

Sheet:'TimeSheet List'
Import TimeSheets

 

TimeSheets for each Day of Week (Module: Projects)

Sheet:'TimeSheet Days'
Import TimeSheets