Enum() mvSQL runtime function in Jitterbit App Builder

Summary

The Enum Function in App Builder replaces the Primary Key with the Enum expression. The Enum function can only be used to lookup values on tables that have a single Primary Key defined (not more than one PK), and that have one of their columns marked as the Enum usage type.

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time. The Enum function in App Builder can be used to keep rules created in the Business Logic Layer human readable. Using Enum in App Builder is useful when building any app that multiple people will work on, to help simplify the understanding of rules.

When the Enum Function is used in mvSQL in either a Where clause or an IIF statement, the resultant SQL panel on the Business SQL screen for the Rule will display the Primary Key value UUID (from the table where the Enum column resides). This can be useful when confirming that the Rule is substituting the correct data.

Syntax

enum(<LookupTableName>,'<EnumValue>')

Example of Enum being used in a Where Clause with multiple values:

TableID IN (Enum(LookupTableName, 'EnumValue'), Enum(LookupTableName, 'EnumValue'), Enum(LookupTableName, 'EnumValue'), ...)

Parameters

Lookuptablename

The name of the table we will be looking up the enumerated value in.

Enumvalue

The value in the enumerated type column. This should be a string.

Returns

The Primary Key gets replaced with the enum value looked up in the lookup table at runtime. The type it is replaced with is dependent on the type of the Primary Key of the lookup table being examined.

Remarks

The enum function is typically used in the where clause, but could be used in other parts of the mvSQL statement.

Examples

Example #1

In this example Enum is used in the Status table, then added to an existing Business Logic Rule (the Status (Source) Object), and then exposed to the Application UI on the Status Multi-Row Panel.

1. Add the Enum column to the Status table, set the Logical Data Type to String and the Usage to Enum

   

2. Click the Results button for the Status table and specify the values for the Enum column

3. You can either create a new Rule to leverage the Enum value or modify an existing one. In this example, we'll add the Enum column to the existing Status (Source) Rule

   

4. With the Enum column added to a Rule, we can add it to the Application UI Layer to expose it there. Here we add it as a Control to the Status Multi-Row Panel:

   

Example #2

In this example Enum is used in the EmployeeType table and then added to a new Rule using the Enum function in a Where clause (in the mvSQL).

1. Add the Enum column to the EmployeeType table, set the Logical Data Type to String and Usage to Enum

   

2. Click the Results button for the EmployeeType table, and specify the values for the Enum column. Alternatively you could setup a configuration page in the app where Users can specify these values, or expose the column as a Control to the application UI and input the data there.

   

3. You can use the Enum function on an existing rule, but in this example we will create a new Rule targeting the Employee table

   

4. In the Rule, we'll add the Enum function to a Where clause in the mvSQL

   

Example #3

Enum(EmployeeTypeLookup, 'Fulltime')

returns the Primary Key of the EmployeeTypeLookupTable. Ex:

'17457c85-b0c9-4d45-8681-5ee566429ddd'

Used in an mvSQL statement, the typical statement where this would be used is:

select * from Employee where EmployeeTypeId = enum(EmployeeTypeLookup,'Fulltime')

rather than:

select * from Employee where EmployeeTypeId = '17457c85-b0c9-4d45-8681-5ee566429ddd'