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 case	Description
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 subscription

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

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

Your ability to access product pages on the EPD website is determined by the license your company purchased.
As an administrator, log in to BMC Helix iPaaS Cloud Studio.
On the projects page, click Import.
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.
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.
(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

Next to the environment name, click ... and select Project Variables.

Update the following project variables for the BMC Helix Innovation Studio and BMC Helix iPaaS applications:

Project variable	Action
BMC Helix Innovation Studio
Helix_Platform_API_Prefix	Enter 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_Url	Enter the BMC Helix Innovation Studio URL.
Helix_Platform_User	Enter the user ID to access BMC Helix Innovation Studio.
Helix_Platform_Password	Enter the password for the user to access BMC Helix Innovation Studio.
BMC Helix iPaaS
BHIP_API_Name	Enter the name of the API that is created in BMC Helix iPaaS. This API receives the webhook requests from BMC Helix iPaaS.
BHIP_API_User_Roles	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.
BHIP_Host	Enter the URL to access BMC Helix iPaaS.
BHIP_Username	Enter the user ID to access BMC Helix iPaaS.
BHIP_Password	Enter the password of the user to access BMC Helix iPaaS.
BHIP_Vendor_API_Profile_Username	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.
BHIP_Vendor_API_Profile_Password	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_Name	Enter the name for the APIKEY to be used when creating the authentication profile.
BHIP_Vendor_API_Profile_Type	Enter a security profile type. You can set the following values for this variable: BASIC ANONYMOUS APIKEY The default value is set to BASIC. Enter comma separated values to select multiple profile types (ANONYMOUS,BASIC). Important: 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
Email_From_Address	Enter the email address from which the notification emails must be sent.
Email_Recipients	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_SMTP	Enter SMTP host details for email configuration.
Database variables
DB_Host	Enter the host name of the database server.
DB_Name	Enter the name of the external database in the database server.
DB_Username	Enter the user name to access the database server.
DB_Password	Enter 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:

Variable	Value
type	Name of the external record
dbField1	Database field name for the first condition
dbOp1	Database operator for the first condition
dvValue1	Value against the first condition
dbField2	Database field name for the second condition
dbOp2	Database operator for the second condition
dvValue2	Value against the second condition
dbLop1	Database logical operator to join the first and second conditions

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

Open the DWP Catalog Questionnaire Lookup project.
In Workflows, select 5.0 Field Mappings > External Record Definition.
Click the ... ellipses and then click View/Edit.
In the script, navigate to the external record global variable and update the value as required.
To return to the workflow, click .

Important

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

In the Connectivity section, from the Show list, select Endpoints.
Click the ellipsis ... on the Database Endpoint block and then click View/Edit.
On the Configure Database Endpoint page, complete the fields and save your configuration.
For information about the configuration fields, see Database connection .

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:

To deploy the project, next to the project name, click the ellipsis ..., and select Deploy Project.
To enable the integration, next to the Enable Integrations workflow, click the ellipsis ..., and select Deploy.

The following image shows the steps to deploy the project and enable it by deploying the workflow:

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:

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

Task 7: To create a BMC Helix Digital Workplace service

Important

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 .

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

Under Service Options, click Edit for the following options and provide the details to create the service:

Configuration	Description
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 .
Workflow	Define a basic workflow for the service. For more information about defining a workflow, see Workflows for service fulfillment . Click here to view the sample process 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.
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 .

Click Save, and then click Close.
Publish the service.
For details about publishing a service, see Approving and publishing services .

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 Name	Actions performed
External Record Flow Controller	Receives the external record request call, processes the data, and responds with the output data
Database Wrapper	Dynamically 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.