About Jinja

Quickbase Pipelines uses Jinja templating to enable dynamic expressions, filters, and access to variables, by providing Python-like expressions while ensuring that the templates are evaluated in a sandbox.

Jinja is a modern and designer-friendly templating language for Python. It is a text-based language and thus can be used to generate any markup as well as source code.

Pipelines is currently supporting Jinja 2.11.2, for various formatting within pipeline steps.

Resources for Jinja

Test expression in realtime:

https://cryptic-cliffs-32040.herokuapp.com/

For syntax and semantics:

https://jinja.palletsprojects.com/en/2.11.x/templates/

Statements

  • For statements use {% ... %}

Expressions

  • For expressions use {{ ... }} to print to the template output

Comments

  • For comments not included in the template output use {# ... ##}

Variables

What attributes a variable has depends heavily on the application providing that variable.

Dot

You can use a dot (.) to access attributes of a variable. The following examples do the same thing, a is an object with hello as a property:

  • {{ a.hello}}
  • {{ a['hello'] }}
  • {{a.get('hello')}}

For example, when you drag the variable from the popup menu, Pipelines uses variables with (.) operator

Custom Variables

You can set a custom variable using the expression:

{% set iterated = false %}

This expression will allow you to store dummy variables.


Data Types

  • Boolean - represents the values true and false
  • Integer - Integers are whole numbers without a decimal part.
  • Floating point - numbers can be written using a ‘.’ as a decimal mark
  • Sequence [‘a’] everything between the two brackets is a list.
  • String -a sequence of characters, either as a literal constant or as some kind of variable
  • Tuple - can contain two values of different data types
  • Mappings - {'dict': 'of', 'key': 'and', 'value': 'pairs'}

Logic Conditions

IF

Example

{% if  some_condition %} {{do something }}
{% elif  some_other_condition %} {{do something }}
{% else %} {{do something }}
{% endif %}

LOOP

Example

{% for item in ['a', 'b', 'c']%}
{{ loop.index }} {{item}} “\n”
{% endfor %}
output: 1 a \n 2 b \n 3 c \n

IF/THEN/ELSE

Examples of IF/THEN/ELSE:

{% if a.value %}{{a.value}}{% else%} {{CLEAR}}{% endif %}

Or

{% if a.value is none %}{{CLEAR}}{% else%}{{a.value}}{% endif %}

Example of accounting for Null and Zero values:

{% if a.value > 0 %}{{a.value}}{% elif a.value == 0 %}0 {% else %}{{CLEAR}}{% endif %}

Loop over each item in a sequence

To display a list of users provided in a variable called users:

{% for user in users %}
{{ user.username }}
{% endfor %}

Currently unsupported features

  • Dicts are not ordered
  • Unsupported extensions. Filter(see below) or use the loop.index instead
    • {% continue %}
    • {% break %}

Inside of a for-loop block, you can access some special variables:

Variable Description
loop.index

The current iteration of the loop. (1 indexed)

loop.index0

The current iteration of the loop. (0 indexed)

loop.revindex

The number of iterations from the end of the loop (1 indexed)

loop.revindex0

The number of iterations from the end of the loop (0 indexed)

loop.first

True if first iteration.

loop.last

True if last iteration.

loop.length

The number of items in the sequence.

loop.cycle

A helper function to cycle between a list of sequences. See the explanation below.

loop.depth

Indicates how deep in a recursive loop the rendering currently is. Starts at level 1

loop.depth0

Indicates how deep in a recursive loop the rendering currently is. Starts at level 0

loop.previtem

The item from the previous iteration of the loop. Undefined during the first iteration.

loop.nextitem

The item from the following iteration of the loop. Undefined during the last iteration.

loop.changed(*val)

True if previously called with a different value (or not called at all).

Loop Cycle

{% for row in [1,2] %}
{{ loop.cycle('odd', 'even') }} -  {{row}} row
{% endfor %}
output: odd - 1 row even - 2 row

Filters

Variables can be modified by filters. Filters are separated from the variable by the pipe symbol ( | ) and may have optional arguments in parentheses. Since multiple filters can be chained, the output of one filter is applied to the next. You can also filter like this for the entire block:

{% filter upper%}This text becomes uppercase{% endfilter%}

Some of the filters QuickBase currently supports:

Term Definition
abs
(x, /)

Return the absolute value of the argument.

base64_encode(value) Encode a string using Base64 and return the encoded bytes.

Example of usage: {{‘Test string’ | base64_encode}} output: VGVzdCBzdHJpbmc=
base64_decode(value)

Decode the Base64 encoded string and return the decoded bytes.


Example of usage: {{‘VGVzdCBzdHJpbmc=’ | base64_decode}} output: Test string

capitalize
(s)

Capitalize a value. The first character will be uppercase, all others lowercase.

default
(value, default_value='', boolean=False)

If the value is undefined it will return the passed default value, otherwise the value of the variable:

{{ my_variable|default('my_variable is not defined') }}

{{ QB_VARIABLE | default(42) }}

Note: If the variable ‘QB_VARIABLE’ is not defined, the value used will be 42.

escape
(s)

Convert the characters &, <, >, ‘, and ” in strings to HTML-safe sequences. Use this if you need to display text that might contain such characters in HTML. Marks return value as markup string.

first
(seq)

Return the first item of a sequence.

{{ [1,2,3] | first}} output: 1

float
(value, default=0.0)

Convert the value into a floating point number. If the conversion doesn’t work it will return 0.0. You can override this default using the first parameter.

For example, the float and int are just added like so: {{a.value|float}} and {{a.value|int}}. Replace would be {{a.value|replace("$","") for example. This would remove a $ sign without leaving a space.

format
(value, *args, **kwargs)

Apply the given values to a printf-style format string, like string % values.

From JSON  
int
(value, default=0, base=10)

Convert the value into an integer. If the conversion doesn’t work it will return 0. You can override this default using the first parameter. You can also override the default base (10) in the second parameter, which handles input with prefixes such as 0b, 0o and 0x for bases 2, 8 and 16 respectively. The base is ignored for decimal numbers and non-string values.

For example, the float and int are just added like so: {{a.value|float}} and {{a.value|int}}. Replace would be {{a.value|replace("$","") for example. This would remove a $ sign without leaving a space.

join
(value, d='', attribute=None)

Return a string which is the concatenation of the strings in the sequence. The separator between elements is an empty string per default, you can define it with the optional parameter:

{{ [1, 2, 3]|join('|') }} output: 1|2|3
{{ [1, 2, 3]|join }} output: 123

It is also possible to join certain attributes of an object:

{{ users|join(', ', attribute='username') }}
last
(seq)
Return the last item of a sequence
length
(obj, /)

Return the number of items in a container.

list
(value)

Convert the value into a list. If it was a string the returned list will be a list of characters.

lower
(s)

Convert a value to lowercase.

map
(*args, **kwargs)

Applies a filter on a sequence of objects or looks up an attribute

{{ titles|map('lower')|join(', ') }}

max
(value, case_sensitive=False, attribute=None) only in the latest

Return the largest item from the sequence.

{{ [1, 2, 3]|max }} ouput: 3

md5
(text)

Returns MD5 hex-digest string generated from the text input.

Example usage:

{{ "some text" | md5 }}
output: 552e21cd4cd9918678e3c1a0df491bc3
min
(value, case_sensitive=False, attribute=None)
only in the latest

Return the smallest item from the sequence.

{{ [1, 2, 3]|min }} output: 1

random
(seq)

Return a random item from the sequence.

reject
(*args, **kwargs)

Filters a sequence of objects by applying a test to each object, and rejecting the objects with the test succeeding.

If no test is specified, each object will be evaluated as a boolean.

Example usage:

{{ numbers|reject("odd") }}

replace
(s, old, new, count=None)

Return a copy of the value with all occurrences of a substring replaced with a new one. The first argument is the substring that should be replaced, the second is the replacement string. If the optional third argument count is given, only the first count occurrences are replaced:

{{ "Hello World"|replace("Hello", "Goodbye") }} output: Goodbye World

round
(value, precision=0, method='common')

Round the number to a given precision. The first parameter specifies the precision (default is 0), the second the rounding method:

  • 'common' rounds either up or down
  • 'ceil' always rounds up
  • 'floor' always rounds down

 

  • If you don’t specify a method 'common' is used.

{{ 42.55|round }}output: 43.0

select
(*args, **kwargs)

Filters a sequence of objects by applying a test to each object, and only selecting the objects with the test succeeding. If no test is specified, each object will be evaluated as a boolean.

Example usage:

{{ numbers|select("odd") }}
{{ numbers|select("odd") }}
{{ numbers|select("divisibleby", 3) }}
{{ numbers|select("lessthan", 42) }}
{{ strings|select("equalto", "mystring") 

 

{%- for column in [1, 2, 3]| select("odd")%}
{{column}}
{%- endfor %}
output: 1 and 3
sha1
(text)

Returns SHA1 hex-digest string generated from the text input.

Example usage:

{{ "some text" | sha1 }}
output: 37aa63c77398d954473262e1a0057c1e632eda77
sha224
(text)

Returns SHA224 hex-digest string generated from the text input.

Example usage:

{{ "some text" | sha224 }}
output: 9e13fe8526578d5792074d5bcaa9f222df928c7c158804ac70e904f8
sha256
(text)

Returns SHA256 hex-digest string generated from the text input.

Example usage:

{{ "some text" | sha256 }}
output: b94f6f125c79e3a5ffaa826f584c10d52ada669e6762051b826b55776d05aed2
sha384
(text)

Returns SHA384 hex-digest string generated from the text input.

Example usage:

{{ "some text" | sha384 }}
output: cc94ec3e9873c0b9a72486442958f671067cdf77b9427416d031440cc62041e2ee1344498447ec0ced9f7043461bd1f3
sha512
(text)

Returns SHA512 hex-digest string generated from the text input.

Example usage:

{{ "some text" | sha512 }}
output: e2732baedca3eac1407828637de1dbca702c3fc9ece16cf536ddb8d6139cd85dfe7464b8235b29826f608ccf4ac643e29b19c637858a3d8710a59111df42ddb5
                                        
slice
(value, slices, fill_with=None)

Slice an iterator and return a list of lists containing those items. Useful if you want to create a div containing three ul tags that represent columns:

{%- for column in [1,2,3,4]|slice(2)%}
       {{column}}
{%- endfor %}
output: [1, 2] [3, 4]
sort
(value, reverse=False, case_sensitive=False, attribute=None)

Sort an iterable


{%- for column in [4,2,3,4]|sort%}
       {{column}}
{%- endfor %}
output: 2 3 4 4

{% for user users|sort(attribute="age,name") %} ...{% endfor %}

string
(object)

Make a string unicode if it isn’t already. That way a markup string is not converted back to unicode.

striptags
(value)

Strip SGML/XML tags and replace adjacent whitespace by one space.

{{ "<html>foo"| striptags}} output: foo

sum
(iterable, attribute=None, start=0)

Returns the sum of a sequence of numbers plus the value of parameter ‘start’ (which defaults to 0). When the sequence is empty it returns start.

{{ [1,2,3]|sum }} output 6

It is also possible to sum up only certain attributes:

Total: {{ items|sum(attribute='price') }}

timestamp_to_time (value)

Converts UNIX timestamp to UTC Date Time format.

 

Example of usage:

input : {{1608112432| timestamp_to_time}}

output: 12-16-2020 01:53 AM

title
(s)

Return a titlecased version of the value. I.e. words will start with uppercase letters, all remaining characters are lowercase.

{{ "hello world" | title }} output: text:Hello World

tojson
(value, indent=None)
only in the latest

Dumps a structure to JSON so that it’s safe to use in <script> tags. It accepts the same arguments and returns a JSON string. Note that this is available in templates through the |tojson filter which will also mark the result as safe. Due to how this function escapes certain characters this is safe even if used outside of <script> tags.

The following characters are escaped in strings:

  • <
  • >
  • &
  • '
trim
(value, chars=None)

Strip leading and trailing characters, by default whitespace.

truncate
(s, length=255, killwords=False, end='...', leeway=None)

Return a truncated copy of the string.

{{ "foo bar baz qux"|truncate(9) }} output: foo...

unique
(value, case_sensitive=False, attribute=None)
only in the latest

Returns a list of unique items from the given iterable.

{{ ['hello', 'world', 'helloworld', 'HelloWorld']|unique|list }} output: ['hello', 'world', 'helloworld']

upper
(s)

Convert a value to uppercase.

urlencode
(value)

Quote data for use in a URL path or query using UTF-8.

wordcount
(s)

Count the words in that string.

List of Builtin Tests¶

{% if variable is defined %}
{{variable}}
{%else %}
UNDEFINED
{% endif %}

Here are the tests that we support

 

TEST Description
Boolean

Return true if the object is a boolean value.

Defined Return true if the variable is defined.
Divisibleby Check if a variable is divisible by a number.
Even Return true if the variable is even.
Float Return true if the object is a float.
Ge Whether or not the number is greater than or equal to.
Gt Whether or not the number is greater than.
In In a sequence
Integer If an integer
Le Less than or equal to
Lt Less than
Mapping Return true if the object is a mapping (dict etc.).
Ne Not equal
None True if the variable is none.
Number True if the variable is a number
Odd True if the variable is odd
Sequence Whether or not the variable is a sequence
String  
True  

Working with JSON

from_json

If you have a string that you need to load as JSON...

field {"foo":[1,2]}$ore

...then use the from_json expression

{% set json_field=a.field\ from_json %){{json_field('foo')}}

To_json(value, indent=None)

Dumps a structure to JSON so that it’s safe to use in <script> tags. It accepts the same arguments and returns a JSON string. Note that this is available in templates through the |_ filter which will also mark the result as safe. Due to how this function escapes certain characters this is safe even if used outside of <script> tags.

The following characters are escaped in strings:

<

>

&

'

This makes it safe to embed such strings in any place in HTML with the notable exception of double quoted attributes. In that case single quote your attributes or HTML escape it in addition.

The indent parameter can be used to enable pretty printing. Set it to the number of spaces that the structures should be indented with.

Note that this filter is for use in HTML contexts only.

Formatting and Escaping

It is sometimes desirable – even necessary – to have Jinja ignore parts it would otherwise handle as variables or blocks. For example, if, with the default syntax, you want to use {{ as a raw string in a template and not start a variable, you have to use a trick.

The easiest way to output a literal variable delimiter ( {{ ) is by using a variable expression:

{{ '{{' }}

{% raw %}
{% for item in seq %}
{{ item }}
{% endfor %}
{% endraw %}

Function Calls

You can use macros to avoid repeated tasks

{% macro render_dialog(title, description='dialog') -%}
{{title}} - {{description}}
{{ caller() }}
{%- endmacro %}
{% call render_dialog('Hello World') %}
{% endcall %}
output: Hello World

Example - audit trail creation process

Here are two examples of using $prev to monitor changes to any specific fields on a Projects table. In these examples we check to see which of the fields are different from their previous value, then create an Audit Log record that’s related to that project:

Example - searching for a field value:

In our example, we have a field name: {{a.value}}. In your search jinja, you would re-write it as {{a.$prev.value}} or if the field is {{b.address_field}} it would be {{b.$prev.address_field}}.

Note: In our example $prev goes after the initial letter, so a. or b. etc.

Example - workaround for address fields with spaces

Address fields in Quickbase using the ‘parent’ label have the safe field name applied when creating the keys for address sub-fields in the pipelines schema. This means that any time a you try to access a sub-field of an address field that contains spaces in the name, the jinja expressions become invalid due to the spaces, or may get interpreted as a jinja expression that differs from the user’s intent.

To work with this behavior in a jinja expression, we suggest you follow these guidelines:

Properties of objects in jinja can be accessed either with the formats of {{a.my_field}} or through {{a['my_field']}}, which supports spaces in the property name.

Example - Sum of two numeric field values in another field

In this example, we perform a sum of two numeric field values in another field:

{{a.field_1 + a.field_2}}

JINJA TIPS

  • Pipelines ignores fields with empty values in pipe’s mapping, i.e. it does not send them to the remote service, to avoid clearing a remote field accidentally. This, however, is a problem when you want to explicitly clear this field in an Update pipe.
  • Solution: use template value {{CLEAR}} to clear a fieldthis is not bold in the rendered value