Skip to Content

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.

Note

The Enum function cannot be used in migration rules.

You must populate Enum column data in order for the function to work properly (either in the Business Logic Layer or the Application UI Layer).

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

    Enumtabledefinition

  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

    EnumbusineSSLogic

  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:

    Enumapp

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

    2018 10 10 10 28 32 window

  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.

    2018 10 10 10 25 17 window

  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

    2018 10 10 13 48 23 window

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

    2018 10 10 10 25 49 window

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'