Phocas has a new user documentation site. This site will be retired soon.

Transform a column in Designer

Owned by Helen Gosper
Last updated: Dec 03, 2023 by Denise McGettigan
3 min read

After you add a data item to a stream in a database design, you can transform the data columns in the following ways:

  • Expression

    • Calculate - Add a column based on a calculation, which you create by entering a simple formula. Available for numeric data.

    • Concatenate - Join two or more of the existing columns together. Available for text data.

    • Duplicate - Copy an existing column.

  • If-Then - Add a column based on a conditional statement using if-then-else logic.

If you need to add a column type that is more complex, a Phocas consultant can help you.

Create an expression (to add a column, or concatenate or duplicate columns)

  1. In the Stream section of the Design tab, click the green plus at the left of the data stream and select Expression.

  2. In the Calculate window, edit the default column title, if required.

  3. Enter the applicable expression:

    • To calculate: Add, subtract, multiply or divide two or more columns using standard mathematical notation (+, -, * and /) with brackets, if required. For example, value-cost.

    • To concatenate: Enter the names of the existing columns that you want to concatenate, joined by a plus symbol. For example, Branch+Region. You can also include other text in the concatenation in single quotes.  For example, to join Branch and Region together separated by a hyphen, enter the expression Branch+'-'+Region.

    • To duplicate: Enter the name of one of your existing columns.  

  4. Click Save. A new column displays to the right of your existing raw data columns. Proceed to map the new column in the usual way.

Create an if-then statement (to add a column)

The If-Then option is useful for creating advanced 'transforms' without needing to have SQL knowledge. These columns are useful for many reasons, such as:

  • To clean up source data, where a name has been entered inconsistently throughout.
    If [Name] Is equal to ‘Joe Smith Then ‘Joseph Smith’
    If [Name] is equal to ‘J Smith' Then ‘Joseph Smith’

  1. In the Stream section of the Design tab, click the green plus button at the left of the data stream and select If-Then.

  2. Configure the if-then statement as required (see examples below) and click Save.

Example - Create a Country column using part of a country name (taken from an account code prefix).

Example - Create groups on the fly using keywords in a product name. Use semi-colons to separate multiples.