Skip to Content

Subquery vs. business object in Jitterbit App Builder

This article will discuss Subqueries and Business Objects in the Business Logic Layer, in App Builder. First we'll look at a high level definition of these two objects, and then we'll look at each object in detail.

  • Subqueries are queries that reside within queries and are used by Business Objects and other Rules (e.g., CRUD). Subqueries on their own are not exposed to the Application UI Layer.
  • Business Objects are objects that are leveraged to build the Application UI Layer. Business Objects can use Subqueries.

Subquery

Subqueries are used as an intermediary between a table and its Business Objects, and are queries that reside within queries. Subquery Rules are used to perform a calculation or make an adjustment to data (typically numerical in nature), for display purposes only or to be passed along and used by another Rule. By default, Subqueries have all CRUD settings disabled, and will run faster because of this. In general you want to keep a Subquery object as simplified as possible, in terms of its logic. Having a Subquery that selects excess Columns can, at times, lead to performance issues.

An example of a Subquery Rule is if we want to create an object to find the maximum OrderID of the Order table and add one to it. We can call this Subquery Order (OrderID + 1). This Subquery could then be used by a CRUD Insert Rule, whose functionality is to copy an Order and generate a new unique OrderID upon doing so.

Example subquery for Order (OrderID + 1)

  1. Navigate in your app to the Orders page
  2. Go to the App Workbench > + Rule
  3. Assign the Name. For example: Order (Order + 1)
  4. Set the Purpose as Subquery
  5. Set the Target table as Order
  6. Click Save
  7. Click the Columns tab
  8. Click + Column and add in the Expression Max(OrderID) + 1
  9. Assign an Alias. For example: MaxOrderID
  10. Click the Validate button and make sure there are no errors

At this point you have a working Subquery. To leverage this Subquery from an app, it would need to get connected to, or used by, another Rule like a CRUD Insert Rule.

Business Object

Business Objects are the building blocks of the Business Logic Layer and are used to build out the Application UI Layer in App Builder. Most of the time anything presented on the Application UI Layer comes from an associated Business Object. Business Objects can have information associated with them, such as Events and Reach. Business Objects can also connect to and use Subqueries. Depending on their purpose, Business Objects can be very simple objects, like Source and List objects, or very complex depending on their purpose.

In the example we'll take a look at creating a simple Source Business Object for an Order table in the Northwind data source. There are a few different ways Source objects can be created, the Introduction to App Builder training manual discusses the different options available. Here we'll use the shortcut method available from the Table in the Data Layer.

Example Business Object for Order (Source)

  1. Navigate in your app to the App Workbench page
  2. Click the Tables tile
  3. Locate the Order table
  4. Click the Open Record button

  5. Click the Publish to Business Layer button

    • Click Proceed
    • If App Builder has already created a Source Business Object for the table, you will not see this button available

  6. After the Source object is created, click the back button

  7. The new Order (Source) object will now appear with the Business Rules
  8. Click the Design button on the Order (Source) object to review the underlying Business SQL
  9. Click the Validate button to run the object and confirm there are no errors