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.
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
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.
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}}}")
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",""))
=IF({{{amount}}}>"150","Premium","Standard")
=IF("{{{payment_type}}}"="Donation","Donor",IF("{{{payment_type}}}"="Membership fee","Member",""))
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}}}")