Throughout integration data pipelines, it is common to store small amounts of data in computer memory for re-use. These are Data Elements (global and local variables) in Jitterbit they have a type and scope. For a better understanding, it’s important to name variables with a naming convention indicating their purpose and use. By using a vocabulary based on a variable type and intention, you can find and manage your appropriate variable name by type faster. The Formula Builder filtering and alphabetic sorting feature speed up access and comprehension. This approach is very convenient as your global variable list grows.

Note, all Jitterbit variable names are case-insensitive. We begin by discussing some critical variable types with more variable naming conventions in future posts.

Types

A variable value has a data type. These types include integers and strings. They can also comprise more complex data types, like time, arrays and dictionaries (key-value pairs).

Date/Time

Date/time variables hold date and time values that are convertible into various formats. They hold start times, end times and are used to calculate durations. I like to start the name with a (:) to identify the value as a date/time. For example, $:name where name is some sign of the variable’s purpose like $:startTime or $:duration.

CSV (list)

Comma-Separated Values (CSV) are a standard format of strings and used in files and database queries (where-in). While() loops are often used to grow the list. I like to begin all CSV-type variable names with L or L_ (for List) –for any variable with even the potential of having 2 or more on (in) the list. For example, use a CSV list name of Lwherein for a parameterized SQL query.

Array

Arrays are a collection of data elements. They can have 1 or more dimensions. The Jitterbit functions DBLookupAll() and SfLookupAll() return 2-dimensional arrays. I like to begin all Array variable names with A or A_. Convert an Array type to a CSV (string) within a While() loop by using an Array index to get access to an individual Array cell. For example,

Lname=Null();
i=0;While(i < Length(Avalues),
  If(IsNull(Lname),
    Lname=Avalues[i++]
  ,
    Lname+=","+Avalues[i++]
  )
)

Note the use of the name – Avalues versus Avalue – so one can write “value=Avalues[i],” and we achieve clarity. Conversion from a CSV to an Array is as simple as Avalues=Split(Lname,”,”).

I rarely use the ReadArrayString() function, to build an n-dimensional array from a string. Unfortunately, it has not been reliable for arrays of 2 or more dimensions.

If using DBLookAll() or SfLookupAll(), take advantage of the column/field name used in the query to get access to the returned array’s 2nd dimension. For example, companyPhone = ACompanies[i][“phone”] where “phone” is the name of a column/field name queried from a database or Salesforce. Arrays are helpful when the order (or dimensions) is relevant. But Arrays have no natural function to find if a value already exists in the Array (searchable). Have a look at the Script below. With an extra “helper” array, you can use the FindValue() function to search collections and add unique values.

search4="c";
Set(Arr,"a",-1); Set(Arr,"b",-1); Set(Arr,"c",-1); // Create array Arr: {a,b,c}
// Create the helper array Ahlpr: {0,1,2}
Ahlpr=Array(); i=0;While(i < Length(Arr), Set(Ahlpr,i++,-1));
//FindValue("c",Arr,Ahlpr); // Find "c"; in Arr; returns index of "c" in array Arr
If(IsNull(idx=FindValue(search4,Arr,Ahlpr)), // If search4 not found
  Set(Arr,search4,-1); Set(Ahlpr,Length(Arr)-1,-1) // add search4 to Arr
, //else
  idx // return its index
);

Note, the “helper” array above can hold unique values (they don’t have to be consecutive nor numeric) so you can actually “map” values using these two arrays.

Dictionary

Dictionaries hold key-value pairs. That is, given a key, you can get access to or write the value in the dictionary.

I use a more interesting naming convention for Dictionaries. valuesByKey where values is a root name of the Dictionary and Key is a descriptive name of the Dictionary key. When I see “By” in a variable name, I know it’s a Dictionary. Again, note the use of plural values vs value so one can read value=valuesByKey[“akey”]. Dictionaries can contain complex types like Arrays or CSVs (or another Dictionary). If so, I use LvaluesByKey if I’m using a Dictionary of CSVs or AvaluesByKey for a Dictionary of Arrays. I’ll leave you to define a naming convention for a Dictionary of dictionaries.

Continue with Variable Scope