How to handle PO Importer Validation Errors

The primary items that will create validation errors are Vendor Names, Product IDs and Warehouse values.  When these values are missing in Acctivate, but are in your import file, a validation error will occur.  You will either need to correct the data in your spreadsheet or add the data in Acctivate.

If you are importing from one of our Memorized Reports and you are receiving errors on every line this may be because you exported out of QuickBooks® without blank columns.  Open your spreadsheet and verify that columns A, D, F, H, J, L, N, P, R, T, V and X are all blank (if you used the report with Sites then column Z should also be blank). If these columns are not blank it can be corrected in a couple of different ways. If you have made modifications to your data and don’t want to redo them then you can simply insert blank columns. If you have no modifications, or just a few, then you may want to re-export the data from QuickBooks®. When you re-export make sure to click the Advanced button on the Send Report to Excel screen and make sure the “Space between columns” is checked.

ACCTivate!, QuickBooks

How does the Acctivate PO Importer work?

For QuickBooks® Users

When you install the PO Importer our two QuickBooks® memorized reports will be placed on your desktop.  You will need to import the report that works best for you.  You can do this by clicking on the Reports Menu and then clicking the Memorized Reports > Memorized Reports List. In the Memorized Reports window, you can click on the Memorized Reports button in the bottom left of the window and click on Import Template.  Browse to your desktop and select the report that you need, the one with or the one without sites.

Once imported you can find the report in the Memorized Report section of QuickBooks®, the name of the report is “Open Purchase Order Detail” if you don’t use sites, or “Open Purchase Order Detail with Site” if you are using sites.  Run the report and Export the data using the Excel button at the top of the report window.

Once the data has been exported to Excel there may be some modifications needed.  For example, you may be using different names for your warehouses during the transition.  In this case you will need to update your Site Names so they match the Acctivate Warehouse IDs.

The program will require you to have a valid Product ID in the import file.  The application looks for the Item in both the Product ID and the Alt Product ID.

If you only have a single Warehouse you don’t need to specify the Warehouse ID, the application will simply use the Purchasing Default Ship To Warehouse identified in Acctivate.  If you have multiple Warehouses you need to verify that the Warehouse Names in the spreadsheet match the Warehouse IDs as they appear in Acctivate.

There are other fields that the PO Importer will use the Default Value in Acctivate for, if you have them set.  Those fields are:

  • Vendor Terms – The Default Terms for the Vendor will be used if setup and it will use the Purchasing Default Terms when isn’t a value at the Vendor level.
  • FOB – The Purchasing Default FOB is used when set and if no default is setup the FOB on the PO will be left blank.
  • Ship Via – the Purchasing Default Ship Via is used and if no default is setup the Ship Via on the PO will be left blank.

Once your spreadsheet is ready to go all you have to do is launch the PO Importer, browse to the spreadsheet and click the Validate button.  Once your data is validated click the Import button and you’re done.  The Importer can import 100+ POs in seconds after the validation is complete.  So if you click the Import button and the window pops up right away indicating the POs have been created don’t worry, it’s just that fast.

Importing Data other than QuickBooks® Data

If you’re not using our QB Import option then you are going to need to create your own import file. Your spreadsheet, at minimum, will need to have the following columns: Vendor Name, PO Date, Product ID, Description, Qty and Unit Cost.  Additional fields you might like to specify if you don’t want to use system defaults are: Warehouse ID, PO Number, Terms, FOB, and Ship Via.

If you do not specify a PO Number then the PO Importer will use the numbering scheme specified in Acctivate, including any Prefix or Suffix you have setup.

We can setup the PO Importer to use any of the system defaults that are found in Acctivate, as well as configure it to define values that may not have defaults in Acctivate.

We have many customers using the PO Importer to create all of their POs and each import file is different from the next.  One of the more complex setups we have worked with was used to import POs for apparel orders.  Contact us today to discuss how our PO Importer can help you import your POs into Acctivate today.

ACCTivate!, Custom Reporting

New Customers by Month

This Acctivate Report is a rather simple one but can be quite useful. Do you want to send out monthly communications to your new customers?  Do you want to send a list of new customers to your sales staff with order totals so they can reach out and contact these new customers?  Or just report on new monthly sales?

Our Acctivate New Customers by Month Report looks for the date of a customer’s first invoice (not the date they were created but the first Invoice date) and if the date falls within the specified date range the customer is listed.  The original report was designed to show the Territory (or Sales Rep), customer name, phone number and amount of the invoice.  The amount displayed is the Invoice subtotal minus shipping.

The report is grouped by Month and Territory with a total new customer count and total sales amount at each level.

This report could be easily modified to add more contact info or be grouped differently to be more useful to your organization.  Maybe you need a report grouped by Rep then Date with page brakes after each Rep so you can easily distribute the report to supervisors.  Or maybe you want to take it one step further and add sales detail, or better yet add the ability to show or hide detail each time you run the report.

The foundation of this report is a simple one but is one that could be very helpful for your organization.  Please reach out to use if you are interested in this type of report, we’d be more than happy to help you get the data you need out of Acctivate.




ACCTivate!, Custom Reporting

Multi Year Sales History/Forecasting Report

Knowing how your company is doing year over year is something every company wants to know.  You can look at a comparative P&L for this info but what if you want to know how sales for a specific customer are going, or maybe for a product?  Our Acctivate Multi Year Sales History/Forecasting Report was originally designed to group the results by Customer by Product.  Since the original design we have created a version that first groups by the Acctivate Product Class.  We could just as easily do this grouping by Sales Category.

This report shows sales history broken out by month for an entire year and when run for a range of years there is a row for each year, for easy month to month comparison.  Because the report is grouped by product we are able to show the sales amount total and the quantity total, which can be very helpful in giving you a quick way to identify possible trends and seasonality of your products.

The Forecast numbers are shown for future months and are based on Quotes that have been entered into the system.  This could be adjusted to use Booked Orders instead of Quotes if that makes sense for your organization.

As with any of our reports, this is a starting point and can be modified to meet your specific needs.  Just reach out to use and let us know how we can help.


ACCTivate!, Custom Reporting, Uncategorized

Inventory Detail Journal by Account

This report can be used for reconciling any of the GL accounts Acctivate works with but it was designed to specifically reconcile the Accrued Purchase Receipts account in QuickBooks.  This can often be a very challenging task and we hope this report will help in your efforts. This report shows both the ins and outs into an account where the standard Acctivate Inventory Detail Journal Report does not.  Knowing both what came into an account and what went out is critical in your reconciling process and this report helps you do just that.

If you are having a difficult time reconciling your Accrued Purchase Receipts account? Contact us to purchase this report and make things a little easier during that reconciliation process.



ACCTivate!, Custom Reporting

Daily Open Orders Report

Our Acctivate Daily Open Orders Report displays open orders grouped by the due date and displays the daily total due in the header section of each days grouping.  It also includes a grand total for all open orders at the end of the report. This report can be used to make sure you don’t miss any orders or even help you plan your day.

The fields on this report are Order Number, Customer Name, Order Date, Due Date, Order Total and Workflow Status.  It is not currently setup to show order detail but that is something can be added for those needing something more.


ACCTivate!, Custom Reporting

Backorder/Fulfillment Report

This Acctivate Backorder/Fulfillment Report is used to determine the quantity needed to fulfill all Open Orders. This report is grouped by Product and shows both a summary line and order details for each product. The summary line shows Product ID, Product Description, minimum SO age date for all open Orders, Open Order Average Price, Quantity needed for Orders, Quantity on Open Purchase Orders, Quantity on Hand, Quantity in Test Warehouse and Quantity needed to Fulfill Open Orders. The details displayed are the Order Number, Order Date, Promise Date, Price, Quantity Ordered and Quantity Needed. Since it may not always be necessary to see the detail of each order this report has the ability to show and hide the details.




Concur, Custom Integration, How Did We Help, QuickBooks, QuickBooks Automation

Automate Concur Invoicing and Concur Expense Integration into QuickBooks®

The Problem

We recently had a customer come to us that was beginning to utilize Concur Invoicing and Concur Expense for all 50 of their companies and wanted the import files generated by Concur to automatically create the appropriate transactions in QuickBooks®. An import file was being generated each day when new Invoices were entered into the Concur system. That meant if one invoice was entered each day for each company file there could be a total of 250 files each week that would then need to be imported into QuickBooks®; not to mention the files generated to create the Bill Payments in QuickBooks® as well. All of these files needed to be downloaded via FTP, decrypted and then manually imported into the correct QuickBooks® file. This was a very time consuming process the customer did not want to go through.

The Solution

We took this challenge head on, working with the team at Concur to develop a solution that would streamline and automate the entire process. Our solution has a simple setup process after which all the user has to do is monitor an error folder in case any issues arise. Our custom solution downloads the import files from the FTP site, decrypts the files, archives a copy of the file, opens the correct QuickBooks® file, creates the Bills and Bill Payments, closes QuickBooks® and moves to the next set of files. If another company is added then a quick 30 second setup is needed and the next time the program runs the new company falls right in line and everything gets done for the new file with no additional work needed.

This solution is a huge time saver for the customer and it is a solution that could be easily adapted to work for other Concur users. We were also able to get the Concur Expense product to import as well.

Please contact us if you would like to automate your Concur importing too!


Multiple Ship To Address Importer Guide

This utility is used to Add and Update Ship To Addresses for existing customers in QuickBooks®

See our Multiple Ship To Address Importer application page for pricing and purchase information.

This product has been designed to work with a currently open QuickBooks® file so prior to launching this application make sure you have your QuickBooks® file open.

The first time the program is run you will be prompted for a Product Key. You must enter a valid Product Key for the application to continue. Your Product Key will be provided when you purchase the product. Our Product Keys are generated for the computer the application is being run on. If you are planning on running the application on multiple computers you will be provided a Product Key for each system. Make sure that you are entering the correct Product Key for the system you are on.

Once you have successfully entered your Product Key you will see the main screen of the program. The Company Name of the Open QuickBooks® file will be displayed at the top of the screen, helping to ensure you are logged into the correct file, for those that may have more than one.

The first step in running the application is to select the type of import you want to do.

There are 2 options:

  1. Add new / Keep existing – Addresses in your spreadsheet will be compared to any current addresses in the file, if the address in the spreadsheet is new it will be added and if the address name already exists the address in the spreadsheet will be ignored.
  2. Add new / Update existing – Addresses in your spreadsheet will be compared to any current addresses in the file, if the address in the spreadsheet is new it will be added, if the address name already exists the address will be updated with the information in the spreadsheet.


Type of Import

Once you have selected your option the Select Spreadsheet button will be activated and you can now select the spreadsheet you wish to import.

After selecting the spreadsheet you will be able to select the sheet within the Excel workbook that contains your address information.

MultiShipToSheetSelectWith the proper sheet selected click the Validate Spreadsheet button. If there are problems with the data in the spreadsheet, for example the Customer does not exist in QuickBooks® or you have duplicate address names in the spreadsheet, a list of errors will appear in the lower part of the application window.

MultiShipToErrorAt this point you can print the list of errors and warnings, correct the issues in the spreadsheet and re-select the spreadsheet; or you can import what passed validation and then take care of the errors.

Once the import is complete you will receive a message indicating the application is done, once you click the okay button the application will reset so you can do another file or you can exit the program.

Custom Advanced Reporting (CAR), Custom Reporting, QQube, QuickBooks

Custom Reporting Series at Intuitive Accountant – More About Reporting Tools

Donovan has posted his second article at the Intuitive Accountant, “QuickBooks Custom Reporting – A Series:  More about Reporting Tools.”  This article covers Xpanded Reports, QQube, QODBC and the Custom Advanced Reporting (CAR) tool.  To learn more about these tools have a look at the article, and let us know what you think.  Make sure that you’re watching for follow up articles in the series.