Are you ready to up your smart fields and rollup fields game? Well, you've come to the right place!
Like with all things, there are many ways you can do the following examples but if you'd like to see how we've done it, read on...
Creating a custom salutation
Automatically take "Lady Eleanor Rose Fancington Humperdinck the 3rd" and return "Lady E Humperdinck"? Don't mind if we do!
There's a few different options, so we've dedicated a whole separate article to them!
Calculating the Gift Aid amounts of a donation
Let's suppose that you want to calculate the total amount from a donation including Gift Aid, but only if that payment has (or will have) had Gift Aid claimed. We'd want this to calculate regardless of where that Gift Aid was claimed - through Beacon or externally like JustGiving/Enthuse.
Since we want the value to be shown as a currency amount, we're going to set the field type as 'currency'. Then we'll type in the following formula:
This will look at both the 'Gift Aid claimed?' and the 'Gift Aid claim' field, and only adds +25% to the amount if one or both are true.
Note: Here is the formula in a copy/paste-able format:
=IF(OR({{{gift_aid_claimed}}}=TRUE,{{{gift_aid_claim}}}<>""),{{{amount.value}}}*1.25,{{{amount.value}}})
If you'd like the Gift Aid amount on it's own (not including the donation), it's a very similar formula:
Note: Here is the formula in a copy/paste-able format:
=IF(OR({{{gift_aid_claimed}}}=TRUE,{{{gift_aid_claim}}}<>""),{{{amount.value}}}*0.25,0.00)
Creating a record label field
Sometimes no single field makes sense to have as the primary field of a record. For example, perhaps you want to create a label field for Events that has both the name of the event and the date (as shown below).
If, for example, you would like a label field that looks like this...
...then you can create a new 'template' smart field that doesn't even need a formula! Simply take away the equals sign, and place the elements where you'd want them to appear - like a good old mail merge:
Note: Here is the formula in copy/paste-able format:
{{{name}}} ({{{start_date}}})
Calculating the total financial impact of a supporter
Some of your donors may also fundraise on your behalf and it would be very handy, in those circumstances, to know the total impact those individuals have.
Total impact = total donations + total fundraising
In order to create this smart field, we need to know the total amount and individual has both donated and fundraised. We'll need to create a rollup field for each by creating a new currency field and clicking the rollup field toggle. If you haven’t used rollup fields before, have a read of this article to learn more before implementing this smart field. To create the fundraising total rollup field, choose the following settings:
Summarise record type: Payment
Related field: Fundraiser
Type of summary: Sum of values
Field to sum up: Amount
For the total donated rollup field, choose the following settings:
Summarise record type: Payment
Related field: Payer
Type of summary: Sum of values
Field to sum up: Amount
Note: You can also select different fields to sum up, including custom fields like total + Gift Aid
Once you've got your two rollup fields, we need to sum those two fields in a currency smart field by creating a new currency field with the following formula:
Note: Here is the formula in copy/paste-able format:
={{{c_total_donated.value}}}+{{{c_total_fundraised.value}}}
Calculating how much of a financial goal you've achieved (%)
In this example, we'll assume that you have a campaign with a fundraising goal. Wouldn't it be nice to have a field that tells you how close you are to achieving your goal or by how much you've smashed it (you legend!)? Percentage smart fields are here to help. From a record on the Campaigns record type, create a new field on the card of your choosing. You're going to want to make the Field type: Percent and you'll want to enter the following formula:
Note: Here is the formula in copy/paste-able format:
=({{{financial_total.value}}}/{{{financial_goal.value}}})*100
Setting donor giving tiers based on their donation totals
In this example we are going to use a rollup field to find the total giving for each donor, in conjunction with a rules-based smart field to categorise them into various giving tiers.
Let's start by creating our rollup field. Create a new currency field and click the rollup-field toggle. Choose the following settings:
Summarise record type: Payment
Related field: Payer
Type of summary: Sum of values
Field to sum up: Amount
Filtered for: Type is Donation
Our drop-down, rules-based smart field is going to use this rollup to set our categories:
£1000 and over: Platinum
£500 - £999: Gold
£200 - £499: Silver
Under £200: Bronze
Even though a total could fall into multiple categories (e.g. £400 would be greater than 1, and also greater than £199), our categories are evaluated in the order they appear; making it easy to set priorities!
Automatically assign a region from a postcode
Do you segment your UK supporters or events into regions? Instead of manually recording someone's region (or asking them to tell you, even through they've already given you an address), you can use a rules-based smart field with our special 'Postcode region' condition to create a 'Region' field.
When creating your new field, select 'Drop-down list' as your field type, and toggle on 'Is rules-based smart field?'.
When setting your criteria, you'll be able to choose a special option from the field list: Address > Postcode area. This criteria looks at the start of a UK postcode (e.g. 'SE' from SE23 1JG) and you can then select all the areas that relate to each region.
You can add as many different regions as you like! If you'd like region suggestions, we have a list of common regions and their corresponding postcodes here.
Calculating VAT
There are a few ways you might want to track VAT on a payment. In this example we'll take a look at calculating:
Whether a payment is VATable
VAT amount
And either
The Amount (ex VAT)
The Amount (inc VAT)
This will depend on whether in the 'Amount' field you are storing the Amount excluding VAT or the Amount including VAT (recommended).
VAT status - Is a payment VATable?
Not all payments are VATable. First of all, we'll need to create a rules-based smart field to automatically determine whether a Payment is VATable; this will let our other smart fields know whether to do any further calculations.
When creating your new field, select 'Drop-down list' as your field type, and toggle on 'Is rules-based smart field?'.
Next determine which 'Types' of Payment are VATable, and assign the value 'Yes' to those that are, and 'No' when none of those conditions are met:
You should have one rule and one fallback value.
Great! Now that we know which of our payments are VATable, we can create some other useful fields.
If you are storing the total amount paid in the 'Amount' field
The VAT amount
Whilst the payment 'Amount' field tells us the total someone has paid, if this figure includes VAT, it's often useful to be able to see exactly how much VAT was paid.
Set the field type as 'currency', toggle on 'Is smart field?' and enter the following formula:
Here, we've used our newly created 'VATable?' field to determine whether we should calculate the VAT amount, and then used our formula to work out the VAT amount.
The amount excluding VAT
Rather than now having to manually work out the total amount paid excluding VAT, lets create a final smart field to work this out for us
Set the field type as 'currency', toggle on 'Is smart field?' and enter the following formula:
Here, we've once again incorporated our new 'VATable?' field to determine whether the smart field should run the calculation or not, and then used our formula to work out the total amount excluding VAT. If the payment is not VATable, the 'Amount (value)' will display instead.
If you are storing the total amount excluding VAT in the 'Amount' field
The VAT amount
Whilst the payment 'Amount' field tells us how much someone has paid, if this figure excludes VAT, it's often useful to be able to see exactly how much VAT was, or should be paid.
Set the field type as 'currency', toggle on 'Is smart field?' and enter the following formula:
Here, once again, we've used our newly created 'VATable?' field to determine whether we should calculate the VAT amount, and then used our formula to work out the VAT amount.
The amount including VAT
Rather than now having to manually work out the total amount paid including VAT, let's create a smart field to work this out for us.
Set the field type as 'currency', toggle on 'Is smart field?' and enter the following formula:
Here, we've once again incorporated our new 'VATable?' field to determine whether the smart field should run the calculation or not, and then used our formula to work out the total amount including VAT. If the payment is not VATable, the 'Amount (value)' will display instead.
That's it! We now have a system for calculating VAT on only those payments that are VATable.
Why not try adding the 'VAT amount' and 'Amount (ex VAT)' fields to a new card, and set the visibility to show the card only when 'VATable' is 'Yes'?
Congratulations!!! You've made it through all the examples. You're definitely going to be the Beacon legend of your office!
