EZ Exporter Calculated Fields - Functions

Functions allow for greater flexibility when manipulating data. You can combine multiple functions in a single formula (e.g. use the output of a function as input to another).

add_days(datetime, num_days)

Add days to a date/time value. The output by default will be in ISO 8601 format (use the format_datetime() function to change the date/time format).

Examples:

Add 5 days to the current date and time.

add_days({{ _current_datetime }}, 5)

The above will return something like 2019-02-27T20:46:05.049382-05:00.

To subtract days, simply use a negative number.

add_days({{ _current_datetime }}, -5)

The above will return something like 2019-02-17T20:46:05.049382-05:00.

You can combine this function with the format_datetime() function to change the output to the desired format.

format_datetime(to_datetime(add_days({{ _current_datetime }}, 5)), "%Y-%m-%d")

The above formula will return something like 2019-02-27.

add_months(datetime, num_months)

Add months to a date/time value. The output by default will be in ISO 8601 format (use the format_datetime() function to change the date/time format).

Examples:

Add 2 months to the current date and time (let's assume today is 2023-05-10)

add_months({{ _current_datetime }}, 1)

The above will return something like 2023-06-10T20:46:05.049382-05:00.

To subtract days, simply use a negative number.

add_months({{ _current_datetime }}, -1)

The above will return something like 2023-04-10T20:46:05.049382-05:00.

If current date is 2023-01-31, adding 1 month will output 2023-02-28.

If current date is 2023-01-01, subtracting 1 month will output 2022-12-01.

days_diff(end_date, start_date)

Returns the number of days between two dates.

days_diff(to_datetime("09/28/2017"), to_datetime("09/25/2017"))

The above will return 3.

You can use this function with built-in fields as well.

days_diff(to_datetime({{ updated_at }}), to_datetime({{ created_at }}))

For differences in hours, minutes, or seconds, you can use:

  • hours_diff(end_date, start_date)
  • minutes_diff(end_date, start_date)
  • seconds_diff(end_date, start_date)

decode_url(url)

Decode an encoded URL.

Example:

Let's say the field landing_site contains this value:

https%3A%2F%2Fwww.myshop.com%2F%3Futm_source%3Dnewsletter%26utm_medium%3Demail

Using this formula:

decode_url({{ landing_site }})

Will output: 

https://www.myshop.com/?utm_source=newsletter&utm_medium=email

dict(list_of_value_pairs)

Convert a list of value pairs to a dictionary (key/value pairs).

Examples:

If a field called "data" has this value: [["a", 1], ["b", 2]]

Using the formula:

dict({{ data }})

Will output:

 {"a": 1, "b": 2}

More advanced example, using this formula:

dict([[i.split(":")[0], {i.split(":")[1]: i.split(":")[2]}] for i in ["a:1:123", "b:2:123"]])

Will output:

{"a": {"1": "123"}, "b": {"2": "123"}}

encode_url(url, except_chars="")

Encode a URL to RFC 2396 compliant format.

Optional Arguments:

  • except_chars - skip these characters when encoding

Example:

Let's say the field landing_site contains this value:

https://www.myshop.com/?utm_source=newsletter&utm_medium=email

Using this formula:

encode_url({{ landing_site }})

Will output: 

https%3A%2F%2Fwww.myshop.com%2F%3Futm_source%3Dnewsletter%26utm_medium%3Demail

If we don't want to encode the "/" character, we can set except_chars="/":

encode_url({{ landing_site }}, except_chars="/")

We can skip multiple characters as well:

encode_url({{ landing_site }}, except_chars="/?")

The above won't encode the "/" and "?" characters and will output something like this:

https%3A//www.myshop.com/?utm_source%3Dnewsletter%26utm_medium%3Demail

flatten_list(data)

Flattens a two-dimensional list/array. Note that all elements in the list must be lists themselves.

For example, the data might look something like this:

[[1, 2, 3], [4, 5, 6]]

 Using this function will return:

[1, 2, 3, 4, 5, 6]

float(data)

Coerce the data to a float data type in order to perform arithmetic operations.

For example, you might have a custom text metafield with the value "$14.50", which you want to multiply by 2. To do so, you'll need to strip the dollar symbol first and coerce the remaining text to a float before you can multiple it by 2:

float({{ variants.metafields.custom.cost_to_manufacture }}.replace("$", "")) * 2

format_datetime(datetime, format_str="%Y-%m-%d %H:%M:%S")

Formats a datetime value to a string. Please click here for a detailed explanation with examples.

get_attribute_value(data, attribute_name, results_separator=",", return_raw_results=False, ignore_empty_values=True, recursive=True)

Retrieve an attribute's value from a list of attributes.

Note that if the data contains nested attributes, this function will return all values that match the attribute_name across the entire data set by default (set recursive=False in the function argument to disable this behavior).

Optional Arguments:

  • results_separator - the character to use as the data separator for the results
  • return_raw_results - return the results in raw JSON format
  • ignore_empty_values - specify whether to ignore blank/empty values, set to False to include them in the results
  • recursive - recursively search nested lists for the attribute name, set to False to disable

Examples:

Retrieve just the value of the "code" attribute from the "discount_codes" data:

get_attribute_value({{ discount_codes }}, "code")

Retrieve the tax line "title" of the "line_items.tax_lines" data:

get_attribute_value({{ line_items.tax_lines }}, "title")

Retrieve the equivalent size in the US from the EU size (where variants.option1 is the EU sizing). This is basically a key/value lookup.

get_attribute_value('{"W36": "5.5", "W37": "6", "W38": "8"}', {{ variants.option1 }})

If the data you're using for the key/value lookup itself contains apostrophes/quotes, you can surround it with 3 consecutive single quotes (or 3 consecutive double quotes) to avoid conflicts.

get_attribute_value('''{"LET'S GO!": "Let's Go Now!", "Cat's Food": "Cat Food"}''', {{ variants.option1 }})

You can also "escape" them using the \ character

get_attribute_value('{"LET\'S GO!": "Let\'s Go!", "6\'5\"": "195.58"}', {{ variants.option1 }})

get_total(data, attribute_name)

Calculate the total value of a specific attribute from a list of attributes. The total is automatically rounded to 2 decimal places.

Example:

Retrieve the total quantity ordered of all line items in an order.

get_total({{ line_items }}, "quantity")

get_url_query_param_value(url, param_name)

Extract the value of the specified query parameter from the given URL.

Example:

Let's say the field landing_site contains this value:

https://www.myshop.com/?utm_source=newsletter&utm_medium=email

Using this formula:

get_url_query_param_value({{ landing_site }}, "utm_source")

The above formula will output "newsletter".

get_url_query_parameters(url)

Retrieve all the query parameters from the given URL. The ouput will be in JSON dictionary format.

Example:

Let's say the field landing_site contains this value:

https://www.myshop.com/?utm_source=newsletter&utm_medium=email

Using this formula:

get_url_query_parameters({{ landing_site }})

The above formula will output:

{"utm_source": ["newsletter"], "utm_medium": ["email"]}

get_value_by_position(data, position, default_if_none="")

Return the value from the data at the specified position (starting from 1 to pull from the beginning, or -1 to pull from the end). The data can be a normal text/string or an array/list.

Optional Arguments:

  • default_if_none - if a value at the position specified doesn't exist, return the value specified in this argument (defaults to an empty/blank value if not specified).

Examples:

Return the second element from the tags field which has the value of "red, green, blue".

get_value_by_position(to_list({{ tags }}), 2)

The data is first converted to a list/array data type and the second element, "green", is returned.

Return the fifth character from the email field which has the value of "hello@example.com".

get_value_by_position({{ email }}, 5)

This will return the character "o".

You can use this function multiple times in a formula as well.  Going back to the previous example, you might want to return the fifth and sixth characters like this:

get_value_by_position({{ email }}, 5) + get_value_by_position({{ email }}, 6)

This would return the characters "o@".

has_numbers(data)

Return True if the data contains numbers, False otherwise. This function is mainly used with conditional expressions.

Example:

"contains numbers" if has_numbers({{ shipping_address.address1 }}) else "does not contain numbers"

int(data)

Coerce the data to an integer type.

For example, you might have a line item property which is always stored as text in the Shopify backend and you'd like to add to it:

int(search_attributes({{ line_items.properties }}, "Age Now")) + 50

join_lists(list1, list2, value_separator=": ", group_separator=", ", ignore_empty_values=False, return_raw_results=False)

Combine values from two separate lists/array where you can specify a separator between values and also between group of values.

Optional Arguments:

  • value_separator - the character to use as the separator between values in the list
  • group_separator - the character to use as the seprator between group of values in the list
  • ignore_empty_values - specify whether to ignore blank/empty values, set to True to exclude them in the results
  • return_raw_results - return the results in raw JSON format

Examples:

Export the data in the format: Ttitle1: Rate1, Title2: Rate2

join_lists(to_list({{ line_items.tax_lines.title }}), to_list({{ line_items.tax_lines.rate }}))

The above formula will export the data like this:

NY State Tax: 0.04, New York County Tax: 0.00375, New York Municipal Tax: 0.045

Export the data in the format:

Title1 -> Rate1 
Title2 -> Rate2

join_lists(to_list({{ line_items.tax_lines.title }}), to_list({{ line_items.tax_lines.rate }}), value_separator=" -> ", group_separator="\n")

The above formula will export the data like this:

NY State Tax -> 0.04
New York County Tax -> 0.00375
New York Municipal Tax -> 0.045

Also chec out our blog post for more details.

len(data)

Return the length/size of the data.

Examples:

len("abcdef")

The above will return a result of 6 (i.e. the number of letters in the text).

len(to_list("apples,oranges,dragon fruit"))

The above will return a result of 3 (i.e. the number of elements in the list).

load_json_data(data, strict=False)

Convert a raw JSON string to a Python object (list or dictionary) so we can perform additional operations to it.

Example:

Convert the payment_gateway_names JSON data to a list Python object and return the last item in the list.

load_json_data({{ payment_gateway_names }})[-1]

If the payment_gateway_names value is ["paypal", "shopify_payments"], the formula above will return shopify_payments.

You can also use a static value and simply surround the JSON array data with single quotes:

load_json_data('["apple", "banana", "oranges"]')

md5_hash(data)

Returns the MD5 hash of the given data.

A sample output of an MD5 hash is ca57ba9132bd6da76c452f8f4abfa1fc.

Examples:

You may want to get the MD5 hash of your customer's email addresses to see their avatar (if they're using Gravatar).

md5_hash({{ email }})

Another potential use case is to get the MD5 hash of your product descriptions to quickly check if they're identical. Even a 1 character difference will give a completely different MD5 hash.

md5_hash({{ body_html }})

normalize_text(text)

This function will automatically convert non-ASCII characters such as Á, Ç, and ô to their closest ASCII equivalent.  If there's no ASCII equivalent, they will simply be removed from the data.

This is particularly useful if your supplier or dropshipper's systems do not support unicode/UTF-8 data.

For example, if the customer's name is Jôam Doê, you can use a formula like this:

normalize_text({{ shipping_address.name }})

In the exported CSV file, the text will be converted to Joam Doe (the special accented characters are replaced with Latin characters).

replace_multiple_characters(data, replace_map)

Replace occurrences of multiple characters from the text data based on the mapping provided.

The replace_map parameter must be in the format '{"value_to_replace": "new_value"}', for example:

'{"a": "b", "xyz": "def", "-": "."}'

Example:

A note field might contain the following text:

Fill-in the-banks.

Using this formula:

replace_multiple_characters({{ note }}, '{"bank": "blank", "-": " ", ".": "!"}')

Will output:

Fill in the blanks!

replace_text_with_html_entities(text, entity_type="mnemonic")

This function will search and replace special characters in your data with HTML entities.

For example, if your data contains Greek characters, your supplier or dropshipper may ask you to have them converted because their systems don't support them. This is actually a use case one of our customers encountered.

HTML entities can be encoded in different ways, such as mnemonic or code points. By default the function will convert special characters in the text to mnemonic:

replace_text_with_html_entities("Γ")

The above formula will convert the gamma character to: Γ

To replace them using code points instead, simply specify code in the entity_type parameter:

replace_text_with_html_entities("Γ", entity_type="code")

The above formula will output & #915;

round_down(value, decimals=0)

Round down ("floor").

Examples:

round_down(4.2)

Will return 4.0.

round_down(4.569, 2)

Will return 4.56.

round_half_up(value, decimals=0)

"Normal" rounding.

By default, will round to the nearest tenth. An optional decimals parameter is provided to specify the number of decimal places.

Examples:

round_half_up(4.5)

Will return 5.0.

round_half_up(4.565, 2)

Will return 4.57.

round_half_up({{ total_price }} * 0.49, 2)

Will return 7.14 if total_price is 14.58.

round_up(value, decimals=0)

Round up ("ceiling").

Examples:

round_up(4.2)

Will return 5.0.

round_up(4.561, 2)

Will return 4.57.

Another use case that has come up as well is to round up to the next hundred. This is also possible using a more complex formula like this:

int(round_up({{ subtotal_price }} / 100)) * 100

If the value of the subtotal_price field is 1128, the output will be 1200. If the value is 298, this gets rounded to 300.

search_attributes(data, keyword, search_key="name", result_key="value", default="", return_all_results=False, results_separator=",")

Searches a list of attributes for a specific key and returns its value.

Examples:

You have the following Line Item Properties:

subcategory: "Birthday Rings"
engraving: "Jane Doe"
birthday_message: "Happy Birthday! Hope you like this gift.

In your report, you only want the "engraving" property to be included. With the search_attributes() function, you can use this formula:

search_attributes({{ line_items.properties }}, "engraving")

This will pull just the value of the "engraving" property and put that value in the column you specified. If the order doesn't have that property, it will simply be blank in the report. If you want the default value to be something else, you can do something like this:

search_attributes({{ line_items.properties }}, "engraving", default="N/A")

In this case, if the "engraving" property doesn't exist, the value in the exported CSV will be "N/A" instead.

The search_attributes() function can be used in other dynamic fields as well such as Note/Cart Attributes and Tax Lines. By default, the function specifically searches the "name" key and returns the content of the "value" key as returned by the Shopify API. We built the search_attributes() function with flexibility in mind so it can be used in other fields where the keys are different.

To give you an example, let's say multiple taxes are applied to your orders. In this case, you'd want to search the line_Items.tax_lines field, which could look something like this:

[
    {
        "title":"NY State Tax",
        "price":"4.00",
        "rate":0.04
    },
    {
        "title":"New York County Tax",
        "price":"4.87",
        "rate":0.04875
    }
]

In your report, you may want a column called "NY State Tax Price" and "NY County Tax Price". You can use the search_attributes() function this way:

"NY State Tax Price" column

search_attributes({{ line_items.tax_lines }}, "NY State Tax", search_key="title", result_key="price", default="N/A")

"NY County Tax Price" column

search_attributes({{ line_items.tax_lines }}, "New York County Tax", search_key="title", result_key="price", default="N/A")

search_list(list_data, keyword, exact_match=False, return_all_results=True, results_separator=',')

Search a list of data and return the results.

For example, let's say we have a tags field with the value: "men, xmen, xxl, discounted"

If we want to search the field for a tag containing the word "men", we can do something like this:

search_list(to_list({{ tags }}), "men")

This will return the result: "men,xmen"

Note that in the formula, we converted the tags field to a list first before passing it to the search_list() function. Also note that by default, we're separating the results with a comma. This can be changed by specifying the results_separator parameter in the function.

Now, if we want to do an exact match (i.e. searching for "men" should only return "men" and exclude "xmen"), we can override the exact_match parameter like this:

search_list(to_list({{ tags }}), "men", exact_match=True)

set(data)

Generate a unique set of values from a list.

Example:

If you have a note field with a comma-separated list of values like this: a,b,b,c,c,d

Using this formula:

",".join(set(to_list({{ note }})))

Will output something like: a,b,c,d

Note that the ordering of the values may differ when using this function.

sha1_hash(data)

Returns the SHA-1 hash of the given data.

A sample output of a SHA-1 hash is 2aae6c35c94fcfb415dbe95f408b9ce91ee846ed.

Example:

You may want to export the SHA-1 hashes of your customer's email addresses instead of the actual email addresses to somewhat anonymize or hide them before sharing the data with a third-party while still having a way to link them to the actual addresses later (i.e. the hash value will always be the same for a given input, so you can have a mapping of the hash value to the email in another system or database).

sha1_hash({{ email }})

You may also want to convert the email address to all lowercase first before hashing it:

sha1_hash({{ email }}.lower())

You can also append additional data to the field and pass them together to the hash function:

sha1_hash({{ email }}.lower() + "topsecretstring")

sha256_hash(data)

Returns the SHA-256 hash of the given data.

Example:

Export the SHA-256 hash value of the product's HTML description so you can quickly check for changes.

sha256_hash({{ body_html }})

str(data)

Coerce the data into string/text data type so you can perform text operations.

For example, you might want to change the formatting of the variant price to use a comma instead of a dot:

str({{ variants.price }}).replace(".", ",")

sorted(data, reverse=False)

Sort a list of data.

For example, you may have a tags field that contains a comma-separated string of values like this: dog, cat, ape, bear

Using this formula:

sorted(to_list({{ tags }}))

Will output: ["ape", "bear", "cat", "dog"]

In the above formula, we also applied the to_list() function to convert a comma-separated string into a list/array data type so we can properly sort it.

To sort in reverse/descending order, we can specify the reverse=True in the function argument:

sorted(to_list({{ tags }}), reverse=True)

Which will output: ["dog", "cat", "bear", "ape"]

strip_html_tag_contents(data, html_tag_name)

Remove the contents inside a specific HTML tag.

For example, a product's body_html field might contain CSS data inside the style tag. We can strip out the CSS inside this tag using this formula:

strip_html_tag_contents({{ body_html }}, "style")

strip_html_tags(data)

Remove the HTML tags from the data.

For example, if the data field looks something like this:

<p>Hello world!</p>

Using strip_html_tags({{ field_data }}) will result in:

Hello world

This is particularly useful when trying to get the product description without the HTML code:

strip_html_tags({{ body_html }})

strip_non_numeric_chars(data, except_chars="")

Remove all non-numeric characters from the data.

An optional except_chars argument is available to make an exception for certain non-numeric characters you'd like to keep.

Examples:

Let's say you have a phone number for the shipping address field with the value:

+1 (555) 700-4328

Using the formula:

strip_non_numeric_chars({{ shipping_address.phone }})

Will output: 15557004328

To include the "+" and "-" characters, you can use the formula:

strip_non_numeric_chars({{ shipping_address.phone }}, except_chars="+-")

Which will output: +1555700-4328

strip_url_query_string(url)

Removes the query string part of the given URL (the part starting from the "?" character).

Example:

Let's say the value of variants.image_src field is this:

https://www.myshop.com/images/3329381384.jpg?v=1235324

Using the formula:

strip_url_query_string({{ variants.image_src }})

Will output:

https://www.myshop.com/images/3329381384.jpg

to_datetime(datetime_string, dayfirst=False, yearfirst=False, isoformat=False)

Convert a date/time text to a datetime "object" so we can perform datetime operations such as adding/subtracting days or changing the formatting.

If the datetime string is ambiguous (e.g. 01/05/10) where it can't be determined whether the first value is the day or the month or the year, the function will assume the order is MM/DD/YY.  Set the value of dayfirst=True or yearfirst=True to explicity specify it.

If the datetime string is in ISO-8601 format, set isoformat=True. This is useful if you’re figuring out the date of a specific day of a given week (e.g. “2022-W01-2” will return the Tuesday’s date of the first week in year 2022).

Examples:

Convert the created_at date where the value is "2017-09-28" to display "Thursday September 28, 2017"

format_datetime(to_datetime({{ created_at }}), "%A %B %d, %Y")

The data is converted first to a datetime object using this function and the value is then passed as an argument to the format_datetime() function to do the conversion.

Return Saturday's date of the same week as the created_at date.

to_datetime(format_datetime(to_datetime({{ created_at }}), "%Y-W%W-6"), isoformat=True)

The above formula converts the created_at field value to an ISO-8601 datetime string format first, which includes the week number and the specific day of the week as an integer (where 1=Monday and 7=Sunday, in the example above we specified "6" which corresponds to Saturday).  It's then converted to a datetime object that will return the date for Saturday of that week.

to_list(data, separator=",")

Convert text data into a list/array.

For example, a tags field data may look something like this: "men, xxl, sale"

Applying this function like this:

to_list({{ tags }})

Will return this result: ['men', 'xxl', 'sale']

This is useful when we want to do exact searches against the data (see search_list() function).

An optional parameter separator can also be specified if we want to separate the data based on a different character other than a comma. For example, a name field may have the value "The name is Bond" and we want to split up each word in the text value. We can then do something like this to separate the text by the space character:

to_list({{ name }}, separator=" ")

This will return: ["The", "name", "is", "Bond"]

Related Articles:

App: EZ Exporter

Tags: advanced features