Helpful examples of virtual columns

Using virtual columns to better import your data with import templates can save you loads of manual spreadsheet adjustment time! Here we compile some handy use cases and formula examples to get you using them like a pro.

help_outline Be sure to replace any column references in the following formulas with your real references. The ones in this guide are just examples!

---

link Examples

• Adding fee amounts
• Gift Aid claimed
• Gift Aid declaration start date
• Combining columns for notes
• Creating IDs
• Duplicating a column to map to two places
• Conditional data
  • Setting a tier or type based on an amount
  • Setting a person's type based on a payment type
  • Prioritising one column over another

---

link Adding fee amounts

If an external payment provider has multiple fees, you will want to add them together to map into the 'Gateway fees' field in Beacon.

={{{platform_fee}}}+{{{processing_fee}}}

link Gift Aid claimed

When importing payments from an external service, it can be valuable to mark individual payments as claimed for Gift Aid rather than simply marking them all as claimed. This is particularly useful for services that claim Gift Aid on your behalf, where you might have a declaration for the donor even if they didn't give one to that service.

The 'Gift Aid claimed?' checkbox needs a value to be TRUE / FALSE, Yes / No, or 1 / 0. You can base the virtual column on either a 'Gift Aid amount' column:

=IF({{{Gift Aid amount}}}>0,"Yes","No")

Or, from a column with text about whether it's been claimed:

=IF("{{{DonationTaxStatus}}}"="Claimed", TRUE, FALSE)

link Gift Aid declaration start date

Usually, Gift Aid declarations can have a start date backdated 4 years prior to the declaration date. If you're importing declarations that only have the date it was given, you can use a virtual column to calculate the date 4 years earlier.

help_outline Date formats vary wildly. This formula works best with dates in the YYYY-MM-DD format (e.g. 2021-04-17)

=TEXTJOIN("-",TRUE,YEAR("{{{Declaration date}}}")-4,MONTH("{{{Declaration date}}}"),DAY("{{{Declaration date}}}"))

link Combining columns for notes

If there are several columns in your spreadsheet that you'd like to add to a single 'Notes' field on a record, you can combine them into a single column for import.

=TEXTJOIN(CHAR(10)&CHAR(10),TRUE,"{{{column1}}}","{{{column2}}}","{{{column3}}}")

check_circle_outline CHAR(10) adds line breaks to help neatly separate the information in a long text field. TEXTJOIN will automatically ignore blank columns so there won't be any strange formatting!

link Creating IDs

If you're importing payments (or anything else) that you'd like to have an ID for (e.g. to easily deduplicate with future imports), but there isn't one in your sheet, you can create an ID using a virtual column.

=TEXTJOIN("-",TRUE,"{{{full_name}}}","{{{payment_amount}}}","{{{payment_date}}}")

link Duplicating a column to map to two places

Sometimes you need to map a column to two different fields. Whatever the reason, it's easy to use a virtual column to replicate another column.

="{{{column to copy}}}"

link Conditional data

Just like Excel or Google Sheets, you can use IF statements to map different data depending on criteria in your spreadsheet (see Excel's description of IF functions). We go through a few common uses here:

link Setting a tier or type based on an amount

=IF({{{amount}}}>"150","Premium","Standard")

link Setting a person's type based on a payment type

=IF("{{{payment_type}}}"="Donation","Donor",IF("{{{payment_type}}}"="Membership fee","Member",""))

help_outline This is a 'nested' IF function - It's checking for two different criteria, or otherwise leaving it blank.

link Prioritising one column over another

Sometimes you might have two columns referring to the same thing (e.g. 'Approach' and 'Source' would both be the Campaign), and you'd like to use one over the other unless it's blank.

=IF("{{{Approach}}}"="","{{{Source}}}","{{{Approach}}}")

---