Skip to content

Add Transformation

Unless the column source (see above) is set to JavaScript, you will see a button labelled Add Transformation. Clicking this button shows a dropdown menu with a list of transformations, all of which are described in detail below. Select a transformation to add it to the list of transformations. Transformations are applied to the source data in top-down order.

When a transformation doesn't match the data type of the source (for the first transformation) or the result type of the previous transformation (for the second and subsequent transformations), the transformation is shown in red in the drop-down menu to indicate that it is not applicable. For example, applying the Change Time Zone transformations makes little sense for anything other than a date/time value.

You can still apply transformations with mismatching type, but the results most likely won't be useful. Mismatched transformations have a red warning sign next to them. Hover over it with your mouse to see what the problem is.

Note that for high-security source items, such as the full credit card number, only a limited number of transformations is available.

You can remove unwanted transformations by clicking on the button with an X in it on the right hand side of the transformation. You can also reorder transformations using drag-and-drop.

Preview

There is always exactly one column selected, shown with a blue header. This is the column that any column settings apply to. Other than that, the column selection has no direct effect on the output. You can select a different column by clicking on its header.

To the right of the table there are a number of buttons that can be used to add a new column (plus sign), to remove the currently selected column (bin), and to move the currently selected column to the left or the right (arrows).

There are also buttons to copy the current column definition and to paste a column definition from the clipboard over the currently selected column. Copy-and-paste works across tabs within the same browser and across browser sessions.

Finally, there is a button to import a set of column headers from a CSV file (see below)

Note that there always has to be at least one column in the table, which means you can't remove a column when it is the only column left.

Fields

Name: This is a short name that is used when displaying the exporter in other parts of the Evergiving Backend. You should choose a short and distinct name for the exporter. The exporter name is mandatory and must be unique.

Description: Add a short description of the export.

Campaigns: Select the campaign(s) using this export. The data shown in the export preview will come from this campaign.

Output Type: This determines which type of output the exporter generates

Filename Prefix: Enter a prefix for the export filename.

Export Reference Prefix: The export reference is the unix timestamp of the export.

Append Export Reference Prefix To Export Filename: Select Yes if you want to add the prefix entered in the field below.

Filename Date Format: allows you to pick a format for the filename instead of the standard date stamp. For example: DD-MM-YYYY-HH:MM

Override File Extension: When set, this overrides the extension used for naming the output file. The default extensions are .csv for CSV files, .xls for Excel Spreadsheet, and .xml for XML files.

Include Column Headers in Output: This setting is only visible when the output type is set to
Comma-separated Values (CSV) or Excel Spreadsheet (XLS). If it is enabled, the first line of the generated CSV or XLS data will contain the header names. Otherwise, column headers are omitted during export.

CSV Field Separator: This setting is only visible when the output type is set to Comma-separated Values (CSV). It can be used to configure the character or string used to separate individual fields in a record (line). Just as with other text input fields, if the setting begins and ends with a double quote, it is interpreted as a JSON string -- in short, it can contain escape sequences for newlines, tab characters, unicode characters, and so on.

CSV Record Separator: This setting is only visible when the output type is set to Comma-separated Values (CSV). It can be used to configure the character or string used to separate individual fields in a record (line). Just as with other text input fields, if the setting begins and ends with a double quote, it is interpreted as a JSON string -- in short, it can contain escape sequences for newlines, tab characters, unicode characters, and so on.

CSV Character Set: Select the encoding (only for CSV).

Problem with accented characters in the CSV file when opening with Excel:
Evergiving creates CSV files that are compliant with the UTF-8 standard. If you use Microsoft Excel to open the CSV files, you may run into an issue when your CSV file also contains non-English characters (such as é, ç, ü, etc):

Microsoft Excel is unable to properly display UTF-8 compliant CSV files when they contain non-English characters. To resolve this issue, please do the following after exporting the CSV file from Evergiving.

On a Windows machine:
Method 1
- On a Windows computer, open the CSV file using Notepad.
- Click "File > Save As".
- In the dialog window that appears - select "ANSI" from the "Encoding" field. Then click "Save".
- That's all! Open this new CSV file using Excel - your non-English characters should be displayed properly.

Method 2

  • On a Windows computer, click "File > New" in Excel.
  • Click "Data" tab.
  • Then click "From Text" option. Select the CSV you file you exported from Evergiving.
  • Excel will display "Text Import Wizard".
  • In step-1 of this wizard:
  • Select "Delimited" radiobutton.
  • In "File origin" field - select "65001 : Unicode (UTF-8)".
  • Click "Next >" button.
  • In step-2 of this wizard:
  • Select "Comma" checkbox.
  • Click "Finish" button.
  • In the dialog window that appears - click "OK" button.
  • Excel will display your CSV file - including non-English characters - properly.

On a Mac:
You should use either the "Numbers" application, or the free LibreOffice, instead of Excel.

Distribution List: enter the email(s) who will receive the notification.

Template for email subject: Enter a subject. The default subject is:
[account name] Export of [campaign charity name] data.

Template for email body: Enter the email body. The default text is:
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.

Here a list of all the available placeholders:

  • [file download link] This placeholder is mandatory
  • [my full name]
  • [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]
  • [link expiry date and time]
  • [signature download link]
  • [photograph download link]

Output Destination:

  1. Upload to SFTP and notify by email: transfer the file to your SFTP (port 22, SSH) and receive a notification per email.
  2. Upload to S3 and send secure download link by email: transfer the file to a secure location in the cloud (a newly spawned S3 bucket), and send an email with a https:// link to it so all you need to do is click the link and the file downloads. The database is then securely wiped 24 hours later.

The email can go to a single email address or a distribution list. The email will come from export-bot@evergiving.com. It will have the following contents:

Hello,

An encrypted file of {CHARITY_NAME} data from {EVERGIVING_ACCOUNT} is ready to download from https: //evergiving-high-sec-export-production.s3.amazonaws.com/Export-{FILE_NA… and will be available for one day before it expires.

Sincerely yours, the Evergiving bot

The email subject and email body are configurable.

Target SFTP Connection: The export will be uploaded to the SFTP selected here.

Encrypt for Recipient: The export will be encrypted using the GPG public key selected here. Leave this blank for an unencrypted export.

Filter: This option allows you to filter the pledges before exporting them. It is mandatory to add a filter if you set an automatic export.

Click on Add Logic / Condition. You can choose between Any of this condition must be satisfied (for example condition one OR condition 2 must be satisfied) or All of this conditions must be satisfied (for example condition one AND condition 2 must be satisfied).

To filter the pledge created yesterday, select Capture Date, for Operator select Relative Range, for value select Yesterday.

ActiveRecord Query (Legacy): this option is for advanced users. Enter a query to filter the pledges to export. For example to filter pledges that were created the day before the cron schedule runs:
pledges.created_at >= CURRENT_DATE - INTERVAL '1' DAY AND pledges.created_at < CURRENT_DATE

Automatic Export Trigger:
You have different options to trigger an automatic export:
- Cron-based export schedule: Add a CRON expression to schedule an automatic export. For example:
0 11 * * 1-5 to run the export only on weekdays at 11am.

Here is an excellent reference for generating cron expressions: http://crontab.guru/examples.html. The CRON scheduler run on the account's time zone.

  • Calendar-based export schedule: to configure the automatic export, you need to enter:

  • Start on: select the start date

  • When to run this export: choose the frequency (daily, weekly, etc)
  • Repeat Every: choose when to repeat the export. Example: Daily, every three days
  • At What Time(s) Should This Export Run: enter the time(s) when to trigger the export. The time displayed are in your account default time zone.

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

  • a data source (an attribute of the pledge, the row number, or the export timestamp)
  • a column source, in which case the output of another column is used as the input for this column.
  • JavaScript. Columns of this type are populated with the return value of a JavaScript snippet.

Note that when using columns as input for other columns, it's possible to create a circular dependency. For example, if you have two columns A and B with A using B as input and B using A as input, you will have created a cycle for which valid output cannot be determined. The schema editor will show "(circular dependency)" in the column output if this is the case.

Add Transformation / JavaScript editor:
Depending on the source (column or Javascript), you can add a transformation or a javascript code. Read more about it below.

Title: Enter the column's title. If 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 an export builder with your team.

Comment: Use this to enter a free-form comment for the currently selected column. You can use this to document the business logic for reference.

Hide Column in Output: When enabled, the selected column will not be included in the output.
Hidden columns are shown with a red background in the table preview. The primary reason for creating a hidden column is so that it can be used as a source for other columns. This technique allows complex column contents to be assembled from parts, each of which can be transformed individually.

Always Quote this Column in CSV Output: This setting is only visible when the output type is set to Comma-separated Values (CSV). 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.)

Cell Format: This setting is only visible when the output type is set to Excel Spreadsheet (XLS). It specifies the cell format to apply to all cells in this column.

It is important to understand the difference between this setting and transformations such as Format Date and Format Number. Transformations change the actual values in the column, turning them into string values. The cell format, on the other hand, leaves the values untouched. The cell format is only used by Excel when displaying the values.

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.

  • Put the @ symbol in the Cell Format column to have the data in Text format.
  • Put a 0 in the Cell Format column to have the data in Number format.

Cell Type: This setting is only visible when the output type is set to Excel Spreadsheet (XLS) and the column type is Javascript. It specifies the Excel cell type to use for this column. For non-Javascript columns, the cell type can be deduced from the source or the last applied transformation. For JavaScript columns, however, it's necessary to specify the column type.

Add a new column: Add a new column. The new column will be added at the right of the currently selected column.

Remove column: Remove the currently selected column. This action cannot be undone.

Left arrow: Move the selected column to the left.

Right arrow: Move the selected column to the right.

Copy the selected column definition: This will copy the source, any transformation or javascript code, the title, the comment, and other settings.

Paste a column definition into the selected column: After copying a column definition, paste it in the selected column.

Bulk import column header names from a CSV file: You can import a CSV file containing the headers and the descriptions of the columns.
-CSV text should use comma as field separator, newline as 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. The second line, if present, contains a description for each column.

-Toggle view: The table view shows you a live preview of the output, displayed as a simple table. The CSV shows a live preview of the output when the output type is set to Comma-separated Values (CSV). Note that there is currently no preview available for Excel.
-Any changes you make to the exporter settings or the column settings are reflected here immediately.

Transformations

Convert to Uppercase: This transformation takes a string and converts it to upper-case
characters.

Convert to Lowercase: This transformation takes a string and converts it to lower-case
characters.

Convert to Title Case: This transformation takes a string and converts it to title-case, which means to turn the first letter of each word to upper-case and other letters to lower-case.

Append String: This transformation takes a string and adds a constant string to the end of it.

Just as with other text input fields, if the string begins and ends with a double quote, it is interpreted as a JSON string -- in short, it can contain escape sequences for newlines, tab characters, unicode characters, and so on.

Note that any leading and trailing whitespace characters are stripped from the string unless it is quoted.

Prepend String: This transformation takes a string and adds a constant string to the
beginning of it.
Just as with other text input fields, if the string begins and ends with a double quote, it is interpreted as a JSON string -- in short, it can contain escape sequences for newlines, tab characters, unicode characters, and so on.

Note that any leading and trailing whitespace characters are stripped from the string unless it is quoted.

Append Source: This transformation takes a string and adds a string to the end of it which is taken from another data source. For example, this allows you to join a supporter's first and last name.

Map: This transformation lets you apply a mapping to the source value. For each pair of values in the mapping table displayed when you add this transformation, if the source value matches the value in the first column it will be replaced by the value in the second column.

If you enable the default value using the checkbox, a special row is shown at the beginning of the mapping table. Use it to set a replacement value that should be used when the source value doesn't match any of the mappings. If the default value is disabled and there is no replacement for a source value then it will be left as-is.

Just as with other text input fields, if any string in the mapping table begins and ends with a double quote it is interpreted as a JSON string.

Format Number: This transformation takes a number and formats it as a string using the numeral.js library. See their website for how the format string is interpreted.

Format Date: This transformation takes a date and formats it as a string using the moment.js library. See their documentation for how the format string is interpreted.

Convert to Time Zone: This transformation takes a date and converts it to the given time
zone. You can either select a constant time zone, or the team's or
account's time zone.

Replace Regexp: This transformation takes a string and applies a regular expression to it, then replaces the match with the given string. The replacement string can include special replacement patterns.

The meaning of the Global search, Case-insensitive and Multi-line flags is described in Advanced Searching with Flags.

Add Days to Date: This transformation takes a date and advances it by the given number of days. For example, using this transformation with the number 1 sets the date to the following day ("tomorrow") and the number -1 sets the date to the previous day. It returns the new date.

Advance to Month: This transformation takes a date and advances it until the first day of the month given by the selected source, incrementing the year by one if necessary.

The source must yield a number between 1 (for January) and 12 (for December). Behaviour is currently undefined if the number is outside this range (no error is raised).

Note that if the date is already on the first of the given month then it will be left unchanged. Use the Add Days to Date transformation if this behaviour is not desired.

Advance to Day of Month: This transformation takes a date and advances it until the day of the month given by the selected source, incrementing the month (and possibly the year) by one if necessary.

If the given value is too large it will be set to the last day in the month. For example, if the value is 40 and the month is February in a leap year, the day of month will be set to the 29th.

Note that if the date is already on the given day of month then it will be left unchanged. Use the Add Days to Date transformation if this behaviour is not desired.

Round:This transformation rounds up the value. The value must be a number. Midway values are always rounded up. For example, the value 23.5 becomes 24, and −23.5 is rounded to −23.

Set Blank unless Source equals: This transformation looks at a given source and compares it to a given value. If the source matches the comparison value, the transformation does nothing. If the source doesn't match the comparison value, the transformation sets the column blank.

Set Blank if Source matches: This transformation looks at a given source and tries to match it against a given regular expression. If the source matches the regular expression, the transformation sets the current column blank. If it doesn't match then the transformation does nothing.

Set Blank if equal to Source: This transformation looks at a given source and compares it to the value in the current column. If the values are identical, the current column is set blank. If the values are different then the transformation does nothing.

Use Source if Blank: This transformation uses the given source if the column source (perhaps after transformations) is blank.

Set Blank if Source Blank: This transformation sets the current column value to blank if the given source is blank.

Pad Left: This transformation adds the given padding string at the left side of the source string until the minimum string length has been reached.

Format Phone Number: This transformation uses Google's libphonenumber to format phone numbers. It should be applied to a phone number source. libphonenumber is pretty smart about handling numbers so there should be no need for prior massaging of the input phone numbers in any way. (For example, it's fine if the number contains spaces, dashes or parentheses.)

Use the Source for Country Code argument to choose the country that should be used when interpreting the phone number. It defaults to the country of the supporter's primary address. Note that his is only used for phone numbers that don't include a country code. The source selected must contain a ISO_3166-1 alpha-2 country code, not a country name.

The Format drop-down box lets you select how the phone number should be formatted. National prints the number as it would be dialed from another phone in the same country. International prints the number with international country prefix. E.164 prints the number in the E.164 standardized format. Mobile Dialing prints the number as it should be dialed from a mobile phone. Number type doesn't format the phone number, instead it outputs the phone number's type as determined by libphonenumber: one of FIXED_LINE, MOBILE, FIXED_LINE_OR_MOBILE, TOLL_FREE, PREMIUM_RATE, SHARED_COST, VOIP, PERSONAL_NUMBER, PAGER, UAN, VOICEMAIL, or UNKNOWN.

If the Filter Invalid checkbox is unchecked, a best effort is made to format numbers even when they are invalid (e.g. too short). If it is checked, invalid numbers are instead replaced by a string Invalid: <reason>, where <reason> is one of +INVALID_COUNTRY_CODE, TOO_SHORT, TOO_LONG, and NOT_A_NUMBER.

What does IS_POSSIBLE mean, you ask? It means that there is nothing obviously wrong with the number: it's not too short or too long, for example. But through validation checking has determined that it's not a valid number after all. For example, it could mean that while at first glance the number is long enough, a number starting with certain digits needs to be longer.