The PI Integration for QuickBooks Desktop (QBDT) allows you to exchange data between Project Insight (PI) and QuickBooks desktop. This data exchange is initiated through use of the QuickBooks Web Connector. The QuickBooks Web Connector is a free Windows application provided by Intuit. The QuickBooks Web Connector may have been installed when you installed QBDT, but if not, it can be downloaded here: https://quickbooks.intuit.com/community/Help-Articles/Set-up-QuickBooks-Web-Connector/td-p/185852.
You can import invoices, vendor bills, vendor credits, checks and credit card charges from QBDT into PI.
TIP: QBDT invoices are imported into PI as new invoices, while vendor bills, vendor credits, checks and credit card charges are imported into PI as new expense entries. Please note QBDT transactions must be linked to a job, and that job must be matched up to a PI project before those transactions will be imported. This means that jobs in QBDT need to be named exactly the same as projects in PI or they have a valid PI project value stored in a designated QBDT custom field.
You can also export invoices, expense entries and time entries from PI into QBDT.
TIP: Before transferring to QBDT, PI expense entries and time entries must be on approved expense reports or time sheets, respectively.
Install and Configure the Add-on
The PI Integration for QuickBooks Desktop add-on can be installed from the Get Add-ons page in the Administration section in the left navigation of PI. Once installed, click on [Configure]. Here you'll find your QuickBooks Web Connector password to import into Web Connector.
You must select at least one import or export option. Again, QBDT invoices are imported into PI as new invoices while vendor bills, vendor credits, checks and credit card charges are imported into PI as new expense entries.
Export Data to QuickBooks
- Export invoice records from Project Insight into QBDT as new invoices. This option allows you to create invoice records in PI, review them, and when ready, use the QuickBooks Web Connector to create QBDT invoices from the PI invoice records.
- Export expense entries from Project Insight into QBDT as new vendor bills. This option allows your team to create expense entries in PI, submit them as an expense report, review and approve the expense report, and when ready, use the QuickBooks Web Connector to create QBDT vendor bills from APPROVED PI expense reports. This option is common for teams submitting reimbursable expenses on an expense report
TIP: For this option to export correctly you'll need to add the user's QBDT vendor name to their user profile.
- Export Time Entries. This option is common if you are using QuickBooks payroll and are using hours entered in PI to pay your people. Time entry must follow one of the time sheet approval methods which allows your team to create time entries in PI, submit them on a time sheet, review and approve the time sheets, and when ready, use the QuickBooks Web Connector to create QBDT time entries from APPROVED PI time sheets.
TIP: For this option to export correctly you'll need to add the user's QBDT employee name to their user profile.
Specific Settings for Exporting Data to QuickBooks
Export Invoice Records
When exporting invoice records to QBDT, there are two other options you can consider
- Include a list of the time/expense entries in each line item description when exporting invoice records to QuickBooks - This option is not common and will really increase the size of your invoices in QBDT
- Create invoices in QuickBooks at customer level - use this if you do not use jobs in QBDT i.e. all invoices are attached to the customer not a customer/job. If you you do invoice at the customer level only, consider using QBDT invoice custom fields to identify your project. See Advanced Setting below
Export Expense Entries
If you are exporting expense entries to QBDT, you must let QBDT know how to match your PI user to a QBDT vendor. Do this by
- Setting up a user custom field for the QBDT vendor name. See Custom Field Definition Form for how to do this
- Selecting the Custom Field For QB Vendor Name from the drop down list in this app admin
- Setting the Vendor Name Template. this should match how you enter in your Vendor names in QBDT. The default is [[LastName]], [[FirstName]], but if you enter your user vendor names first name then last name, just switch the template around
i.e. [[FirstName]] [[LastName]]
Export Time Entries
To export time entries to QBDT, you must let QBDT know how to match your PI user to a QBDT employee. Do this by
- Setting up a user custom field for the QBDT employee name. See Custom Field Definition Form for how to do this
- Selecting the Custom Field For QB Employee Name from the drop down list in this app admin
- Entering the list of valid QuickBooks Payroll Items in this app admin (also enter the default to use in the event A QuickBooks Payroll Item wasn't selected on the user's profile.
TIP: The default QuickBooks Payroll Item prevents errors when you transfer data to QBDT. Don't worry if it is not right, you can fix it in QBDT and then also in PI so it doesn't happen again
- Selecting the individual user's QuickBooks Payroll Item in their profile
Mapping Time Codes to Payroll Items
This feature allows you to set a QBDT payroll item based on the time code your user picks for a time entry. Do this for the exceptions.
E.g. if users spends 85% of their time on projects and there is one QBDT payroll item for projects, just set that one per above. You then only need to map time codes if there are other codes that, when used, you don't want to do to the user's default QBDT payroll item. A common use for this is for non-project time. In the below example, user's have Project Time as their profile QBDT Payroll Item. However, if a user selects a non-project time code, say CPE/Training, I want those entries to go to the "Admin Time" QBDT payroll item. The maps would look like this
Import Data from QuickBooks
- Import invoices from QuickBooks into Project Insight as new invoices.
- Import vendor bills/credits, checks, credit card charges and journal entries from QuickBooks as new expense entries
TIP: Currently, you must choose to either import or export invoice records and expense entries; you can't do both
ANOTHER TIP: Time entries can only be exported from PI into QBDT
General settings govern what to do with certain data getting passed through the integration. These are
- The PI user account to use for the integration. The best practice here is to set up a user just for this function. Doing so helps you distinguish data created by a real person and data created by the integration. In the screen below, a user named "QBDT Integration" was set up in the User Admin of PI
- Create new companies in Project Insight from customers in QuickBooks when a match can not be found. This is a QBDT to PI invoice import option and does not need to be checked if you are exporting PI invoice records to QBDT
- Create new customers in QuickBooks from companies in Project Insight when a match can not be found. This is a PI to QBDT export option
TIP: Before selecting this option, decide if QBDT is the system of record for companies. If it is, do not check this box.
ANOTHER TIP: Company names will be truncated to 41 characters which may result in duplication errors from QuickBooks.
- Create new jobs in QuickBooks from projects in Project Insight when a match can not be found. This is a PI to QBDT export option
TIP: this is common if you are using customer/jobs in QBDT and reduces the data entry needed by your QBDT accountants
ANOTHER TIP: Project names will be truncated to 41 characters which may result in duplication errors from QuickBooks.
- Use the names when trying to match up Project Insight projects to QuickBooks jobs. This is a common look up technique for QBDT and should be checked
- Use the names when trying to match up Project Insight companies to QuickBooks customers. This is a common look up technique for QBDT and should be checked
- Specify how to match up QB jobs to PI projects:
- Job Custom Field Name - enter in the customer custom field you added to your QBDT to hold the PI project property. Having this field lets you match QBDT jobs to PI projects. This is auto set for new invoices, and can also be manually entered in QBDT to hookup in-flight projects
- Project Property - select from the drop down
TIP: This is almost always "Number" which is the PI auto assigned project number
Advanced settings allow you to set some more uncommon data handling options in your QBDT integration.
- Create new Project Insight projects from jobs in QuickBooks when a match can not be found. This super rare import option creates new PI projects in PI if the integration cannot find a corresponding project to a QBDT job
- Do not lock invoice records when they are exported. When an invoice record is exported to QBDT, PI locks that invoice. Checking this option leaves the PI invoice record unlocked. It is recommended that you leave this unchecked as there is a temporary unlock option in the Invoice Record Settings admin.
- QuickBooks Invoice Custom Field Name for Project Name. This setting is handy, especially if you are invoicing in QBDT at the custom level (not using jobs). It allows you map the PI project name to a QBDT custom field that is on your QBDT invoice. It helps your customer and your QBDT reporting
- QuickBooks Invoice Custom Field Name for Proposal Number. If you are using the PI proposals feature, this setting is handy for the same reason as option 3 above. It allows you map the PI proposal number to a QBDT custom field that is on your QBDT invoice. It helps your customer and your QBDT reporting
- Customers in QuickBooks have 2nd-level customers (subsidiaries). Jobs are then found nested at the 3rd level. This setting lets the integration know that QBDT jobs are like sub-jobs. this happens when there is a mega-corp at the top with SBUs (subsidiaries), and those SBUs have the jobs. If you have this going on, that's awesome, and you will also need to set up a company custom field that is a Company Selector, then select that company custom field in the "PI Parent Company Property" drop down
- Expense Markup Percent: N.n%. If you mark up expenses you charge your customer, this is a setting you want to use. The percent markup for is imported expense amounts. When importing expenses from QBDT the billable cost will be adjusted as follows: If the markup percent is set to 0 (zero) then no price adjustment will be applied, positive values will be a markup and negative values will be a markdown (discount).
TIP: This setting only for importing expenses
- Colon Character Replacement. Since QBDT uses colons in their syntax for identifying information, if your data normally contains colons, you'll set a replacement value here. The default is a dash, but you can make it something else (you data may also contain dashes and if it does, that may be really confusing)
TIP: You cannot use a pipe | either, since the QBDT syntax also uses pipes
Date & Time Integration Last Completed
This is the date & time you last completed a web connector integration run. It lets the integration know what set of transactions it should look for so it doesn't create duplicates
Running the QuickBooks Web Connector
Please see Using the QuickBooks Web Connector with Project Insight for how to run the QuickBooks Web Connector
Export to QuickBooks .iif File
Unsupported QuickBooks Fields
- Address Info Tab
- First Name
- Last Name
- Alt. Ph.
- Alt. Contact
- Ship To
- Additional Info Tab – no fields on this tab are supported
- Payment Info Tab – no fields on this tab are supported
- Job Info Tab – no fields on this tab are supported