Smart fields

Intelligent, read-only fields that auto-calculate their values every time you update a record in Beacon

Updated over a week ago

Heads up! This is a guide for admins. If you're not an admin yet, ask your Beacon admin to promote you so you can start managing fields.

Most of the time, you only need to store data that's static: data that doesn't change unless you change it. Sometimes that's not enough though - for example you might want to add several number fields together to calculate a score, or divide a total payment amount by the number of instalments you'll make.

Enter smart fields. Intelligent, read-only fields that auto-calculate their values every time you update a record in Beacon.

In Beacon there are three ways of using smart fields:

  • Formulas - These can be used with short text fields, number and currency fields, and date fields. You use these by writing what you would when working with Excel or Sheets (e.g. =(1+3); =IF( X > Y , "Yes" , "No" ))

  • Templates - These are used to combine text from multiple fields without needing to use a formula (e.g. displaying a person's name and membership number on a single field)

  • Rules-based - These are useful to auto-categorise records (e.g. payments into "income streams") based on rules that you define. Drop-down fields only.

Once you've taken a look below, you might want to check out:


How to create a formula smart-field

Formula smart fields are where the real power of smart fields lies. With formula smart fields, you can use many of the functions and operators that you're used to using in Excel, including:

  • Maths: +, -, *, /, ^, %

  • Logic: IF(), AND(), OR(), NOT(), XOR()

  • Comparisons: =, >=, >, <=, <, <>

With formula fields, you can use all of the above to build out smart fields that automatically update, to calculate things like the balance that a member still owes you, or a flag to determine if a team member should call someone or not. Let's go over how to do it:

  1. When creating a new field, choose the Number, Currency, Short text, or Date type depending on if you want the output to be a number, currency, text, or a date.
    ​
    Click the "Is smart field?" toggle.

  2. Create an Excel-like formula in the template. Most excel formulas will work; feel free to send us a message if you get stuck!
    ​

    Screen Recording 2021-09-20 at 06.30.38 pm

  3. Click 'Create' and then 'Save' on the Card: overview. You'll now see a smart field value has been automatically set.

Note: If you're working with dates (either displaying a date in a particular way in a short text field, or manipulating dates for a date field), see our guide here for more detail

You'll need to give Beacon a short time to calculate your smart fields - especially if it's having to sort through lots of data! For larger databases, it make take a few minutes for your smart fields to update.

Refresh Page

How to create a template smart-field

Templates are smart fields that don't require any formulas. They are used to join up text from other fields and are very useful for labelling records in Beacon. They're often used for setting labels on records like donations, e.g. "Β£10 from Jess".

To set up a template field:

  1. Create a new field and select short text as your field type.

  2. In the Add Template field, use the rightmost menu to select the fields you'd like to include. Make sure you include spaces and any other characters you'd like to include.

Smart field template

You'll need to give Beacon a short time to calculate your smart fields - especially if it's having to sort through lots of data! For larger databases, it may take a few minutes for your smart fields to update.

Page refresh - anchorman

How to create a rules-based smart field

Note: Rules based smart fields are available on all record types except Payments. To use rules based smart fields on the Payments record type you will need the Finance element πŸ‘

Do you need to have a predefined list of options but want those options to be automatically and dynamically selected? Enter Rules-based smart fields; the trusty drop-down list field made smart, allowing you to automatically categorise your records based on rules that you define.

Data often lives in different places in your database. Rules-based smart fields can use data from a variety of different places to standardise data into a simpler list of categories.

Note: Rules-based smart fields are particularly useful in a finance context to auto-categorise your payments into "income streams"; often referred to as "ledger codes" in accounting systems.

Each rule can use:

  • Fields on the current record type (e.g. payments)

  • Fields on record types that are pointed at by this record type (e.g. person)

To set up a rules-based smart field:

  1. Create a new field and select drop-down list as your field type.

  2. Toggle Is rules-based smart field?
    ​

  3. Add as many rules as you like! Here's an example of rules you can use for an "Income stream" field:
    ​

    Screenshot 2021-07-08 at 09.33.21

  4. Click and drag rules to re-order them by priority (higher priority at the top)
    ​

    Screen Recording 2021-07-08 at 09.43.27 am

  5. Add a fallback value, which will be used when none of your rules match.
    ​

    Screenshot 2021-07-08 at 09.34.51

You'll need to give Beacon a short time to calculate your smart fields - especially if it's having to sort through lots of data! For larger databases, it may take a few minutes for your smart fields to update.


Frequently asked questions

What triggers a smart field to update?

Smart fields in Beacon recalculate when any field referenced by the smart field is updated. This can be either:

  • A referenced field on the same record

  • A referenced field on a linked record
    ​

Which functions are supported in Beacon?

Loads! Beacon supports the vast majority of common Excel functions to build your formulas, but there are some exceptions that are NOT supported:

  • ISBLANK

  • IFERROR

  • IFS (nest regular IF statements instead)

  • COUNT/COUNTIF/COUNTA (because these are based on cell ranges)

  • VLOOKUP (just... no 🀨)

  • CONCAT (as it's based on cell ranges - use CONCATENATE or TEXTJOIN instead)

  • TEXT (if date related, use Beacon's DATEFORMAT function instead)

  • REGEXREPLACE

  • REGEXEXTRACT

  • REGEXMATCH
    ​

Can I use TODAY() in my formula smart fields?

Smart fields in Beacon recalculate when a field referenced by the smart field is updated. This can be either:

  • A referenced field on the same record

  • A referenced field on a linked record

Today's date isn't a field that's updating, so whilst your formula will calculate correctly when you create it, as soon as tomorrow comes it might not be accurate anymore!

For example:

You have a field that calculates 'Age' using the years between someone's date of birth and TODAY(). Bob is 65, but his birthday is tomorrow. When his birthday comes and goes, his 'Age' will still show 65, unless something triggers the smart field to recalculate and update to 66.

Workaround: Manually trigger the field to update.

If there's a field that includes TODAY() that you want to use, you will want to update something that the formula references on any records you want to update. Often it's easiest to have a checkbox for this sole purpose, such as 'Update age?' or 'Recalculate smart fields?'.

Include this new field in your formula, so that it triggers the update. For example, your base 'Age' formula might be:

=DATEDIF({{{date_of_birth}}},TODAY(), "Y")

If you add a checkbox called 'Update age?', add it to your formula:

=DATEDIF({{{date_of_birth}}},TODAY(), "Y")&LEFT("{{{update_age}}}",0)

Bulk updating this field to be ticked, and unticked again, will ask your smart field to check that it's correct and you'll have accurate data!

In a smart field, what fields can I reference?

Lots of fields can be referenced in your smart fields!

What fields can I reference?

What fields can't I reference?

Short text

Email address

Number

Urls

Currency

Long text fields

Rating

Phone numbers

Percent

File upload

Checkbox

Link to a user

Drop-down list

Location

Date

Point to another record

In a rules-based smart field, what fields can I reference?

What fields can I reference?

What fields can't I reference?

Short text

Email address

Point to another record

Urls

Currency

Long text fields

Drop-down list

Phone numbers

Checkbox

File upload

Link to a user

Location*

Number

Rating

Date

Percent

* With the exception of automatically assigning regions based on 'postcode areas'. 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.

In a smart field, can I reference other smart fields?

Yes, absolutely!

In a smart field, can I reference rollup fields?

You sure can!

In a smart field, can I reference other smart fields on a linked record?

No. Smart fields can't be passed to another smart field when they're on different records. No problem though - reference the original fields used in that smart field instead!

In a smart field, can I reference rollup fields on a linked record?

No. Rollup fields one step away don't play nice with smart fields, just rollups on the same record.

In a smart field, can I reference another smart field on a linked record via a rollup field?

For example: Reference the 'Amount (Net)' smart field via a 'Last payment' rollup field on People.

No. There's a bunch of very complex stuff going on behind the scenes with both smart fields and rollup fields, so trying to do one via another is not currently possible.

Did this answer your question?