Skip to main content

Transforming your data

You can do complex transformations on your data when it passes between channels.

Quickbase Pipelines uses the jinja2 templating language for data transformation. Pipelines supports all of the jinja2 templating filters or options.

Note: The formulas used as part of jinja2 are different from the Quickbase formula language.

Where can I use a jinja expression?

You can enter a jinja expression in most fields, except for a few situations like accounts, or in the case of the table selection in Quickbase. Just start entering a jinja expression, like {{a.hello}} and the field will begin to check and format your code. For example:

Always test your pipeline after you make changes to make sure that your data is passed and formatted as you expect. For more on using jinja see About jinja.

You can add:

You can also:

Working with date and time

You can use jinja expressions to work with date and time.

Adding the current date and time

You can set the current date and time in a field using {{time.now}}

To set the time at the beginning of today (00:00:00) use {{time.today}}

Using now to generate a full date and time

Here's a way to display a full date and time.

{% now "%B %-d, %Y %-I:%M" %}

Converting to a future or past date

To add a date in the future or in the past, you can add {{time.delta}} to {{time.now}} or {{time.today}} This is particularly helpful for setting a due date for a task.

Yesterday's date

Display yesterday's date

{{(time.today - time.delta(days=1))|date_ymd}}

Convert a string to a datetime object

You can use time.parse() to convert the string to a datetime object and then format it:

{{ time.parse("2022-10-12T17:26:42Z").strftime("%a, %B %d") }}

Convert UNIX timestamp to UTC format

The UNIX timestamp is a way to track time as a running total of seconds. The UNIX timestamp is the number of seconds between a particular date and the UNIX Epoch. This point in time technically does not change no matter where you are located on the globe.

Example of usage:

input : {{1608112432| timestamp_to_time}}

output: 12-16-2020 01:53 AM

Using now to create a copyright footer

This little bit of code can create a footer with a copyright symbol:

© {% now "%Y" %}

Setting a relative time

You can use {{time.delta}} to get relative times. The available options are years, months, weeks, days, hours, minutes, and seconds. Note that all of them are plural, as opposed to a singular year, or month, which are used for absolute time.

For example, to get the same time one hour from now you can use:

{{time.now + time.delta(hours=1)}}

To get a time at the beginning of the day one week from now, use:

{{time.today + time.delta(days=7)}}

Months and years are based on the current date and how many days are in a given month. Using {{time.now + time.delta(months=1)}} results in the same date one month from now, unless the next month has fewer days than the current day of the month. Then, it results in the last day of next month.

For example, if today is 2020-01-27 and we add time.delta(months=1), the result is 2020-02-27, but if today is 2020-01-31, since February 2020 has 29 days, the result will be 2020-02-29.

Setting an absolute time

You can use {{time.delta}} to get an absolute date or time in a year, month, day, hour, minute, or second from now.

For example, to get the same date and time as now in 2016 you can use:

{{time.now + time.delta(year=2016)}}

Adding a time zone filter

All dates and times in Pipelines are in UTC. In general, you won’t need to do any time zone conversion yourself if you map between Date/Time fields, even between different channels.

If you want to print the time in a text field in the user’s time zone, you can use the time zone code.

For example:

The time in Japan is: {{ time.now | timezone('JP') }}

Tip: To find your time zone code, see this Wikipedia article.

Set a specific timezone

You can also set the time to a specific time zone.

{% set timezone = 'Europe/Bratislava' %}

Specifying date formats

Warning: Do not use the filter below to transform dates between date fields in two different channels. The date format of the channel will be handled automatically by Pipelines. This filter is intended for converting dates into strings for text fields.

Dates displayed in Pipelines are usually formatted as a timestamp. If you need to format the date in another format or display just the date options, you can use code to specify the date format, including

  • date_ymd
  • date_dmy

  • date_mdy

SAMPLE DATE CODE RESULT
{{time.now|date_ymd}} 2019-12-15
{{time.now|date_mdy}} 12-15-2019
{{time.now|date_dmy}} 15-12-2019

You can also specify the symbol used to separate the dates or specify no symbol. The default symbol is a dash.

SAMPLE DATE SYMBOL CODE RESULT
{{time.now|date_ymd('')}} 20191215
{{time.now|date_mdy('/')} 12/15/2019
{{time.now|date_dmy('.')}} 15.12.2019

Specifying flexible date formats

For flexible date formats, use the .format function. For example, to return a string representing the previous month, for example, 202008 in the form YYYYMM, use this code:

{{ "{:%Y %m %d}".format(time.now - time.delta(months=1))}}

Where %Y is the year with century as a decimal number, %m is the month as a zero-padded decimal number, and %d is the day of the month as a zero-padded decimal number.

In this example, the output would be:

Inserting a partial date into a field

For example, if you wanted to insert only the YYYYMM into a field (not the date), you could use this code:

{{ time.now.strftime('%Y%m') }}

To clear a date

To clear a date use the jinja expression {{CLEAR}} in your step. You can also use {{CLEAR}} to explicitly clear out a field, typically in an update step.

Parse a date time value for API

This code parses a date time value from a Quickbase step into a format that the restful API can consume.

{{a.created_at.strftime('%Y-%m-%dT%H:%M:%S')}}

 

Check to see if a field is blank

To check if something is blank, try this pattern:

{% if a.field_label is none %}TRUE{% endif %}

You could either change the resulting word to FALSE or use is not none instead of is none.

Numeric calculations and conversions

You can transform data in your pipeline using numeric calculations:

Calculation needed Sample code
Incremented by 1 {{a.count + 1}}
Discounted value {{a.sum - (a.sum * a.discount_percents / 100)}}

Some channels return numbers as text fields, which will result in an error if you try to use them for calculations. You can convert text to an integer or a floating-point number by adding |int or |float.

Calculation needed Sample code
Convert text to integer and add 1 {{a.text_integer|int + 1}}
Convert text to floating number and multiply by 1.35 {{a.text_float|float * 1/35}}

If-Then-Else statements

You can use code instead of the user interface to create If-Then-Else conditions for your pipeline.

The if statement in Jinja is comparable with the Python if statement. In this example, if the user variable is defined, we can iterate over the users and extract the username and use it to test to see if a variable is defined, not empty or not false:

{% if users %}

<ul>

{% for user in users %}

{% endfor %}

</ul>

{% endif %}

Another example:

{% if kenny.out%}

Kenny is out today.

{% elif kenny.quit%}

Kenny quit today!

{% else %}

Kenny is working --- so far

{% endif %}

Transforming text

You can transform text using code. For example, you can use the pipe character | in the curly brackets plus the word “upper” to make the link text uppercase.

You can also use the built-in filter “upper” to make a string uppercase:

Adding |upper to {{aname}} to get {{aname|upper}} would transform Quickbase Pipelines to Quickbase PIPELINES.

Combine two text fields

You can combine two text fields:

{{a.field_one ~ a.field_two}}Managing HTML in your pipeline

Removing HTML tags

Some channels return fields containing HTML tags. If you want to transfer these fields to a channel without HTML support, you may need to extract just the text. There are two ways to remove HTML tags, one that removes all tags and formatting, and one that converts HTML to Markdown.

Action needed sample code
Remove all HTML tags and formatting {{a.body|text}}
Convert text to markdown {{a.body|html2text}}

Converting special characters

Some channels expect HTML input, but some special characters are invalid in HTML. To convert the characters &, <, >, ‘, and ” to HTML-safe sequences, you can use the code: “|escape”

For example, if a company name is Johnson & Son, you can use

{{a.name|escape}} to change the name to

Johnson &amp; Son

Email conversions

Email from different providers often use unique code and formatting. If you want to perform a transformation, for example, to extract only the body of an email from a specific provider, your best approach would be to use a regular expression to capture that content.

Calculate the number of days between two dates

To calculate the number of days between two dates, you can use::

(a.manual_end_date - a.manual_start_date).days

Adding items to a list field

If your channel has a name field and you want to add a new name without replacing existing names, use “|append”

For example:

{{a.name|append('new_name')}}

You can also append multiple tags:

{{a.name|append('new_name1', 'new_name2')}}

Summarizing search results in a single field

You can summarize information of a search pipe into a single field. To summarize the information from the search, delete the Loop step that is automatically added. Add other steps if there are items in the array, or, if they're empty no other steps are needed.

For example, to send a summary using Gmail, use the For-Each loop in the body of the Send an Email pipe.

Sample code:

# {{a|count}} messages received for the last day #

{% for m in a %}

## {{m.subject}}

### from: {{m.from}}

### to: {{m.to}}

{{m.body}}

***

{% endfor %}

This results in an email with a list of all the emails found from the Gmail Search pipe.

In this example, the {{a|count}} code returns the number of messages received on the last day.

The temporary variable m: {% for m in a %} means that future references to a single message will use m instead of a, such as {{m.body}}

Clear a field in an Update pipe

Pipelines ignores fields with empty values to avoid clearing a remote field accidentally. If you want to clear this field in an Update pipe, you’ll need to specify the value {{CLEAR}}.

You can combine the CLEAR code with conditions. For example:

{% if a.priority != 'no priority' %}{{a.priority}}{% else %}{{CLEAR}}{% endif %}

If a.priority is different from 'no priority' then a.priority will be set in the field. Otherwise, the field will be cleared.

Related articles

Top