Using Kintone App as an External Master

You may want to use master data managed by an external system in Questetra BPM Suite. In this article, we are going to use data that has been managed by kintone as the external options master for Search Select Box in Questetra BPM Suite.

For relaying between kintone and Questetra BPM Suite, AWS API Gateway and Lambda are available. For creating AWS resources we will use Terraform.

What is a Search Select Box?

A Search Select Box is one of the Data Item types. When you enter a search string and press the search button a list of options that contain the search string will be shown from the master data, and you can select one from the list to enter the data.

AWS Services to Use

The method introduced in this article uses the following services from AWS (Amazon Web Service). The purpose is shown in parentheses.

• Lambda (application creation)
• API Gateway (creating an API)

How it works

The general mechanism is as shown in the figure below.

When the Search Select Box sends a GET request to the API Gateway, the event is sent to Lambda. Based on the event, Lambda queries RDS for data and returns the result. 

Outline of Procedure

Create the kintone App, assign the App’s information to variables and with Terraform create an AWS resource (API Gateway and Lambda functions). Set the endpoint of the API Gateway as the URL of the option data in the Search Select Box.

Here is the general flow.

1. Prepare the kintone App
2. Get the Terraform template from GitHub
3. Set the AWS credentials to run Terraform
4. Install the package used by the Lambda function
5. Set the template variables
6. Create the AWS resources
7. Create an App that uses a Search Select Box in Questetra BPM Suite

Required Environment

You need the following to proceed with the steps in this article:

• Terraform
• AWS CLI
• Node.js / npm command (used to create the Lambda function code)

Procedure

1. Prepare the kintone App

First, prepare a kintone App. You must create a new one if you haven’t done so already.

If you already have a kintone App, please skip 1-1. Create the App and go to 1-2. Check the field codes.

1-1. Create the App

Click the + (plus) button in the Apps page of kintone’s portal site.

It’s alright to create any kind of App, but in this article we will choose a Customer Database App. From Customer Service add a Customer Database App.

After the App is created, create some data records using the + (plus) button in the upper right. This time only the Company Name field will be used, so it’s not necessary to fill in the other fields.

1-2. Check the field codes

Check both the field codes of the field to be used as the display label and the one to be used as the choice ID. The display label will be searched.

In this article, the Company Name field is used as the display label.

Click the gear button and go to the settings screen.

Click the gear button of the Company Name field and open the Settings page.

Make a note of the Field Code (company_name) displayed in the settings screen.

At the same time, make a note of the Field Code for the field to be used as the choice ID.

In this article, “Record ID” is used as the choice ID. Record ID is a field that is automatically created as meta information even if it is not listed in the field list. The field code is set to “$id”.

1-3. Create API Token

Create an API Token to retrieve App data with the kintone REST API. Open the App Settings tab on the settings screen and go to API Token.

Click “Generate” and make a note of the following three pieces of information:

• API Token
• Domain (xxxxx.kintone.com or xxxxx.cybozu.com)
• App ID (Numeric)

After that, please remember to click Save.

Don’t forget to click the Update App button on the following screen (click to save the API token).

2. Get the Terraform template from GitHub

Download the Terraform template from this GitHub repository.

3. Set the AWS credentials for Terraform execution

Open the AWS IAM console and select the user you want to run Terraform from “Users” on the left menu.

Click [Create Access Key] on the [Security Credentials] tab and make a note of the displayed access key ID and secret access key.

Execute the following AWS command.

aws configure

When you execute this command you will be prompted for four pieces of information:

• Access key ID
• Secret access key
• AWS Region
• Output format

Enter the Access key ID and Secret access key that you made a note of earlier, and the AWS region and output format that you want to use as default (reference).

For example, type:

AWS Access Key ID [None]: {Access key ID}
AWS Secret Access Key [None]: {Secret Access Key}
Default region name [None]: us-east-2
Default output format [None]: json

4. Install the package used by the Lambda function

To install the necessary packages, execute

npm install

in the lambda-src directory of the template downloaded in step 1 (node_modules directory will be created).

5. Set the template variables

Create a vars.tfvars file directly under the Choices-AWS-Lambda-RDS directory of the template downloaded in step 1, and specify the values ​​of the variables defined in variables.tf (variables that have a default value set in variables.tf do not need to have a value specified in vars.tfvars).

For example, the contents of the vars.tfvars file might look like this.

aws_region = "us-east-2"
kintone_domain = "xxxxx.kintone.com"
kintone_api_token = "{API Token}"
kintone_app_id = 5
kintone_app_value_field = "$id"
kintone_app_display_field = "company_name"

6. Create the AWS resources

Create the AWS resources so that you can retrieve the kintone App data as option data via HTTP.

In the directory, execute

terraform init

to initialize it as the workspace. Moreover, execute

terraform plan -var-file=vars.tfvars

to see your changes (this command does not apply any changes). After you see the log and your changes you should see a message saying that you will be creating 11 resources like this:

Plan: 11 to add, 0 to change, 0 to destroy.

If there are no problems. execute

terraform apply -var-file=vars.tfvars

to apply your changes.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value:

The above will be displayed, so you type

yes

to confirm the execution.

AWS resource creation is complete when the following is displayed.

Apply complete! Resources: 11 added, 0 changed, 0 destroyed.

Outputs:

api_url = {API Gateway ENDPOINT}

Make a note of the endpoint of the API Gateway that is displayed.

After the AWS resource is created, access the API Gateway endpoint with a browser and check the operation. If you see the XML as shown below, you’ve succeeded.

7. Create an Application Using a Search Select Box in Questetra BPM Suite

Create an App in Questetra BPM Suite and add “Search Select Box” to the data items (Display name is “Search Select”). Select “Get choices list via HTTP” in “Type of data source”, enter the API endpoint in “URL”, and click “Apply and Close”.

Let’s release the App and check the form. Enter the search string and press the search button to display the options that match the conditions.

What did you think? We used kintone this time, but you can use other databases as external masters in a similar way. Please consider using an external service according to the nature and purpose of the master data.

Reference) To use ”Dependent Parent Data Items”

When using the kintone App as an external master in the way described here, it is necessary to pay attention to the “dependent parent data items” in the Search Select Box.

Normally, child choices whose [Choice ID] matches the Parent [Option ID] are filtered (see here for more details). However, the data in the kintone App does not allow a forward match search, so child choices whose [Choice ID] includes the Parent [Choice ID] are filtered.

Also, if you set the [Choice ID] in alphanumeric values, kintone is searched by word (”_” and ”#” are also considered part of the the word). Therefore, the Child [Choice ID] must be designed so that the Parent [Choice ID] is considered a different word.

Bad examples

It is not possible to filter the Child choices with the Parent choices if the following expressions are used, because the Child [Choice ID] is considered as a unique word, so the search won’t include the Parent [Choice ID].

Parent Choices

Choice ID	Display Label
h	Hot
c	Cold

Child Choices

Choice ID	Display Label
h1	Coffee
h2	Tea
c1	Iced Coffee
c2	Iced Tea
c3	Orange Juice

Good Examples

The following expressions add “-” (a hyphen) to the Child [Choice ID] which makes it possible to filter the Child choices with the Parent choices.

Parent Choices

Choice ID	Display Label
h	Hot
c	Cold

Child Choices

Choice ID	Display Label
h-1	Coffee
h-2	Tea
c-1	Iced Coffee
c-2	Iced Tea
c-3	Orange Juice