Beacon

Helpful examples of virtual columns

Updated a month ago

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


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}}}

Virtual column fees

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}}}"))

Virtual column Gift Aid start 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}}}")

Virtual column combining for notes

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}}}")

Virtual column payment ID

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}}}")

Virtual column campaign


Formulas gif

close

Contact Us

It'd be great to hear from you! To reach out, fill in your details below and we'll get back to you.

close

Register for webinar

To join this webinar, please enter your details below and we'll send you details on how to join.



close