Use the CONCAT expression in a calculated field

Transcript

In this video, you’ll learn what the CONCAT expression does and how to create a CONCAT text expression in a calculated field.

The CONCAT expression allows you to chain words or fields together in one field. For example, your organization wants to combine the project reference number, the project owner name, and the project name into one field in a report, creating a customized project ID. That can be done by using the CONCAT expression and the appropriate Workfront fields.

The CONCAT expression includes the name of the expression and the data points needed to create the desired information in the calculated field.

There is no limit to the number of strings you can use in a CONCAT expression as long as it is written correctly. However, Workfront recommends that you try to keep the expression as easy to maintain as possible.

Let’s build the unique project ID using the CONCAT expression.

In the custom form, select Calculated in the field options.

Give the field a name, and if needed, fill in the label instructions.

Next, decide what field format to use. If the field is calculating some type of number, it’s recommended that a number format is used, especially if you’re planning to use this field in a view and to sort information by this field. However, the system will not prevent you from using other formats if you wish.

In this case, leave the format as text.

Click in the calculation box where you can either start typing in the expression, if you already know how to build it, or search for and plug in the needed elements. We’re going to use the latter option.

First, find the expression in the expression search box, in this case, CONCAT. When found, click it to add it to the calculation box.

The expression is now ready for the data points and/or strings that need to be chained together. Remember, in this example, your organization wants to combine the project reference number, the project owner name and the project name. So the first data point or string to place in the CONCAT expression is the project reference number.

Go to the field search box and type the name of the field.

When it appears in the list, click it to put it in the calculation box.

The system automatically starts adding the necessary formatting to make the calculation work in Workfront.

The next data point needed is the project owner’s name.

Once again, go to the field search box, type in the name of the field, and click it to put it in the calculation box.

There is one change you need to make to get the project owner’s name instead of the auto-generated ID number Workfront assigns to each user. We have to remove ID next to owner, place a period, and then type in name, making sure that the curly brackets encase it.

This is called referencing and tells Workfront to pull the project owner’s name into the field.

The only additional thing needed is a comma between the two data points.

For more information on calculation structure, see the learn data expression structure tutorial.

From here, you can continue to add the desired data points or strings needed for the calculated field, and the system will continue to put in the appropriate formatting.

Once entered, the expression for the custom project ID looks like this in the calculation box.

Now, if the expression is saved as is, the information pulled into the field will be strung together as one word, making it difficult to understand. So to make the information easier to read, add dashes or spaces.

To do that, add an opening quotation mark, a dash, a closing quotation mark, and a comma between each data point within the expression.

Once entered, it looks like this in the calculation box.

If the expression looks correct and an error message doesn’t appear, save the field and the custom form.

From here, wherever that custom form is attached, the system can start calculating the unique ID, and you can use that information in many ways, like a view in a report. -