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 the jinja2 templating language 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 expressiion, like {{a.hello}} and the field will begin to check and format your code. For example:

More about jinja

For more on using jinja see About jinja.

Some basics

Always test your pipeline after you make changes to ensure that data is passed and formatted as you expect.

You can add:

Numeric calculations and conversions
If-Then-Else statements using code
Adding tags to a list field

You can also:

Transform text
Manage HTML data by adding tags, removing tags, and converting special characters
Summarize search results into a single field
Clear the value in a field

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 %}

Working with dates and times

We have a whole section on Working with dates and times.

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 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.

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 that does not support HTML, 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}}`

Markdown fields

Some channels expect HTML in some of their fields. If you see the M symbol this means that we have added markdown support to this field, and any markdown will be automatically converted to HTML.

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 & Son

Email conversions

Email from different providers often use unique code and formatting. If you want to preform 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 to a single field. To summarize the information from the search, you need to remove the For-Each loop by clicking on the X. Then you can add markdown syntax to achieve a formatted summary of all results.

For example, to send a summary via 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 will result in an email containing 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 in 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.

Did this page help?