Questetra BPMS: Process, Batch Extract by Filter as TSV
Questetra BPMS: Process, Batch Extract by Filter as TSV
Extracts process data that match the specified saved filter. Stores the extracted data list as a TSV string. The filter is specified by ReportID which must be saved in the target platform in advance.
Configs
  • U: Select HTTP_Authz Setting *
  • A1: Set Target Platform URL (eg. “${var[applicationRoot]}” ) *#{EL}
  • A2: Set Filter ID (ReportID) (eg. “1234” ) *#{EL}
  • A3: Set Max Num of Extraction (eg. “100” )#{EL}
  • B1: Select STRING that stores Extracted TSV (update) *
  • B2: Select NUMERIC that stores Num of Extracted Process (update)
  • B3: Select NUMERIC that stores Num of Filtered Process (update)
Script (click to open)
// GraalJS Script (engine type: 2)

//////// START "main()" /////////////////////////////////////////////////////////////////

main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strAuthzSetting     = configs.get      ( "AuthzConfU" );   /// REQUIRED ///////////
  engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );
const strTargetUrl        = configs.get      ( "StrConfA1" );    /// REQUIRED ///////////
  if( strTargetUrl      === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: TargetPlatformURL} is empty \n" );
  }
const strReportId         = configs.get      ( "StrConfA2" );    /// REQUIRED ///////////
  if( strReportId       === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A2: FilterID} is empty \n" );
  }
let   strLimit            = configs.get      ( "StrConfA3" );    // NotRequired /////////
  if( strLimit          === "" ){
      strLimit = "1000";
  }else if( isNaN( parseInt(strLimit) ) || parseInt(strLimit) < 0 ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A3: Max Num of Extraction} must be a positive integer \n" );
  }else if( parseInt(strLimit) > 1000 ){
    engine.log( " AutomatedTask ConfigWarning: " +
                " {A3: Max Num of Extraction} is limited to 1000" );
    strLimit = "1000";
  }
const strPocketTsv        = configs.getObject( "SelectConfB1" ); /// REQUIRED ///////////
const numPocketExtracted  = configs.getObject( "SelectConfB2" ); // NotRequired /////////
const numPocketFiltered   = configs.getObject( "SelectConfB3" ); // NotRequired /////////


//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Some workflow data is referenced via Expression Language in Config.)


//// == Calculating / 演算 ==
/// Get Processes
/// Questetra Workflow API
/// https://questetra.zendesk.com/hc/en-us/articles/360014835832-M418
/// https://questetra.zendesk.com/hc/ja/articles/360014835832-M418
// request1, prepare
let request1Uri = strTargetUrl + "API/OR/ProcessInstance/list";
let request1    = httpClient.begin(); // HttpRequestWrapper
    request1    = request1.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
    request1    = request1.queryParam( "reportId", strReportId );
    request1    = request1.queryParam( "limit", strLimit );
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
// request1, try
const response1     = request1.get( request1Uri ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + request1Uri );
const response1Code = response1.getStatusCode() + "";
const response1Body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1Code + "\n" + response1Body + "\n" );
}
// response1, parse
/* 
engine.log( response1Body ); // debug
{
  "count": 3,
  "processInstances": [
    {
      "activeTokenNodeName": [
        "Task 1"
      ],
      "data": {
        "0": {
          "dataType": "STRING",
          "id": 51199977,
          "processDataDefinitionNumber": 0,
          "subType": null,
          "value": null,
          "viewOrder": 1
        },
        "1": {
          "dataType": "STRING",
          "id": 51199978,
          "processDataDefinitionNumber": 1,
          "subType": null,
          "value": "I am a cat.  I was lifted up lightly on his palm.\n\nWhen I accustomed myself to that position",
          "viewOrder": 2
        },
        "3": {
          # # #
         },
        "6": {
          "dataType": "SELECT",
          "id": 51200370,
          "processDataDefinitionNumber": 6,
          "subType": null,
          "value": [
            {
              "display": "NO",
              "value": "false"
            }
          ],
          "viewOrder": 7
        }
      },
      "processInstanceDebug": false,
      "processInstanceEndDatetime": "2021-08-05T14:41:53+0900",
      "processInstanceId": 1536916,
      "processInstanceIdForView": "p1536916",
      "processInstanceInitQgroupId": 1,
      "processInstanceInitQgroupName": "Org",
      "processInstanceInitQuserId": 14,
      "processInstanceInitQuserName": "SouthPole",
      "processInstanceSequenceNumber": 1,
      "processInstanceStartDatetime": "2021-08-05T14:25:02+0900",
      "processInstanceState": "ENDED",
      "processInstanceTitle": "Input Test",
      "processModelInfoCategory": "TestUse",
      "processModelInfoId": 2454,
      "processModelInfoName": "Input Output test for all data types",
      "processModelVersion": 1,
      "starred": false
    }
  ]
}
*/
const response1Obj = JSON.parse( response1Body );

engine.log( " AutomatedTask Parsed: Number of Received Instances: " + response1Obj.processInstances.length );
engine.log( " AutomatedTask Parsed: Number of Filtered Instances: " + response1Obj.count );

let strTsv = "";
for( let h = 0; h < response1Obj.processInstances.length; h++ ){
  strTsv += response1Obj.processInstances[h].processModelInfoName.replace( /\t/g, " " ) + '\t';
  if(         response1Obj.processInstances[h].processModelInfoCategory !== null ){
    strTsv += response1Obj.processInstances[h].processModelInfoCategory.replace( /\t/g, " " ) + '\t';
  }else{ strTsv += '\t'; }
  strTsv += response1Obj.processInstances[h].processInstanceId + '\t';
  if(         response1Obj.processInstances[h].processInstanceTitle !== null ){
    strTsv += response1Obj.processInstances[h].processInstanceTitle.replace( /\t/g, " " ) + '\t';
  }else{ strTsv += '\t'; }
  strTsv += response1Obj.processInstances[h].processInstanceState + '\t';
  strTsv += response1Obj.processInstances[h].processInstanceInitQuserName + '\t';
  strTsv += response1Obj.processInstances[h].processInstanceStartDatetime.slice(0,19).replace('T',' ') + '\t';
  if(         response1Obj.processInstances[h].processInstanceEndDatetime !== null ){
    strTsv += response1Obj.processInstances[h].processInstanceEndDatetime.slice(0,19).replace('T',' ') + '\t';
  }else{ strTsv += '\t'; }

  Object.keys(response1Obj.processInstances[h].data).forEach(function(key){
    if( response1Obj.processInstances[h].data[key].dataType === "STRING" || 
        response1Obj.processInstances[h].data[key].dataType === "DECIMAL" || 
        response1Obj.processInstances[h].data[key].dataType === "DATE" || 
        response1Obj.processInstances[h].data[key].dataType === "DATETIME" || 
        response1Obj.processInstances[h].data[key].dataType === "QUSER" || 
        response1Obj.processInstances[h].data[key].dataType === "QGROUP" || 
        response1Obj.processInstances[h].data[key].dataType === "DISCUSSION" ){
      if( response1Obj.processInstances[h].data[key].value !== null ){
        strTsv += response1Obj.processInstances[h].data[key].value
                        .replace( /\n/g, ' ' ).replace( /\t/g, ' ' );
      }
    }else if( response1Obj.processInstances[h].data[key].dataType === "SELECT" ){
      if( response1Obj.processInstances[h].data[key].value !== null ){
        for( let j = 0; j < response1Obj.processInstances[h].data[key].value.length; j++ ){
          strTsv += response1Obj.processInstances[h].data[key].value[j].display;
          if( j !== response1Obj.processInstances[h].data[key].value.length - 1 ){
            strTsv += " "; // (Multiple: Space-separated)
          }
        }
      }
    }else if( response1Obj.processInstances[h].data[key].dataType === "FILE2" ){
      if( response1Obj.processInstances[h].data[key].value !== null ){
        for( let j = 0; j < response1Obj.processInstances[h].data[key].value.length; j++ ){
          strTsv += response1Obj.processInstances[h].data[key].value[j].name;
          if( j !== response1Obj.processInstances[h].data[key].value.length - 1 ){
            strTsv += " "; // (Multiple: Space-separated)
          }
        }
      }
    }else{ // "LIST" is not supported
      strTsv += "N/A";
    }
    strTsv += "\t";
  });
  strTsv = strTsv.slice( 0, -1 ); // delete last "\t"

  if( h !== response1Obj.processInstances.length - 1 ){
    strTsv += "\n";
  }
}


//// == Data Updating / ワークフローデータへの代入 ==
engine.setData(   strPocketTsv,       strTsv );
if(               numPocketExtracted  !== null ){
  engine.setData( numPocketExtracted,  new java.math.BigDecimal( response1Obj.processInstances.length ) );
}
if(               numPocketFiltered   !== null ){
  engine.setData( numPocketFiltered,   new java.math.BigDecimal( response1Obj.count ) );
}


} //////// END "main()" /////////////////////////////////////////////////////////////////


/*
Notes-en:
- When the process reaches this automatated task, an extraction request is sent to the target workflow platform.
    - The target workflow platform is set by the URL. (REST API connection destination)
        - `https://example.questetra.net/`
    - If the target is the same platform, variable settings are also possible. (System variable)
        - `${var[applicationRoot]}`
- The output TSV text is stored as the simplest tab-delimited string.
    - The TAB code and line feed code in each data are converted to " " (space).
    - Double quotes are also preserved unescaped.
- Only "Display items" are stored in TSV according to the display settings of the filter.
    - However, regardless of the filter settings, the 8 properties are stored in the first column of TSV.
        - AppName, Category, Process ID, Title, Satus, Start User, Start Time, End Time
    - If you need to change the order or delete the column, put "TSV String, Convert" in the downstream.
        - https://support.questetra.com/addons/tsv-string-convert-2021/

APPENDIX-en
- If the number of processes of the filter extraction result is 1000 or more, the data after 1001 will not be stored.
    - Even if 1000 or less, there is a possibility of timeout when there are many data items.
    - To control the number of extraction results
        - Extract only the processes whose status status is `Running`
        - Extract only the date data of the previous month
            - After: `#today.addMonths(-1).getFirstTimeInMonth()`
            - Before: `#today.addMonths(-1).getLastTimeInMonth()`
- "HTTP Authorization Setting" is required for automatic communication (OAuth2 API connection).
    - Register "API Client" (OAuth2) in advance as the connection destination. (Requires System Admin privileges)
        - Scope
            - Read-only API Access
        - Redirect URL
            - `https://s.questetra.net/oauth2callback`
        - Make a __note__ of your registration details.
            - Client ID
            - Client Secret (Consumer Secret)
            - Authorization Endpoint URL
            - Token Endpoint URL
    - Set "HTTP Authorization Setting" in the App as client. (Select this setting name in Addon config)
        - Authorization Endpoint URL:
            - _(See your note above)_
            - Example: `https://example.questetra.net/oauth2/authorize`
        - Token Endpoint URL:
            - _(See your note above)_
            - Example: `https://example.questetra.net/oauth2/token`
        - Scope:
            - `read`
        - Client ID
            - _(See your note above)_
        - Client Secret:
            - _(See your note above)_
- Table type data is not supported for extraction.
    - If an unsupported type, "`N/A`" is output.
    - The file names are extracted for the File type. (Multiple: Space-separated)
    - The display strings of the selected options are extracted for the Select type. (Multiple: Space-separated)


Notes-ja:
- 案件が自動処理工程に到達した際、ターゲットとなるワークフロー基盤に対して抽出リクエストが投げられます。
    - ターゲットとなるワークフロー基盤は URL で設定します。(REST API 接続先)
        - `https://example.questetra.net/`
    - ターゲットとなるワークフロー基盤が同一基盤の場合、変数による設定が便利です。(システム変数)
        - `${var[applicationRoot]}`
- 出力TSVテキストは「もっともシンプルなタブ区切り文字列」として格納されます。
    - 各データ内のTABコードや改行コードは " " (半角スペース)に変換されます。
    - ダブルクオート文字も、エスケープされていない状態で保持されます。
- フィルタの表示設定に従って「表示データ」だけがTSVに格納されます。
    - ただし、フィルタの表示設定に関わらず、案件プロパティ(8項目)はTSV先頭列に格納されます。
        - アプリ名, カテゴリ, プロセスID, 件名, 状態, 開始ユーザ, 開始日時, 終了日時
        - AppName, Category, Process ID, Title, Satus, Start User, Start Time, End Time
    - 並び順を変更したい場合や特定列を削除したい場合は、下流工程に『TSV 文字列, 変換』を置くなどします。
        - https://support.questetra.com/ja/addons/tsv-string-convert-2021/

APPENDIX-ja:
- フィルタ抽出結果の案件数(プロセス数)が1000件以上ある場合、1001件以降のデータは格納されません。
    - 抽出結果が1000件以下となるケースでも、データ項目の数が多い場合など、タイムアウトする可能性があります。
    - 抽出結果の数をコントロールする工夫
        - 状態ステータスが `未終了` の案件(プロセス)に絞って抽出する
        - 案件内の日付データが前月のものに絞って抽出する
            - 以降/After: `#today.addMonths(-1).getFirstTimeInMonth()`
            - 以前/Before: `#today.addMonths(-1).getLastTimeInMonth()`
- 自動通信(OAuth2 API接続)のために "HTTP認証設定" が必要です。
    - あらかじめ接続先に、[APIクライアント](OAuth2)を登録します。(システム管理者権限が必要です)
        - スコープ(Scope)
            - データを取得する API へのアクセス / Read-only API Access
        - リダイレクトURL(Redirect URL)
            - `https://s.questetra.net/oauth2callback`
        - 登録内容を __メモ__ します。
            - クライアントID(Client ID)
            - クライアントシークレット(Consumer Secret)
            - 認可エンドポイントURL(Authorization Endpoint URL)
            - トークンエンドポイントURL(Token Endpoint URL)
    - 通信元であるワークフローアプリにて[HTTP認証設定]を設定します。(この設定名をAddonコンフィグで選択)
        - 認可エンドポイント URL:
            - (メモを参照)
            - 例: `https://example.questetra.net/oauth2/authorize`
        - トークンエンドポイント URL:
            - (メモを参照)
            - 例: `https://example.questetra.net/oauth2/token`
        - スコープ:
            - `read`
        - クライアントID
            - (メモを参照)
        - クライアントシークレット:
            - (メモを参照)
- テーブル型データ項目内のデータは抽出サポート外です。
    - サポート外のデータ型は "`N/A`" が出力されます
    - ファイル型を指定した場合、ファイル名が抽出されます(複数:スペース区切り)
    - 選択型を指定した場合、表示テキストが抽出されます(複数:スペース区切り)

*/

Download

2021-09-14 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/questetra-bpms-process-batch-extract-by-filter-as-tsv-2021/
The Add-on import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.

Notes

  • When the process reaches this automated task, an extraction request is sent to the target workflow platform.
    • The target workflow platform is set by the URL. (REST API connection destination)
      • https://example.questetra.net/
    • If the target is the same platform, variable settings are also possible. (System variable)
      • ${var[applicationRoot]}
  • The output TSV text is stored as the simplest tab-delimited string.
    • The TAB code and line feed code in each piece of data are converted to a single space.
    • Double quotes are also preserved unescaped.
  • Only data that is displayed according to the filter’s display settings is stored in the TSV. (Ascending order of data definition number)
    • However, regardless of the filter settings, the 8 properties are stored in the first column of TSV.
      • AppName, Category, Process ID, Title, Status, Start User, Start Time, End Time
    • If you need to change the order or delete a specific column, put “TSV String, Convert” downstream in the process..

Capture

Extracts process data that match the specified saved filter. Stores the extracted data list as a TSV string. The filter is specified by ReportID which must be saved in the target platform in advance.
Extracts process data that match the specified saved filter. Stores the extracted data list as a TSV string. The filter is specified by ReportID which must be saved in the target platform in advance.

Appendix

  • If the number of processes of the filter extraction result is 1000 or more, the data after 1001 will not be stored.
    • Even if 1000 or less, there is a possibility of timeout when there are many data items.
    • To control the number of extraction results:
      • Extract only the processes whose status status is Running
      • Extract only the date data of the previous month
        • After: #today.addMonths(-1).getFirstTimeInMonth()
        • Before: #today.addMonths(-1).getLastTimeInMonth()
  • HTTP Authorization Settings are required for automatic communication (OAuth2 API connection).
    • Register “API Client” in advance as the connection destination. (Requires System Admin privileges)
      • Scope
        • Read-only API Access
      • Redirect URL
        • https://s.questetra.net/oauth2callback
      • Make a note of your registration details.
        • Client ID
        • Client Secret (Consumer Secret)
        • Authorization Endpoint URL
        • Token Endpoint URL
    • Set “HTTP Authorization Setting” in the App as client. (Select this setting name in Addon config)
      • Authorization Endpoint URL:
        • (See your note above)
        • Example: https://example.questetra.net/oauth2/authorize
      • Token Endpoint URL:
        • (See your note above)
        • Example: https://example.questetra.net/oauth2/token
      • Scope:
        • read
      • Client ID
        • (See your note above)
      • Client Secret:
        • (See your note above)
  • Table type data is not supported for extraction.
    • If an unsupported type, “N/A” is output.
    • The file names are extracted for the File type. (Multiple: Space-separated)
    • The display strings of the selected options are extracted for the Select type. (Multiple: Space-separated)

See also

1 thought on “Questetra BPMS #Process: Batch Extract by Filter as TSV”

  1. Pingback: TSV String, Sum Numeric Column – Questetra Support

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d