This blog post is part of a series designed to demystify the process of understanding and writing your own business rules.

There are a handful of business rule functions that perform similarly but have slightly different designs and purposes. Today’s post focuses on those similar business rule functions and how to decide which one will output the information you need.

CONCATENATE, JOIN, or JOINNB

These three functions allow you to combine multiple fields of data including explicit/static values, and other business rule functions in any format and order you define.

CONCATENATE allows you to combine up to 8 values together exactly as those values appear in the rule. You can extend the length by nesting other CONCATENATE functions within the primary CONCATENATE rule.

When to Use: When you need to combine data points or the results of other functions.

Caution: blank fields may alter the look of the result, and spaces aren’t entered automatically between values – they must be explicitly defined.

  • CONCATENATE( Value 1, Value 2, Value 3, Value 4, etc )
  • CONCATENATE( $brand, “ “, $title) will combine the brand of the product (“Nike”) with the title (“Air Zoom Vomero 12”), and ensure there’s a space between the values.
    • “Nike Air Zoom Vomero 12”
      • The space between “Nike” and “Air” is explicitly inserted by the rule with the double quotes and space between them, otherwise the result would read “NikeAir Zoom Vomero 12”

JOIN ultimately does the same thing as CONCATENATE to bring values together, but allows you to combine up to 8 values and choose how those values should be delimited. Here you’ll define a delimiter as the first variable, then define the values in the same way as CONCATENATE.

When to Use: when you need to combine multiple data points or the results of other functions by the same delimiter, and when you need to generate new strings of field data on the fly so it can be used for other purposes.
Caution: blank fields may alter the result in ways you may not intend.

  • JOIN( “ | “, Value 1, Value 2, Value 3, Value 4, Value 5, Value 6, Value 7, Value 8 )
  • JOIN( “ | “, $category, $subcategory, $producttype) will combine Category and SubCategory field data and separate them by a pipe, generating a field that may provide you the data you need to convert into a valid Category for a Marketplace or Digital Marketing feed.
    • “Clothing | Boys Clothing | Boys Shorts”
      • If any of the values are blank, you’ll see multiple delimiters in a row: Clothing || Boys Shorts

JOINNB also allows you to combine up to 8 values and choose how those values should be delimited, but it will also eliminate blank values from the result, so you won’t have multiple delimiters back to back. This function offers the most control, flexibility, and the cleanest result simply by using the function. Your chosen delimiter is applied with the first variable, then values are defined in the same way as in JOIN.

When to Use: When you need to combine multiple data points or the results of other functions by the same delimiter, and you don’t know if or when those fields or functions have data in them.
This function is also often used to generate new strings of field data, so the resulting data can be used for other purposes like direct outbound mapping, or converting the resulting data into valid values.

    • JOINNB( “,“, Value 1, Value 2, Value 3, Value 4, etc )
    • JOINNB( “,“, $image1, $image2, $image3)
      • “https://www.example.com/firstimage.jpg, https://www.example.com/thirdimage.jpg
        • Result when $image2 is blank

Pro Tips

Extend the length of any of the above rules by nesting the same function in the place of any of the values. Just remember to close each function with the appropriate number of parenthesis!

  • JOINNB( “ | “, Value 1, Value 2, Value 3, Value 4, Value 5, Value 6, Value 7, JOINNB( “ | “, Value 1, Value 2, Value 3, Value 4 ) )

ISBLANK and IFBLANK

Both of these functions will allow you to evaluate blank data, but they have slightly different purposes.

ISBLANK allows you to evaluate a field or function to determine if it is or generates no value. The output of the rule is ‘TRUE’ or ‘FALSE’. It’s most often used within the IF and SELECTCASE functions, which both require evaluation of a condition to a TRUE or FALSE value, but there are other practical uses including simple rules that generate TRUE/FALSE for excluding products from a feed.

When to Use: when you need to determine if a single field is blank or if a function generates a blank.

  • ISBLANK( Value )
  • ISBLANK( $brand )
    • “FALSE” – result when the Brand field is blank for the selected product
    • “TRUE” – result when the Brand field is not blank for the selected product

IFBLANK allows you to return the first non-blank result in a comma separated list. A maximum of 8 fields or other functions can be evaluated with a single IFBLANK. The output is entirely different from ISBLANK in that you won’t generate a TRUE or FALSE result — instead, you’re returning a field value, static value, or result of a rule.

When to Use: when you have multiple fields you can use for a particular purpose and know the order in which you wish to use them, but you don’t know which are populated from one product to the next.

  • IFBLANK( Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8 )
  • IFBLANK( $bulletpoint1, $amazonbulletpoint1, $bulletpoint2, $amazonbulletpoint2, $bulletpoint3, $amazonbulletpoint3 )
    • “60 Mile range to access from broadcast tower; receives free HD channels including ABC, CBS, NBC, PBS, Fox, Univision and more”
      • Result when the $amazonbulletpoint1 is the first value populated
  • IFBLANK( $size, $exactsize, “See Description” )
    • “XXL” – when the result is populated in $size
    • “See Description” – when $size and $exactsize are blank

Pro Tips

  • Combine ISBLANK with NOT if you want to find out when something is NOT blank.
    • NOT( ISBLANK($brand) )
  • Extend the length of IFBLANK by nesting the same function (or other functions) in the place of any of the values. Just remember to close each function with the appropriate number of parenthesis!
    • IFBLANK( Value 1, Value 2, Value 3, Value 4, Value 5, Value 6, Value 7, IFBLANK( Value 1, Value 2, Value 3, Value 4 ) )
  • Combine these two functions together to evaluate the first non-blank field to TRUE/FALSE
    • ISBLANK( IFBLANK( $size, $exactsize ) )
    • When $size is populated, the result will be FALSE because the value is not blank.
    • When $size and $exactsize are blank, the result will be TRUE because both values were checked and were blank.

LEFT or LEFTWORD

The purpose of these functions is to grab part of a value starting from the left side of the data to a specific length. One offers additional flexibility for a cleaner result.

LEFT allows you to define the number of characters (from the left side of the value) to include from a field. Requires you define the field and the number of characters.

When to Use: when you need a hard cutoff at a certain number of characters (usually to ensure you’re at or below the maximum length of a field).

Caution: this will end the value at the character count regardless of location.

  • LEFT( $description, 2000 )
    • “These U shelves are functional and attractive alike[imagine more content here]It’s easy to mount and comes with all necessary mounting hardware. It measu”
      • Result when the 2000th character hit in the middle of the word “measure” and cut it off.

LEFTWORD allows you to define both a maximum length of the value and the delimiter where the cutoff should happen before the maximum length, which provides a better-looking output because it reduces the possibility a field may be cut off in the middle of a word.

When to Use: when you need a hard cutoff at a certain maximum length but want a clean, rather than abrupt, end point, and know that the delimiter data generally will exist for most products.

Caution: If the delimiter doesn’t exist in the field at all or if the first instance of the delimiter exists after the maximum defined length, the rule generates a blank.

Caution: If the last delimiter falls well short of the maximum length, this is still where the cutoff will happen, and the overall length may be very short.

  • LEFTWORD( $description, 2000, “.” )
    • “These U shelves are functional and attractive alike[imagine more content here]It’s easy to mount and comes with all necessary mounting hardware.”
      • Result when the 2000th character was previously in the middle of a word, but with LEFTWORD defining a period as the last cutoff, we end at the period before the word “measure”, so the result looks intentional.
  • Caution Example: LEFTWORD(“the quick brown. fox”, 12, “.”)
    • The yellow highlighting indicates the 12th character of the value.
    • The period exists after the 12th character, so this design would return a blank.
  • Caution Example: LEFTWORD( $description, 2000, “.” )
    • “These U shelves are functional and attractive alike. It’s easy to mount and comes with all necessary mounting hardware.”
      • This may be the entire description if no more periods exist after these 120 characters.

Pro Tips

  • For LEFTWORD, pick a common delimiter like spaces, periods, and commas to end your data. This way you can ensure fields that may not have the delimiter before the maximum length aren’t excluded from use.
  • You can evaluate a function using LEFT or LEFTWORD by nesting the other functions in the place of a field or static value.
    • LEFTWORD( JOINNB( “ “, $shortdescription, $bullet1, $bullet2, $bullet3, $bullet4, $bullet5 ), 2000, “ “)
      • This will apply the LEFTWORD to the result of the JOINNB, which combines multiple data fields together. The LEFTWORD will trim it to a maximum of 2000 characters and cut off after the first space before it hits 2000 characters.