How do I integrate Pacific Timesheet with Quickbooks using the IIF interface?

Question

How do I integrate Pacific Timesheet with Quickbooks using the IIF interface? 

Answer

QuickBooks Export File

Intuit QuickBooks allows the importing data from an external source. Pacific Timesheet provides a QuickBooks-compatible timesheet export file that you can use to transfer time activities directly into QuickBooks for both invoicing and payroll. The format of the file is the Intuit Interchange Format (IIF), and uses the Timer Activity import feature of QuickBooks.

Note: More advanced QuickBooks integration, including bi-directional transfer of jobs, employees and codes, is supported through the Time Bank data integration facility. You can find out more by contacting your sales representative or visiting our website at http://www.pacifictimesheet.com/.

To set up your system for proper export you will need to configure the following properties:

Property	Description
Enabled	Check this option to enable the QuickBooks export file support. When enabled you will see an "Export - QuickBooks" item in the options menu on the Timesheets and Approvals tabs.
Company	The QuickBooks company name. This value is found by doing a Timer Activity Export from QuickBooks, as detailed below.
Company Create Time	The QuickBooks company create time. This value is found by doing a Timer Activity Export from QuickBooks, as detailed below.
Template	The QuickBooks timer activity export file template. This is detailed below.

You can control which time entries are exported by specifying any of the following filters:

Property	Description
Billable Filter	Use this filter to export only billable or non-billable hours. You might use this filter if you are only using QuickBooks for billing, for instance.
Payable Filter	Use this filter to export only payable or non-payable hours.
Hours Filter	Use this filter to only export work hours or leave hours.
Employee Filter	Use this filter to only export specific employee types.

Getting the Company Name and Create Time

The first step to configuring your system for proper QuickBooks exporting is to determine the Company name and Company Create Time. To do this you need to start QuickBooks Pro and run the Timer Lists export under the File > Utilities > Export menu. Save this file to a convenient location and open it with a text editor such as Notepad or Wordpad. Look for lines like the following:

!TIMERHDR        VER        REL        COMPANYNAME        IMPORTEDBEFORE        FROMTIMER        COMPANYCREATETIME

TIMERHDR         7         0        Arpent        N        Y        1165339129

The TIMERHDR line has the Company name and Company Create Time values we need. In this case the Company name is Arpent, and the Company Create Time is 1165339129.

The TIMERHDR fields are:

VER	The version number of the QuickBooks Timer, for example 7. You can find this information by first running an Timer Activity export in QuickBooks and viewing the output. You will need to modify the template replacing the example value with your specific value.
REL	The release number of the QuickBooks Timer. You can find this information by first running an Timer Activity export in QuickBooks and viewing the output. You may need to modify the template to replace the example value with your specific value, otherwise QuickBooks may generate a warning message when importing the data.
COMPANYNAME	The name of your QuickBooks company. If you leave this as ${System.company} it will be automatically substituted with the Company property entered above.
FROMTIMER	Indicates whether you are exporting or importing data.  It should always be the value "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. You will need to modify the template replacing the example value with your specific value.

Configuring the Import Template

The export file generated by Pacific Timesheet will contain the TIMERHDR records above, as well as TIMEACT records for each timesheet's time entries. The format of a TIMEACT might look like the following:

!TIMEACT        DATE        JOB        EMP        ITEM        PITEM        DURATION        PROJ        NOTE        XFERTOPAYROLL        BILLINGSTATUS

TIMEACT        3/25/08        ABCompany:Job 123        Bill Williams        Support                02:00                        N        1

The TIMEACT fields are:

DATE	The date the activity was performed, formatted as YYYY-MM-DD (the standard QuickBooks date exchange format). This is generally set to the variable ${TimeEntry.date}, described below.
JOB	The QuickBooks job, which is in the format Customer:Job. This is generally set to the variable ${Customer.name}:${Project.name}, described below.
EMP	The name of the employee. This is meant to map to the QuickBooks employee name and must match exactly. Outputted as "Lastname, Firstname" by default. This is generally set to the variable ${User.name} or ${User.fullName}, described below.
ITEM	The name of the service item assigned to the activity. This is generally set to the variable ${Task.name}, described below.
PITEM	The name of the payroll item. This is generally set to the variable ${PayCode.name}, described below.
DURATION	This is the duration of the activity in hours and minutes, in HH:MM format. This is generally set to the variable ${TimeEntry.hours}, described below.
NOTE	This field contains the timesheet cell notes. Note that any newline or tab characters will be replaced with a space on the export. This is generally set to the variable ${TimeEntry.notes}, described below.
XFERTOPAYROLL	"N" if not for payroll, or "Y" if for payroll.
BILLINGSTATUS	Indicates the billing status of the activity, either "1" for billable, or "0" for non-billable. By default populated with the Task billable status. This is generally set to the variable ${TimeEntry.billFlag}, described below.

You can control which values from Pacific Timesheet are substituted into the TIMEACT records by editing the template. The following template variables are supported (and are case-sensitive):

Variable	Description
${TimeEntry.date}	The date of the time entry, formatted as YYYY-MM-DD (the standard QuickBooks date exchange format). This is generally used for the DATE field of a TIMEACT record.
${TimeEntry.dateUS}	Just like ${TimeEntry.date}, only formatted as M/DD/YY.  Some older versions of QuickBooks require this format.
${TimeEntry.hours}	The time entry hours, formatted as hours:minutes (the standard QuickBooks hours format). This is generally used for the DURATION field of a TIMEACT record.
${TimeEntry.notes}	The time entry notes, if any. This is generally used for the NOTE field of a TIMEACT record.
${TimeEntry.billFlag}	The time entry billable flag, with the value "0" if the time entry is not billable, or "1" if the time entry is billable.
${Customer.name}	The name of the Customer for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Customer to something else (e.g. Client), you still use Customer.name for the variable name.
${User.name}	The name of the employee, formatted "Lastname, Firstname". This is generally used for the EMP field of a TIMEACT record.
${User.fullName}	The name of the employee, formatted as "Firstname Lastname". This is generally used for the EMP field of a TIMEACT record.
${User.id}	The ID of the employee. This is generally used for the EMP field of a TIMEACT record.
${Customer.id}	The ID of the Customer for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Customer to something else (e.g. Client), you still use Customer.id for the variable name.
${ProjectGroup.name}	The name of the Project Group for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Project Group to something else (e.g. Client Portfolio), you still use ProjectGroup.name for the variable name.
${ProjectGroup.id}	The name of the Project Group for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Project Group to something else (e.g. Client Portfolio), you still use ProjectGroup.id for the variable name.
${Project.name}	The name of the Project for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Project to something else (e.g. Job), you still use Project.name for the variable name.
${Project.id}	The ID of the Project for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Project to something else (e.g. Job), you still use Project.id for the variable name.
${TaskGroup.name}	The name of the Task Group for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Task Group to something else (e.g. Phase), you still use TaskGroup.name for the variable name.
${TaskGroup.id}	The ID of the Task Group for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Task Group to something else (e.g. Phase), you still use TaskGroup.id for the variable name.
${Task.name}	The name of the Task for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Task to something else (e.g. Activity), you still use Task.name for the variable name.
${Task.id}	The ID of the Task for this time entry. This is generally used for the JOB or ITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Task to something else (e.g. Activity), you still use Task.id for the variable name.
${PayCode.name}	The name of the Pay Code for this time entry. This is generally used for the PITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Pay Code to something else, you still use PayCode.name for the variable name.
${PayCode.id}	The ID of the Pay Code for this time entry. This is generally used for the PITEM field of a TIMEACT record. Note that even if you customized the terminology and renamed Pay Code to something else, you still use PayCode.id for the variable name.

Note that when using the .name variables you need to be careful to match the names in Pacific Timesheet with the names in QuickBooks. If your names in Pacific Timesheet cannot be made identical to those in QuickBooks you will want to use the ID field instead. Change the appropriate template fields to use the .id variables instead, then update the ID property of the employees, projects or other objects in Pacific Timesheet with the name as it appears in QuickBooks.

Exporting To QuickBooks

Once you have configured the company name, company create time and template as needed, you can generate the QuickBooks export file. To do this click on the Timesheets or Approvals tab in Pacific Timesheet and list the timesheets you are interested in exporting. Click the options menu (the icon button with a small triangle to the upper-right of the timesheet list) and choose the "Export - QuickBooks" item. Save the resulting file to the appropriate location on your computer.

Next, start QuickBooks, then go to the File > Utilities > Import > Timer Activities and import the file you just saved. QuickBooks will import the data and generate a detailed report of the import results. You can import data as often as your needs require, but be careful not to import the same file twice as QuickBooks will generate duplicate timer activities.