Pledge Exports

To create Pledge, Timesheet and Fundraiser exports you need to be an:

1. Admin users or
2. Admin Ops users with read/write access to the Exports and at least read only access to Campaigns, Field and Pledges

Note

Currently the Agent exports are accessible by Super Admins, but this is likely to change.

Pledge Export Builder

The Pledge Export Builder page contains 3 parts

1. Exporter Settings where you set the format and settings for the export and 
2. Schema where you build the template.
3. Preview where you can see a preview of what the export will look like.

Exporter Settings

Exporter Settings

Field	Description	Requirement
Name	The unique identifier for the export.	Mandatory
Tags	Automatically added tags. Can manually add "Final export", "Reporting", or "Workflow".	Optional
Description	A brief description of the export.	Optional
Type	The type of data to be exported (select either Pledge or Lead or Any).	Mandatory
Campaign Selection Type	Option to select from "All Campaigns", "All Active Campaigns", and "Select Campaigns".	Mandatory
Campaigns	Select one or more campaigns that this export will be available for.	Mandatory
Output Type	Format of the export file (CSV, XLS, Salesforce, API, EBIC, or Blackbaud). XML is not supported directly.	Mandatory
Last Used Approx	Displays the last time an export was run, enhancing tracking and management.	Auto-populated

Output Filename Options

File Naming convention The file naming convention is Export-{batch number}.csv (e.g. Export-1378181286.csv). The same batch number is automatically stored against each pledge for reference and retrieval. The batch number is a Unix or Epoch date time stamp e.g. 1378181286. This means it is: completely unique; a searchable and cross reference-able number; and also a date (see http://www.epochconverter.com/ for more info). The file name may be personalised by inserting an alphanumeric value before the timestamp Export-{YOUR REF}1378181286.csv, using the fields Filename Prefix and Export Reference Prefix when creating /editing an Export Builder

Field	Description
Filename prefix	A prefix for the export filename.
Filename suffix	A suffix for the export filename.
Export Reference Prefix	Prefix to the export reference (i.e., the UNIX timestamp of the export).
Append Export Reference Prefix To Export Filename	Yes if you want to add the prefix entered in the "Export Reference Prefix" field, else No.
Prepend External Reference to Call Recording Filename	Yes to prepend the value stored in the External Reference Number field to the call recording filename, else No.
Filename Date Format	Allows you to pick a format for the filename instead of the standard date stamp e.g., DD-MM-YYYY-HH:MM instead of the default.
Override File Extension	When a value is added here, this overrides the extension used for naming the output file - this doesn't change the format of the file, just the extension. The default extensions are .csv for CSV files, .xls for Excel spreadsheets.

Output Format Options

Field	Description
Include Column Headers in Output	Yes/No. Visible only for CSV or XLS output types. Enables the inclusion of column headers in the first line of the export.
CSV Field Separator & Record Separator	Configures the characters used to separate fields and records in CSV format. Standard separators include "\r\n" for new lines, "/r" for carriage returns, ‘
CSV Character Set	Selects the encoding for the CSV file. Visible only for CSV output type.

Export Destination

Field	Description
Distribution List	List of email addresses who should receive the export.
Template for Email Subject	Initially blank in the export builder. Default subject format is [account name] Export of [campaign charity name] data. Type a new subject in the template to override.
Template for Email Body	Appears blank in the export builder, with a default text template provided. To customize, add new text in the email body section of the template.

Default Email Body Template

Hello, 
A file from `account name` is ready to download from `file download link` and will be available until  `link expiry date and time`.    This file was exported through `schema name`.  

Sincerely yours, the Evergiving bot  

Evergiving (c) 2017

Email Body Template Merge Fields

The export email can be edited within the schema template and additional merge fields are available:

total number of records  
total number of alien pledges  
total number of approved pledges  
total number of pending pledges  
total number of invalid pledges  
total number of cancelled pledges  
total number of direct debit pledges  
total number of pledges with successful instant payments  
total number of credit card pledges  
amount sum  
amount average  
condition  
active filter set  
campaign name  
campaign charity name  
account name  
schema name  
file download link  
link expiry date and time  
signature download link  
photograph download link  
form pdf download link  
call recording download link

Output File Name Settings

Field	Description
Output Destination	Choose to upload to SFTP and notify by email, or upload to S3 and send a secure download link by email. Selecting SFTP allows for additional options to export signatures, call recordings, form PDFs (e.g., SEPA), and photographs.
Signature File Name Template	Modify the default file name format for signature exports.
Photograph File Name Template	Modify the default file name format for photograph exports.
Form PDF File Name Template	Modify the default file name format for form PDF exports.
Call Recording File Name Template	Modify the default file name format for call recording exports.

Example Filename Format Modifications

Template	Example Output
{{pledge.supporter_lastname}}_{{pledge.supporter_firstname}}_{{pledge.created_at \| date:"%Y%m%d"}}.png	doe_john_20190812.png
{{pledge.id}}_signature_{{office.name}}.jpg	9876543_signature_London.jpg

For more information about liquid templates see https://shopify.github.io/liquid/tags/iteration/

Note

You can only change the file type if they are compatible e.g. png and jpg. The files will still be exported in a zip file. Only the filenames inside the zip file will change, and not the name of the containing folder.

Note

Through https://github.com/waysact/evergiving/issues/8873 transliterate is now available (see https://api.rubyonrails.org/classes/ActiveSupport/Inflector.html#method-i-transliterate)

It is possible to add a lot of information in the filename using list-of-liquid-template-elements

Export Encryption

The Encrypt for Recipient drop list displays all the GPG keys uploaded to Account -> GPG Public Keys.

If you want to export credit card numbers, you need to first add a GPG key and save that change before the "Card Number - full plain text PAN" source will become available. Without an encryption key you can only export the Display Card i.e. 413373XXXXXX1234.

Export Filter

Add filters to control the type of pledges allowed to be exported with this schema. The ActiveRecord isn't really needed now, only in rare cases where the export needs are so complex that the standard filters won't meet the full requirements.

Export Schedule

You can set an export to run automatically so the customer doesn't need to manually export. If you do this, it is mandatory to add a filter to the export. It is advisable in automated exports to add a filter to only allow through pledges that have not previously been exported with that schema. This means the charity will only receive each pledge once and prevents duplications.

Most customers will be using the calendar based schedule but Super Admins can add a cron expression to schedule an automatic export (e.g 0 11 * * 1-5 to run the export only on weekdays at 11am).

The CRON scheduler run on the account’s time zone. http://crontab.guru/examples.html.

Note

On occasion when an automatic calendar based export happens, multiple email notifications announcing this may occur. However only one export file is sent to the charity's SFTP server.

Schema Builder

For each column in the schema you will see the above fields.

Below the main interface, you will find a series of buttons, each with distinct functionalities:

• Add a new column: Adds a new column to your schema.
• Delete column: Removes the selected column from your schema.
• Move column left: Shifts the selected column one position to the left.
• Move column right: Shifts the selected column one position to the right.
• Copy column: Copies the selected column.
• Paste column: Pastes the copied column into the schema.
• Bulk import column header names from a CSV file: Allows you to import a CSV file containing headers and descriptions for the columns.
  • The CSV text should use a comma as the field separator, a newline as the record separator, and double quotes for quoting.
  • Only the first one or two lines of CSV text will be used; subsequent lines are ignored.
  • The first line defines the column names, and the second line, if present, contains a description for each column.
• Toggle View: Switches to a table view for a live preview of the output. This feature is only available for CSV exports.

Source

Use this setting to select the data source for this column - it can be one of the following -

Source Type	Description
Data Source	An attribute of the pledge, the row number, or the export timestamp.
Column Source	The output of another column is used as the input for this column.
JavaScript	Populated with the return value of a JavaScript snippet. The editor uses JSHint for validation, flagging any issues directly below the problematic line. Supports auto-completion with "ctrl+space" for ease of coding.

Add Transformation

The "Add Transformation" feature becomes available when the column source is not set to JavaScript. It provides a dropdown menu listing various transformations. Below, each transformation is detailed for your selection and inclusion in the transformation list.

Transformations can be applied even if they do not match the data type of the column (for example, applying a date format to a text field); however, such applications may not yield meaningful outcomes. An orange warning icon will appear next to any mismatched transformations, indicating a potential issue. Hovering your mouse over the icon will display details about the mismatch. Additionally, the order of transformations can be adjusted through a drag-and-drop interface for optimal organization.

The following transformations are available:

Transformation	Function	Examples/Notes
Change Display	Modifies how data is displayed	- Convert to Uppercase - Convert to Lowercase - Convert to Title Case
Add String	Adds a string to the source	- Append String - Prepend String Strings can contain escape sequences if quoted.
Add Source	Combines data from different sources	- Append Source - Prepend Source Useful for combining fields like names.
Map Data	Applies a mapping to the source value	- Map Allows replacement or retention of original values based on a mapping table.
Format Data	Changes the format of the data	- Format Number - Format Date - Convert to Time Zone - Replace Regexp - Round - Pad Left
Change Dates	Modifies date values	- Add Days to Date - Advance to Month - Advance to Day of Month
Change Phone Numbers	Formats or analyzes phone numbers	- Format Phone Number Utilizes libphonenumber for formatting and validation.
Conditional Data Display	Shows or hides data based on conditions	- Set Blank unless Source equals - Set Blank if Source matches - Set Blank if equal to Source - Use Source if Blank - Set Blank if Source Blank
Force Data Type (2022)	Ensures a column outputs as a specific data type	- Force numeric data - Force boolean data - Force string data
Validate with JSON Schema (2022)	Validates the column output against a given JSON schema	Failed validations reveal the data type in the error message.

Title

The title / column name that will appear in the export. If left blank, the name of the Source will be exported as title.

Comment

Enter a comment for the column. It can be really useful to share information about the data requirements for that column, or to record other useful information.

It isn't included in the export file the customer receives.

Hide Column in Output

When enabled, the selected column will not be included in the export file.

Use cases are to create a hidden column is so that it can be used as a source for other columns or to cross check transformations used on other columns.

Additional Options

In addition, for CSV exports you will see an additional feature Always quote this column in CSV output. When enabled, CSV fields corresponding to the selected column will always be quoted, even if quoting would otherwise be unnecessary (because the data doesn’t contain spaces or any other special character.)

For XLS exports you will see an additional feature Cell format. Most data will display in Excel with the cell format "General". To have the data export in a different format, put the @ symbol in the Cell Format column to have the data in Text format or put a 0 in the Cell Format column to have the data in Number format. The Transformation feature available in all exports changes the actual values in the column, turning them into string values. The Cell Format, on the other hand, leaves the values untouched. For example, if you used the Format Number transformation, you could no longer use the number in an Excel formula to add some amount to it (because it would actually be a string, not a number). But using cell format, the number is still a number and can be used in calculations. You can also add a date format like DD/MM/YYYY in the cell format field, although this causes an error in the preview so the preferable setup might be adding Date in the Cell Format field and using the transformation to set the date format.

The Cell Type feature is only available for columns with the Source Javascript in exports with the Output Type XLS. It specifies the Excel Cell Format to use for this column.