Dynamically fetching answers for BMC Helix Digital Workplace questionnaires by using Jitterbit Harmony

BMC Helix iPaaS, powered by Jitterbit provides a prebuilt integration template that enables you to dynamically fetch answers for BMC Helix Digital Workplace questionnaires from an external database. This integration template provides you the flexibility to use a database that suits your requirement and integrate the products with minimum configuration.

To use the integration template with the values defined out of the box, you update the project variables with details of your systems and deploy the integration template. 

The template provides the following capabilities: 

Use caseDescription
Use databases
  • Ability to use external databases for using lookup values
  • Flexibility to use multiple types of databases
Filter data

Ability to filter the lookup values by using filter criteria in a questionnaire

BMC Helix Digital Workplace to BMC Helix iPaaS data flow

The following image gives an overview of the data flow for fetching BMC Helix Digital Workplace Catalog questionnaire answers from external records:

Before you begin

You require the following items to successfully set up and use this integration: 

Required versions
  • BMC Helix iPaaS  21.3 or later
  • BMC Helix Digital Workplace 21.3 or later
  • BMC Helix Innovation Studio 21.3 or later

Authentication and permissions
  • Administrator access to BMC Helix Innovation Suite
  • Administrator access to BMC Helix Digital Workplace
Jitterbit Harmony subscription

A valid  BMC Helix iPaaS Open link subscription

Task 1: To download and import the integration template project file

  1. Download the DWP Catalog Questionnaire Lookup 2022-07-01 Open link file to your system.
    This file contains the BMC Helix iPaaS Cloud Studio project DWP_Catalog_Questionnaire_Lookup.


    Your ability to access product pages on the EPD website is determined by the license your company purchased.

  2. As an administrator, log in to BMC Helix iPaaS Cloud Studio. 
  3. On the projects page, click Import.
  4. Click Browse and then select the DWP Catalog Questionnaire lookup.json file you downloaded.
    The Project Name and Organization fields are automatically populated depending on the values defined.
  5. From the Environment list, select the environment to which you want to import this integration template, and click Import.
    The project opens after the integration template is imported.
  6. (Optional) To open the project file at a later time, select the environment where the integration templates are available, select the DWP_Catalog_Questionnaire_Lookup project, and click View/Edit.

Task 2: To update the project variables for the integration template

  1. Next to the environment name, click ... and select Project Variables.
  2. Update the following project variables for the BMC Helix Innovation Studio and BMC Helix iPaaS applications:

    Project variableAction
    BMC Helix Innovation Studio
    Helix_Platform_API_PrefixEnter a predefined prefix for the Web API and data source connection names that are created for BMC Helix Digital Workplace in BMC Helix Innovation Studio.
    Helix_Platform_UrlEnter the BMC Helix Innovation Studio URL.
    Helix_Platform_UserEnter the user ID to access BMC Helix Innovation Studio.
    Helix_Platform_PasswordEnter the password for the user to access BMC Helix Innovation Studio.

    BMC Helix iPaaS


    Enter the name of the API that is created in BMC Helix iPaaS.

    This API receives the webhook requests from BMC Helix iPaaS.


    Enter comma-separated values of the user roles assigned for the BMC Helix iPaaS API.

    Only a user with these roles can access the APIs. Leave this value blank to restrict access only to administrators.


    Enter the URL to access BMC Helix iPaaS.


    Enter the user ID to access BMC Helix iPaaS.


    Enter the password of the user to access BMC Helix iPaaS.


    For security profile type BASIC, enter the user name to create the API profile.

    The Jitterbit API and the webhook API use this user name for authentication.


    For security profile type BASIC, enter the password for the security profile created.

    The Jitterbit API and the webhook API use this password for authentication.

    BHIP_Vendor_API_Profile_ApiKey_NameEnter the name for the APIKEY to be used when creating the authentication profile.

    Enter a security profile type.

    You can set the following values for this variable:

    • BASIC 
    • APIKEY

    The default value is set to BASIC.

    Enter comma separated values to select multiple profile types (ANONYMOUS,BASIC). 


    • For profile types supported by the source application, the security profiles are automatically created by the integration template when you enable the integration. 
    • BMC Helix iPaaS does not support OAuth authentication for this application.
    Email_From_AddressEnter the email address from which the notification emails must be sent.

    Enter the email address of a recipient who receives the integration failure mail notification.

    Add multiple email addresses by using comma separated values; for example, email1@abc.com, email2@abc.com.

    Email_SMTPEnter SMTP host details for email configuration.
    Database variables
    DB_HostEnter the host name of the database server.
    DB_NameEnter the name of the external database in the database server.
    DB_UsernameEnter the user name to access the database server.
    DB_PasswordEnter the password of the user to access the database server.

(Optional) Task 3: To update out-of-the-box mappings

Out-of-the-box field mappings are defined in the External Record Definition script.

$externalRecords = {
	"<external-record-name1>" : {
  	"dbTable": <Database-table-name1>,  // Database table name from which the values will be used for external record
    "documentSchema": [{  // Response Schema from Database as Array of json object.
      "<db-field1>": "<db-value1>",
      "<db-field2>": "<db-value2>",
      "<db-field3>": "<db-value3>", ...  // Multiple data fields can be added
		"queryVars": ["type", "dbField1", "dbOp1", "dbValue1", "dbLop1", "dbField2", "dbOp2", "dbValue2"],
		"fieldMappings": {
    	"<db-field1>": {
        "name": "<external-record-field1>",
        "resourceType": "<external-record-type>",
        "fieldOption": "<OPTIONAL>|<SYSTEM>|<REQUIRED>",
        "defaultValue": "<default-value>"
      "<db-field2>": {
        "name": "<external-record-field2>",
        "resourceType": "<external-record-type>",
        "fieldOption": "<OPTIONAL>|<SYSTEM>|<REQUIRED>",
        "defaultValue": "<default-value>"
      },...   // Needs to be defined for all the database fields specified in the document schema
	}, ...   // Multiple external record definition schema can be added

The field mapping is provided between the external database and the external record created in BMC Helix Innovation Studio. At a time, the field mapping schema works for one data source connection. You can fetch data from multiple external records corresponding to separate database tables that are created for a given data source connection.

You can specify the filter criteria by using the following queryVars variables in the BMC Helix Digital Workplace Catalog questionnaire:

typeName of the external record
dbField1Database field name for the first condition

Database operator for the first condition

dvValue1Value against the first condition

Database field name for the second condition

dbOp2Database operator for the second condition
dvValue2Value against the second condition
dbLop1Database logical operator to join the first and second conditions

To update the out of the box field mappings, perform the following steps:

  1. Open the DWP Catalog Questionnaire Lookup project.

  2. In Workflows, select 5.0 Field Mappings > External Record Definition.
  3. Click the ... ellipses and then click View/Edit.
  4. In the script, navigate to the external record global variable and update the value as required.
  5.  To return to the workflow, click .


    If the integration template is already deployed, you must deploy it again after you make changes to the mappings.

    To deploy the integration, see Task 5: To deploy and enable the project.

Task 4: To configure the database endpoint

  1. In the Connectivity section, from the Show list, select Endpoints.
  2. Click the ellipsis ... on the Database Endpoint block and then click View/Edit.
  3. On the Configure Database Endpoint page, complete the fields and save your configuration.

    For information about the configuration fields, see  Database connection Open link .

Task 5: To deploy and enable the project

Deployment is a one-time activity that initializes the integration configurations. The UI displays a message for the deployment status.

To deploy the project and then enable the integration:

  1. To deploy the project, next to the project name, click the ellipsis ..., and select Deploy Project.
  2. To enable the integration, next to the Enable Integrations workflow, click the ellipsis ... for the Enable Integration operation, and select Run

The following image shows the steps to deploy the project and enable it by running the operation:

After you enable the integration, the following events occur:

  • The API is created in BMC Helix iPaaS, powered by Jitterbit API manager.
  • Documents, external records, and data source connections are created in the BMC Helix Digital Workplace Catalog workspace in BMC Helix Innovation Studio.

(Optional) Task 6: To set the time for API debug mode

By default, the debug mode is set to 2 hours after you run the integration. Debug logs are updated for the time set for the debug mode. To increase the debug mode to a longer period of time, perform the following steps:

  1. In BMC Helix iPaaS, powered by Jitterbit, select API Manager > My APIs.
  2. Open the API created for the integration.
    The API name is the value defined in the BHIP_API_Name project variable.
  3. Select Enable Debug Mode Until: and set it for the required date and time.
  4. Save and publish the API.

Task 7: To create a BMC Helix Digital Workplace service 


Repeat this procedure for each service for which you want to fetch the data from an external database.

For a detailed description of the procedure to create a service, see Adding and updating services Open link .

  1. Log in to BMC Helix Digital Workplace Catalog.
  2. Select Services > Services.
  3. Select Create New > Service.
  4. Enter a name and version for the service, for example, Service1 and 1.0
  5. From Template, select the catalog service under which you want to create the service, for example, IT Request

  6. Click Create Service.
  7. Under Service Options, click Edit for the following options and provide the details to create the service:

    Catalog Profile

    Define the profile to be displayed in BMC Helix Digital Workplace Catalog.

    For more information about creating the catalog profile, see Adding catalog profile details to a service Open link .


    Define a basic workflow for the service. For more information about defining a workflow, see Workflows for service fulfillment Open link .

    Define the following elements to configure your workflow and then click Save:

    • Under General settings, for Input Variables, enter the following values:

      • Name—Enter a unique name for your variable.

      • Variable ID—Add a unique variable ID.
      • Variable Type—Select Input/Output Variable > Input.
      • Data Type—Select Service Broker Context.
    • Set Service Request Status element:

      • Map the Service ID field to the SB Context > Service Request > Service Request ID.

      • Set the Status field to "Active".

    • Receive Task element: Add this element to the workflow.

    Add a questionnaire for the service.

    Important: While creating the questionnaire, in Map Dataset, make sure you select the external record that is created in BMC Helix Innovation Studio after you run the integration. The external record is created with the name that you have provided in the field mappings section.

    For more information about adding a questionnaire, see Adding a questionnaire to a service Open link .

  8. Click Save, and then click Close.

  9. Publish the service. 
    For details about publishing a service, see  Approving and publishing services Open link .

Workflows included in the integration template

The following workflows are defined as a part of the integration template. Refer to the following details for an overview of the tasks defined in the workflow operations and configurations defined within each workflow.

External Record Workflow

The following operations are included in this workflow:

Operation NameActions performed
External Record Flow ControllerReceives the external record request call, processes the data, and responds with the output data
Database WrapperDynamically builds the SQL statement from the external record mapping that you defined and executes the query. Then, the operation processes the result and sends it back as an array of JSON objects.
Was this page helpful? Yes No Submitting... Thank you