EZ Exporter Calculated Fields - Functions

Functions provide great flexibility while keeping the formula simple.

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.

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)

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)

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.

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 - include blank/empty values in the results

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

get_total(data, attribute_name)

Calculate the total value of a specific attribute from a list of attributes.

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

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"

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

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 special accented characters such as Á, Ç, and ô to their closest ASCII equivalent.

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_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 & Gamma;

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

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.

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.

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.

round_up(4.561, 2)

Will return 4.57.

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_separatorparameter 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)

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_url_query_string(url)

Removes the query string part of the given URL (the part after 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_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 below).

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']


App: EZ Exporter

Tags: advanced features