Skip to end of metadata
Go to start of metadata
Batches AREN'T generic

 We have tried to use batch to group contributions. We have found two problems

  1. the existing batch UI is expecting the batches to contain financial_trxn records and only financial_trxn.
  2. the existing batch UI is ignoring the batch type and expects every batch to be an accounting batch (containing financial_trxn)
  3. the UI/code has a lot of hardcoded assumptions about the status_id and other "generic" data

So if you want to use batch for something else than financial trxn, you will need to invest time to

  1. refactor the existing code to make accounting batches specific (eg. right now the batches don't set a batch type), so the UI only displays them and ignore other batches
  2.  write a separate UI to manage your new type of batch

Moreover, on at least one case (sepa batches) we needed extra columns. For these reasons, we decided to create a specific entity instead of trying to re-use the batch system. Before investing too much time, might be worthwhile checking if it's not a more productive approach if you need to batch something else than financial transactions

 

 

Batches

General Concepts

Batches will be utilized for grouping a variety of items but initially will include two types

Financial transactions:  collecting financial transactions into meaningful groups for export and reconciliation with external financial systems.

Future Phase: Receipts: collecting transactions based on their contribution type and receipt existence

Financial Batches:

Batching is affected by the accounting set up. Two options:

The purchase and payment of an order are in separate transactions (AR account)

The purchase and payment are in one transaction (no AR account)

The database will be set up for AR. A future phase will implement cash basis accounting (ie, no AR transactions).

Batch types

Automatic

All real time transactions that are processed through a payment processor need to be assigned to batches automatically.  Automatic batches are created according to a specific setup for each payment processor and payment vehicle.

Automatic batches are generally created at a specific time each day according to the rules of the payment processor. An automatic batch is considered closed as soon as a new batch of the same type is created.  Automated batches are also marked closed when they are export.

Phase 1 will not support Automatic batches.

Manual

Users need to be able to create batches to meet their the specific needs for collecting transactions into logical groups. Batches are created in advance and users select a batch to contain transactions as they are entered into the system. Batches are considered open when they are created and remain open until they are closed. Closed batches can be re-opened or exported. While a batch is closed no new transaction can be added to the batch or removed from it. Once a batch is exported it cannot be reopened and thus cannot have transactions in it added or removed.

Use Cases

Automatic batch setup

User’s define the parameters that will be used to automatically create the batches as necessary.

User either selects an automated batch configuration to edit, or adds a new one through the automated batch list page.

User can enable and disable batch configurations through the automated batch list page.

The batch setup screen presents the following information:

List of enabled payment processors

List of enabled payment methods

A field for entering the daily start time of new batches

A batch description field

The name format for batch names. The batch name is constructed at “user batch name”+’YYYY-MM-DD’ e.g AUTHNET-CC-2011-07-15

Manual Batch Creation

User creates a new batch for holding financial transactions.

Create Menu items for

  1. Contributions > Financial Transactions Batches > New Batch
  2. Contributions > Financial Transactions Batches > Open Batches
  3. Contributions > Financial Transactions Batches > Closed Batches
  4. Contributions > Financial Transactions Batches > Exported Batches

Users add a new batch by defining the following: batch name, optional payment method, description, optional manual number of transactions, optional manual batch total. The batch created date is assigned automatically. (Implement this using existing civicrm_batch for fields common to other sorts of batches and civicrm_financial_batch for fields specific to financial batches.)

Users can edit names and descriptions of batches, manual number of transactions, and manual batch total, and set the batch to closed. Closing a batch requires 1) that a non-null manual number of transactions match the number that have been assigned to the batch and 2) that a non-null batch total match the total value of the transactions assigned to the batch. A batch exported date is assigned automatically. Administrators may have permission to re-open a batch that has not been exported, in which case the batch close date is set to null. Once the the batch is exported it cannot be changed.

If a batch has not had any transactions assigned, it may be deleted.

Manual Batch Assignment

User searches for transactions that have not been assigned to a batch (similar to find contributions). Search can include payment method, transaction type, date range

The search result list provides a task option for “add to batch” this presents the user with a list of currently open batches

Saving the batch associates the selected transactions with the specified batch.

Batch export

Users need to select the batches that they want to export to the financial system.  The batch selection screen displays the list of non-exported batches, the batch name, the transaction type of the batch, the batch create date, the batch close date and the total transaction amount of the batch.

The user selects one or more batches for export and assigns an filename to contain the export data. The user click the “create export”  button to initiate the batch creation. A validation step ensures that all selected batches are either closed or validate for closure before the export begins. Once a batch is exported it is automatically closed and cannot be reopened or altered.

Once the export file is created the user selects it via the download link on a page of previously generated exports.

The export process summarizes the transactions by financial account.  The output includes an account name, number, debit, and credit field. Other outputs types can be generated in the future.

Batch reporting

Users can report on open and closed batches. The detail reporting lists individual transactions contained in a batch.

Receipts Batches

Receipts batches can either include all transaction of certain contribution types or only transactions of a certain transaction types without receipts. 

Receipt batches will be manual, no automatically created receipt batches

Receipt batches do not have the option of open/closed, all are considered closed once created, they can be deleted based on permission level

Receipt batches do not have names

The receipt reports will be different for different organizations, but one receipt report will be generated for phase 1

Use Cases

Batch creation /assignment:

Users selects the contribution type(s), start date, end date, selects either ALL transactions or only transactions without a receipt.  User then “Creates receipt”

The receipt batch report is displayed on screen and can be downloaded

Batch Reporting

Users can create reports with one or more receipt batches based and can create report based on  individual contact or contacts  with a particular tag, group, custom data field, etc.  The detail report(s) would mirror the report from the bath creation/assignment.

Batch deleting

Users with proper permissions can delete batches.

Users would search for a batches for a contact and date range.  Preview the batch delete the batch.

--------

From previous version of page:

Bank deposits encompassing multiple contributions need to be added in a way that supports rapid bulk data entry of cheques for new and existing contacts. See [|]Batches for Data Entry and Financial Auditing|confluence/display/CRM/Finance+and+Accounting#FinanceandAccounting-BatchesforDataEntryandFinancialAuditing||||||||\

Technical Specification

Manual Batches

Schema

NB: Reuse of civicrm_batch has obsoleted this schema spec.

Create a new table civicrm_financial_batch with the following fields:

  • id INT
  • batch_type_id INT NOT NULL fk to Batch Type options in civicrm_option_values
  • contact_id INT fk NOT NULL to civicrm_contact.id of batch creator
  • description VARCHAR (255) NOT NULL
  • payment_instrument_id INT NULL fk to Payment Instrument options in civicrm_option_values
  • manual_number_trans INT NULL
  • manual_total DECIMAL (20,2) NULL
  • open_date DATETIME NOT NULL DEFAULT NOW()
  • close_date DATETIME NULL
  • export_date DATETIME NULL
  • batch_status_id INT NOT NULL fk to Batch Status options in civicrm_option_values

Create new record in civicrm_option_group for batch types and batch statuses

Create new records in civicrm_option_value for Manual batch, Automatic batch; Open, Closed, Reopened, Exported

Create new permissions in Drupal (and if supported, in Joomla and WordPress) for:

  • Create manual batch
  • Edit own manual batches
  • Edit all manual batches
  • View own manual batches
  • View all manual batches
  • Delete own manual batches
  • Delete all manual batches

Create new Menu entries under Contributions, after Manage Price Sets:

  • Insert a menu spacer/divider
  • New Batch
  • Open Batches
  • Closed Batches
  • Exported Batches

Create a new reserved Activity Type of "Export of Financial Transactions Batch".

Forms / Pages
New Batch

Buttons at top and bottom: Save, Save and New, Cancel

Fields as defined in Edit Batch below.

On Save, leaves user on the Batch Transactions form for that batch. On Save and New, leaves user on blank New Batch form. On Cancel, redirects to CiviCRM Home.

Batch Transactions

Across the top of the form, list the batch information as follows:

  • Created by: display_name of contact_id
  • Description: description
  • Payment Instrument: label of payment_instrument_id
  • Type: label of batch_type_id
  • Entered transactions: manual_number_trans
  • Assigned transactions: number of assigned transactions
  • Entered total: manual_total
  • Assigned total: total of total_amount of assigned transactions
  • Opened: open_date

Below that, provide a fieldset, initially closed, of the financial_trxn's assigned to the batch. Do not display the search criteria. Enable a single action of "Remove from Batch" when one or more records have been selected. The results lists the currently assigned financial_trxn records as per the entity_batch records pointing to this batch, using the following columns where the contact and contribution information is looked up for each financial_trxn (use entity_financial_trxn where entity_table='civicrm_contribution' and financial_trxn_id = financial_trxn.id):

  • checkbox to select financial_trxn for removal
  • ID - the financial_trxn.id
  • symbol for contact type
  • Contact Name
  • Amount
  • Type (to be confirmed)
  • Source (to be confirmed)
  • Received
  • View link - for financial_item (or its encompassing item, eg view a contribution if the item is a line item in contribution)
  • Remove link - after confirmation dialog, removes the transaction from the batch and returns to this page

Below that, provide a fieldset that cannot be closed that replicates the Find Contributions form, modified to require that Contribution Status be Completed, and only displaying transactions that have not been assigned to batches. Use force=1 so that Edit Search Criteria comes up closed when there are financial transactions available to be selected and assigned. There should be no Print button. It should include the following columns:

  • checkbox to select contribution for assignment
  • ID - the financial_item.id (Q: should we show the entity_id too?)
  • symbol for contact type
  • Contact Name
  • Amount
  • Received
  • Type (to be confirmed)
  • Source (to be confirmed)
  • View link - for financial_item (or its encompassing item, eg view a contribution if the item is a line item in contribution)
  • Assign link - after confirmation dialog, assign the transaction to the batch and returns to this page

Once at least one contribution is selected, the - actions - select field changes to "Assign to Batch". Clicking Go brings up a confirmation dialog, and then redirects back to this page, refreshed to include the new transactions in the number and total.

NB: Use a transaction around the assignment of one or more financial_items to a batch to prevent collisions. Inside the transaction, check that the status of the transaction is completed and that it is not assigned to a batch already, rolling back on errors. Wherever Contribution Status can be modified from Completed to something else, use a transaction around changing a transaction status, checking inside the transaction that the financial_item is not assigned to batch, rolling back if it is.

Edit Batch

Provide a form that allows user to edit the following fields of manual batches that are in the Open but not Closed or Exported status:

  • Created by: display_name of contact_id (only available if user has Edit All Batches permission)
  • Name
  • Description: description
  • Payment Instrument: label of payment_instrument_id
  • Type: label of batch_type_id (view only)
  • Entered transactions: manual_number_trans
  • Assigned transactions: number of assigned transactions (view only)
  • Entered total: manual_total 
  • Assigned total: total of total_amount of assigned transactions (view only)
  • Opened: created_date (view only)
  • Status: batch status (validation for switching from open to closed same as closing batch; UI does not allow changing status to Exported)
Open Batches

Display a search results profile with the following fields with a possible actions of 'Close Batches', 'Export Batches' and 'Delete Batches' available when batches are selected:

  • Checkbox to select batch
  • Created by: display_name of contact_id
  • Name: name
  • Description: description
  • Payment Instrument: label of payment_instrument_id
  • Type: label of batch_type_id
  • Entered transactions: manual_number_trans
  • Assigned transactions: number of assigned transactions
  • Entered total: manual_total 
  • Assigned total: total of total_amount of assigned transactions
  • Opened: opened_date 
  • Transactions link - opens view of assigned transactions with option to assign more
  • Edit link - opens form allowing batch fields to be edited (includes option to delete)
  • Close link - after confirmation dialog, closes batch and redirects to Open Batches
  • Export link - after confirmation dialog, closes and exports batch and redirects to Open Batches
  • Delete link - after confirmation dialog, deletes batch leaving its transactions unassigned and redirects to Open Batches

Search criteria should be all fields displayed in results, excepted: description, link. In addition, provide a checkbox for Entered transactions <> Assigned Transactions, and for Entered total <> Assigned total. (NB: these should not checked by default).

Closed Batches

Display a search results profile with the following fields with possible actions of 'Reopen Batches', 'Export Batches' and 'Delete Batches' available when batches are selected for batches that are closed but not exported:

  • Checkbox to select batch
  • Created by: display_name of contact_id
  • Description: description
  • Payment Instrument: label of payment_instrument_id
  • Type: label of batch_type_id
  • Entered transactions: manual_number_trans
  • Assigned transactions: number of assigned transactions
  • Entered total: manual_total 
  • Assigned total: total of total_amount of assigned transactions
  • Opened: opened_date
  • Closed: closed_date
  • Transactions link - opens view of assigned transactions
  • Reopen link - after confirmation dialog, changes status to Reopened and redirects to Closed Batches.
  • Export link - after confirmation dialog, does an export of transactions, makes export file available for download, and redirects back to Closed Batches.
  • Delete link - after confirmation dialog, deletes batch leaving its transactions unassigned and redirects to Closed Batches

NB: export of one or more batches involves the following:

  • set batch status to Exported for each batch
  • use an export field mapping saved via an administrative form (see the Exporting a Batch section below) to create a .csv file of the transactions in all of the batch(es)
  • save the file to server disk and create an entry for it in civicrm_file
  • create an "Export of Financial Transactions Batch" Activity, attaching the .csv file to it via civicrm_entity_file
  • attach the .csv file to each batch via additional civicrm_entity_file records
Exported Batches

Display a search results profile with the following fields with possible actions of 'Download Batches' and 'Delete Batches' available when batches are selected:

  • Checkbox to select batch
  • Created by: display_name of contact_id
  • Description: description
  • Payment Instrument: label of payment_instrument_id
  • Type: label of batch_type_id
  • Entered transactions: manual_number_trans
  • Assigned transactions: number of assigned transactions
  • Entered total: manual_total 
  • Assigned total: total of total_amount of assigned transactions
  • Opened: opened_date
  • Closed: closed_date
  • Exported: exported_date
  • Transactions link - opens view of assigned transactions
  • Download link - opens file download dialog of file of batched transactions
  • Delete link - after confirmation dialog, deletes the batch. Transactions previously assigned to batch become available for assignment to a different batch.

The Download Batches action downloads a compressed .zip file if two or more batches are selected, else the uncompressed single file.

Exporting a Batch
.csv (comma separated values) Format

CRM_Financial_BAO_ExportFormat::output should be refactored so the actual outputting can be overridden in the IIF and CSV subclasses as the first will output using Smarty and the second using fputcsv. The downloading of the resulting output file should remain in this class. If more than one batch is being exported at the same time, process the output file for each batch, then zip all of the files together and download the resulting compressed file. After any export (ie from either Open Batches or Closed Batches form), redirect the user to Exported Batches form, and display a status message "Batch civicrm_batch.title successfully exported."

Add code in this class before the downloading to create an Activity to which the downloaded file is attached:

  • source_contact_id: current user
  • source_record_id: civicrm_batch.id
  • activity_type_id: lookup in civicrm_option_value table where name == 'Export of Financial Transactions Batch'
  • subject: 'Total [nnn], Count [mmm], Batch [batch name]' where batch name = civicrm_batch.title, nnn = civicrm_batch.total, mmm=civicrm_batch.count

  • activity_date_time: now()
  • details: put label and value and line break for the following fields from civicrm_batch record: title, description, civicrm_contact.display_name for created_id ("Created by"), created_date, civicrm_contact.display_name for modified_id ("Last Modified by"), lookup of label for payment_instrument_id ("Payment Instrument")
  • target_contact_id: current user

Implement the following function. Try to use bulk / array operations where they exist for performance rather than row operations. Also, review the feasibility of refactoring the IIF code so that getting the data for the arrays is done in a common function. It might make sense to create the array needed for the IIF export and then reformat it for CSV by 'flattening' it so that the financial_trxn information is copied into additional fields in the financial_item rows.

/**
* $params - array of civicrm_batch.id for batch or batches to export
*/
CRM_Financial_BAO_ExportFormat_CSV::export( $params ) {

/*
// civicrm_batch is related to the records in the batch via civicrm_entity_batch
retrieve all civicrm_entity_batch records where batch_id same as in $params // unit test should check that all have entity_table='civicrm_financial_trxn'

foreach batch_entity
retrieve the financial_trxn record // preferrably in bulk
foreach financial_trxn retrieve all civicrm_entity_financial_trxn records // preferrably in bulk
where civicrm_entity_financial_trxn.financial_trxn_id == financial_trxn.id && civicrm_entity_financial_trxn.entity_table == 'civicrm_financial_item'
foreach entity_financial_trxn
retrieve the financial_item record // preferrably in bulk
create output array row with fields from table below from financial_trxn, entity_financial_trxn, and financial_item fields // preferrably in bulk
Use fputcsv() to output to a csv format that uses double quotes around all values, uses \n for line endings, and includes column names in the first row (make sure to escape fields).

*/
}
database fieldcolumn name in .csv
civicrm_financial_trxn.trxn_dateTransaction Date
civicrm_financial_account.accounting_code (lookup using civicrm_financial_trxn.to_financial_account_id)Debit Account
civicrm_financial_account.name (lookup using civicrm_financial_trxn.to_financial_account_id)Debit Account Name
civicrm_financial_trxn.total_amountDebit Account Amount (Unsplit)
civicrm_financial_trxn.trxn_idTransaction ID (Unsplit)

civicrm_option_value.label (lookup using civicrm_option_value.value == civicrm_financial_trxn.payment_instrument_id and civicrm_option_value.option_group_id == civicrm_option_group.id and civicrm_option_group.name=='payment_instrument' )

(NB: civicrm_financial_trxn.payment_instrument_id needs to be moved from civicrm_contribution in schema)

Payment Instrument

civicrm_financial_trxn.check_number

(NB: this field needs to be moved from civicrm_contribution in schema)

Check Number

civicrm_contribution.source (lookup using civicrm_entity_financial_trxn where entity_table='civicrm_contribution'

and financial_trxn_id points to current civicrm_financial_trxn record)

Source
civicrm_financial_trxn.currencyCurrency
civicrm_financial_trxn.status_id (lookup string value from option_value)Status
civicrm_entity_financial_trxn.amountAmount
civicrm_financial_account.accounting_code (lookup using civicrm_financial_item.financial_account_idCredit Account
civicrm_financial_account.name (lookup using civicrm_financial_item.financial_account_idCredit Account Name
civicrm_financial_item.descriptionItem Description

 

.iif (Intuit Interchange File) Format

The iif_import_kit.zip file attached to this page contains some documentation on creating .iif files for import into QuickBooks.

A common pattern to avoid partially imported files is to include records at the top to create/update the accounts table in QuickBooks to ensure that the accounts specified later in the file exist, and the import is not partially successful and partially unsuccessful. The .iif file produced by CiviCRM will follow this pattern. One record will be created for each account that is named in the batch.

The main body of the .iif file is a set of double entry bookkeeping transactions. Each includes a !TRNS record and one or more !SPL records. The !TRNS record will be created from entries in the civicrm_financial_trxn table, while the "splits" for those records will be created from the civicrm_financial_item records related to that civicrm_financial_trxn record via civicrm_entity_financial_trxn records.

In pseudocode, this can be implemented as:

  for each civicrm_financial_trxn in batch (search civicrm_entity_batch where entity_table='civicrm_financial_trxn' and batch_id=civicrm_financial_batch.batch_id) {
    output (or store to an array for processing by Smarty) the !TRNS (journal entry) record using the civicrm_financial_trxn.to_financial_account_id
save the civicrm_financial_trxn's payment_instrument and check_number the journal_entry array
if the civicrm_financial_trxn.from_financial_account_id is null {
      for each civicrm_financial_item related to the civicrm_financial_trxn (search civicrm_entity_financial_trxn for entity_table='civicrm_financial_item' and financial_trxn_id= this civicrm_financial_trxn.id) {
        output (or store to an array for processing by Smarty) the !SPL (split) record using the civicrm_financial_item.financial_account_id
          use civicrm_entity_financial_trxn.amount as the split amount
          save civicrm_financial_item.currency and civicrm_financial_item.description to the split array/record
      }
    } else {
      output (or store to an array for processing by Smarty) the !SPL record using the civicrm_financial_trxn.from_financial_account_id
        use civicrm_financial_trxn.total_amount as the split amount, civicrm_financial_trxn.currency
    }
  }
 Overview of .iif implementation
There's comments in the code too, but here's an overview:
CRM_Core_BAO_Batch::exportFinancialBatch() is called (from where?) and passed a set of batch ids.
Using the desired output format, it instantiates a subclass of CRM_Financial_BAO_ExportFormat, e.g. $exporter = CRM_Financial_BAO_ExportFormat_IIF
It then compiles financial data without regard to output format, trying to stay neutral, but passing all data items through $exporter->format() so that data is escaped properly for the desired output format.
The subclass is then responsible, besides implementing format() if necessary, to implement export(), which should take the appropriate subset of financial data and pass it on to the smarty template via self::assign(). Then export() can either:
  • call $this->output() to use the default output, which runs the tpl from getTemplateFileName() (which you can override) and then presents it as a download to the user,
    OR
  • implement output() to do what you want, e.g. for csv I don't think it makes sense to use smarty/tpl, since php has built-in csv output functions and also there will need to be multiple files if we also want to include an accounts/customers list (also see CRM_Export_BAO_Export for a strategy where it's written to a temp db table first)
In addition to being picky about the file formatting, Quickbooks only allows certain combinations of data. Here are some I've come up against:
  1. AR transactions MUST have a customer associated with the TRNS record (the line describing the total amount for the To account). Currently the sample data (and possibly the use cases?) have it modeled to be the org that is using civi. The implementation currently works, but creates a customer record in quickbooks for the org itself, which may not be desirable, because incoming money usually comes from customers (e.g. donors).
  2. We don't distinguish between AR and BANK account types in the civi model. Depending on the existing quickbooks setup at the org this may either create duplicate accounts or cause rejections.
  3. This is just a sample data issue but for AR transactions the to account and from account can't be the same account.
  4. The SPL lines technically don't need to include a customer, but pending resolution of (1) it may or may not be needed/desired.
.iif AccountType

.IIF files need to define the accounts used in the file at the top of the file, and one part of this definition is the Account Type. While they are conceptually closer to CiviCRM's Account Relationship than to CiviCRM's option_group for financial_account_type, it is better to store these strings with the financial account because the financial_type and account relationship are converted into financial_account_id at the time the transaction is recorded, and aren't really available at the time of export. Storing them with the Account is also the same model as QB uses. So a new option_value_group called qb_account_type should be created as follows:

Value

Description
APAccounts payable

AR

Accounts receivable

BANK

Checking or savings

CCARD

Credit card account

COGS

Cost of goods sold

EQUITY

Capital/Equity

EXEXP

Other expense

EXINC

Other income

EXP

Expense

FIXASSET

Fixed asset

INC

Income

LTLIAB

Long term liability

NONPOSTING

Non-posting account

OASSET

Other asset

OCASSET

Other current asset

OCLIAB

Other current liability

This needs to be a new nullable text field in civicrm_financial_account which appears in the UI when viewing and editing financial accounts. It also needs to be added to the tarball and to the upgrade. The default data for accounts in the tarball and on upgrade need to be changed as follows (NB: I've inserted the Value from the table above for qb_account_type_id rather than the ID which will eventually be auto-generated):

ID | name | qb_account_type_id | accounting_code | description

 1 | Donation| INC | 4200 | Default account for donations

 2 | Member Dues | INC | 4400 | Default account for membership sales

 3 | Campaign Contribution | INC | 4100 | Sample account for recording payments to a campaign

 4 | Event Fee | INC | 4300 | Default account for event ticket sales

 5 | Banking Fees | EXP | 5200 | Payment processor fees and manually recorded banking fees

 6 | Deposit Bank Account  | BANK | 1100 | All manually recorded cash and cheques go to this account

 7 | Accounts Receivable | AR | 1200 | Amounts to be received later (eg pay later event revenues)

 8 | Accounts Payable | AP | 2200 | Amounts to be paid out such as grants and refunds

 9 | Premiums | COGS | 5100 | Account to record cost of premiums provided to payors

 10 | Premiums inventory | OCASSET | 1375 | Account representing value of premiums inventory |NULL| 0 | 0 | 0 |NULL| 0 | 1 | 0

 11 | Discounts | INC | 4900 | Contra-revenue account for amounts discounted from sales |NULL| 0 | 0 | 0 |NULL| 0 | 1 | 0

 12 | Payment Processor Account  | BANK | 1150 | Account to record payments into a payment processor merchant account

Note: the Refunds account should be removed from the Default set of accounts


 

 

 

Labels
  • None

Creative Commons License
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-Share Alike 3.0 United States Licence.