CRM-11405 Quickbooks IIF export

Current state is that the export works in the sense that it produces a file formatted in a way Quickbooks will accept, but the sample data (which was removed in the latest civicrm_generated?) doesn't have valid transactions.

I don't think there is a UI yet that allows you to test it, but you can create a menu xml mapping to CRM_Core_BAO_Batch::exportFinancialBatch to call it and it currently has hardcoded settings (batch id list and output format).

see also http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-%C2%A0Overviewofimplementation

Could you get Edsel to work on this once he has an account in JIRA.

recheck export after 11640 resolved for relevant fields

Attaching a simple .iif file

Here is a Portable version of a QuickBooks 2011 Canadian Edition file.

Dave, I'm having an issue with getting the Income Account for Member Dues to match up during import to QB. Though the Account Name and Account # appear to me to match, it is creating a new bank account instead called Member Dues with no Account Number. Could you assist?

A space at the beginning of the first line is no longer there, so we are getting farther in terms of getting the iif file into QB.

An error is now generated saying that the Account Type cannot be changed on line 2. This line describes the Deposit account as an AR type rather than a BANK type.
We will need the following mapping of accounts to account types in QB:
AP - accounts payable
AR - accounts receivable
BANK - checking or savings accounts or payment processors
COGS - Cost of Sales
INC - Income
EXP - Expense
? - Inventory (I'm going to export my Chart of Accounts to determine this later today).

More details to come.

dgg, please review whether you agree with approach at http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-++Batches#CiviAccountsSpecifications-Batches-.iifAccountType

I'm going to ping Dave D about this too. Thanks.

The QB account type was being stored in the 'grouping' field for the option value for the financial account type, but we only did that because there was nowhere else for it. Don't see a problem with having a specific spot for it, just not sure I understand the reasoning mentioned on the wiki about it not being available at export time? If the reason is that the mapping is better done at the individual financial account level rather than the financial account type level then that makes sense.

I think it is reasonable to put it in the grouping field of the financial_account_type option value lookup, though that is not actually the intended semantics for that field.

Doing that would eliminate needing to add a field to the schema and all the attendant changes on that. We would just need to add OCASET (Other Current Asset) for Premiums Inventory to the list of Account Types. While this changes the lookup from a traditional set of account types a bit towards those supported by QB, that's probably a good compromise at this point. dgg?

DD, dgg and I have agreed to put a text field in the civicrm_financial_account table since we need to know if an ASSET account is BANK or AR.

I am trying to test the import again and running into slightly different problems with the file. Removing the initial space on the first line, and changing AR to BANK for the 2nd line seems to get past that error. However, I'm now getting
Error on Line 3: Imported account has a duplicate account number.

Then Your data has NOT been imported successfully.

FYI, I've attached the exported ChartOfAccounts.iff from QB.

Modifying the .iif file so that the account names were the same as those exported from QB instead of displayed in its interface solves the import problem. The transactions for a few memberships and event fees look good in QB.

All that is required to close this issue is to make changes that dgg will document to output the correct QB Account type in the .IIF file.

financial_account.account_type_code now contains the needed string values. Just need to replace $dao->to_qb_account_type with $dao->to_account_type_code AND $dao->from_qb_account_type with $dao->from_account_type_code in ExportFormat/IIF.php.

We're not handling the Pay Later -> Completed case properly on this export. To recreate:

• New contribution - Financial Type = Donation, Pending status
• Edit the contribution to reflect check received - change status to Completed, Paid By Check, and add check number

This gives us 2 financial_trxn rows and 1 financial_item row. First financial_trxn has to_account = Accounts Receivable (AR). Second has from_account = Accts Receivable and to_account = Bank Deposit Account (BANK)

The IIF export has picked up the 2nd financial trxn but not the first, AND I think it's missing the financial_item since I don't see any Income (INC) account. (example attached)

I'm also getting these notices when I export this contribution. It's hitting code where financial_trxn record has FROM account defined (IIF.php starting line 229):

— Notices ----
Notice: Undefined property: CRM_Core_DAO::$total_amount in CRM_Financial_BAO_ExportFormat_IIF->makeIIF() (line 234 of /Users/dgg/svn/crm_v4.3/CRM/Financial/BAO/ExportFormat/IIF.php).
Notice: Undefined variable: itemDAO in CRM_Financial_BAO_ExportFormat_IIF->makeIIF() (line 236 of /Users/dgg/svn/crm_v4.3/CRM/Financial/BAO/ExportFormat/IIF.php).
Notice: Trying to get property of non-object in CRM_Financial_BAO_ExportFormat_IIF->makeIIF() (line 236 of /Users/dgg/svn/crm_v4.3/CRM/Financial/BAO/ExportFormat/IIF.php).
Notice: Undefined variable: itemDAO in CRM_Financial_BAO_ExportFormat_IIF->makeIIF() (line 237 of /Users/dgg/svn/crm_v4.3/CRM/Financial/BAO/ExportFormat/IIF.php).
Notice: Trying to get property of non-object in CRM_Financial_BAO_ExportFormat_IIF->makeIIF() (line 237 of /Users/dgg/svn/crm_v4.3/CRM/Financial/BAO/ExportFormat/IIF.php).
Notice: Undefined variable: itemDAO in CRM_Financial_BAO_ExportFormat_IIF->makeIIF() (line 238 of /Users/dgg/svn/crm_v4.3/CRM/Financial/BAO/ExportFormat/IIF.php).
Notice: Trying to get property of non-object in CRM_Financial_BAO_ExportFormat_IIF->makeIIF() (line 238 of /Users/dgg/svn/crm_v4.3/CRM/Financial/BAO/ExportFormat/IIF.php).
Notice: Undefined property: CRM_Core_DAO::$total_amount in CRM_Financial_BAO_ExportFormat_IIF->makeIIF() (line 234 of /Users/dgg/svn/crm_v4.3/CRM/Financial/BAO/ExportFormat/IIF.php).

Financial_Transactions_6_20130115123059.iif is the export for above bug report.

I tested w/ pay later - pending and a pay later - completed, as well as a regular online donation. I see all the expected items and accounts. Don't have QB set-up to actually import the file and verify results.

The .csv files no longer have data in the columns for Amount,"Credit Account","Credit Account Name","Credit Account Type","Item Description" (see Financial_Transactions_2_20130117121452.csv).

The .iif files should have the same money format for the split line amounts as is used for the transaction line amounts, ie 3.00 rather than 3 (see Financial_Transactions_1_20130117113521.iif).

Dave, the .iif files look good now and import properly into QB, apart from missing the decimal values on the split lines.

Amount formatting is fixed and I was not able to replicate csv issue, transferring to Joe for QA

Formatting now good. Csv issue reported at http://issues.civicrm.org/jira/browse/CRM-11467?focusedCommentId=47981&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-47981