Expressions

There are various expressions that can be used in Senta to achieve a multitude of outcomes:

• In placeholders - useful for formatting placeholders in special ways, e.g. by formatting text.

• In date offsets - handy for choosing dates in more complex ways, e.g. the earliest of two dates.

• In filters - ensure that tasks/Key dates/jobs only show for clients who fulfil certain criteria e.g. who do not have a specific service switched on.

• In calculated fields - calculate the values stored in fields e.g. calculate the total of two other field values.

Expressions can be tested in the Expression tester.

When using expressions in placeholders, they must be surrounded by curly brackets {}

• AND

• BETWEEN

• CHOOSE

• CONCAT

• CONTAINS

• DATEFORMAT

• DATEDIF

• DATEOFFSET

• DAY

• DEFAULT

• EACH

• EARLIEST

• EMBEDDED PLACEHOLDERS

• FIXED

• IF

  • IF with Yes/no values

  • IF in date offsets

  • IF a value exists

• IF/ELSE

• LATEST

• LEFT

• LEN

• LOWER

• MAX

• MIN

• MOD

• MONTH

• NOT

• OR

• RAND, RANDBETWEEN, CHOOSE

• SUBSTITUTE

• SUM

• TODAY

• TRIM

• UKPAYESUFFIX

• UPPER

• YEAR

AND

This expression allows you to add extra conditions to an IF expression, if you want more than one criteria to be fulfilled.

AND(client.fees > 1000, client.grade = "A")

The result will return true if the value in the client fees field is 1000 and the client grade field value is A. The result will return false if that criteria has not been met.

If you would like to use this in a placeholder to output "Yay" if the result is TRUE, you would use this in conjunction with IF and ensure that the entire expression is surrounded in curly brackets:

{IF( AND(client.fees > 1000, client.grade = "A"), "Yay", "Boo") }

"Yay" will output if the result is true, and "Boo" will output if the result is false.

BETWEEN

Checks to see if the value is between lowerbound and upperbound. This also includes if the value is equal to lower bound or upperbound.

BETWEEN(value, lowerbound, upperbound)

BETWEEN(client.turnover, 50000, 100000) would return true if the client turnover is between 50,000 and 100,000

CHOOSE

Allows you to pick from a selection of options using a number to denote the option you want:

CHOOSE (1, "Field 1", "Field 2", "Field 3") would return "Field 1"

Can be combined with RAND, RANDBETWEEN, CHOOSE expression to randomly choose between two options:

CHOOSE( RANDBETWEEN( 1, 2 ), "Option A", "Option B" )

To create an expression that randomly selects a user, find the User ID(s) within the URL of each User profile:

Add the User IDs to the expression:

CHOOSE( RANDBETWEEN( 1, 4 ), "u12345", "u0876234", "u567891", "u0987121")

CONCAT

Combines multiple field references or specified text into a single string

CONCAT ("+44","7707123456") would return +447707123456 

CONTAINS

Allows you to output set text if a specific term is used within a field.

IF(CONTAINS(client.software, "Xero"), "We will access Xero to retrieve your accounting records.","Please upload your records to the client portal as soon as possible.")

DATEFORMAT

This expression can be used to format dates to display in a specific way.

For example, Payment is due {DATEFORMAT(job.paymentdate, "DD/MM/YYYY")}

This expression replaces the existing date formatting option for placeholders, as DATEFORMAT can be combined with other expressions.

For example, {IF(job.paymentdate, "{DATEFORMAT(job.paymentdate,"DD/MM/YYYY")}", "n/a")} would display the job payment date in the format DD/MM/YYYY if there is a date present, otherwise "n/a" would be displayed.

The date formatting needs to be in quotes when used with the DATEFORMAT expression.

DATEDIF

You can use this expression to calculate days remaining until a certain date, or a number of days between 2 dates:  

DATEDIF(client.date1,client.date2)

DATEDIF(client.date1,job.date)

The DATEDIF expression can also be used to calculate the number of days between a date field and today's date:

DATEDIF(client.date,TODAY())

You can use DATEDIF within an IF expression, to check whether either of the date sources exists or not:

{IF(AND(client.date1,client.date2),DATEDIF(client.date1,client.date2))}

You can choose whether specific text will appear in an email if it is sent within 7 days of a tax deadline:

{IF(DATEDIF(TODAY(),job.duedate) < 7, "Remember to submit your tax return soon!.")}

You can calculate a client's age using DATEDIF:

DATEDIF(client.contact1.dateofbirth, TODAY, "y")

DATEOFFSET

This expression allows you to dynamically set a date offset in placeholders or use one in another expression, such as a filter.

DATEOFFSET( job.filingdate, "-2d" )

Here is an example of how to use this expression in a placeholder:

Please ensure this is submitted by {DATEOFFSET( job.filingdate, "-2d" )}

This can be combined with the existing DATEFORMATexpression to format dates. For example:

Make sure you return the letter by {DATEFORMAT( DATEOFFSET( job.filingdate, "-2d"), "DDD MMM YYYY" )}

You can use this expression within other expressions, for example:

IF( DATEOFFSET( job.date, "-2d" ) > DATEOFFSET( job.filingdate, "+3d" ))

DAY

Gets the day as a number.

DAY(job.date) will return 25 if the job date is the 25th. 

This expression can be used with the = operator as a filter expression. For example:

DAY(job.date)=25 will only meet criteria if the job date is 25th of the month.

DEFAULT

The DEFAULT expression is used so that calculations have a default value of zero if there’s no value in either field. If you don't use DEFAULT, the calculation can fail.

For example, you might record the salary of an individual client in a money field, plus any additional income in a separate money field:

If you wish to bring the sum of those 2 figures together in order to populate an email you can use the following expression:

{DEFAULT( client.salary, 0 ) + DEFAULT( client.moreincome, 0 )}

Taking the above as an example, you can perform additional basic arithmetic operations by following the same principle.

Subtraction:

{DEFAULT( client.salary, 0 ) - DEFAULT( client.moreincome, 0 )}  

Multiplication:

{DEFAULT( client.salary, 0 ) * DEFAULT( client.moreincome, 0 )}  

Division:

{DEFAULT( client.salary, 0 ) / DEFAULT( client.moreincome, 0 )} 

Please note that in the example above, {client.salary} and {client.moreincome} refers to the field references associated with the Salary and Additional income fields.

EACH

EACH is used to pull a list of Services offered to a client into a Send email task or letter:

{each(service)}

- {service.title}

{service.sales | optional}

{service.terms | optional}

{if(service.fees.setup)} £{service.fees.setup} setup {endif}

{if(service.fees.monthly)} £{service.fees.monthly} each month {endif}

{if(service.fees.annual)} £{service.fees.annual} per year {endif}

{endeach}  

The EACH expression pulls the data relating to each Service selected for a client.  

Go to our Placeholders guide for more information on setting up Service fields.

EARLIEST

Picks the earliest of two dates.  The two dates can be field references or date offsets.

EARLIEST (date1, date2)

EARLIEST (job.previous_task_completion, duedate-30d)

EMBEDDED PLACEHOLDERS

You can use placeholders in the format {client.title} within other placeholders.

{IF(client.language = "French", "Je vous écris au sujet de votre entreprise {client.title}", "I am writing to you regarding your company {client.title}")}

FIXED

Shows a number with comma-separated thousands and a fixed number of decimal places.

FIXED (number, places)

FIXED (1000, 2) would show 1,000.00

Please note: It is important in any number fields in Senta to not include a comma e.g. £1234.56 rather than £1,234.56.

IF

Lets you put in different text, dates, numbers, etc. based on a condition.

The IF expression follows this format: IF (condition, option1, option2)

Option1 will display if the condition is true and option2 will display if the condition is false.

IF(client.taxnumber <> "", "We have your tax number on file.", "We do not have your tax number on file yet.")

IF(client.bookkeepingmethod = "Paper records", "We require you to send your bookkeeping records to the office address.", "")

The IF expression supports the following operators: 

= equals

<> does not equal

!= (this is the same as the <> above)

< is less than

> is greater than

<= is less than or equal to

>= is greater than or equal to

IF with Yes/no values

You can also use an IF expression to populate an email based on a Yes/no field or task. This means that you can have a different value inserted into an email depending on whether yes or no has been selected. 

For example, if you have a Yes/no field in your practice where you record whether your clients are based in the UK or not, you can use an IF expression to send a custom email to your clients, based on whether they live in the UK or not.

The screenshot below, illustrates a Yes/no radios field with the option to choose whether the clients are based in the UK or not:

The standard format of an IF expression based on a Yes/no field/task answer is the following:

IF(client.fieldreference, "option1", "option2")

To break this down further:

client.fieldreference = the field reference used in the yes/no field (for example, client.clientbase). This condition is checking if client.clientbase = true (yes).

option1 = text that will be inserted based on a yes answer

option2 = text that will be inserted based on a no answer

The IF expression we could use is:

IF(client.clientbase, "UK resident", "non-UK resident")

This will populate different text dependent on the answer. For example, we could populate this sentence:

All of our _______________ clients are required to...

To populate the blank space with the IF expression (including curly brackets as this is a placeholder):

All of our {IF(client.clientbase, "UK resident", "non-UK resident")} clients are required to...

If the answer is yes, the e-mail will populate as:

All of our UK resident clients are required to...

If no is the answer, the e-mail will populate as:

All of our non-UK resident clients are required to...

IF in date offsets

The IF expression can also be used within date offsets, so that the start or due date offset for a task can vary based on client data. For example:

IF(client.clienttype = "Individual", job.paymentdate-7d, job.paymentdate-3d)

Using the expression above, if the client type is "Individual" then the date offset will be "job.paymentdate-7d", and if the client type is not "Individual" the date offset will be "job.paymentdate-3d".

IF a value exists

IF expressions can be used to check whether any value exists. For example, to see if a value has been entered into a field.

IF(client.contact1.email) checks if there is an email address entered for the primary contact.

You can also check whether a secondary contact exists before pulling their information into a placeholder:

IF(client.contact2)

Dear {client.contact1.firstname} {IF(client.contact2, "and {client.contact2.firstname} ")}

Use IF to check which services are switched on:

IF(services.serviceid)

[*]The service ID is found in the URL after you click into the specific service:        

For example, the service ID for the Accounts production service is s2abc130b6a22.

This could be used in an email task:

{IF (services.s2abc130b6a22 , "You are on our Accounts production service", "You are currently not on our Accounts production service")}

IF/ELSE

You can use the expression {if}...{else}...{endif} if you wish to output your text options on multiple lines or paragraphs. For example:

{IF (client.clienttype = "Individual")} 

Your self assessment tax return is due {job.taxreturnfilingdate}.

Please return the required information included on the Self Assessment Tax Return checklist.

{ELSE} 

Your corporation tax return is due {job.taxreturnfilingdate}.

Please return the required information included on the Corporation Tax Return checklist.

{ENDIF}

LATEST

Picks the latest of two dates.  The two dates can be field references or date offsets.

LATEST (date1, date2)

LATEST (job.previous_task_completion, duedate-30d)

LEFT

Reduces text to a set number of characters: 

LEFT (s, n)

s = the original string of text

n = the number of places to reduce it to

For example, if we were composing an email or letter, and we wanted to just pull the first name of a contact, we could formulate the expression like this:

{LEFT (client.contact1.firstname, 1)}

If the contact's name is "Dianne", the expression will output "D".

LEN

Allows you to determine the number of characters within a value.

LEN(client.name) would return 5 if the client name was "Senta" .

This expression can also be used to confirm if a field such as UTR is the correct amount of characters

LEN(client.utr) = 10 would return true if it is 10 digits, and false if not. 

LOWER

Changes text to lower case.

LOWER (text)

LOWER ("ACCOUNTS") would show "accounts"

MAX

Returns the maximum of 2 or more values.

MAX(n1, n2, ...)

MAX (client.q1turnover, client.q2turnover, client.q3turnover, client.q4turnover) would return the highest turnover value across 4 quarters

MIN

Returns the minimum of 2 or more values.

MIN(n1, n2, ...)

MIN (client.q1turnover, client.q2turnover, client.q3turnover, client.q4turnover) would return the lowest turnover value across 4 quarters

MOD

Returns the modulus (remainder) when dividing two numbers.  

MOD(dividend, divisor)

MOD(7, 3) would return 1

MOD(9, 3) would return 0

This can be used combined with the MONTH expression in the expression filter to customise Jobs based on set months:

MOD( MONTH( job.date ), 3 ) = 1 would return correctly if the month of the job date is January/April/July/October 

To break that down a little more -

MONTH( job.date ) would return 1 in January, 4 in April, 7 in July, 10 in October

MOD(4, 3) would return 1 

MOD( MONTH( job.date ),3) = 1 will only return true in an expression filter if the job month number is 1, 4, 7 or 10

Examples for each quarter period 

MOD( MONTH( job.date ), 3 ) = 1  -  January, April, July, October

MOD( MONTH( job.date ), 3 ) = 2   -  February, May, August, November

MOD( MONTH( job.date ), 3 ) = 0   -  March, June, September, December

MONTH

Used with date placeholders to find the month.

MONTH(job.date) would return the month number of the job date, such as 3 for March.

NOT

Used to determine if criteria has not been fulfilled.

In the example below, you could add this expression to the expression filter, so that a task will only run if a specific service is not turned on.

NOT(services.serviceid)

The service ID is found in the URL after you click into the specific service:    

For example, the service ID for the Accounts production service is s2abc130b6a22.

The expression would be formulated like so:

NOT(services.s2abc130b6a22)

You could also use this filter so that a job only kicks off if someone is not a former client.

NOT(client.clientstate = "Former client")

The operator "<>" can be used as an alternative to NOT. This operator means does not equal. For example, the two expression below achieve the same outcome:

IF(client.taxnumber <> "", "We have your tax number on file.", "We do not have your tax number on file yet.")

IF(NOT(client.taxnumber), "We have your tax number on file.", "We do not have your tax number on file yet.")

OR

This expression allows you to add extra conditions to an IF expression, if you want one of several criteria to be fulfilled.

IF(OR(client.software = "Xero", client.software = "QBO"))

This expression will return true if either "Xero" or "QBO" are selected.

RAND, RANDBETWEEN, CHOOSE

Useful for generating random numbers.

RAND() gives you a random number greater than or equal to 0 and less than but not equal to 1.

RANDBETWEEN() gives you a random integer between your two values, inclusive, i.e. greater than or equal to the first number, and less than or equal to the second number

RANDBETWEEN() can be used with CHOOSE to randomly choose options.

Randomly choose between 2 options:

CHOOSE( RANDBETWEEN( 1, 2 ), "Option A", "Option B" )

Randomly select a user:

CHOOSE( RANDBETWEEN( 1, 4 ), "u12345", "u0876234", "u567891", "u0987121" )

REMOVESPACES

You can remove spaces from the field in a placeholder with this expression. 

REMOVESPACES(client.title)

ROUND

Rounds a number with decimal places to the nearest whole number.

ROUND(1.467) would return 1

ROUND(1.931) would return 2

SUBSTITUTE

You can use this expression to remove spaces of other characters or to replace some characters.

SUBSTITUTE (client.vatnumber, " ", "")

SUM

Returns the sum of 2 or more values, but also allows for missing values.

SUM (n1, n2, ...)

SUM (client.q1turnover, client.q2turnover, client.q3turnover, client.q4turnover) would return the sum of the 4 fields

TODAY

This expression returns today's date:

TODAY()

This can also be combined with DATEFORMAT. 

DATEFORMAT(TODAY, "DD MM YYYY") would show that days date in the format 01 01 2021   

DATEFORMAT(TODAY, "DD MMM YY") would show that days date in the format 01 Jan 21

TRIM

Removes excess spaces from text.

TRIM (text)

TRIM("Hello    ") would not show the spaces after Hello.

UKPAYESUFFIX

Can be used within UK based PAYE payroll jobs. 

When making payments to HMRC, the reference number will consist of an account reference number in the format 123AB12345678, and an additional four digits at the end to identify the period, two for the tax year end (e.g. tax year ending April 22 would be 22) and two for the month of the tax year (e.g. April is 01, May 02 etc)

The expression ukpayesuffix(job.date,job.period) would calculate these based on the job date.

{ukpayesuffix(job.date, job.period)} would show 0522 if the job was for payroll in August 2021

{client.payereference}{ukpayesuffix(job.date,job.period)} would show 123AB123456780522 for a payroll August 2021

UPPER

Changes text to upper case.

UPPER (text)

UPPER ("payroll") would show "PAYROLL"

YEAR

Will pull the year in 2 digit number form.

YEAR(job.date) will show "21" if the job is in 2021. 

This can be used with = as an expression filter:

YEAR(job.date) = 21 will only meet criteria if job date is in the year 2021