Payment information can be exported from the Payment Summary Report (that's the report that gives headline number for each payment request sent) and the Detailed Payment Report (that's the report that shows individual members).
Exporting the Payment Summary Report
Go to Financials > Payment reports and click on 'Export report' on the tool bar. The system will export aggregated numbers for each payment request for the accounting period.
Exporting information from the Detailed Payment Report
By clicking on the hyper-linked numbers in the Payment Summary Report, you will be taken to the Detailed Payment Report that gives payment information for members.
In the tool bar of the Detailed Payment Report is the 'Export' option. The system will give you the option to export 'Screen data', that is the information you see in the table of members or export Detailed Data.
Exporting Detailed Data
The default payment fields that appear in the export are determined on whether you are exporting a paid report, outstanding report or a combination of paid, pending and outstanding. Information on the payment fields are below.
The benefit of using the detailed data export is you can combine membership information alongside payment information.
To add fields to the export, just select them from the left hand side of the page.
You can determine the order you want the fields to appear in the export by dragging and dropping them in to position. To move multiple fields at the same time, hold down the control button and click on the fields.
Exporting group names
To include group names (group names includes Membership Categories, Courses, Classes, Optional Extras, Fundraising Appeals etc) in your export file, select 'Group names' from the list of system fields that appear at the bottom of the Export Detailed Data page. This will add the Group Name field to the right hand side pane with an 'Edit' link. Click on the 'Edit' link and you will see the following options:
- Display selected group names in one column in the exported file
When exporting to a spreadsheet, this concatenates all group names in to one cell, under the column heading ‘Group names’.
- Display selected group names in separate columns
When exporting to a spreadsheet, this splits out all group names in to different cells. Each group name will have it’s own column. This makes it possible to filter data for specific groups in the exported file.
- Display member data for selected groups in separate rows
Members can be associated to multiple groups and the data fields in each group can be different. So rather than a consolidated view of all data for all groups in one row, you can now split out each group’s data in to their own rows. When you select this option in conjunction with exporting the membership expiry date, the membership expiry date will appear in it’s own column, allowing you to easily filter on the date.
For all three options above, once you’ve selected the group names to export, you can determine the order in which the group name appears in the export by moving them in to your preferred order on the right-hand-side pane of the Edit Group Names pop-up.
Information on system fields
You can select each of the fields below to be included in your export:
Payment description - This is the name of your Membership Categories, Courses, Classes, Optional Extras, Fundraising Appeals, Ad-hoc payment request etc.
Amount paid - This is the amount paid against the payment request.
Amount pending - This applies for Direct Debit payments that are in the process of going through the BACS system.
Amount outstanding - The amount outstanding for the payment requests.
Payment request date - The data in which the payment request was made.
Payment date - the date in which payment was made.
Payment method - The method in which payment was received (for example, PayPal, Cash etc).
Merchant transaction ID and Customer transaction ID - for PayPal payments, the transaction ID given to the merchant on receipt of when a member pays is different to the transaction ID given to the member for the same transaction. For Direct Debit payments, the merchant and customer transaction ID is the same.
Failed transaction reason - this is the reason why a pre-authorised payment has failed.
Pre-approval status (this is the same as pre-authorisation) - whether the member has pre-authorised payments or not.
Pre-approval key - when a member pre-authorises payments, a unique key is generated by the payment provider (ie. PayPal or GoCardless).
Overdue by - this is the number of days payment is overdue by.
Last reminder sent - this is the date in which the last reminder was sent to the member.
Date added - this is the date the member was added to the system.
Lead member first name and last name - in the scenario where members are linked together, this field identifies who the lead member is.
Lead member ID - this is a numerical reference to who the lead member is for linked members
Group names - as described in the section above.
Membership expiry date - for Membership categories that auto-renew, the system calculates the expiry date as renewal date minus 1 day.
PSO member reference number - this is a number that uniquely identifies a person on the database. Typically used when doing a mass update.
The file can be exported in three formats: a) Spreadsheet - TAB separated file (recommended). b) Spreadsheet - Comma separated file (CSV. Only use if you are sure you have no commas in your data) c) Text file.
You can also save your export criteria so if it's a regular report you're running, you'll find this feature useful.