Matching Payments when Importing Revenue
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.
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:
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.
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)
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.
|Note: Please ensure that the formatting is consistent when entering payment information, so that the import doesn't fail.|
Import the file and open the resulting Batch.
You will see that the Payment has been automatically applied against the Recurring Gift or Pledge.
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.