Skip to main content
All CollectionsImporting your dataTemplates
Helpful examples of smart columns
Helpful examples of smart columns

Some handy use cases and formula examples for setting up smart columns

Updated over a week ago

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}}}
Smart column fees

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")
Smart column Gift Aid start date

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}}})
Smart column combining for notes

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}}})
Smart column payment ID

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}}})
Virtual column campaign

Formulas gif
Did this answer your question?