Using smart 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.
Note: The column references in this guide all have curly brackets around them like {{{this}}}. You won't need these - simply replace any column references in the following formulas with your real columns. The ones in this guide are just examples!
Examples
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}}}
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 smart 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)
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 smart column to calculate the date 4 years earlier.
Tip: Date formats can vary wildly. This formula works with most dates, but if in doubt use the YYYY-MM-DD format (e.g. 2022-09-25).
=DATESUBTRACT({{{Declaration date}}}, 4, "years")
Tip: DATESUBTRACT is a Beacon-made formula to make date manipulation easy! Check out our smart date guide article to see all the options.
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}}})
Note: 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!
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 smart column.
=TEXTJOIN("-",TRUE,{{{full_name}}},{{{payment_amount}}},{{{payment_date}}})
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 smart column to replicate another column.
={{{column to copy}}}
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:
Setting a tier or type based on an amount
=IF({{{amount}}}>"150","Premium","Standard")Setting a person's type based on a payment type
=IF({{{payment_type}}}="Donation","Donor",IF({{{payment_type}}}="Membership fee","Member",""))Note: This is a 'nested' IF function - It's checking for two different criteria, or otherwise leaving it blank.
Prioritising one column over another
Sometimes you might have two columns referring to the same thing (e.g. 'Approach' and 'Source' might both be the Campaign), and you'd like to use one over the other unless it's blank.
=IF({{{Approach}}}="",{{{Source}}},{{{Approach}}})
