Using Formulas - Auto-completing Short Description

How to get Short Desc automatically filled out with data to summarise a form.

Short Desc is a system field that behaves like a text field by default. It has the special behaviour (and purpose) that its contents appear in the form register, like a summary of each form.

When the form register shows a list of forms with short descriptions then users can quickly understand what the forms contain and find forms they want. The register’s search functionality works with the short description text too, making it even more useful.

The Short Desc may be included in the filenames of output PDFs, and so it's output shouldn't be made too long. Generally, aim to have it output a maximum of around 100 or 200 characters.

Users won’t necessarily fill-out short descriptions, or do it in a systematic way, but you can set short description’s contents automatically based on other data entered into the form (like people, quantities, etc) by making it auto-complete with a formula. That saves the user time and makes short descriptions nicely consistent.

To make the Short Desc autocomplete using a formula, go into the Short Desc settings panel and turn on Autocomplete and Show formula refs.

Functions that are often useful in auto-completing Short Descriptions

CONCAT(expression1,[expression2],...)

Joins two or more text strings into one string. For example, CONCAT("one","two") returns "onetwo". 

Usage: CONCAT(TOP!A,", ",TOP!B) 

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges. The 2nd argument “ignore_empty” is always set to true (or 1, which is like true).

Usage: TEXTJOIN(", ",TRUE, TOP!A,TOP!B)

TEXTJOIN Function is quite similar to the CONCAT function. The difference between the two is that while TEXTJOIN accepts a delimiter, CONCAT doesn’t.

IF (condition, result when true, result when false)

An IF statement has two possible results. The 1st result is used if the condition is True, the 2nd result if the condition is False.

Usage: IF(TOP!A="Yes", "Please give more details.", "")

TEXT(expression,[format])

Change the way a number appears by applying formatting to it with format codes. [format] text must appear in double quotation marks.

Usage: TEXT(TOP!F,"DDDD, DD-MMM-YY")

Let’s see a few examples to understand how these functions works.

Example 1

In this example, let’s assume we want to show the Briefing Day, Date, Time, and Location from a Daily Work Pre-start Meeting form.

Using the formula CONCAT(expression1,[expression2],...), wecan join the text strings as shown below: 

CONCAT(TEXT(TOP!G,"DDDD,DD-MMM-YY"), IF(TOP!H="" ,"",CONCAT(", ",TOP!H)), ", " , TOP!I) 

Using the formula TEXTJOIN(delimiter, ignore_empty, arg_list),we can also join the text strings as shown below: 

TEXTJOIN(",",TRUE, TEXT(TOP!G,"DDDD, DD-MMM-YY"), TOP!H, TOP!I) 

Example output in the Forms Register:

Example 2

In this example, let’s assume we want to show the Briefing Day, Date, the names of the first 12 attendees, and a count of any additional names, from a Daily Pre-start Meeting form. 

Using the formula TEXTJOIN(delimiter, ignore_empty, arg_list), we can join the text strings as shown below:

TEXTJOIN(", ",TRUE, TEXT(TOP!G,"DDDD, DD-MMM-YY") , IF(COUNTA(T1!A:A)=0, "" , CONCAT( "Attendees: " , TEXTJOIN(", ",true,T1!A1:A12) , IF(COUNTA(T1!A1:A12)=COUNTA(T1!A:A), "" , CONCAT(" + ",COUNTA(T1!A:A)-COUNTA(T1!A1:A12)," more") ) ) ) )

The above formula can be written with newlines and extra spacing, such as below if that helps you to understand them. Damstra Forms ignores extra spaces and newlines, and at some point will remove them.

Example output in the Forms Register: