Matching Payments when Importing Revenue

From DARSwiki
Revision as of 14:24, 22 February 2016 by Spet3064 (Talk | contribs) (Method)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search


This article will be updated on a regular basis. Updates may be required in response to updates to DARS or changes to business processes or errors.

Refer to DARSWiki Conventions for information on icons and other conventions which may apply to this article.

Ensure you are familiar with the Data Protection laws before adding data to records in DARS. Refer to DARSWiki Further Help for further information and relevant links.

Please think twice before printing this article. If a printed copy is necessary, ensure it is printed double-sided and always recycle old versions.


This article serves as a reference guide to the Revenue Import functionality of DARS.

In this article you will learn how to link imported revenue payments to their recurring gift automatically - without having to manually select the recurring gift to apply the payment to in the Revenue Batch.

You will require the Revenue Administrator and Local Site Administrator role(s) in order to access and use the functionality described in this user guide. If you are unsure of your role and access rights to the DARS system, please contact the DARS Helpdesk (see DARSWiki Further Help for contact details).

This article presumes some prior knowledge on importing revenue records into DARS. Further information can be found in the Data Import article.

About Applying Imported Payments

Payments can be into DARS using the Data Import process. This results in a Revenue Batch containing all the information relating to the payment, for example the Amount, Date, Constituent and Payment Method. If the payment is to be applied to a Recurring Gift, it is possible within the batch to select which regular gift to apply this payment against, by selecting it from a drop down list under the Application column.

"Screenshot showing drop down list in applications column when importing revenue"

This can be very time consuming if the resulting batch is very large, as each payment needs to be applied individually.

The solution is to include details in the import file which allow DARS to automatically map the payment to it's recurring gift record.

The overall process steps to import data into DARS via batch entry are:

"Step-by-step flow chart for importing payments matching RG"

This document covers each step in detail.


Firstly, the Recurring Gift or Pledge record must already exist in DARS. Make a note of the Pledge or Recurring Gift's Revenue ID and the Amount (or next instalment amount). It might be useful to keep a spreadsheet containing details of all regular gifts, including the Constituent's DARS LookupID, to use as a mapping list in Excel. Then each month when the Payment details come in, the DARS ID's can be mapped to each payment using a VLookup or Index/Match function in Excel. To get a list of all your existing recurring gifts and pledges, you may copy and amend the template query SYSW-GIFT-KLW-All active recurring gifts for College X to be used for the Vlookup or Index/Match.

"Screenshot of a revenue record showing the Revenue ID and Amount"

When populating the Import File, include the following fields:

  • Revenue Streams Applied amount 1: Payment amount
  • Revenue stream TypeCode1: 0 equates to Payment (see Possible TypeCode1 values)
  • Revenue streams Application lookup ID 1: Revenue LookupID for the Recurring Gift/Pledge record itself that you want to apply the payment to
  • Revenue streams Application applied amount 1: Amount of the payment you want to apply (usually this will be the same as the payment amount because you want to apply the whole amount)

"Screenshot of a revenue import file showing the new columns listed above"

UsefulInfo.jpg Note: When creating the Batch Template to use for your import, these fields are not explicitly added. They will be automatically added to the Header File when it is generated. For testing purposes, we used the sample Enhanced Revenue Batch PROC-DEMO-KLW-Importing Payments which users are welcome to copy and adjust for their own purposes.

Populate the import spreadsheet with the details of the payment's received (including the values for the new columns above) and create the Import Process in DARS to import the file.

If the Import Spreadsheet was generated from the Batch Template (i.e. from the Export Header File function) all the fields should auto-map.

ImportantInfo.jpg Note: Please ensure that the formatting is consistent when entering payment information, so that the import doesn't fail.

"Screenshot of a revenue import process showing auto mapped fields"

Import the file and open the resulting Batch.

"Screenshot of a revenue import process showing auto mapped fields"

You will see that the Payment has been automatically applied against the Recurring Gift or Pledge.

"Screenshot of a revenue import process showing auto mapped fields"

Possible TypeCode1 values

Other Revenue stream TypeCode1 codes for reference are:

0 – Payment
1 – Pledge
2 – Recurring gift
3 – Matching gift claim
4 – Planned gift
5 – Membership
6 – Event registration fee
9 – Grant award
10 – Donor challenge claim
100 - Opportunity

For all payments (whether pledge or recurring gift) the value imported will need to be 0.

Personal tools
  • Page
  • Discussion
  • Variants
    DARS User Support
    DARS Support Centre
    Advancing Oxford