Skip to Content

Generate a random letter in Jitterbit App Builder

Configuration

App Builder does not currently have a function to randomly generate a letter of the alphabet or a number between two values, but you may need a random letter or set of characters generated for various reasons including implementing a character code in an app. For this task, we will have to "escape App Builder" and use a SQL script to generate a random number, and then work with Business Objects to convert the random number into a corresponding letter of the alphabet.

  1. Create a Subquery. In this example we will call it Application (Random Number Generator). Then we will use the SELECT RAND() function which selects a random number between 0 and 1. To select a random number between two values a and b, we use the function SELECT RAND()*(b-a)+a; and substitute in our a and b values. in this case, we want a number between 1 and 26 (becasue there are 26 letters of the alphabet), so we choose 1 and 26 as our a and b values respectively. This will give us a decimal value, but we want an integer, so we use the FLOOR() function to generate a random integer number that is inclusive (random integer >= 1 and <= 26).

  2. Next we need to create a Letter table with integers corresponding to each letter of the alphabet. The LetterID column is a Primary Key Integer. The Letter column is a String.

  3. Now we can convert the random integer into a corresponding letter by crossing the Letter table with the Application (Random Number Generator) Business Object we created earlier in a subquery called Letter (Random Number to Letter Conversion) and filtering WHERE IIF(LetterID = AR.Rand, 1, 0) using a Flag. The results should provide a random letter every time you run the results or refresh.