You can use Zed Axis to batch update, edit and append to transactions in QuickBooks. It is possible to export transactions to Excel, modify those transactions using the powerful editing features in Excel like search and replace or copy down, and then re-import those updated transactions back into QuickBooks selecting the option to modify the existing transactions. This feature goes beyond what is possible in the client data review feature in QuickBooks by including many more types of transactions and fields that can by updated.
The options available are when importing, (the identifier used for all transactions is the transactions reference number (DocNumber for QuickBooks Online)
- Duplicate - if a match is found Axis will create another transaction with the same reference number.
- Skip - if a match is found Axis will skip that transaction and will continue with the import.
- Modify - If a match is found Axis will modify the transaction and replace the line data wih the data being imported (important read the notes below for more about modifying)
- Append - if a match is found Axis will append the imported data to the existing transaction (ie retain existing rows) (QuickBooks Desktop only)
Watch the demonstration of this process below
Please note the following when using Zed Axis to modify transactions:
- We recommend that you backup your company data file prior to doing a large batch modify as it cannot be undone.
- Modifying an existing transaction will undo any linking that transaction has with other transactions, so for example payments will be un-applied and links to sales order, estimates will change.
- It is very important that you map the transaction reference number as that is how Zed Axis identifies the transaction to modify, in some instances there may be duplicate reference numbers in which case Axis will modify the first transaction that it finds with the matching reference number.
- When modifying you need to map all the line fields that you wish to appear, as when modifying the transaction Axis first deletes the line content and replaces it with what is being imported.