Exporting Time to QuickBooks

Privilege needed: Administrator

This help page will address a number of topics having to do with exporting time data from Unanet into QuickBooks.  The following topics are covered:


Overview

The QuickBooks Pro product has the capability of importing data from an external source.  Data can include lists that correspond to QuickBooks' lists, budgets, and individual transactions.  We will be utilizing the Timer Activity imports to facilitate the importing of time data into QuickBooks.   This is facilitated by creating a comma (or tab) separated file in a format that QuickBooks can understand (the QuickBooks IIF format).  This format includes the use of column heading tags (keywords) and row heading tags to help identify the beginning and end of each transaction. 

*To learn more about the QuickBooks Import capability, refer to the help documentation that accompanies the QuickBooks product.

Unanet has prepared a sample time export template that can be used as a starting point for pulling time data from Unanet in a format that is compatible with the QuickBooks IIF format.   With this template, you can possibly facilitate the following activities:

The sections below provide additional information about the usage of the template and several assumptions dealing with how data must be setup in Unanet and QuickBooks in order to successfully move time data between the systems.  

NOTE: It is important to note that both QuickBooks and Unanet can be configured and used in a number of ways.  The sample QuickBooks template provided is based on a standard Unanet setup and assumes that QuickBooks is configured in a fashion similar to the setup reflected in the QuickBooks sample company files.  If you are using Unanet or QuickBooks in a unique fashion, you will most likely need to modify the template to meet your particular needs.  Check out the template help for more general  information on templates.


IIF Field Layout Basics

In order to import time data into QuickBooks, the file must be created with a certain syntax.  *To learn more about the QuickBooks Import capability, refer to the help documentation that accompanies the QuickBooks product.

The output generated by the sample QuickBooks time template can be best viewed in a spreadsheet type software product (one that can support the viewing of comma delimited data.  If you were to view this output, the row and column headings would appear as displayed in the table below.  

This table illustrates the row and column headings that are necessary to transfer the time data from Unanet into QuickBooks.  The cells with a gray background indicate the fields that QuickBooks requires.  

!TIMERHDR VER REL COMPANYNAME FROMTIMER COMPANYCREATETIME      
TIMEHDR                
!TIMEACT DATE JOB EMP ITEM PITEM DURATION NOTE BILLINGSTATUS
TIMEACT                

The first several rows in the output file contain important keyword header information.  Following these rows, you will have a number of rows that reflect the individual timesheet cells being exported from Unanet.  Each of these rows will begin with the first column TIMEACT.

The table below provides additional information about the purpose of each field.

Tag Description

First Column Required Tags

!TIMERHDR Required.  Must be in the first column of the first row.
TIMEHDR Required.  Must be in the first column of the second row and indicates the beginning of the header data.
!TIMEACT Required.  Must be in the first column of the third row.
TIMEACT Required.  Must be in the first column of every additional row and indicates the beginning of timer activity data.

TIMERHDR  Row Fields

VER The version number of your QuickBooks Pro Timer.  You can find this information by first running an Timer Activity export in QuickBooks and viewing the output.  

It may look something like:   Version 7.0D

You will need to modify the template replacing the example value with your specific value.

REL The release number of your QuickBooks Pro Timer.  You can find this information by first running an Timer Activity export in QuickBooks and viewing the output.

It may look something like:   Release R1P

You will need to modify the template replacing the example value with your specific value.

COMPANYNAME The name of your QuickBooks Pro company.  You can find this information by first running an Timer Activity export in QuickBooks and viewing the output.

It may look something like:   Rock Castle Construction

You will need to modify the template replacing the example value with your specific value.

FROMTIMER Indicates whether you are exporting or importing data.  We will always populate this field with a "Y".
COMPANYCREATETIME A unique number that comes from your QuickBooks Pro company file.  You can find this information by first running an Timer Activity export in QuickBooks and viewing the output.

It may look something like:   890239742

You will need to modify the template replacing the example value with your specific value.

TIMEACT  Row Fields

DATE The date the activity was performed.  Enter the date in a MM/DD/YY format.  For example, you would enter June 21,2001 as 06/21/01.  This is the workdate from the Unanet Timesheet cell entry.
JOB We populate this field with the Unanet Project Organization concatenated with the Project Code of the project that the time was incurred for.  This is meant to map to the QuickBooks "Customer:Job" entry.  

Again, you may prefer to use the Organization Account Number field concatenated with the Project Account Number fields or some other fields if you have other requirements.

EMP We populate this field with a concatenation of the Unanet user's name (last name, first name and middle initial).  This is meant to map to the QuickBooks employee name and must match exactly.
ITEM The name of the service item assigned to the activity.  We populate this field with the Unanet Task Name.
PITEM We populate this field based on the value of the Unanet Pay Code.  The template will need to have an if or a CASE statement expression to map the various Unanet Pay Codes to the QuickBooks Payroll Items.  For example: a Unanet Pay Code of RT could be mapped to the QuickBooks "Salary" payroll item, and "OT" could be mapped to the QuickBooks "Overtime Pay".  You will need to populate the template with the appropriate values.
DURATION This is the duration of the activity in hours and minutes.  Enter the duration in an HH:MM format.  For example, if the activity lasted 4 hours and 15 minutes, you would enter 4:15.  The duration should not be larger than 23:59.  We populate this with the hours from each Unanet Timesheet cell. 
NOTE This field contains the timesheet cell "comments" from Unanet.  Note that any newline characters will be replaced with a space on the export.
BILLINGSTATUS Indicates the billing status of the activity.  If the Unanet project type is billable, then we supply a "1", otherwise we supply a "0".

*To learn more about the QuickBooks Import capability, refer to the help documentation that accompanies the QuickBooks product.

An example of an export file is presented below:


IIF File Naming

When you are exporting the data out of Unanet, in preparation for the import into QuickBooks, the extension given to the file is important.  QuickBooks is expecting the file extension to be "IIF" or "iif".  When on the Export -- Time screen, one of the options is to supply the output filename.  You can at this point provide the appropriate IIF extension.  If not, you will need to rename the file prior to importing it into QuickBooks.

For example, an appropriate file name may look like:   january_time.iif


Importing into QuickBooks

Once you have the file exported from Unanet, you may want to first review it by opening the file using a spreadsheet program.  You may want to verify that the data is as you expect it (particularly if you have further modified the template).  

When ready to import the file into QuickBooks, you can open QuickBooks and select the "Pay Roll and Time" tab, then the Timer Import/Export activity option --- select the appropriate file that you exported from Unanet.  After the transactions have been imported into QuickBooks, you can see your employee time activity by accessing the "Use Weekly Timesheets" functionality.  The imported data entries should be visible.

Return to Unanet Table of Contents page


Copyright 1998-2001 Computer Strategies, Inc., All Rights Reserved.
Last revised: February 13, 2003.