New Column

Make sure you first read the intro to no-code exploration steps

The New Column step is a powerful tool that, as the name implies, always creates new columns in your table (without touching the existing ones, or adding/removing rows).

It can be used to:

  1. Run calculations based on related data: e.g. for each Account, create a column with that account's "Number of Users". This is exactly what we did in the example above!

  2. Add related columns from hasOne relations: e.g. for each City, add its Country's name

  3. Use Excel-like expressions/formulas: e.g. to multiply two numeric columns

Technical users will feel tempted to think of this in terms of database joins. While Supersimple technically compiles to a SQL query (or several) in the end, we find it helpful to instead focus on the step's semantic intent instead of the implementation detail.

The important thing to note is that the number of rows in the table does not change when applying this step.

Technically, the base table left-joins on the new column's values.

The Based on dropdown lets you choose which relation to use for the calculation/aggregation. In this case, we can choose from the relations that the Account data model has. One of those is called Users, and references the User data model.

You can use "New Column" to aggregate up data from any hasMany relations.

You can also add multiple Summarizations to calculate multiple columns. Filters are applied to any of the related data before aggregating it up. This step would create two new columns, both only considering Role=Admin users:

  • "Number of Users" (a number: how many such rows there are)

  • "First user signup date" (a date: the earliest "Created At" timestamp from the related Users data)

When selecting a hasOne type of relation from the "Based on" dropdown, you won't need to summarize the data.

Instead, you can choose one or more columns to just "pull in" the values for, creating a new column for each. For example, when starting from Accounts and selecting their Onboarding response related data (which every Account has just 0 or 1 row for), we can add the "Revenue range" and "Number of employees" survey data:

We now have two new columns in our table. Wherever the Account doesn't have an Onboarding Response (remember: hasOne relations have either zero or one matching rows), these fields will be shown as empty (indicated by a dash).

Custom formulas

You can also use Excel-like custom formulas to do some more advanced things. To use a Formula, click the fx button:

Our Formula editor will guide you through writing any formulas using its built-in autocomplete and documentation. You can also the keyboard to navigate the autocompletion suggestions and hit Enter or Tab to accept them.

Custom formulas can reference any other existing column using the prop("Column name") function, as well as variables using the variable("Variable name") function.

Read also

Last updated